Query by form

[Liberti]

Thank you in advance.

I have been searching the forum with no answers. Here is the situation.

I am running a query based on 4 different criteria on my form entered by the user (Company Name, Business Title, First Name and/or Last Name).
The user can choose any of the four criteria to run the search. I am not sure if my code is correct for any of this.
When I try to run the report it still asks for parameters How do I pass the parameters to the report.

Private Sub cmdfind_Click()

Dim smsgbox As String
Dim QryFileSt As String
Dim QryJoin As String
Dim QrySt As String
Dim stDocName As String

If chkcompname And IsNull(txtcompname) Then
smsgbox = " You have chosen to restrict by company but " & _
"have not selected a company name. Either select a company or " & _
"uncheck the ""Company Name"" checkbox."
MsgBox smsgbox, vbOKOnly, "Company Name Error"
End If

If txtcompname = Null Then
GoTo Line1:
End If

If txtcompname = Not Null Then
QrySt = "txtcompname"
QryJoin = QrySt
QryFileSt = QryJoin

End If
Line1:
If chkbustitle And IsNull(cbobustitle) Then
smsgbox = " You have chosen to restrict by Business Title but " & _
"have not selected a Business Title. Either select a Business Title or " & _
"uncheck the ""Business Title"" checkbox."
MsgBox smsgbox, vbOKOnly, "Business Title Error"
End If

If cbobustitle = Null Then
GoTo Line2:
End If


If cbobustitle = Not Null Then
QrySt = "cbobustitle"
If QryJoin = Null Then
QryJoin = QrySt
QryFileSt = QryJoin
End If
If QryJoin = Not Null Then
QryJoin = QryFileSt & " " & QrySt
QryFileSt = QryJoin
End If

End If

Line2:
If chkfirstname And IsNull(txtFirstName) Then
smsgbox = " You have chosen to restrict by First Name but " & _
"have not selected a First Name. Either select a First Name or " & _
"uncheck the ""First Name"" checkbox."
MsgBox smsgbox, vbOKOnly, "First Name Error"
End If

If txtFirstName = Null Then
GoTo Line3:
End If

If txtFirstName = Not Null Then
QrySt = txtFirstName
If QryJoin = Null Then
QryJoin = QrySt
QryFileSt = QryJoin
End If

If QryJoin = Not Null Then
QryJoin = QryFileSt & " " & QrySt
QryFileSt = QryJoin
End If
End If

Line3:
If chklastname And IsNull(txtLastName) Then
smsgbox = " You have chosen to restrict by Last Name but " & _
"have not selected a Last Name. Either select a Last Name or " & _
"uncheck the ""Last Name"" checkbox."
MsgBox smsgbox, vbOKOnly, "Last Name Error"
End If
If txtLastName = Null Then
End If

If txtLastName = Not Null Then
QrySt = "txtlastname"
If QryJoin = Null Then
QryJoin = QrySt
QryFileSt = QryJoin
End If
If QryJoin = Not Null Then
QryJoin = QryFileSt & " " & QrySt
QryFileSt = QryJoin
End If
End If


'then

'SELECT * FROM Customers2;
'WHERE (((Customers2!CompanyName) = [Forms]![txtcompname]) And ((Customers2![First Name]) = [Forms]![txtFirstName]) And ((Customers2![Last Name]) = [Forms]![txtLastName]) And ((Customers2![Business Title]) = [Forms]![cbobustitle]))

End Sub

 

[The_Doc_Man]

There are several ways to skin this cat.

One way is to have an extra, hidden field that is a yes/no flag.

This only works in the single-user case, but if you are sharing the database with front-end & back-end, you could make the table local to the user rather than in the back-end.

Then do an elimination query that clears the flag for any entries that DON'T match the search criteria. Whatever is left over is your result set.

If this has to be shared with many users simultaneously active, you might have a tougher time of it. How many records are we talking about here? It makes a difference between having say a couple of hundred entries and a couple of hundred THOUSAND entries.

In the latter case, the answer isn't easy at all.

 

[Liberti]

There will only be one user. But there are approximately 1500 business license owners in the database.

Thank you for your response.

Can you explain the flag more or send me to a site that has an example.

Thank you,

Liberti

 

[Liberti]

Modified Code

Here is what I am trying to do now. Is this anywhere closer to what I was. Or am I just running in circles.

I made my old code comments so If it was correct I can still use it. I am getting an error code of incorrect query syntac (#3450)


Function BuildSQLString(strSQL As String) As Boolean

Dim StrSELECT As String
Dim StrFROM As String
Dim StrWHERE As String

StrSELECT = "customers2.* "

StrFROM = "customers2.*"
If chkcompname Then
StrWHERE = " company name = " & txtcompname
End If

If chkbustitle Then
StrWHERE = StrWHERE & " AND business title = " & cbobustitle
End If

If chkfirstname Then
StrWHERE = StrWHERE & " AND first name = " & txtFirstName
End If

If chklastname Then
StrWHERE = StrWHERE & " And last name = " & txtLastName
End If

strSQL = "SELECT " & StrSELECT
strSQL = strSQL & "FROM " & StrFROM
If StrWHERE <> " " Then strSQL = strSQL & "WHERE " & Mid$(StrWHERE, 6)

BuildSQLString = True

End Function