Nope, doesn't work unfortunately.
My code for the form with the combo boxes looks like this:
Option Compare Database
Option Explicit
Private Const strSQL1 = "SELECT ID, Author, Title, Location, Location_2 " &
"FROM qryCombo1 WHERE "
Private strSQL As String
Private strisitor As String
Private firstcriteriaset As Boolean
Private allempty As Boolean
Private Sub Filllist()
firstcriteriaset = False
allempty = True
'If (cboJournal.Value = "" Or cboJournal.Value = Null) Or (cboSubject.Value
= "" Or cboSubject.Value = Null) Then
'allows one or multiple factors to apply to a search using combo boxes
If cboAndOr = "Or" Then
strisitor = " or "
Else
strisitor = " and "
End If
If cboJournal.Value <> "" Then
strSQL = strSQL1 & "Journal = '" & Me!cboJournal.Value & "'"
firstcriteriaset = True
allempty = False
End If
If cboSubject.Value <> "" Then
If firstcriteriaset = False Then
firstcriteriaset = True
strSQL = strSQL1 & "Subject = '" & Me!cboSubject.Value & "'"
allempty = False
Else
strSQL = strSQL & strisitor & "Subject = '" & Me!cboSubject.Value &
"'"
End If
End If
If cboGeogArea.Value <> "" Then
If firstcriteriaset = False Then
firstcriteriaset = True
strSQL = strSQL1 & "Geographical_Area = '" & Me!cboGeogArea.Value &
"'"
allempty = False
Else
strSQL = strSQL & strisitor & "Geographical_Area = '" &
Me!cboGeogArea.Value & "'"
End If
End If
If cboTaxGroup.Value <> "" Then
If firstcriteriaset = False Then
firstcriteriaset = True
strSQL = strSQL1 & "Taxonomic_Group = '" & Me!cboTaxGroup.Value & "'"
allempty = False
Else
strSQL = strSQL & strisitor & "Taxonomic_Group = '" &
Me!cboTaxGroup.Value & "'"
End If
End If
If cboSeries.Value <> "" Then
If firstcriteriaset = False Then
firstcriteriaset = True
strSQL = strSQL1 & "Series = '" & Me!cboSeries.Value & "'"
allempty = False
Else
strSQL = strSQL & strisitor & "Series = '" & Me!cboSeries.Value & "'"
End If
End If
'If all search criteria is empty then clear the listbox
If allempty = True Then
strSQL = "SELECT ID, Author, Title, Location, Location_2 FROM qryCombo1
WHERE ID = -9999"
End If
And then for each combo box on the form I've got it calling the filllist
Private Sub on the AfterUpdate event. This works perfectly but I cannot get
it to work with text boxes, I'm assuming because it was written with comb
boxes in mind. The list box on this form has the following in the Row Source:
SELECT [qryCombo1].[ID], [qryCombo1].[Author], [qryCombo1].[Title],
[qryCombo1].[Location], [qryCombo1].[Location_2] FROM [qryCombo1] ORDER BY
[Author], [Title];
I really want to be able to type an Author name into a text box and have the
records for that author appear in the listbox below it, I've so far only been
able to do this by referring the Row Source property of the list box to
select specific fields from my References table where the author value equals
the value of the txtAuthor box on a form. I can't seem to get the two to
work together on this and I am really not that good at code, the bit's I do
have came from an Access 'how to' book that I no longer have and were added
to by a colleague who has unfortunately left and is out of touch.
Any help really appreciated, and any questions, please ask, I'll see if I
know the answer
Arvin Meyer said:
Your Where clause should look something like this:
WHERE (((Field1) Like "*" & [Forms]![frmSearch]![Find1] & "*" And (Field2)
Like "*" & [Forms]![frmSearch]![Find2] & "*" And (Field3) Like "*" &
[Forms]![frmSearch]![Find3] & "*" And (Field4) Like "*" &
[Forms]![frmSearch]![Find4] & "*" And (Field5) Like "*" &
[Forms]![frmSearch]![Find5] & "*"));
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
diddydi said:
I have a form on which my users can choose various values from 5 drop down
combo boxes to run a filter/query on a table of records. The information is
then shown in a listbox beneath the combo boxes. This great, it's what is
needed. I have a second form which has a text box on it which also fills a
list box with the same data from the same table as the first form. Also
great.
What I want to be able to do is have my 5 combo boxes and at least 3 text
boxes on the same form filtering the same table and showing the data in one
list box on said form. I cannot figure out how to get the two things working
together. I need it to run so that if some of the fields are empty it
doesn't matter, they're allowed to be, but I also need it to work so that if
there are two values selected only records with both of those values in those
fields are shown.
Can anybody help? I've tried query by forms, my combo box form was written
using a help book I no longer have (from which I just adapted the code that
was written) and I really am going nuts trying to figure this out.
