combobox search system

[slames]

I've looked at lots of other threads and faq and am still not sure of how to do this. I need to implement a simple search system.
The user must choose their criteria by which to search either ID, oldID or City. These are currently in a combobox. The second combobox, I then want to be populated with all possible entries dependent on first choice. So if they pick city I want the combobox to list all cities, and if they pick ID I want it to list all of the ID in the system. I don't really know if this is possible, I know you can base the contents of a combobox on a previous combobox if you know what field the user is picking from, but not how to specify the field from which to select.
Once the user has e.g selected city from the first, and melbourne from the next I want the third combobox to display all corresponding records i.e. where city = Melbourne.
If this made sense to you, is it possible, or do I need to implement 3 different sets of comboboxes to achieve this?

Thankyou
Steph

 

[Namlop]

Are the fieldnames in you table the same as the names you show in your first combobox?

If so:

ComboBox2.RowSourceType = "Table/query"
ComboBox2.RowSource = "SELECT " & ComboBox1.Value & "FROM MyTable"

ComboBox3.RowSourceType = "Table/query"
ComboBox3.RowSource = "SELECT * FROM WHERE " & ComboBox1.Value & " = " & ComboBox2.Value I love work.
I can just sit and look at it for hours.

 

[Namlop]

Oeps. I've been a bit sloppy
It should be

ComboBox3.RowSource = "SELECT * FROM MyTable WHERE " & ComboBox1.Value & " = " & ComboBox2.Value

And there's a restriction to this solution. All the fields have to be of the same type.

(If the type is text it should be like this:
ComboBox3.RowSource = "SELECT * FROM MyTable WHERE " & ComboBox1.Value & " = " & Chr$(34) & ComboBox2.Value & Chr$(34) )
I love work.
I can just sit and look at it for hours.

 

[slames]

So if my initial combobox consists of city, ID, oldID, where ids are numerical and city is text this option wont work?

 

[Namlop]

Unfortunatly not.

But how do you fill your first combobox? Manually? I love work.
I can just sit and look at it for hours.

 

[slames]

Yes manually.

 

[Namlop]

So that means you know which field has what type, right?

So you can make an if statement. Something like this

if ComboBox1.Value <> &quot;City&quot; then
ComboBox3.RowSource = &quot;SELECT * FROM MyTable WHERE &quot; & ComboBox1.Value & &quot; = &quot; & ComboBox2.Value
else
ComboBox3.RowSource = &quot;SELECT * FROM MyTable WHERE &quot; & ComboBox1.Value & &quot; = &quot; & Chr$(34) & ComboBox2.Value & Chr$(34)
endif


There are probably better ways to solve this problem, but this is the only solution i've got so far I love work.
I can just sit and look at it for hours.

 

[slames]

Thanks for all your help, I'll try that and see what happens.
Steph

 

[Namlop]

I've just gave it a try.

Private Sub ComboBox0_AfterUpdate()
ComboBox2.RowSource = &quot;Select &quot; & ComboBox0.Value & &quot; From tbl_MyTable&quot;
ComboBox2.Value = ComboBox2.ItemData(0)
End Sub

Private Sub ComboBox2_AfterUpdate()
If ComboBox0.Value = &quot;City&quot; Then
ComboBox4.RowSource = &quot;Select * from tbl_MyTable Where &quot; & ComboBox0.Value & &quot; = &quot; & Chr$(34) & ComboBox2.Value & Chr$(34)
Else
ComboBox4.RowSource = &quot;Select * from tbl_MyTable Where &quot; & ComboBox0.Value & &quot; = &quot; & ComboBox2.Value
End If
ComboBox4.Value = ComboBox4.ItemData(0)
End Sub


It works, but only if you know the which fields have type text I love work.
I can just sit and look at it for hours.

 

[RustyDWO]

Steph

I have 3 comboboxes that create a selection on the previous combo.
I have the RowSource query as follows.

The first combo is simple:
SELECT DISTINCT MYtable.Field1 FROM MYtable;

The second and third are:
SELECT DISTINCT MYtable.Field2 FROM MYtable WHERE (((MYtable.Field1)=[Forms]![Formname]![Combo1].[value]));

Be sure to set the query to unique in the query properties.

In order to reset the next combo search when changing a previous, you put in AfterUpdate_event the following:
Private Sub Combo1_AfterUpdate()
Me!Combo2.Requery
End Sub

 

[slames]

namlop, I had a go using just the first two comboboxes, I had it working once and now it doesn't. I must have changed something, all I get when I bring down the second combobox is a blank field...what have I done? Here's my code - pretty much the same as yours.

Private Sub cbx1_AfterUpdate()
Dim first As Variant
first = cbx1.Value
Combo30.RowSource = &quot;select &quot; & first & &quot; From tblSubscriber&quot;
Combo30.Value = Combo30.ItemData(0)

End Sub

 

[Namlop]

The code is fine.

Check if the names in your combobox are exactly the same as the fieldnames of your table.

Have you tried selecting every value from your first combobox? I love work.
I can just sit and look at it for hours.