Combobox / Listbox Assistance.

[AccessFreak via AccessMonster.com]

Need some help on how I can make things work.

Table (Employee): NameID, Name, City, State

Form (Name): Does not initially pull any data.
Combo44: Default Value = "Name" (With Name, City, State options)
Text50: No default value
Listbox50: Initially runs query:
SELECT Employee.Name ID, Employee.Name, Employee.City, Employee.State FROM
[Key] WHERE (((Employee.Name) Like "*" & [Text50].[Text] & "*"));

Ok... here's the deal. I need to be able to use the dropdown box to select
between Name, City, and State and then be able to use Text50 to supply the
parameter for the listbox query. However, when I change the dropdown box to
something new, I need the listbox's query criteria to shift as well to search
in the specified field.

I've tried using the Visible (yes/no) properties with three different
listboxes and adding the repaint method, but nothing seems to pan out. The
easiest method would be to make three different forms with the three
listboxes as needed. Unfortunately, I need to keep everything on one form.

Thanks for your assistance in advance!!

 

[Douglas J. Steele]

Unfortunately, you really need to rely on the two values changing in order
to update your listbox: if you change the value in Combo44, presumably you
also need to change the value in Text50 in order for things to make sense.
That means it might be best to have a command button that the user has to
click on once they've changed both.

Assuming Combo44 is returning one of Name, City or State, you need code
like:

Dim strSQL As String

strSQL = "SELECT [Name ID], [Name], City, State " & _
"FROM [Key] " & _
"WHERE [" & Nz(Me.Combo44, "Name") & "] LIKE '*" & [Text50].[Text] &
"*'"

Me.Listbox50.RowSource = strSQL

(You may also need a Me.Listbox50.Requery after you reset its RowSource, but
I don't think you will)

If you write the above as a separate sub in your form, you could call it in
the AfterUpdate event of both Combo44 and Text50, but as I point out above,
that may not make sense: if you have Smith in Text50, and change Combo44 to
State, it really won't make sense to look for those employees from the state
of Smith!

One comment: Name isn't a particularly good choice for a field name. It's a
reserved word in Access, and using it for your own purposes can lead to
problems. That's the reason I included the square brackets when building the
SQL string.

 

[AccessFreak via AccessMonster.com]

That's kind of what I figured. It was worth a shot, however. As far as
"Name" is concerned, I don't use it. It was just for illustration purposes,
but thanks for mentioning it in case someone reads this post who doesn't!
Thanks!!!