Error 3021 No Current Record Exception 4

[MeonR]

I'm getting an Error 3021 Exception in an Access2003 VBA Module
the following code is throwing it;

Private Sub Combo27_AfterUpdate()
' Find the record that matches the control.

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Me![Combo27])
Me.Bookmark = rs.Bookmark 'This causes the exception


End Sub

I have not used Access or VBA in a long time I sort of inherited this problem, Any ideas appreciated!

Thanks,
MeonR

"The beatings will continue until morale improves

 

[MajP]

I would consider rewriting the code because it makes little sense. At a minimum build a sql string that returns a single record. Your looping is inefficient, but does not even guarantee you return the last entered record. There has to be some sort on that. And if you feel compelled to open a recordset and find the correct recod, then update that record while you have it open.

 

[Sticthes]

ok, this is starting to blow my mind.... and not in a good way

'Retrieve last number assigned for "tblCode"
LSQL = "SELECT LstNoAssigned FROM tblAutonumber"
LSQL = LSQL & " WHERE CodeDesc = " & "'tblCode'"

Set lrs = db.OpenRecordset(LSQL)

appears to work, however, using the 'tblCode' String variable isnt.

With it enters the function, my watch shows the value being "NewCustID" as it is in the target table, my watch on the recordset "lrs" shows the record count as 0. if i change the variable to the actual string, i.e

LSQL = LSQL & " WHERE CodeDesc = 'NewCustID'"

the recordset properties shows a recordcount as 1 meaning, no error... any ideas as to why my string variable wont work?

btw, also tried

LSQL = LSQL & " WHERE CodeDesc = 'tblCode'"

with no effect

LNewAutonumber = lrs("LstNoAssigned") + 1 <--- this is the line causing the 'no current record' error (using single quotes in this expression gives the error, 'item not found' and the spelling is correct.

 

[Sticthes]

have tried the code u suggested MajP (not sure i fully understand what its doing though, lol) and it is getting me my autonumber, thats the good news, thanks.

However, i'm getting an undefined error with no option to debug and nothing but a blank msgbox (even with my error handler in place)

msgbox err.description

man, do i suck at this *sadface*

 

[Sticthes]

nvm, sorted it, thanks for the help guys, ur code is working a dream MajP, someday i'll look into what its doing... now i gotaa get onto more complicated stuff *yikes*

 

[PHV]

Anyway, your problem was here:
Call NewAutonumber(tblCode[!]:[/!]="NewCustID")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886