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

 

[genomon]

Try enclosing Str(Me![Combo27]) in single quotes.

Gluais faicilleach le cupan làn

 

[MeonR]

rs.FindFirst "[ID] = " & "'" & Str(Me![Combo27]) & "'"

I tried that no good, but thanks, it throws the same exception...

"The beatings will continue until morale improves

 

[TheAceMan1]

How are ya MeonR . . .

The following code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

Now ... try this:

[blue]Private Sub Combo27_AfterUpdate()
   Dim rs As DAO.Recordset

   Set rs = Me.RecordsetClone
   rs.FindFirst "[ID] = '" & Me![Combo27] & "'"
   
   If rs.NoMatch Then
      MsgBox "Record Not Found!"
   Else
      Me.Bookmark = rs.Bookmark [green]'This causes the exception[/green]
   End If
   
   Set rs = Nothing
    
End Sub[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

The problem is there is no records in your recordset, that is why the line fails. If it was a reference issue it would fail at the line
Dim rs As DAO.Recordset

so
Set rs = Me.Recordset
if not (rs.eof and rs.bof then)
rs.FindFirst "[ID] = " & Str(Me![Combo27])
end if

 

[MeonR]

Hello TheAceMan1 and MajP

Thanks!!! for the help, however, no joy it still throws error 3021 on this line - Me.Bookmark = rs.Bookmark. I can take it out and nothing is thrown but none of the textboxes or other controls get filled in with the relevant info...

Any Ideas



"The beatings will continue until morale improves

 

[MajP]

And if you use my suggestion of using the recordset and not the clone?

 

[MajP]

Also this appears to be a string ID so you have to use what AceMan suggests

rs.FindFirst "[ID] = '" & Me![Combo27] & "'"

this does nothing Str(Me![Combo27])

 

[MeonR]

MajP

Thanks, I tried it both ways and added the single quotes it still throws on this line Me.Bookmark = rs.Bookmark, I'll have to fill in each control line by line from the recordset? I Think...

"The beatings will continue until morale improves

 

[MajP]

That line is not needed if you use the recordset and not the recordsetclone as I suggested.

 

[MajP]

BTW, it is not a "try", it has to be one or the other. If the ID is numeric than you do not use single quotes; however you may have to cast it to the appropriate datatype. Something like:
...ID = " & Clng(NZ(me.combo27,0))

If it is text field you have to use single quotes

...ID = '" & nz(me.combo27,"") & "'"

So it has to resolve to either
...ID = 123
or
...ID = '123ABC'

If your ID is a text field and all the values are numeric it still needs to have single quotes
...ID = '123'

 

[MeonR]

MajP, Everyone,

Thanks, that did it!



"The beatings will continue until morale improves

 

[Sticthes]

Hey guys, this was the most useful sounding post that google threw up, so hope i can get some assistance with this same issue - No Current Record error.

Exceled at VB6 at college and programmed several Access dbs using alot of VBA and the need for a db came up and now i'm teaching myself all over again.

The db i'm writing is for the hotel i work at for streamlining the all paper booking system they currently have.

My current problem is revolving around creating unique numbers for both new customers and new bookings.

I have built this from scratch so can answer any extra info needed type questions, but here is the code that is causing a problem


cmdNewCust_Click function call
'Private Sub cmdNewCust_Click()
'Me.NewRecord 'goto new record command needed?
Call NewAutonumber(tblCode = "NewCustID")
End Sub'

function called

'Public Function NewAutonumber(tblCode As String) As Long 'tblcode is the code requiring new autonumber (customer/booking)

Dim db As Database
Dim LSQL As String
Dim LUpdate As String
Dim lrs As DAO.Recordset
Dim LNewAutonumber As Long

On Error GoTo Err_Execute

Set db = CurrentDb()

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

Set lrs = db.OpenRecordset(LSQL)

If lrs.EOF = False Then
Do 'Loop getting to new record
lrs.MoveNext
Loop Until lrs.EOF = True
Else
'Determine new autonumber
LNewAutonumber = lrs("LstNoAssigned") + 1
NewAutonumber = LNewAutonumber
End If

'Determine new autonumber
LNewAutonumber = lrs("LstNoAssigned") + 1

'Update number stored in table
LUpdate = "UPDATE tblAutonumber"
LUpdate = LUpdate & " SET LstNoAssigned = " & LNewAutonumber
LUpdate = LUpdate & " WHERE CodeDesc = " & tblCode

db.Execute LUpdate, dbFailOnError

lrs.Close
Set lrs = Nothing
Set db = Nothing

NewAutnumber = LNewAutonumber

Err_Execute:
MsgBox Err.Description

'MsgBox ("An error occurred while trying to determine the next " + tblCode + " to assign. Contact Administrator!")

End Function'

There is currently no records in the table bound to the appropriate form, but still wish to use this autonumber function to create this first number.

the table referenced 'tblCode' has 2 codes (NewCustID and NewBookingID) which are identified in the function call (the booking form will eventually also use this function with NewBookingID in the function call. the table has no number currently assigned to 'LstNoAssigned' for either code so my first record for both customer and booking should return 1.

sorry for the length of the post, trying to make a comprehensive post and this has been an issue for over a week and starting to get frustrated.

Thanks in advance!

 

[Sticthes]

dang, cant edit the post.

'Update number stored in table
LUpdate = "UPDATE tblAutonumber"
LUpdate = LUpdate & " SET LstNoAssigned = " & LNewAutonumber
LUpdate = LUpdate & " WHERE CodeDesc = " & tblCode

db.Execute LUpdate, dbFailOnError

lrs.Close
Set lrs = Nothing
Set db = Nothing

Exit Function

this was also added into the 'else' statement to update the number and then exit the function without trying to move to the .MoveNext

error still occurs

 

[Sticthes]

ok ok, i have to appologise here...

have added some watches and it turns out 'tblCodes' defined as an external string input is being used in my SQL statement as True or False... have no idea why so i'm guessing i have even bigger problems here.... sadly

Soz

 

[MajP]

That is way to painful. How about simply?

'Public Function NewAutonumber(tblCode As String) As Long    

    Dim strCriteria as string
    dim strSql as string

    strCriteria = "CodeDesc = '" & tblCode & "'"
    newAutonumber = NZ(DMAX("lstNoAssigned","tblAutonumber",strCriteria)) + 1
    strSql = "UPDATE tblAutonumber SET LstNoAssigned = " & newAutonumber 
    strSql = strSql & " " & strCriteria
    currentdb.Execute strSql, dbFailOnError
 
end function

Why are you rolling your own autonumber

 

[Sticthes]

wow.. that looks alot simpler, not sure i understand some of it though... been nearly a decade since i tried doing this stuff LOL

i'm making my own number because (mainly for flexibility i guess)using Access 2k, as far as i know there is no way to use the Access autonumber with an input mask (using 8digit numbers and will probably be adding a date into the booking ID created too).

This is very early stage of this DB atm, have made the customer and payment tables, with payment options being a subform in customer entry so just trying to ensure everything works well before moving onto the next thing.

mind explaining the NZ and DMAX?

thanks for quick reply btw

 

[Sticthes]

tbh, i'm a little reluctant to re-write all the code since i almost fully understand the bit i have written, unles ofc it is total garbage.

i have removed the else from the if statement (realised it was just repeating code.

but i now seem to have a different problem.

"Too few parameters. Expected 1"

using breaks i find

Set db = CurrentDb()

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

Set lrs = db.OpenRecordset(LSQL) <---- this is the problematic code... any ideas?

sorry again, felling more and more like i've never touched access before (kinda annoying)

 

[genomon]

Sigle quotes needed for string criteria.
Try: WHERE CodeDesc = '" & tblCode * "'"

Gluais faicilleach le cupan làn

 

[Sticthes]

man... i knew it would be something simple!

Awesome, thanks.

just need to get back to solving this no record issue then lol
strange considering there are 2 records in the code table