DAO Recordset syntax HELP

[Guest]

Can anyone help? I'm getting a runtime error 3131 in the "set masternum =
...." and i just dont see how i can be getting that error. secondly, for the
findfirst criteria is kind of giving me the same error. it doesnt like the
syntax. to me logically i see that it would work but then again im relativaly
new to the recordset area so if anyone could help me that would be great.
also if you have any other ideas that would be great also. thankyou

Set storenum = CurrentDb.OpenRecordset("SELECT SVNum FROM
tblSVNum;", dbOpenDynaset) 'Assign tblSVNumm to recordset
Set upcnum = CurrentDb.OpenRecordset("SELECT UPCCase FROM tblUPCs;",
dbOpenDynaset) 'Assign tblUPC to recordset
Set masternum = CurrentDb.OpenRecordset("SELECT CustNbr, UPCCase
FROM Sara Lee Master;", dbOpenDynaset) 'Assign Sara Lee Master to recordset
Set final = CurrentDb.OpenRecordset("Final VOIDs", dbOpenDynaset)

storenum.MoveFirst
upcnum.MoveFirst
masternum.MoveFirst

Do Until storenum.EOF
num = storenum!SVNum
Do Until upcnum.EOF
upc = upcnum!UPCCase
Do Until masternum.EOF
criteria = "[CustNbr] = '" & num & "' AND [UPCCase] = '"
& upc & "'"
masternum.FindFirst (criteria)
If masternum.NoMatch() Then
final.AddNew
final!num = num
final!case = UPCCase
final.Update
End If
masternum.MoveNext
Loop
upcnum.MoveNext
Loop
storenum.MoveNext
Loop
storenum.Close
upcnum.Close
masternum.Close
MsgBox "Operation was a success! " & final.RecordCount & " Records
where found and recorded.", vbInformation, "Information"
final.Close

 

[Gunnar Nygaard]

Can anyone help? I'm getting a runtime error 3131 in the "set masternum =
..." and i just dont see how i can be getting that error. secondly, for
the
findfirst criteria is kind of giving me the same error. it doesnt like the
syntax. to me logically i see that it would work but then again im
relativaly
new to the recordset area so if anyone could help me that would be great.
also if you have any other ideas that would be great also. thankyou

Set storenum = CurrentDb.OpenRecordset("SELECT SVNum FROM
tblSVNum;", dbOpenDynaset) 'Assign tblSVNumm to recordset
Set upcnum = CurrentDb.OpenRecordset("SELECT UPCCase FROM
tblUPCs;",
dbOpenDynaset) 'Assign tblUPC to recordset
Set masternum = CurrentDb.OpenRecordset("SELECT CustNbr, UPCCase
FROM Sara Lee Master;", dbOpenDynaset) 'Assign Sara Lee Master to
recordset
Set final = CurrentDb.OpenRecordset("Final VOIDs", dbOpenDynaset)

Have you tried
FROM [Sara Lee Master]

 

[Guest]

thanks for the speedy response. ok i fixed that part now im still getting
that other part where i try to set a string statement to the criteria. any
suggestions? thankyou
criteria = "[CustNbr] = " & num & " And [UPCCase] = " &
upc
masternum.FindFirst (criteria)
If masternum.NoMatch() Then
final.AddNew
final!num = num
final!case = UPCCase
final.Update
End If

 

[Douglas J Steele]

First, I'd advise not using Criteria as a variable name: I suspect it may be
a reserved word. Try using strCriteria instead.

What are the data types of CustNbr and UPCCase? If either is Text, you need
to ensure that there are quotes around the value. For instance, if UPCCase
is text, you need:

strCriteria = "[CustNbr] = " & num & " And [UPCCase] = " & Chr$(34) & upc
& Chr$(34)

As well, you don't need parentheses around the variable when calling the
FindFirst method (although I doubt that's what's causing your problem):

masternum.FindFirst strCriteria

 

[Guest]

Thankyou so much youjust saved me a huge ongoing headache

First, I'd advise not using Criteria as a variable name: I suspect it may be
a reserved word. Try using strCriteria instead.

What are the data types of CustNbr and UPCCase? If either is Text, you need
to ensure that there are quotes around the value. For instance, if UPCCase
is text, you need:

strCriteria = "[CustNbr] = " & num & " And [UPCCase] = " & Chr$(34) & upc
& Chr$(34)

As well, you don't need parentheses around the variable when calling the
FindFirst method (although I doubt that's what's causing your problem):

masternum.FindFirst strCriteria


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

thanks for the speedy response. ok i fixed that part now im still getting
that other part where i try to set a string statement to the criteria. any
suggestions? thankyou
criteria = "[CustNbr] = " & num & " And [UPCCase] = " &
upc
masternum.FindFirst (criteria)
If masternum.NoMatch() Then
final.AddNew
final!num = num
final!case = UPCCase
final.Update
End If