3159: Not a valid bookmark (1 Viewer)

[robsant]

I am stumped with this one, I have been trying to work out what is wrong for a few hours, but need some help please.

The code is as follows.

Private Sub TxtGoto_AfterUpdate()
On Error GoTo Err_Handler
Me.StockSuccess.Visible = False
MsgBox "before ifs"

If (TxtGoto & vbNullString) = vbNullString Then Exit Sub

Dim db As DAO.Database
Dim rsCheckStock As DAO.Recordset
Set db = CurrentDb
Set rsCheckStock = db.OpenRecordset("tblStockDetail")
rsCheckStock.FindFirst "[BarcodeNumber]=" & TxtGoto
MsgBox rsCheckStock!BarcodeNumber.Value

If rsCheckStock.NoMatch Then
MsgBox "Sorry, no such record '" & TxtGoto & "' was found in stock. Check the Production Database.", vbOKOnly + vbInformation: TxtGoto.SetFocus
Else
MsgBox "set bookmark"
Me.Recordset.Bookmark = rsCheckStock.Bookmark

'Check item should be in this location
If (rsCheckStock!Location.Value) <> (Me!cbLocationCode.Value) Then
Me.StockSuccess.Caption = "Item with Barcode : '" & Me.BarcodeNumber & "' should not be in this location, Move it into Quaranteed, and the put it into the correct location! "
Me.StockSuccess.ForeColor = vbRed
Me.StockSuccess.BorderColor = vbRed
Me.StockSuccess.Visible = True
MsgBox "Sorry, the Barcode Number '" & Me.BarcodeNumber & "' Is in the Wrong Location!.", vbOKOnly + vbInformation
TxtGoto = Null
TxtGoto.SetFocus
rsCheckStock.Close
Set rsCheckStock = Nothing
db.Close
Set db = Nothing
Exit Sub
ElseIf (rsCheckStock!StockTakeRef.Value) = (Me!TxtStockTakeRef.Value) Then
Me.StockSuccess.Caption = "Item with Barcode : '" & Me.BarcodeNumber & "' has already been entered onto this stock take!! "
Me.StockSuccess.ForeColor = vbRed
Me.StockSuccess.BorderColor = vbRed
Me.StockSuccess.Visible = True
MsgBox "Sorry, the Barcode Number '" & Me.BarcodeNumber & "' has already been entered onto this stock take!.", vbOKOnly + vbInformation
TxtGoto = Null
TxtGoto.SetFocus
rsCheckStock.Close
Set rsCheckStock = Nothing
db.Close
Set db = Nothing
Exit Sub
ElseIf (rsCheckStock!SoldDate.Value & vbNullString) <> vbNullString Or (rsCheckStock!CustomerSoldTo.Value & vbNullString) <> vbNullString Or _
(rsCheckStock!DespatchNote.Value & vbNullString) <> vbNullString Then
Me.StockSuccess.Caption = "Item with Barcode : '" & Me.BarcodeNumber & "' has already been sold, It should not be here! "
Me.StockSuccess.ForeColor = vbRed
Me.StockSuccess.BorderColor = vbRed
Me.StockSuccess.Visible = True
MsgBox "Sorry, the Barcode Number '" & Me.BarcodeNumber & "' has already been sold, contact Sales!.", vbOKOnly + vbInformation
TxtGoto = Null
TxtGoto.SetFocus
rsCheckStock.Close
Set rsCheckStock = Nothing
db.Close
Set db = Nothing
Exit Sub
Else
'update StockDetails file with StockTakeRef
rsCheckStock.Edit
rsCheckStock!StockTakeRef.Value = Me.TxtStockTakeRef
rsCheckStock.Update
Me.StockSuccess.Caption = "Item with Barcode : '" & Me.BarcodeNumber & "' has been added to the Stock Take!"
Me.StockSuccess.ForeColor = vbGreen
Me.StockSuccess.BorderColor = vbGreen
Me.StockSuccess.Visible = True
TxtGoto = Null
TxtGoto.SetFocus
End If
End If
rsCheckStock.Close
Set rsCheckStock = Nothing
db.Close
Set db = Nothing

Exit_Err:
Exit Sub
Err_Handler:
MsgBox Err.Number & Err.Description
Resume Exit_Err
TxtGoto = Null
End Sub


I am sure this was working, but it was late at night when I was writing it, so cant quite remember. If the txtGoto doesn't exist in the tblStockDetail , if works to that point. However as soon as txtGoto exists I get the 3159: Not a valid bookmark error.

Any help will be fantastic.

Thanks,

Rob.

 

[Cronk]

On what line of your code does the error occur?

 

[robsant]

I think it is on the

Me.Recordset.Bookmark = rsCheckStock.Bookmark

But I am very new to this so not 100%

Assume I know very little and then you wont be far off! I have only been using access for 3-4 weeks..

 

[Cronk]

The first step in debugging is to find exactly where the problem arises.

Put a break point in your code by placing the cursor on a line at the beginning of the routine, say on the line Set db = CurrentDb, and then press F9 (the function 9 key) .Then step through your code line by line by successively pressing the F8 key.

Incidentally, I always reference controls in code. In your case, don't just use txtGoto, use Me.txtGoto

Also do you have the Declaration of variables set on? It should be the default but is not. Do this by Tools | Options in the code menu bar and check Require Variable Declaration. This will point up a lot of code typos early in the piece.

 

[robsant]

Great tips thanks.

I never could get debug to work, so have been using msgbox's to try to track down where code problems were. Only realised when you pointed it out that I had access special keys unticked and error trapping set to just unhandled errors rather than all errors! I have now removed my msgbox error trapping from the code!

I have replaced the txtGoto to Me.TxtGoto, ticked required variables Declaration, and stepped though.

I am definitely getting the error at

Me.Recordset.Bookmark = rsCheckStock.Bookmark

 

[spikepl]

Your code is built on an invalid assumption as to how .Bookmark actually can be used. Read the help file /documentation.

 

[robsant]

I am still confused, I tried to copy the code from elsewhere where I have used it successfully, then modified it slightly. Elsewhere I used:

If (TxtGoto & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[BarcodeNumber]=" & TxtGoto
If rs.NoMatch Then
MsgBox "Sorry, no such record '" & TxtGoto & "' was found.", vbOKOnly + vbInformation: TxtGoto.SetFocus
Else
Me.Recordset.Bookmark = rs.Bookmark


The only difference I can see is with the code that has the problem, I have had to set the recordset differently?

Is there another way I should be doing it so as not to create the error?

 

[spikepl]

I too am confused. You asked for advice, I advised you what to do, but you evidently did not do it

 

[robsant]

Sorry, I didn't understand what you meant. I read the help file / documentation , also have tried to read several sections of different access books, searched the internet, tried compacting and repairing, tried changing the sets to use an sql rather than findfirst, all prior to posting this.

When you put " your code is built on an invalid assumption as to how .bookmark can be used"

I don't know what you mean, thus the reason for my statement "i am confused" is there any chance you can elaborate?

As I have said earlier in this post, assume I know very little about access vba and you won't be far wrong.

 

[robsant]

What also baffles me is that I am 95% sure this was working before I changed a couple of fields on the form. Could that have caused this ? Or what else could I have changed that stopped this from working ?

 

[Cronk]

Use the form's recordset clone to get the bookmark, not on a recordset that probably does not have the same bookmark set, especially when you change form fields.

 

[robsant]

Solved it thanks Cronk.

Your suggestion, with a bit of tidying of the code, and modifying the control source on the form fixed it.