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
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
"Sash" wrote:
[Quoted Text] > 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
>
|