Bookmark on Continuous Form

[Sash]

I'm using the following code to read through a continuous form and update the
table InvLocation. It works if the user does not update the first record on
the continuous form. How can I set the bookmark to always be the first
record on the continuous form? Or maybe I'm totally using bookmark
incorrectly.


Dim rstForm As DAO.Recordset
Dim rstDestination As DAO.Recordset
Dim dbs As DAO.Database

Set dbs = CurrentDb
Set rstForm = Me.RecordsetClone
Set rstDestination = dbs.OpenRecordset("InvLocation")

'CHECK RECORD SOURCE FOR RECORDS
If Not rstForm.BOF And Not rstForm.EOF Then
rstForm.MoveFirst
GoTo UpInvQty
Else
MsgBox "No Inventory Items to Add"
Exit Sub
End If

UpInvQty:
Do Until rstForm.EOF
If rstForm!INV_QTY > 1 Then
Me.Bookmark = rstForm.Bookmark
'SET FORM RECORDSET TO MAIN RECORDSET
'ADD NEW RECORD
rstDestination.addnew
rstDestination!OFFID = rstForm!ID
rstDestination!ITEM_NO = rstForm!ITEM_NO
rstDestination!DESCR = rstForm!DESCR
rstDestination!UM = rstForm!UM
rstDestination!PRICE = rstForm!PRICE
rstDestination!MFR = rstForm!MFR
rstDestination!MFRCat = rstForm!MFRCat
rstDestination!CONTAINS = rstForm!CONTAINS
rstDestination!CHANGE_MFR = rstForm!CHANGE_MFR
rstDestination!Inv_LOCATION = rstForm!Inv_LOCATION
rstDestination!Status = rstForm!Status
rstDestination!DateCounted = rstForm!DateCounted
rstDestination!INV_QTY = rstForm!INV_QTY
rstDestination!CountedBy = rstForm!CountedBy
rstDestination![5orUnder] = rstForm![5orUnder]
'UPDATE RECORD
rstDestination.Update
End If
rstForm.MoveNext
Loop

DoCmd.Close

Dim frmAdded As String
frmAdded = "frmAdded"
DoCmd.OpenForm frmAdded

 

[Tom van Stiphout]

On Wed, 14 Jan 2009 17:01:00 -0800, Sash

I don't immediately see anything wrong with the bookmark code. What
error do you receive if the user updates the first record? That should
have nothing to do with it. I am assuming you save the record if
needed before processing.

Conceptually I am not sure what you are trying to accomplish. Wouldn't
it be smarter and much faster to run an Append query to copy the data
to the destination table?
The other concern is that you appear to make an exact copy of a record
to a second table. Those tables apparently have the same layout. Is
that really a good idea? Often this type of design points to an Excel
mindset and not a relational database.

-Tom.
Microsoft Access MVP

 

[Sash]

I was able to fix this by putting a me.refresh before checking the inventory
field for an amount.

Without boring you with too many details, the database is set-up to count
inventory. I obtain an item master from another system. Basically, the user
goes to a cart and keys in the location which is pulled from the InvLocation
table and the form shows all items previously on that cart. I store the data
here, because if they forgot to count an item they can pull the location and
add to the quantity. Locations were captured during our last count, but are
not stored in the system housing the item master. If something is new to the
cart, they need to be able to look it up and add it to the location. The
same item can be in multiple locations, so I never want the item master
updated which is why I write the data to the InvLocation table.

I share your concern about the database design. No excuse, but I needed a
quick and easy way for users to count, had little to no time to build, and
wasn't even sure the group would continue to use this method. I knew when I
built it, I wasn't following Codd's model. If they continue to use this
approach for counting inventory, I will definately need to take a different
approach.

Thanks again! I'm always greatful for the knowledge shared on this site!!

On Wed, 14 Jan 2009 17:01:00 -0800, Sash

I don't immediately see anything wrong with the bookmark code. What
error do you receive if the user updates the first record? That should
have nothing to do with it. I am assuming you save the record if
needed before processing.

Conceptually I am not sure what you are trying to accomplish. Wouldn't
it be smarter and much faster to run an Append query to copy the data
to the destination table?
The other concern is that you appear to make an exact copy of a record
to a second table. Those tables apparently have the same layout. Is
that really a good idea? Often this type of design points to an Excel
mindset and not a relational database.

-Tom.
Microsoft Access MVP

I'm using the following code to read through a continuous form and update the
table InvLocation. It works if the user does not update the first record on
the continuous form. How can I set the bookmark to always be the first
record on the continuous form? Or maybe I'm totally using bookmark
incorrectly.


Dim rstForm As DAO.Recordset
Dim rstDestination As DAO.Recordset
Dim dbs As DAO.Database

Set dbs = CurrentDb
Set rstForm = Me.RecordsetClone
Set rstDestination = dbs.OpenRecordset("InvLocation")

'CHECK RECORD SOURCE FOR RECORDS
If Not rstForm.BOF And Not rstForm.EOF Then
rstForm.MoveFirst
GoTo UpInvQty
Else
MsgBox "No Inventory Items to Add"
Exit Sub
End If

UpInvQty:
Do Until rstForm.EOF
If rstForm!INV_QTY > 1 Then
Me.Bookmark = rstForm.Bookmark
'SET FORM RECORDSET TO MAIN RECORDSET
'ADD NEW RECORD
rstDestination.addnew
rstDestination!OFFID = rstForm!ID
rstDestination!ITEM_NO = rstForm!ITEM_NO
rstDestination!DESCR = rstForm!DESCR
rstDestination!UM = rstForm!UM
rstDestination!PRICE = rstForm!PRICE
rstDestination!MFR = rstForm!MFR
rstDestination!MFRCat = rstForm!MFRCat
rstDestination!CONTAINS = rstForm!CONTAINS
rstDestination!CHANGE_MFR = rstForm!CHANGE_MFR
rstDestination!Inv_LOCATION = rstForm!Inv_LOCATION
rstDestination!Status = rstForm!Status
rstDestination!DateCounted = rstForm!DateCounted
rstDestination!INV_QTY = rstForm!INV_QTY
rstDestination!CountedBy = rstForm!CountedBy
rstDestination![5orUnder] = rstForm![5orUnder]
'UPDATE RECORD
rstDestination.Update
End If
rstForm.MoveNext
Loop

DoCmd.Close

Dim frmAdded As String
frmAdded = "frmAdded"
DoCmd.OpenForm frmAdded