Find Method when multiple field key

  • Thread starter Thread starter David
  • Start date Start date
D

David

I have no problem with finding a record in an ADODB
recordset when there is only one field for a key field. I
need help, however, in trying to find a record when the
key is composed of two fields. i.e.:

ProNumber is one key field
ProNum is Second field in the key.

I think the following is what needs to be modified:

rst.find "[rst!pronumber] = '" & [HoldProNumber] & "'"

HoldPronumber is the number of the first key field.
HoldProNum is the number of the second key field.

How do I code this in order to get a proper search and
find?

Thanks
 
I'm using 97 so I'm not familiar with the .find
capability, but you might want to create a query that
refers to the appropriate controls in its criteria row for
the two column/fields then view the SQL and copy it to
where you want it. When you run the query, it'll look to
your two reference controls-(key fields).
 
I have no problem with finding a record in an ADODB
recordset when there is only one field for a key field. I
need help, however, in trying to find a record when the
key is composed of two fields. i.e.:

ProNumber is one key field
ProNum is Second field in the key.

I think the following is what needs to be modified:

rst.find "[rst!pronumber] = '" & [HoldProNumber] & "'"

HoldPronumber is the number of the first key field.
HoldProNum is the number of the second key field.

How do I code this in order to get a proper search and
find?
Click to expand...

The Find method's first argument is just a valid SQL WHERE clause
without the WHERE. It can be built up from pieces, using AND, OR, and
any other operators you'ld use in a query SQL - you can try building
the query in the query designer and going to SQL view if you need a
hit for the syntax.

In this case, just try

rst.Find "[pronumber] = '" & HoldProNumber & _
"' AND [ProNum] = '" & HoldProNum & "'"

This assumes that: ProNumber and ProNum are Text datatype fields; if
they're numeric leave off the ' in the string; and that you have VBA
variables named HoldPronumber and HoldProNum (i.e. leave off the
brackets). If the latter are form controls use Me![HoldProNumber] and
Me![HoldProNum].
 
-----Original Message-----
I have no problem with finding a record in an ADODB
recordset when there is only one field for a key field. I
need help, however, in trying to find a record when the
key is composed of two fields. i.e.:

ProNumber is one key field
ProNum is Second field in the key.

I think the following is what needs to be modified:

rst.find "[rst!pronumber] = '" & [HoldProNumber] & "'"

HoldPronumber is the number of the first key field.
HoldProNum is the number of the second key field.

How do I code this in order to get a proper search and
find?
Click to expand...

The Find method's first argument is just a valid SQL WHERE clause
without the WHERE. It can be built up from pieces, using AND, OR, and
any other operators you'ld use in a query SQL - you can try building
the query in the query designer and going to SQL view if you need a
hit for the syntax.

In this case, just try

rst.Find "[pronumber] = '" & HoldProNumber & _
"' AND [ProNum] = '" & HoldProNum & "'"

This assumes that: ProNumber and ProNum are Text datatype fields; if
they're numeric leave off the ' in the string; and that you have VBA
variables named HoldPronumber and HoldProNum (i.e. leave off the
brackets). If the latter are form controls use Me! [HoldProNumber] and
Me![HoldProNum].


.
I must be doing something else wrong.
Click to expand...
ProNumber and ProNum are numeric single type.
HoldProNumber and HoldProNum are VBA defined in a module
as Public, single.
I have tried:
rst.Find "[pronumber] = " & HoldProNumber & " AND [ProNum]
= " & HoldProNum & "". (That didn't work) I get a type
mismatch error.
I have tried:
Exactly as given above. Same error
I have tried:
rst.Find "pronumber = " & HoldProNumber & " AND ProNum = "
& HoldProNum & "". Same error.
(the entire line of code was on one line in my program)
my open statement is : rst.open "tblPickup", cnn,
adopenStatic, adLockOptimistic, adCmdTableDirect

I have also tried without adCmdTableDirect

I Must be missing something somewhere, but am at a loss as
to what.

Thanks for the help.
 
Back
Top