Tags: access, bookmark, customer, database, error, exist, function, microsoft, mysql, oracle, sql, valid

not a valid bookmark

On Database » Microsoft Access

4,540 words with 6 Comments; publish: Fri, 07 Dec 2007 18:49:00 GMT; (250125.00, « »)

I have a function that finds a customer.When the customer does not exist, i get the error not a valid bookmark.

Can you help me with my function ? Is there something wrong with it ? Can i improve it and make it more stable ?

Public Function FindCustomer()

Dim f As Form

Set f = Forms!FCustomers

Dim strCustomerID As String

Dim strBookmark As String

strCustomerID = InputBox("Enter customer number ? ")

If strCustomerID = "" Then

Exit Function

End If

f.RecordsetClone.FindFirst "CustomerID = " & strCustomerID

If f.RecordsetClone.NoMatch Then

MsgBox "customer " & strCustomerID & " does not exist!!"

f.Bookmark = strBookmark

Else

f.Bookmark = f.RecordsetClone.Bookmark

End If

End Function

All Comments

Leave a comment...

  • 6 Comments
    • Add the two statements below that are in bold and underlined. The first one turns off Access' native error messages and the second one turns them back on. Always be sure to turn them back on!

      Public Function FindCustomer()

      Dim f As Form

      Set f = Forms!FCustomers

      Dim strCustomerID As String

      Dim strBookmark As String

      DoCmd.SetWarnings False

      strCustomerID = InputBox("Enter customer number ? ")

      If strCustomerID = "" Then

      Exit Function

      End If

      f.RecordsetClone.FindFirst "CustomerID = " & strCustomerID

      If f.RecordsetClone.NoMatch Then

      MsgBox "customer " & strCustomerID & " does not exist!!"

      f.Bookmark = strBookmark

      Else

      f.Bookmark = f.RecordsetClone.Bookmark

      End If

      DoCmd.SetWarnings True

      End Function

      #1; Tue, 11 Dec 2007 20:53:00 GMT
    • Hi

      I have a nuber of issues with code:-

      1) I assume that customerID is numeric, if so why Dim strCustomerID As String. If it is a string the why not "CustomerID = '" & strCustomerID & "'"

      2) - I do not see where strBookmark has been asigned a value (?) so f.Bookmark = strBookmark is not valid ?

      I think pehaps 2) is your problem, if so the try inserting this line before the FindFirst method.

      strBookmark = f.RecordsetClone.BookMark

      Does that help ?

      MTB

      #2; Tue, 11 Dec 2007 20:54:00 GMT
    • 1) Why would you assume that customerID is numeric? ID "numbers" are seldom really numbers; they're not used for calculations!

      2) I may be wrong, but as I read the post, "When the customer does not exist, I get the error not a valid bookmark" means that if there is a CustomerID that matches the input, the code works fine. His request is really asking how to avoid the Access error message, which DoCmd.SetWarnings False. His code already addresses the possibilty of no match with:

      If f.RecordsetClone.NoMatch Then

      MsgBox "customer " & strCustomerID & " does not exist!!"

      #3; Tue, 11 Dec 2007 20:55:00 GMT
    • Hi

      Sorry I made the wrong assupumtions (my IDs are always numeric, autonumber normally), but I assummed this based on the lack of apostrophies in the critertia !?

      I think SetWarning = False is just to stop irritating warnings and not meant for ignoring errors in code (but on error resume next is - in line error handling) !?

      Of course I could be wrong (again).

      I had also assumed that a current record existed and that a valid bookmark would be available for the current record, hence strBookmark = f.RecordsetClone.BookMark which would return you the the previous selected record.

      However, if there is no current record, ie no records or on a rew record, prior to the FindNext method exicution, then I suggest just removing the offending line of code (f.Bookmark = strBookmark).

      Why try to go to record you know is not there ?

      MTB

      #4; Tue, 11 Dec 2007 20:56:00 GMT
    • You're right in that "SetWarning = False is just to stop irritating warnings" which, as I understood it, was the complaint peljo had. "Not a valid bookmark" is true if a matching record doesn't exist. His msgbox addresses this situation, but he's also getting the standard Access error warning.
      #5; Tue, 11 Dec 2007 20:57:00 GMT
    • Hi Missinglinq

      I am not sure whether peljo has fixed this, but you are right, his msgbox does address the case when NoMatch is True. The error is then produced on the very next line by using the strBookmark variable, which is not a valid bookmark as it never having been assigned a value.

      The error message (not a warning message) will not be suppressed by DoCmd.SetWarning False (not on my machine anyway).

      So I suggest the offending line of code is removed. This will of course leave the RecordsetClone out of synch with the form records (but does it matter?).

      Perhaps peljo could let us know !

      MTB

      #6; Tue, 11 Dec 2007 20:58:00 GMT