Re: Searching Through ComboBoxes

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


"OldPro" wrote:

On Jul 2, 10:17 am, Kgwil...@xxxxxxxxx wrote:
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.


.

Loading