Searching Through ComboBoxes

[Kgwi...@yahoo.com]

I have been trying for the last week to create a form that allows
users to search two fields in order to find it in a database with all
of it's additional information.

I have been using macros up until this point. It has become too
complicated for the use of macros for what I am trying to do, so I'm
going to need some kind of code for the task that I need the form to
do.

Here's the details.
The form that I'm working on is named SearchProjectInventory. It has
2 combo boxes and 1 command button. The first combobox is for the ID
of a project. The name of the combobox is PARID. The second combobox
is named BO_Project_Name. The command button is named Search. The
combo boxes are linked to a table which is named Project_Inventory. I
also need the information filtered inside of the combo boxes. They
currently show duplicate entries for some reason.

Okay, what I need the form to do is simply be able to search both of
the combo boxes separately if need be. Say for instance the user
knows the BO_Project_Name but doesn't know the PARID, then the form
still needs to be able to function. The same goes in vice versa. The
form needs to be able to search if one combo box is filled or both.

I already have another form that will be populated by this one. It is
named Project_Inventory. So when I click the Search button on the
SearchProjectInventory form it should bring up the Project_Inventory
form populated with the information you are looking for.

Thanks in advance

[Kgwi...@yahoo.com]

Some one on the forms section helped me earlier, but I'm getting a
type mismatch error when I click Search. Here's the code that he made
for it...

Private Sub Search_Click()
On Error GoTo Err_Search_Click

Dim stDocName As String
Dim stLinkCriteria As String

'change this to the name of your results form
stDocName = "Project_Inventory"

'check for selection in first combo box
'change COMBO1 to the name of your combo box
If Not IsNull(Me.PARID) Then
stLinkCriteria = "[PARID] = " & Me.PARID & " AND "
End If

'check for selection in second combo box
'change COMBO2 to the name of your combo box
If Not IsNull(Me.BO_Project_Name) Then
stLinkCriteria = "[BO_Project_Name] = " & Me.BO_Project_Name &
" AND "
End If

'remove the last 5 chars
If Len(stLinkCriteria) > 0 Then
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria)) - 5
End If

'open form filtered by criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Search_Click:
Exit Sub

Err_Search_Click:
MsgBox Err.Description
Resume Exit_Search_Click

End Sub

[OldPro]

On Jul 2, 10:17 am, Kgwil...@yahoo.com wrote:

This is easy for a programmer, but not so easy if you haven't
programmed before. Basically, do the following:
1) Make sure there is a reference to DAO.
2) Make sure that no table or recordset is associated with the form
itself.
3) In your button click event add the following code:
Dim db as dao.database
Dim rs as dao.recordset
Set db=currentdb()
Set rs=db.openrecordset("Select * From ProjectInventory")
if cmbPARID<>"" AND cmbBO_Proj_Name <> "" then
rs.findfirst "[ID]=' " & cmbPARID & " ' AND [ProjectName]= ' " &
cmbBO_Proj_Name & " ' "
elseif cmbPARID<>"" then
rs.findfirst "[ID]=' " & cmbPARID & " ' "
elseif cmbBO_Proj_Name <> "" then
rs.findfirst [ProjectName]= ' " & cmbBO_Proj_Name & " ' "
else
msgbox "Please select either a Project Name or a Project ID."
endif

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!"
endif

4) The resultset could be another form that opens up, or it could be a
listbox on the same form. The listbox is easier to program, and uses
less code. lstboxResultset.rowsource= "Select [ID],[ProjectName] From
ProjectInventory WHERE "[ID]=' " & cmbPARID & " ' AND [ProjectName] '
" & cmbBO_Proj_Name & " ' ;"

5) Technically, you don't have to do the .findfirst. If the resultset
is empty, then no records were found. Usually one field with a unique
id is indexed. If the code were to return just one record, then you
would first find the index for the record, and then retrieve that
record based on it's index. Since you are expecting multiple records,
you need to filter the table using the search parameters.
6) Use the DISTINCT keyword in the SELECT statement in the combobox to
restrict the selectable items to unique items. For Example:
cmbBO_Proj_Name.rowsource="SELECT DISTINCT [ProjectName] FROM
ProjectInventory"
7) I haven't debugged this project, and it may not do exactly what you
want; it is just an outline of one possible solution.
8) Don't forget to add code to close the database object db and the
recordset object rs after the data is found (or not found). Example:
rs.close
set rs=nothing
db.close
set db=nothing

After everything I've mentioned, there is still so much that hasn't
been mentioned... I hope this has helped and I wish you well.

[OldPro]

> End Sub- Hide quoted text -
>
> - Show quoted text -

If PARID is text, then include single quotes in the SQL string:

[Klatuu]

I read the other answer that was not working. It has some problems and some
invalid criteria. Try this version. You did not say what fields the combo
boxes are looking up, so you will have to use the real names.

Als as to the combos showing multiple values. You need to add DISTINCT to
your combos' queries:

SELECT DISTINCT SomeField FROM .....

That will cause it to present the value only once regardless of the number
of times it appears in the underlying table.

Private Sub Search_Click()
Dim strWhere As String

If Not IsNull(Me.PARID) Then
strWhere = "SomeFieldName = '" & Me.PARID & "'"
End If

If Not IsNull(Me.BO_Project_Name) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhre = strWhere & "TheOtherFieldName = '" & Me.BO_Project_Name &
"'"
End If

DoCmd.OpenForm stDocName, , , strWhere

End Sub

--
Dave Hargis, Microsoft Access MVP

[Kgwi...@yahoo.com]

> > Thanks in advance- Hide quoted text -

>
> - Show quoted text -

Thanks for the input, I'm about to see what happens after messing
around with this.

[Kgwi...@yahoo.com]

> around with this.- Hide quoted text -

>
> - Show quoted text -

And the combo boxes are looking up the same fields that are in the
corresponding table. The PARID combo box is looking up the PARID
field in the table. The BO_Project_Name is looking up the
BO_Project_Name field in the Project_Inventory table aswell.

[Klatuu]

Wow, I didn't know you could make it this hard.

--
Dave Hargis, Microsoft Access MVP

[Kgwi...@yahoo.com]

> BO_Project_Name field in the Project_Inventory table aswell.- Hide quoted text -

>
> - Show quoted text -

I just ran it with the code and it brings up the Project_Inventory
form but the information doesn't show up. The search is bring up a
blank form.

[Klatuu]

Post back with the code as you wrote it, please.

--
Dave Hargis, Microsoft Access MVP

[Kgwi...@yahoo.com]

On Jul 2, 11:58 am, Klatuu <Kla...@discussions.microsoft.com> wrote:
> Post back with the code as you wrote it, please.
> --
> Dave Hargis, Microsoft Access MVP
>
>
>

> > blank form.- Hide quoted text -

>
> - Show quoted text -

This is what I have.

Private Sub Search_Click()
Dim strWhere As String

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Project_Inventory"

If Not IsNull(Me.PARID) Then
strWhere = "PARID = '" & Me.PARID & "'"
End If

If Not IsNull(Me.BO_Project_Name) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If

strWhere = strWhere & "PARID" = " & Me.BO_Project_Name &"

[Klatuu]

This part, I don't understand.
Are you expecting either value to be found in the same field? That doesn't
sound right. Each combo should be searching on a different field. Also the
syntax in not correct on this line:

strWhere = strWhere & "PARID" = " & Me.BO_Project_Name &"

Should be
strWhere = strWhere & "PARID" = " & Me.BO_Project_Name &"'"

If Not IsNull(Me.BO_Project_Name) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "PARID" = " & Me.BO_Project_Name &"

End If

--

Dave Hargis, Microsoft Access MVP

[Kgwi...@yahoo.com]

On Jul 2, 1:36 pm, Klatuu <Kla...@discussions.microsoft.com> wrote:
> This part, I don't understand.
> Are you expecting either value to be found in the same field? That doesn't
> sound right. Each combo should be searching on a different field. Also the
> syntax in not correct on this line:
> strWhere = strWhere & "PARID" = " & Me.BO_Project_Name &"
> Should be
> strWhere = strWhere & "PARID" = " & Me.BO_Project_Name &"'"
>
> If Not IsNull(Me.BO_Project_Name) Then
> If Len(strWhere) > 0 Then
> strWhere = strWhere & " And "
> End If
> strWhere = strWhere & "PARID" = " & Me.BO_Project_Name &"
>
> End If
>
> --
> Dave Hargis, Microsoft Access MVP
>
>
>

> > End Sub- Hide quoted text -

>
> - Show quoted text -

I'll try to explain better. I have a table named Project_Inventory.
That table is populated by a form also named Project_Inventory. In
that table there is like 20 fields total. The two most important
fields are the PARID and the BO_Project_Name. This new form that I am
creating is going to be used to search for information in the
Project_Inventory table.

The purpose of the SearchProjectInventory form is for the user to be
able to find an entry in the Project_Inventory. In order for them to
find a project in the database, they will either have to know both the
PARID (which is the project's ID number) and the BO_Project_Name
(which is the project's name)... If they don't know the information
for both fields but know it for 1 (either the PARID, or
BO_Project_Name) then they can simply search that field and leave the
other blank resulting in the Project_Inventory form opening and
displaying that information.

Does that help?

[OldPro]

> Does that help?- Hide quoted text -

>
> - Show quoted text -

Again - is PARID a text field or a numeric field?

[Klatuu]

Thanks for the additional info
Better naming conventions would sure help.
Your naming conventions remind me of the USYRU rule book for sailing
regattas where one rule is:
"You may not tack your tack while on a tack"

for a table:
tblProjectInventory
for a form:
frmProject_Inventory
for a text box:
cboPARID

But, that not withstanding,

Private Sub Search_Click()
Dim strWhere As String

If Not IsNull(Me.PARID) Then

strWhere = "PARID = '" & Me.PARID & "'"

ElseIf Not IsNull(Me.BO_Project_Name) Then
strWhere = "BO_Project_Name = '" & Me.BO_Project_Name & "'"
Else
MsgBox "Select One or the Other"
Me.PARID.SetFocus
Exit Sub
End If

DoCmd.OpenForm stDocName, , , strWhere

End Sub
--
Dave Hargis, Microsoft Access MVP

[Kgwi...@yahoo.com]

> Again - is PARID a text field or a numeric field?- Hide quoted text -

>
> - Show quoted text -

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.

[Kgwi...@yahoo.com]

> > Does that help?- Hide quoted text -

>
> - Show quoted text -

Thanks again, I'm learning a lot through you guys. I've just started
working with Visual Basic with Access, thanks for bearing with me.

I'll see if this helps and let you know, thanks again.

[Kgwi...@yahoo.com]

> I'll see if this helps and let you know, thanks again.- Hide quoted text -

>
> - Show quoted text -

The form is still popping up blank. This is the exact code that I
have:

Private Sub Search_Click()
Dim strWhere As String

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Project_Inventory"

If Not IsNull(Me.PARID) Then
strWhere = "PARID = '" & Me.PARID & "'"

ElseIf Not IsNull(Me.BO_Project_Name) Then
strWhere = "BO_Project_Name = '" & Me.BO_Project_Name & "'"
Else
MsgBox "Select One or the Other"
Me.PARID.SetFocus
Exit Sub
End If

DoCmd.OpenForm stDocName, , , strWhere

End Sub

Is there a function to implement in the code that finds a record? I
could do it with the FindRecord macro for the PARID combo box, now
nothing is coming up at all.

[Klatuu]

Good luck. We can help you through this

--
Dave Hargis, Microsoft Access MVP

[OldPro]

On Jul 2, 2:53 pm, Kgwil...@yahoo.com wrote:

> 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]

[Kgwi...@yahoo.com]

> 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.

--
Dave Hargis, Microsoft Access MVP

[Kgwi...@yahoo.com]

On Jul 3, 9:02 am, Klatuu <Kla...@discussions.microsoft.com> wrote:
> 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.

[Kgwi...@yahoo.com]

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

[Kgwi...@yahoo.com]

On Jul 3, 9:02 am, Klatuu <Kla...@discussions.microsoft.com> wrote:

> 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.

[Kgwi...@yahoo.com]

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.

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

<Kgwi...@yahoo.com> wrote in message
news:1183475243....@o61g2000hsh.googlegroups.com...

[Kgwi...@yahoo.com]

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!"

[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
http://I.Am/DougSteele
(no e-mails, please!)

<Kgwi...@yahoo.com> wrote in message
news:1183477380....@57g2000hsv.googlegroups.com...

[OldPro]

On Jul 3, 10:07 am, Kgwil...@yahoo.com wrote:
> 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]

On Jul 3, 10:07 am, Kgwil...@yahoo.com wrote:

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]

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?

--
Dave Hargis, Microsoft Access MVP

[Kgwi...@yahoo.com]

On Jul 3, 11:22 am, "Douglas J. Steele"

> <Kgwil...@yahoo.com> wrote in message

> > 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?

[Kgwi...@yahoo.com]

> ...
>
> 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

[Kgwi...@yahoo.com]

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

[OldPro]

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

[Kgwi...@yahoo.com]

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]

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.

[Kgwi...@yahoo.com]

> Okay, I see one bug... try exchanging the relevant line with:
>
> stdWhere="[ID]='" & cmbPARID & "' OR [BO_Project_Name]= '
> " & cmbBO_Project_Name & "'"

I'm confused with this one, where exactly do I put this? Could you
reply with just the line that it's suppose to replace?

[Kgwi...@yahoo.com]

Well since this isn't working at all, I have went back to using a
macro for this. I got it to find the PARID correctly so far, but it
will not search for the BO_Project_Name.

[Steve Schapel]

Kgwill85,

You should always precede a FindRecord action with a GoToControl action.
In this case, the active control on the Project_Inventory form just
happens to be the PARID (presumably because it is the first in the Tab
Order, but even then, I would still explicitly state it.

I still can't understand how you are relating to the Project_Inventory
form from the form that you have the criteria entered. I will assume
that it is a different form, and that it is already open.

So, I would set up the macro like this...

Action: SelectObject
Object Type: Form
Object Name: Project_Inventory

Condition: [PARID] Is Not Null
Action: GoToControl
Control Name: [Forms]![Project_Inventory]![PARID]

Condition: ...
Action: FindRecord
Find What:=[PARID]

Condition: ...
Action: StopMacro

Action: GoToControl
Control Name: [Forms]![Project_Inventory]![BO_Project_Name]

Action: FindRecord
Find What:=[BO_Project_Name]

This is making quite a few assumptions, as you haven't provided much
information.

--
Steve Schapel, Microsoft Access MVP

[OldPro]

On Jul 6, 2:14 pm, Kgwil...@yahoo.com wrote:

stdWhere should be strWhere and this line should replace the strWhere
line(once the spelling is corrected). There is only one line that
starts with strWhere=...