Searching Through ComboBoxes

[Kgwill85]

It's a text, but most of the entries will be numeric. Only reason
that it is text is because an ID number might not be assigned to it
and "TBD" will be there instead of a number in that case.- Hide quoted text -

- Show quoted text -

If even one entry it text, then it is a text field and single quotes
must be used.
strWhere = "PARID = ' " & Me.PARID & " ' "
If it were an actual numeric field, it would look like this:
strWhere = "PARID = " & Me.PARID & ""
I see another possible problem: Programmers use a special naming
convention to keep the computer from misunderstanding - i.e. mistaking
a field for a memory variable, or a variable for a screen control. I
recommend prefacing screen controls with their type; txtParid for
textbox, cmbParid for combobox, lblParid for label, etc. This is a
standard namming convention. Do the same for memory variables, only
preface with something that will identify their type; iCount for
integer, dCount for double, cCount for currency, sCount for string,
etc. Some conventions are more elaborate, but this has worked well
enough for me. The field name can be left unaltered - if it is
unaltered, then we know it is a field name. Encase it in brackets
anyway - field names can include spaces and field names with spaces
must be bracketed. Use bracket even if there are no spaces; it will
immediatedly be obvious that it is a field name: [PARID]- Hide quoted text -

- Show quoted text -

Okay, I understand why that would be beneficial.

However my code for that strWhere was exactly how you have it and it's
still not working. I posted it a couple of replys up also.

 

[Rick A.B.]

Don't mean to butt in here, Dave has much more experience than I do,
but if it where me I would forget about the search form and just place
a single combobox on my main form. Use the Combobox wizzard to create
a lookup combobox. It sounds as though PARID and BO_Project_Name are
in the same table. Use them both in the combo lookup. If PARID is
not the PrimaryKey also include the Primary Key of the record, it
should show them side by side so if they don't know the PARID they can
just look for the BO_Project_Name, the wizzard will give you the
option of hidding the PrimaryKey. Then in the afterUpdate event of
the combo box populate the form. In the below code I named my
combobox cmbQuickFind

Private Sub cmbQuickFind_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PrimaryKeyField] = " & str(Nz(Me![cmbQuickFind],
0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I use this all the time to quickly find records and move to that
record. Feel free to ignore if this isn't what you had in mind.

 

[Klatuu]

That would not be applicable in this case.
Another approach you may consider is to get rid of the search command button
and put the code in the After Update event of each combo:

Private Sub PARID_AfterUpdate()
If Not IsNull(Me.PARID) Then
strWhere = "PARID = '" & Me.PARID & "'"
DoCmd.OpenForm stDocName, , , "PARID = '" & Me.PARID & "'"
End If
End Sub

Private Sub BO_Project_Name_After_UpDate()
If Not IsNUll(Me.BO_Project_Name) Then
DoCmd.OpenForm stDocName, , , "BO_Project_Name = '" &
Me.BO_Project_Name & "'"
End If
End Sub

If you use this and still get nothing, then there is some other problem.

 

[Kgwill85]

That would not be applicable in this case.
Another approach you may consider is to get rid of the search command button
and put the code in the After Update event of each combo:

Private Sub PARID_AfterUpdate()
If Not IsNull(Me.PARID) Then
strWhere = "PARID = '" & Me.PARID & "'"
DoCmd.OpenForm stDocName, , , "PARID = '" & Me.PARID & "'"
End If
End Sub

Private Sub BO_Project_Name_After_UpDate()
If Not IsNUll(Me.BO_Project_Name) Then
DoCmd.OpenForm stDocName, , , "BO_Project_Name = '" &
Me.BO_Project_Name & "'"
End If
End Sub

If you use this and still get nothing, then there is some other problem.

--
Dave Hargis, Microsoft Access MVP





...

read more »- Hide quoted text -

- Show quoted text -

I'm about to see what happens with this. I'll post my results shortly.

 

[Kgwill85]

Okay now I went back to OldPro's code. But I have a question about
the DAO thing. What do I have to do to make sure it's a reference?

1) Make sure there is a reference to DAO.

I ran the code and I got a compile error that read user-defined type
not defined

 

[Kgwill85]

That would not be applicable in this case.
Another approach you may consider is to get rid of the search command button
and put the code in the After Update event of each combo:

Private Sub PARID_AfterUpdate()
If Not IsNull(Me.PARID) Then
strWhere = "PARID = '" & Me.PARID & "'"
DoCmd.OpenForm stDocName, , , "PARID = '" & Me.PARID & "'"
End If
End Sub

Private Sub BO_Project_Name_After_UpDate()
If Not IsNUll(Me.BO_Project_Name) Then
DoCmd.OpenForm stDocName, , , "BO_Project_Name = '" &
Me.BO_Project_Name & "'"
End If
End Sub

If you use this and still get nothing, then there is some other problem.

--
Dave Hargis, Microsoft Access MVP





...

read more »- Hide quoted text -

- Show quoted text -

I tried this as well and it brought up a blank form. Exactly what the
search button was doing.

 

[Kgwill85]

I also tried the AfterUpdate codes and the same exact result happened.

 

[Douglas J. Steele]

While in the VB Editor, select Tools | References from the menu bar. Scroll
through the list of available references until you find the one for
Microsoft DAO 3.6 Object Libary, select it (by checking the box to its
left), then click on OK to close the dialog.

 

[Kgwill85]

Okay, thanks. The code is running but the form is still blank. This
is the code I have currently.

Private Sub Search_Click()
stDocName = "Project_Inventory"
Dim db As DAO.database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.openrecordset("Select * From Project_Inventory")

If cmbPARID <> "" And cmbBO_Project_Name <> "" Then
rs.findfirst "[ID]=' " & cmbPARID & " ' AND [BO_Project_Name]= ' "
& cmbBO_Project_Name & " ' "

ElseIf cmbPARID <> "" Then
rs.findfirst "[ID]=' " & cmbPARID & " ' "

ElseIf cmbBO_Project_Name <> "" Then
rs.findfirst [BO_Project_Name] = " & cmbBO_Project_Name & " ' "

Else
MsgBox "Please select either a Project Name or a Project ID."

End If

If Not rs.nomatch Then
' This is where you add the code to create the resultset

Else
MsgBox "No record was found matching this criteria!"
End If

DoCmd.OpenForm stDocName, , , strWhere

End Sub

 

[Douglas J. Steele]

Since a combo box with nothing selected is Null, not an empty string (""),
try replacing the 4 comparisons that are like this:

combo <> ""

with:

IsNull(combo) = False

or

Len(combo & vbNullString) > 0

Also, the spaces around the single quotes were only there to make them
obvious. Your code should look like:

Private Sub Search_Click()
stDocName = "Project_Inventory"
Dim db As DAO.database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.openrecordset("Select * From Project_Inventory")

If Len(cmbPARID & vbNullString) > 0 And _
Len(cmbBO_Project_Name & vbNullString) > 0 Then
rs.findfirst "[ID]='" & cmbPARID & "' AND " & _
[BO_Project_Name]= '" & cmbBO_Project_Name & "'"
ElseIf Len(cmbPARID & vbNullString) > 0 Then
rs.findfirst "[ID]='" & cmbPARID & "'"
ElseIf Len(cmbBO_Project_Name & vbNullString) > 0 Then
rs.findfirst [BO_Project_Name] = '" & cmbBO_Project_Name & "'"
Else
MsgBox "Please select either a Project Name or a Project ID."
Exit Sub
End If

If Not rs.nomatch Then
' This is where you add the code to create the resultset
Else
MsgBox "No record was found matching this criteria!"
End If

DoCmd.OpenForm stDocName, , , strWhere

End Sub

I don't understand, though, why you're using FindFirst. Only have the
recordset return the relevant data:

Private Sub Search_Click()
stDocName = "Project_Inventory"
Dim db As DAO.database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strWhere As String

strSQL = "Select * From Project_Inventory"
If Len(cmbPARID & vbNullString) > 0 Then
strWhere = strWhere & "[ID]='" & cmbPARID & "' AND "
End If

If Len(cmbBO_Project_Name & vbNullString) > 0 Then
strWhere = strWhere & "[BO_Project_Name] = '" & _
cmbBO_Project_Name & "'"
End If

If Len(strWhere) = 0 Then
MsgBox "Please select either a Project Name or a Project ID."
Else
strWhere = Left$(strWhere, Len(strWhere) - 5)
Set db = CurrentDb()
Set rs = db.openrecordset(strSQL & " Where " & strWhere)
If rs.BOF = False and rs.EOF = False Then
MsgBox "No record was found matching this criteria!"
Else
' This is where you add the code to create the resultset
End If

DoCmd.OpenForm stDocName

End Sub

Of course, that will always open the Project_Inventory form, whether or not
anything was found. Sounds to me as though maybe all you need to do is open
the form with the Where clause above, and put logic in it to determine
whether there's anything to display on the form:

If Len(strWhere) = 0 Then
MsgBox "Please select either a Project Name or a Project ID."
Else
strWhere = Left$(strWhere, Len(strWhere) - 5)
DoCmd.OpenForm stDocName, , , strWhere
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Okay, thanks. The code is running but the form is still blank. This
is the code I have currently.

Private Sub Search_Click()
stDocName = "Project_Inventory"
Dim db As DAO.database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.openrecordset("Select * From Project_Inventory")

If cmbPARID <> "" And cmbBO_Project_Name <> "" Then
rs.findfirst "[ID]=' " & cmbPARID & " ' AND [BO_Project_Name]= ' "
& cmbBO_Project_Name & " ' "

ElseIf cmbPARID <> "" Then
rs.findfirst "[ID]=' " & cmbPARID & " ' "

ElseIf cmbBO_Project_Name <> "" Then
rs.findfirst [BO_Project_Name] = " & cmbBO_Project_Name & " ' "

Else
MsgBox "Please select either a Project Name or a Project ID."

End If

If Not rs.nomatch Then
' This is where you add the code to create the resultset

Else
MsgBox "No record was found matching this criteria!"
End If

DoCmd.OpenForm stDocName, , , strWhere

End Sub

 

[OldPro]

Okay now I went back toOldPro'scode. But I have a question about
the DAO thing. What do I have to do to make sure it's a reference?

1) Make sure there is a reference to DAO.

I ran the code and I got a compile error that read user-defined type
not defined

When you are in the mode where you actually edit a form's code, there
is a different menu - one of the menu items will allow you to setup
your references. Set a reference to DAO 3.6, if you have it.
When you receive an error message, there should be a debug command.
Click on debug and it will show you the exact line and word where the
error occurs. It could just be a spelling error.

 

[mcescher]

Okay now I went back to OldPro's code. But I have a question about
the DAO thing. What do I have to do to make sure it's a reference?

1) Make sure there is a reference to DAO.

I ran the code and I got a compile error that read user-defined type
not defined

While your in the VB window, choose the Tools menu, and then choose
References...

A window will pop up, and you need to scroll down to Microsoft DAO 3.6
Object Library, and put a checkmark in the box. Click OK, and that
should fix that.

HTH,
Chris M.

 

[OldPro]

Okay, thanks. The code is running but the form is still blank. This
is the code I have currently.

Private Sub Search_Click()
stDocName = "Project_Inventory"
Dim db As DAO.database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.openrecordset("Select * From Project_Inventory")

If cmbPARID <> "" And cmbBO_Project_Name <> "" Then
rs.findfirst "[ID]=' " & cmbPARID & " ' AND [BO_Project_Name]= ' "
& cmbBO_Project_Name & " ' "

ElseIf cmbPARID <> "" Then
rs.findfirst "[ID]=' " & cmbPARID & " ' "

ElseIf cmbBO_Project_Name <> "" Then
rs.findfirst [BO_Project_Name] = " & cmbBO_Project_Name & " ' "

Else
MsgBox "Please select either a Project Name or a Project ID."

End If

If Not rs.nomatch Then
' This is where you add the code to create the resultset

Else
MsgBox "No record was found matching this criteria!"
End If

DoCmd.OpenForm stDocName, , , strWhere

End Sub

When a line is preceeded by a quote, it is a comment. That is to say
it is just a note to the programmer. When I put
" ' This is where you add the code to create the resultset "
it was a note to you that you had to add something here. Either
insert the code to fill a listbox, or call a form with the filter
string as others have suggested.

 

[Klatuu]

There is something else wrong here. Try doing a DLookup from the immediate
window to see if you get a value returned. Also, are both fields in the
record source of the form you are opening?

 

[Kgwill85]

Since a combo box with nothing selected is Null, not an empty string (""),
try replacing the 4 comparisons that are like this:

combo <> ""

with:

IsNull(combo) = False

or

Len(combo & vbNullString) > 0

Also, the spaces around the single quotes were only there to make them
obvious. Your code should look like:

Private Sub Search_Click()
stDocName = "Project_Inventory"
Dim db As DAO.database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.openrecordset("Select * From Project_Inventory")

If Len(cmbPARID & vbNullString) > 0 And _
Len(cmbBO_Project_Name & vbNullString) > 0 Then
rs.findfirst "[ID]='" & cmbPARID & "' AND " & _
[BO_Project_Name]= '" & cmbBO_Project_Name & "'"
ElseIf Len(cmbPARID & vbNullString) > 0 Then
rs.findfirst "[ID]='" & cmbPARID & "'"
ElseIf Len(cmbBO_Project_Name & vbNullString) > 0 Then
rs.findfirst [BO_Project_Name] = '" & cmbBO_Project_Name & "'"
Else
MsgBox "Please select either a Project Name or a Project ID."
Exit Sub
End If

If Not rs.nomatch Then
' This is where you add the code to create the resultset
Else
MsgBox "No record was found matching this criteria!"
End If

DoCmd.OpenForm stDocName, , , strWhere

End Sub

I don't understand, though, why you're using FindFirst. Only have the
recordset return the relevant data:

Private Sub Search_Click()
stDocName = "Project_Inventory"
Dim db As DAO.database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strWhere As String

strSQL = "Select * From Project_Inventory"
If Len(cmbPARID & vbNullString) > 0 Then
strWhere = strWhere & "[ID]='" & cmbPARID & "' AND "
End If

If Len(cmbBO_Project_Name & vbNullString) > 0 Then
strWhere = strWhere & "[BO_Project_Name] = '" & _
cmbBO_Project_Name & "'"
End If

If Len(strWhere) = 0 Then
MsgBox "Please select either a Project Name or a Project ID."
Else
strWhere = Left$(strWhere, Len(strWhere) - 5)
Set db = CurrentDb()
Set rs = db.openrecordset(strSQL & " Where " & strWhere)
If rs.BOF = False and rs.EOF = False Then
MsgBox "No record was found matching this criteria!"
Else
' This is where you add the code to create the resultset
End If

DoCmd.OpenForm stDocName

End Sub

Of course, that will always open the Project_Inventory form, whether or not
anything was found. Sounds to me as though maybe all you need to do is open
the form with the Where clause above, and put logic in it to determine
whether there's anything to display on the form:

If Len(strWhere) = 0 Then
MsgBox "Please select either a Project Name or a Project ID."
Else
strWhere = Left$(strWhere, Len(strWhere) - 5)
DoCmd.OpenForm stDocName, , , strWhere
End If

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Okay, thanks. The code is running but the form is still blank. This
is the code I have currently.

Private Sub Search_Click()
stDocName = "Project_Inventory"
Dim db As DAO.database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.openrecordset("Select * From Project_Inventory")

If cmbPARID <> "" And cmbBO_Project_Name <> "" Then
rs.findfirst "[ID]=' " & cmbPARID & " ' AND [BO_Project_Name]= ' "
& cmbBO_Project_Name & " ' "

ElseIf cmbPARID <> "" Then
rs.findfirst "[ID]=' " & cmbPARID & " ' "

ElseIf cmbBO_Project_Name <> "" Then
rs.findfirst [BO_Project_Name] = " & cmbBO_Project_Name & " ' "

Else
MsgBox "Please select either a Project Name or a Project ID."

End If

If Not rs.nomatch Then
' This is where you add the code to create the resultset

Else
MsgBox "No record was found matching this criteria!"
End If

DoCmd.OpenForm stDocName, , , strWhere

End Sub- Hide quoted text -

- Show quoted text -

This code is giving me a compile error message when I run it. Can
someone point out exactly where in the code is it telling the form to
find and search for these records?

 

[Kgwill85]

There is something else wrong here. Try doing a DLookup from the immediate
window to see if you get a value returned. Also, are both fields in the
record source of the form you are opening?
--
Dave Hargis, Microsoft Access MVP





...

read more »- Hide quoted text -

- Show quoted text -

I'll try that right now, and yes both fields are in the record source
of the form that I am bring up.

What's fustrating is that I got this function to work only for the
PARID using a FindRecord macro. It was pretty straight forward, but I
couldn't get it to search the BO_Project_Name field using another
FindRecord macro, which is weird. Now I'm putting all these codes in
and I'm not pulling up any information when it attempts to run the
search. Go figure

 

[Kgwill85]

I just want to know how to write in a FindRecord function into the
code now. Where exactly do I put it at?

Here's what I have done today and I just need figure out why it's not
searching the fields that I tell it to.

Else
MsgBox "No record was found matching this criteria!"
End If

DoCmd.FindRecord PARID Or BO_Project_Name
DoCmd.OpenForm stDocName, , , strWhere

End Sub

 

[OldPro]

I just want to know how to write in a FindRecord function into the
code now. Where exactly do I put it at?

Here's what I have done today and I just need figure out why it's not
searching the fields that I tell it to.

Else
MsgBox "No record was found matching this criteria!"
End If

DoCmd.FindRecord PARID Or BO_Project_Name
DoCmd.OpenForm stDocName, , , strWhere

End Sub

Assuming your popup form is named Project_Inventory, and your combo
boxes are prefaced with "cmb", then the following code should work...
Note that I have found several mistakes in your (and others) posted
code and have tried to correct them here...

Your code should look more like this:

Private Sub Search_Click()
dim stDocName as string
Dim db As DAO.database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.openrecordset("Select * From
Project_Inventory",dbOpenDynaset)


If cmbPARID <> "" And cmbBO_Project_Name <> "" Then
rs.findfirst "[ID]=' " & cmbPARID & " ' AND [BO_Project_Name]= '
"
& cmbBO_Project_Name & " ' "


ElseIf cmbPARID <> "" Then
rs.findfirst "[ID]=' " & cmbPARID & " ' "


ElseIf cmbBO_Project_Name <> "" Then
rs.findfirst [BO_Project_Name] = " & cmbBO_Project_Name & " ' "


Else
MsgBox "Please select either a Project Name or a Project ID."


End If


If Not rs.nomatch Then

'----------------------------------------------------------------
' This is where you add the code to create the resultset

'----------------------------------------------------------------
strWhere = "[BO_Project_Name] = '" & cmbBO_Project_Name & "'"
if nz(cmbbo_projectname ,"")<>"" then
stDocName = "Project_Inventory"
DoCmd.OpenForm stDocName, , , strWhere
endif

Else
MsgBox "No record was found matching this criteria!"
End If


End Sub

 

[Kgwill85]

Oldpro, I have put this exact code in and now the search button
doesn't bring up the Project_Inventory form. There aren't any errors
coming up though which is a good thing, but I don't know if it's
actually finding the correct record in the form or now.

 

[OldPro]

Oldpro, I have put this exact code in and now the search button
doesn't bring up the Project_Inventory form. There aren't any errors
coming up though which is a good thing, but I don't know if it's
actually finding the correct record in the form or now.

Okay, I see one bug... try exchanging the relevant line with:

stdWhere="[ID]='" & cmbPARID & "' OR [BO_Project_Name]= '
" & cmbBO_Project_Name & "'"

If you hit F9 while the cursor is on that line, then the code will
stop at that line during execution. Hitting F8 thereafter will allow
you to step through the code to see what is happening. Positioning
the cursor over any variable while you are stepping through the code
will show its value.

I assume the popup form is spelled correctly in the above code, and
that it has its recordsource set to the table in question.