Append Field from Continuous Form

[Sash]

I'm trying to append fields where the field INV_QTY contains something
greater than 1. At the moment, I'm only attempting to write two fields until
I can get the code working and then I'll add the remainder.

The form works from a query that prompts the user to key in an item
description and the returns any items containing any part of that description
where the location is null. The user then completes a quantity and location
for inventory purposes.

My issue is that while I'm appending the record correctly, I'm also updating
the existing record -- which I DON'T want to do, because it will then be
eliminated from the next item search.




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")

Do Until rstForm.EOF

If rstForm!INV_QTY > 1 Then
rstDestination.addnew
rstDestination!ITEM_NO = rstForm!ITEM_NO
rstDestination!DateCounted = rstForm!DateCounted
rstDestination.Update
Debug.Print rstForm!ITEM_NO
Debug.Print rstForm!INV_QTY
Me!ITEM_NO = Null
Me!DateCounted = Null
rstForm.MoveNext
Else
rstForm.MoveNext
End If

Loop

DoCmd.Close

 

[Steve Sanford]

Your code is doing just what you told it to do. I've modified your code a
little...and added comments.

If you don't want to change the main recordset, comment (or delete) the 2
lines that set the fields to NULL.

If I am not understanding your problem, please add more info.

'modified code
'------------------------------------
Dim dbs As DAO.Database
Dim rstSource As DAO.Recordset
Dim rstDestination As DAO.Recordset

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

'Check record source for records
If not rstSource.BOF and Not rstSource.EOF Then
rstSource.MoveFirst
Else
Msgbox "No records"
Exit Sub
End If

Do Until rstForm.EOF

If rstSource!INV_QTY > 1 Then
'add new record
rstDestination.addnew
rstDestination!ITEM_NO = rstSource!ITEM_NO
rstDestination!DateCounted = rstSource!DateCounted
'save record
rstDestination.Update

Debug.Print rstSource!ITEM_NO
Debug.Print rstSource!INV_QTY

' this CHANGES the main form recordset
Me!ITEM_NO = Null
Me!DateCounted = Null

End If

rstSource.MoveNext

Loop

' close the form???
DoCmd.Close
'------------------------------------


HTH

 

[Sash]

Steve,
Thanks for your input. The only part that doesn't seem to be working is
where I set the main recordset to null. What is happening is that the
records are being written to InvLocation just fine, but the original
recordset is also being updated. I want to keep the original recordset
intact with no updates or changes.

 

[Steve Sanford]

Sash,

OK. You do understand that ME! refers to the main recordset??

Using these two lines
Me!ITEM_NO = Null
Me!DateCounted = Null

is setting the main recordset fields to NULL!! If you don't want to
change the main recordset, delete the lines.

Please note that you are stepping thru the recordset "rstSource", but
the mainform record is not necessarily the same record as the recordset
"rstSource", unless you set the mainform bookmark the same as the
recordset "rstSource" bookmark.

Once again, "Me!ITEM_NO = NULL" sets the field NULL in main form current
record at whatever record happens to be the current record.

If, in the code, you have a line "rstSource!ITEM_NO=NULL", it also sets
the field in the main form recordset to NULL, but for the current record
in the recordset "rstSource".



"Me!ITEM_NO = NULL"

or

"rstSource!ITEM_NO=NULL"


will change the original recordset!!


HTH