I have an MS Access database that I am pulling and sending information
from/to. I have one table (permit_info) and about 20 different
fields.

I have a userform in Excel that the user can type in a permit number
and it will find the recordset matching that number (my table has a
field with permit numbers in it). What I want to do is allow the user
to search by multiple fields. Say, the person doesn't know the permit
number and wants to search by county and/or applicant name. Can I do
a keyword search box on my user form and have Excel return possible
hits?

I'm guessing some kind of loop that will search by say 3 different
fields, go into Access and pull the recordsets matching those 3
fields, store them temporarily in Excel on a hidden sheet, and then
display them in a combobox???

I'm using Office2k3.
Database path/name: P:\Permit Tracking System Project\Tracking System
Project\Permit.Tracker_v0.01109b.mdb
Table name: permit_info

I'm using ADO to communicate with Access.

Can anyone get me started? Thanks in advance.

Excel uses the same SQL statements that would be used in Access. If you set
up the search you desire in Access and provide the SQL statments these can be
put into an Excel macro.. You can rretrieve the data used one compond SQL
statements that uses multiple criteria. One you get the recordset you can
pull the data directly from the returned recordset to the combobox. The
Recordset is a pointer to the data and you can extract that data as required.
It doesn't need a temporary worksheet for the results.

If you used a query, then you would have to put the results termporarily
into a worksheet before displaying the results in the comboxbox..

"gab1972" wrote:

I have an MS Access database that I am pulling and sending information
from/to. I have one table (permit_info) and about 20 different
fields.

I have a userform in Excel that the user can type in a permit number
and it will find the recordset matching that number (my table has a
field with permit numbers in it). What I want to do is allow the user
to search by multiple fields. Say, the person doesn't know the permit
number and wants to search by county and/or applicant name. Can I do
a keyword search box on my user form and have Excel return possible
hits?

I'm guessing some kind of loop that will search by say 3 different
fields, go into Access and pull the recordsets matching those 3
fields, store them temporarily in Excel on a hidden sheet, and then
display them in a combobox???

I'm using Office2k3.
Database path/name: P:\Permit Tracking System Project\Tracking System
Project\Permit.Tracker_v0.01109b.mdb
Table name: permit_info

I'm using ADO to communicate with Access.

Can anyone get me started? Thanks in advance.

On Jan 15, 9:50*am, Joel wrote:
Excel uses the same SQL statements that would be used in Access. *If you set
up the search you desire in Access and provide the SQL statments these can be
put into an Excel macro.. *You can rretrieve the data used one compond SQL
statements that uses multiple criteria. *One you get the recordset you can
pull the data directly from the returned recordset to the combobox. *The
Recordset is a pointer to the data and you can extract that data as required.
*It doesn't need a temporary worksheet for the results.

If you used a query, then you would have to put the results termporarily
into a worksheet before displaying the results in the comboxbox..



I'm not real familiar with SQL...can you do this using ADO?

Below is an ODBC query I just created. The Command Text is the SQL
statements. The way I normally get my SQL statements is doing the operation
manually.

1) I start the macro recorder in Excel.
2) I go to menu Data - Import External Data - New Database Query
3) Double click on the MS Access Database.
4) Select the File
5) Apply your filter you are looking for in the data base using the wizard
6) Stop Recording Macro.
7) Modify the recorded macro as required. You can extract the SQL
statements a put them into a recordset if you want instead of doing the
query. The query below makes a connection using the ODBC. You can still
makde a connection without doing the query.



With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;" & _
"DBQ=C:\TEMP\Part Log.mdb;" & _
"DefaultDir=C:\TEMP;" & _
"DriverId=25;" & _
"FIL=MS Access;" & _
"MaxBufferSize=2048;PageTimeout"), Array("=5;")),
Destination:=Range("F26"))

.CommandText = Array( _
"SELECT `1234 & 5678 parts received`.ID", _
"`1234 & 5678 parts received`.Program", _
"`1234 & 5678 parts received`.`Procurement Part Number`", _
"`1234 & 5678 parts received`.`Upscreen Part Number`")
.Name = "Query from MS Access Database_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

"gab1972" wrote:

On Jan 15, 9:50 am, Joel wrote:
Excel uses the same SQL statements that would be used in Access. If you set
up the search you desire in Access and provide the SQL statments these can be
put into an Excel macro.. You can rretrieve the data used one compond SQL
statements that uses multiple criteria. One you get the recordset you can
pull the data directly from the returned recordset to the combobox. The
Recordset is a pointer to the data and you can extract that data as required.
It doesn't need a temporary worksheet for the results.

If you used a query, then you would have to put the results termporarily
into a worksheet before displaying the results in the comboxbox..



I'm not real familiar with SQL...can you do this using ADO?