Find Method When Multiple Field Key

[dave]

I have an ADODB recordset with a multiple field key:

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

Both are numeric and defined as Single.

HoldProNumber holds the number of the first key field.
HoldProNum hold the number of the second key field.

HoldProNumber and HoldProNum are VBA defined numeric as
Single. Their numbers are derived from a list box located
on a different form. The numbers are saved into these two
Public variables and carried to the On Load event of the
new form. No problem. Now I need to retrieve the actual
record from the table "tblPickup", display the record in
my new form and be able to make changes to the data.

Your help in using the find method for multiple field keys
was:

rst.find "[pronumber] = '" & HoldProNumber & "' AND_
[ProNum] = '" & HoldProNum & "'"

This (you wrote) 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 have tried the syntax exactly as written. I have also
tried it as:

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

My open statement for the recordset is:

rst.open "tblPickup", cnn, adOpenDynamic, adLockOptimistic

Any Additional help will be greatly appreciated.
I have programmed for 30 years in COBOL, and it may be
that I'm too close to the problem and doing it the "old"
way to see where I'm going wrong.

The error message I get with either syntax is:

Run-Time Error '3001'

Arguments are of the wrong type, are out of acceptable
range, or are in conflict with one another.

Thanks again for your help.

Dave

 

[John Vinson]

I have an ADODB recordset with a multiple field key:

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

Both are numeric and defined as Single.

Are these numbers with decimal portions? If not - use Long or Integer.
Single floating point numbers have roundoff error and you may well
have difficulties with values being off in the 7th decimal place.

HoldProNumber holds the number of the first key field.
HoldProNum hold the number of the second key field.

I have tried the syntax exactly as written. I have also
tried it as:

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

This should be correct (with ] for }, probably a typo).

...
Run-Time Error '3001'

Arguments are of the wrong type, are out of acceptable
range, or are in conflict with one another.

Thanks again for your help.

Let's see what the actual values are. Try:

Dim strSQL As String
strSQL = "[pronumber] = " & HoldProNumber & _
" AND [ProNum] = " & HoldProNum
Debug.Print strSQL
rst.Find strSQL

What's the value of strSQL? Do they in fact (exactly!) match the
values of fields in your table?

 

[dave]

When I run the debug routine suggested I receive the
following in the immediate window:

[ProNumber] = 1302 And [ProNum] = 5

(These are the correct numbers for the record
selected in the list box)


I the step to the next statement, the rst.Find strSQL and
I get the same runtime error 3001.

I have checked out the origination table and the record is
still in there.

I noticed you left the final two " marks from the end of
the strSQL string so I tried that with the find. It
didn't work that way either. I put the & and the two "
marks at the end and tried again with the message again.

-----Original Message-----

I have an ADODB recordset with a multiple field key:

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

Both are numeric and defined as Single.

Are these numbers with decimal portions? If not - use Long or Integer.
Single floating point numbers have roundoff error and you may well
have difficulties with values being off in the 7th decimal place.

HoldProNumber holds the number of the first key field.
HoldProNum hold the number of the second key field.

I have tried the syntax exactly as written. I have also
tried it as:

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

This should be correct (with ] for }, probably a typo).

...
Run-Time Error '3001'

Arguments are of the wrong type, are out of acceptable
range, or are in conflict with one another.

Thanks again for your help.

Let's see what the actual values are. Try:

Dim strSQL As String
strSQL = "[pronumber] = " & HoldProNumber & _
" AND [ProNum] = " & HoldProNum
Debug.Print strSQL
rst.Find strSQL

What's the value of strSQL? Do they in fact (exactly!) match the
values of fields in your table?


.

 

[dave]

I changed all of the fields in question to long rather
than single. I get the same error message.

-----Original Message-----

I have an ADODB recordset with a multiple field key:

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

Both are numeric and defined as Single.

Are these numbers with decimal portions? If not - use Long or Integer.
Single floating point numbers have roundoff error and you may well
have difficulties with values being off in the 7th decimal place.

HoldProNumber holds the number of the first key field.
HoldProNum hold the number of the second key field.

I have tried the syntax exactly as written. I have also
tried it as:

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

This should be correct (with ] for }, probably a typo).

...
Run-Time Error '3001'

Arguments are of the wrong type, are out of acceptable
range, or are in conflict with one another.

Thanks again for your help.

Let's see what the actual values are. Try:

Dim strSQL As String
strSQL = "[pronumber] = " & HoldProNumber & _
" AND [ProNum] = " & HoldProNum
Debug.Print strSQL
rst.Find strSQL

What's the value of strSQL? Do they in fact (exactly!) match the
values of fields in your table?


.

 

[John Vinson]

I noticed you left the final two " marks from the end of
the strSQL string so I tried that with the find. It
didn't work that way either. I put the & and the two "
marks at the end and tried again with the message again.

Concatenating "" to a string concatenates... an empty string. I.e. it
doesn't change the content of the string you're building one iota!

I really DON'T know why the Find isn't working; my only suggestion is
to change the datatype from Single to Long (in a copy of the database
perhaps). Anyone?

 

[dave]

I did know that, but I was grasping at straws. Still am.
Dave

 

[dave]

I have researched through several areas and came upon an
obscure reference that in part says, "the find method
works for single column searchs only there is no support
for multiple-column searchs". I don't know why not, but
I guess it doesn't work the way we think it should.

 

[John W. Vinson]

I have researched through several areas and came upon an
obscure reference that in part says, "the find method
works for single column searchs only there is no support
for multiple-column searchs". I don't know why not, but
I guess it doesn't work the way we think it should.

YOW!

I'd be very interested in that cite, if you kept a record of it.

I guess the alternatives would be to use the Seek method or the
FindFirst method on a DAO recordset instead.

 

[dave]

John: I finally found it in the help menu of access
under the Find Method close to the bottom. It was in
access 2003.

I really do appreciate your time on this and following
through.

Dave