G
gab1972
I have been desperately trying to figure this out and I'm stumped.
So
I'm turning to my fellow Google Groupers.
I have a macro enriched Excel file that calls/edits information in a
MS Access database. There are about 20 fields in the database. I'm
using ADODB connection/recordset codings to get/edit my information.
I know how to code a SEARCH string in order to find one field of
information, but not multiple fields.
For example, I manage a database of permits. A few of the fields in
my database are permit number (permit_num), County (county),
Applicant
(app), and Route Name (route_nam) - to name a few. If I wanted to
search for the permit_num, my SEARCH would call that permit number.
But what if the user didn't have a specific piece of information. I
have 7 counties I deal with. What if the user knew the permit was in
Richland county, but didn't know anything else. There could be 10
different permits for that county. Or, more specifically, what if
the
user only new it was Main St. There could be 50 different
possibilities.
If I go into the database myself, I can do an Edit, Find and type in
a
wild card name like "main", click Find and then scroll through the
possibilities. I want to be able to do this through a VBA macro. I
just don't know how to go about doing it in the most efficient way.
Already, I can populate a Form Box with just a list of the permit
numbers.
The user types in the permit number and that is my search string
variable
that Excel goes into Access and finds. I want to have a Form Box
where
the user can type any variable and have Excel search for that in any
field
in Access and return a list of possible results.
database name: Permit.Tracker
table name: permit_info
typical ADODB line:
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=Z:\COMMON FILES\Encroachment Permits
\Permit.Tracker\Database\Permit.Tracker.mdb;"
typical recordset search line looking for one piece of info:
str = "SELECT permit_num FROM permit_info WHERE county = '" & sCounty
& "'"
rs.Open str, cn, adOpenKeyset, adLockOptimistic
Any ideas? Anyone up for the challenge?
Thanks in advance.
So
I'm turning to my fellow Google Groupers.
I have a macro enriched Excel file that calls/edits information in a
MS Access database. There are about 20 fields in the database. I'm
using ADODB connection/recordset codings to get/edit my information.
I know how to code a SEARCH string in order to find one field of
information, but not multiple fields.
For example, I manage a database of permits. A few of the fields in
my database are permit number (permit_num), County (county),
Applicant
(app), and Route Name (route_nam) - to name a few. If I wanted to
search for the permit_num, my SEARCH would call that permit number.
But what if the user didn't have a specific piece of information. I
have 7 counties I deal with. What if the user knew the permit was in
Richland county, but didn't know anything else. There could be 10
different permits for that county. Or, more specifically, what if
the
user only new it was Main St. There could be 50 different
possibilities.
If I go into the database myself, I can do an Edit, Find and type in
a
wild card name like "main", click Find and then scroll through the
possibilities. I want to be able to do this through a VBA macro. I
just don't know how to go about doing it in the most efficient way.
Already, I can populate a Form Box with just a list of the permit
numbers.
The user types in the permit number and that is my search string
variable
that Excel goes into Access and finds. I want to have a Form Box
where
the user can type any variable and have Excel search for that in any
field
in Access and return a list of possible results.
database name: Permit.Tracker
table name: permit_info
typical ADODB line:
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=Z:\COMMON FILES\Encroachment Permits
\Permit.Tracker\Database\Permit.Tracker.mdb;"
typical recordset search line looking for one piece of info:
str = "SELECT permit_num FROM permit_info WHERE county = '" & sCounty
& "'"
rs.Open str, cn, adOpenKeyset, adLockOptimistic
Any ideas? Anyone up for the challenge?
Thanks in advance.