using text boxes and combo boxes together

G

Guest

How do I get a list box to show me a specific set of records when using a
textbox and a combobox together to filter/query the main table? I can get
the list box to show me the records I'm after using just text boxes or just
comboboxes but not both together and that's what I really need on my form.

Any help would be realy appreciated

diddydi
 
G

Guest

Try this

Select *
From TableName
Where Field1 Like IIF(Forms![FormName]![TextBoxName] is null Or
Forms![FormName]![TextBoxName] = "","*", Forms![FormName]![TextBoxName]) And
Field2 Like IIF(Forms![FormName]![ComboName] is null Or
Forms![FormName]![ComboName] = "","*", Forms![FormName]![ComboName])
 
G

Guest

One more thing
On the after update event of the combo and the text box write the code

Me.ListBoxName.Requery

To refresh the list
 
G

Guest

Didn't work I'm afraid, no idea why. I'm sure there's a way of doing it but I
can't figure it out and I really am not sure what I'm doing in VBA, any other
ideas would be greatly appreciated

diddydi

Ofer said:
Try this

Select *
From TableName
Where Field1 Like IIF(Forms![FormName]![TextBoxName] is null Or
Forms![FormName]![TextBoxName] = "","*", Forms![FormName]![TextBoxName]) And
Field2 Like IIF(Forms![FormName]![ComboName] is null Or
Forms![FormName]![ComboName] = "","*", Forms![FormName]![ComboName])
--
I hope that helped
Good luck


diddydi said:
How do I get a list box to show me a specific set of records when using a
textbox and a combobox together to filter/query the main table? I can get
the list box to show me the records I'm after using just text boxes or just
comboboxes but not both together and that's what I really need on my form.

Any help would be realy appreciated

diddydi
 
G

Guest

Do you get an error message?
Or the list doesn't refresh?

Post the following
1. Row source of the list box
2. The name of the form
3. The name of the combo
4. The name of the text box
--
I hope that helped
Good luck


diddydi said:
Didn't work I'm afraid, no idea why. I'm sure there's a way of doing it but I
can't figure it out and I really am not sure what I'm doing in VBA, any other
ideas would be greatly appreciated

diddydi

Ofer said:
Try this

Select *
From TableName
Where Field1 Like IIF(Forms![FormName]![TextBoxName] is null Or
Forms![FormName]![TextBoxName] = "","*", Forms![FormName]![TextBoxName]) And
Field2 Like IIF(Forms![FormName]![ComboName] is null Or
Forms![FormName]![ComboName] = "","*", Forms![FormName]![ComboName])
--
I hope that helped
Good luck


diddydi said:
How do I get a list box to show me a specific set of records when using a
textbox and a combobox together to filter/query the main table? I can get
the list box to show me the records I'm after using just text boxes or just
comboboxes but not both together and that's what I really need on my form.

Any help would be realy appreciated

diddydi
 
G

Guest

The list doesn't refresh. When the form opens the list box has all of the
records from the underlying table showing and the column headers, after I
type something into the text box all the records and the column heads
dissappear, it's almost as though the lsit box can no longer find the table
it's getting the data from.

this is what's written in the Row Source for the listbox:-

SELECT * FROM [References]
WHERE Author Like IIF(Forms![TestForm]![txtAuthor] is null Or
Forms![TestForm]![txtAuthor] = "","*", Forms![TestForm]![txtAuthor]) And
Journal Like IIF(Forms![TestForm]![cboJournal] is null Or
Forms![TestForm]![cboJournal] = "","*", Forms![TestForm]![cboJournal]);

the form is called 'TestForm'

the combobox is cboJournal and the textbox is txtAuthor. The table the data
is coming from is called References and I've called the listbox lstResults.
The combobox gets it's data from the Journal table rather than the References
table so that could be one area where I'm going wrong but that doesn't
explain why the textbox doesn't work.

Thanks

Ofer said:
Do you get an error message?
Or the list doesn't refresh?

Post the following
1. Row source of the list box
2. The name of the form
3. The name of the combo
4. The name of the text box
--
I hope that helped
Good luck


diddydi said:
Didn't work I'm afraid, no idea why. I'm sure there's a way of doing it but I
can't figure it out and I really am not sure what I'm doing in VBA, any other
ideas would be greatly appreciated

diddydi

Ofer said:
Try this

Select *
From TableName
Where Field1 Like IIF(Forms![FormName]![TextBoxName] is null Or
Forms![FormName]![TextBoxName] = "","*", Forms![FormName]![TextBoxName]) And
Field2 Like IIF(Forms![FormName]![ComboName] is null Or
Forms![FormName]![ComboName] = "","*", Forms![FormName]![ComboName])
--
I hope that helped
Good luck


:

How do I get a list box to show me a specific set of records when using a
textbox and a combobox together to filter/query the main table? I can get
the list box to show me the records I'm after using just text boxes or just
comboboxes but not both together and that's what I really need on my form.

Any help would be realy appreciated

diddydi
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top