Select field you want to search, select value, then Search

[lilanngel]

Hi,

The goal of my project is to create 2 combo boxes and one "button" that enables the user to search my database.

The first combo box (called FieldCBO) lists the field names: Eg. "Last Name, First Name, Address" fields from the "CONTACTS" table.

The second combo box (called ValueCBO) is bounded to the first combo box so that if you select the "Last Name" field, everyone's last name will show up (e.g. Smith, Doe, etc), if you select the "First Name" field, everyone's first names will show up (e.g. John, Jane, etc)...

Unfortunately, I can't get this second combo box. I believe something is wrong with my "Row Source: SELECT Forms![Search Form]!FieldCBO FROM CONTACTS" because an error message stating "The Access database engine does not recognize 'Forms![Search Form]!FieldCBO' as a valid field name or expression



What should I put in place of "Forms![Search Form]!FieldCBO"?

Thank you for your help!!

[SoftwareMatters]

I think you need something like this:
SELECT Contacts.LastName FROM CONTACTS WHERE Contacts.LastName=[Forms![Search Form]!FieldCBO;

NB: Replace LastName with the correct field name in your database.

You will also need to requery the 1st control after each selection.

[lilanngel]

I think you need something like this:
SELECT Contacts.LastName FROM CONTACTS WHERE Contacts.LastName=[Forms![Search Form]!FieldCBO;

NB: Replace LastName with the correct field name in your database.

You will also need to requery the 1st control after each selection.

Thanks, JD!

The FieldCBO is actually a list of "fields" in the CONTACTS table... so I don't think I want to set the Contacts.LastName to equal it, right? (Sorry if I'm incorrect).

I want to base the value list in the second combo box on the field selection from the first combo box.

E.g. if the person selects Last Name from FieldCBO, the ValueCBO automatically populates with everyone's last name

if the person selects First Name from FieldCBO, everyone's first names will show up.

But I'm not sure how to do this..

[SoftwareMatters]

Sorry I misunderstood but now I get it!

This should be easy to achieve using code.

You could set the Rowsource of your 2nd combo to contain all the required fields and then on the AfterUpdate of the first combo set the ColumnWidths to show the appropriate field. Below is an example of waht I mean:

 
Private Sub FieldCBO_AfterUpdate()
Me!ValueCBO = Null
Select Case Me!FieldCBO
    Case 1
        Me!ValueCBO.ColumnWidths = "6 cm;0;0"
    Case 2
        Me!ValueCBO.ColumnWidths = "0;6 cm;0"
    Case 3
        Me!ValueCBO.ColumnWidths = "0;0;6 cm"
End Select
Me!ValueCBO.Requery
End Sub

This is just an example but hopefully you get the idea.

Another method would be to set the rowsource depending on the selection.

[haquocquan]

I think you can not do it. Because: at a time, combo gets value in one field.
If you add options on form, you will do it.