Searching Through ComboBoxes

[Kgwill85]

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

 

[Kgwill85]

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

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]

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

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]

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

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- Hide quoted text -

- Show quoted text -

If PARID is text, then include single quotes in the SQL string:
stLinkCriteria = "[PARID] =' " & Me.PARID & " ' AND "

 

[Guest]

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

 

[Kgwill85]

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










- Show quoted text -

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

 

[Kgwill85]

Thanks for the input, I'm about to see what happens after messing
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.

 

[Guest]

Wow, I didn't know you could make it this hard.
--
Dave Hargis, Microsoft Access MVP

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

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.

 

[Kgwill85]

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

 

[Guest]

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

 

[Kgwill85]

Post back with the code as you wrote it, please.
--
Dave Hargis, Microsoft Access MVP






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

End If


DoCmd.OpenForm stDocName, , , strWhere


End Sub

 

[Guest]

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

 

[Kgwill85]

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














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

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?- Hide quoted text -

- Show quoted text -

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

 

[Guest]

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

 

[Kgwill85]

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.

 

[Kgwill85]

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








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

 

[Guest]

Good luck. We can help you through this

 

[Kgwill85]

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

 

[OldPro]

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]