I'm sorry. I was adressing myself to hjozinovic, refering to the code he wrote. The had already consulted the link you gave me, but I find it very confusing, and am not able to translate it into my problem.
Hey PhilippeM:
I understand your frustration in learning VBA, I was there once myself. But it isn't really that hard once you learn a few basics.
The first thing that you want to understand is that when you're using a combo box to select which field you want to search on, this in itself is confusing to the user, but not so much for the developer. I've found this to be very true.
What I would suggest, and it will also make your code a bit more involved, but also a great deal flexible in what you can search on.
First thing I would do, is to create a unbonded control for each item that you're wanting to search on. For example:
You have the following fields in your table:
-
CustomerID, FirstName, LastName, Address, City, State, Zipcode, Phone
-
And you want to be able to search by say Firstname, LastName, State, and Zipcode
You would then create unbonded controls on your form that would be named:
FirstName, LastName, State, and ZipCode
Now here is what you need to keep in mind. When referring to controls on a form. in the VBA code module that is for the form, you can refer to the control by the ME. reference. for Example:
The other thing you're going to do is to create a button on the form that will be named SearchBtn. The Caption will be set to "Search"
So when you have that completed you will have a form that has a Fields for each criteria item that you want to be able to search on.
The last step before we start writing any code is, to create an area that will list the results of the search. For me, I've always enjoyed using the ListControl that comes with MS Access. So at this point create a List Control on the form and name it "ResultsList"
Now a few explainations about the list control. If you have already worked with the Combo Box control, then you have a real good idea here of how the list control works as well. You simply are going to be providing the SQL Statement that is going to go into the RowSource property of the list control. This is what the VBA code that we're going to write is going to populate.
The other settings of the control can either be set on the control itself, or we can do it through VBA code. I'll show you how to control that through VBA.
So now that you have the List control on the form and have it placed where you want to result window to be, you'll have a better idea as to how the look of the form is going to look like.
Ok. Now let's get into the meet and potatoes of the operation the VBA code.
So the first thing we're going to do is right-click on the Search button you created on the form, and go to it's properties.
Scroll down until you see the On Click event box. once you are there go ahead and click in the box. you will then see a button on the right end of that field that will display ... at the end. Click on the ... button it will ask you what you want to write. Select "Code Builder" from the list and click ok.
You will then be taken to another screen that will allow you to script out what you're wanting this button to do when it is clicked.
Paste the following code in between the Private Sub SearchBtn_Click() and the End Sub statements
-
Dim strSQL As String
-
Dim Criteria As String
-
-
strSQL = "SELECT CustomerID as ID, FirstName, LastName, City, State, ZipCode, Phone " & _
-
"FROM Customers WHERE "
-
If IsNull(Me.FirstName) And _
-
IsNull(Me.LastName) And _
-
IsNull(Me.State) And _
-
IsNull(Me.ZipCode) Then
-
MsgBox "Must Enter at least one value in " & _
-
"order to search database.", vbOKOnly
-
Else
-
If Not IsNull(Me.FirstName) Then
-
If Len(Criteria) > 0 Then
-
Criteria = Criteria & " AND FirstName = '" & Me.FirstName & "'"
-
Else
-
Criteria = Criteria & "FirstName = '" & Me.FirstName & "'"
-
End If
-
End If
-
If Not IsNull(Me.LastName) Then
-
If Len(Criteria) > 0 Then
-
Criteria = Criteria & " AND LastName = '" & Me.LastName & "'"
-
Else
-
Criteria = Criteria & "LastName = '" & Me.LastName & "'"
-
End If
-
End If
-
If Not IsNull(Me.State) Then
-
If Len(Criteria) > 0 Then
-
Criteria = Criteria & " AND State = '" & Me.State & "'"
-
Else
-
Criteria = Criteria & "State = '" & Me.State & "'"
-
End If
-
End If
-
If Not IsNull(Me.ZipCode) Then
-
If Len(Criteria) > 0 Then
-
Criteria = Criteria & " AND ZipCode = '" & Me.ZipCode & "'"
-
Else
-
Criteria = Criteria & "Zipcode = '" & Me.ZipCode & "'"
-
End If
-
End If
-
-
strSQL = strSQL & Criteria
-
Me.ResultList.ColumnCount = 7
-
Me.ResultList.BoundColumn = 1
-
Me.ResultList.ColumnHeads = True
-
Me.ResultList.ColumnWidths = "720;1440;1440;720;720;720;720"
-
Me.ResultList.RowSourceType = "Table/Query"
-
Me.ResultList.RowSource = strSQL
-
Me.ResultList.Requery
-
End If
-
-
If you take a look at the code, you will notice that I've put at the top of the code an if statement that checks to see if any of the fields have a value, if so, then it will build the Criteria string that will get combine with the SELECT statement string that is defined in strSQL.
Then I check each field and build the Criteria section one field at a time. once that has completed, I set the values for the control, and populate the RowSource property with the resulting strSQL Variable that now has the SQL String that will be put into the RowSource property.
When I execute me.resultlist.requery it runs the sql statement and populate the form with what the SQL statement says.
I've went ahead and attached an example of what I've just written, in hopes it may answer some more questions that you may have.
If you need more help, let me know.
Joe P.
15
