Loop SQL

[B. Edwards]

Something like:

"INSERT INTO tblVoids
(VoidId, VoidCheckID,
VoidItemID,VoidAmount,VoidReasonID,VoidManagerID,VoidQTY)
SELECT tblCheckDetails.VoidId, tblCheckDetails.SalesId,
tblCheckDetails.ItemId, tblCheckDetails.Amount,
tblCheckDetails.ReasonId, tblCheckDetails.ManId, 1 FROM tblCheckDetails
WHERE tblCheckDetails.SalesId = " & Forms!frmFXVoidItem!txtSalesId

 

[George Nicholson]

For every record in tblCheckDetails that meets the SalesID criteria, the
following would add one record to tblVoids.

This is what you asked for (I think...), but I have no idea if this is
really what you want to do since I have no idea what the relationships
between those tables are or how they relate to the 2 forms you are pulling
data from.

HTH,


Dim db as DAO.Recordset
Dim rs as DAO.Database

Set db = CurrentDB
Set rs = db.OpenRecordset("SELECT [SalesID] FROM tblCheckDetails WHERE
[SalesID] = " & Forms!frmFXVoidItem!TxtSalesID)

Do While Not rs.EOF
CurrentDb.Execute "INSERT......<your code>

rs.MoveNext
Loop

 

[Wolfgang Kais]

Dear DS.

I need to Loop this statement until all records in the tblCheckDetails
that have this SalesID (Forms!frmFXVoidItem!TxtSalesID) table are
added to the tblVoids...How would I do this?

CurrentDb.Execute "INSERT INTO tblVoids (VoidID,VoidCheckID," & _
"VoidItemID,VoidAmount,VoidReasonID,VoidManagerID,VoidQTY)" & _
"Values(" & Forms!frmFXVoidItemReason!TxtVoidID & "," & _
Forms!frmFXVoidItem!TxtSalesID & "," & _
Forms!frmFXVoidItem!TxtItemID & "," & _
Forms!frmFXVoidItem!TxtAmount & "," & _
Forms!frmFXVoidItemReason!TxtReasonID & "," & _
Forms!frmFXVoidItem!TxtManID & "," & 1 & ")"

Probably, you want something like this:
CurrentDb.Execute "INSERT INTO tblVoids (VoidID,VoidCheckID," & _
"VoidItemID,VoidAmount,VoidReasonID,VoidManagerID,VoidQTY)" & _
" SELECT VoidID,SalesID,ItemID,Amount,ReasonID,ManID,1 FROM" & _
" tblCheckDetails WHERE SalesID=" & Forms!frmFXVoidItem!TxtSalesID

 

[DS]

I need to Loop this statement until all records in the tblCheckDetails
that have this SalesID (Forms!frmFXVoidItem!TxtSalesID) table are added
to the tblVoids...How would I do this?
Thanks
DS

CurrentDb.Execute "INSERT INTO tblVoids
(VoidID,VoidCheckID,VoidItemID,VoidAmount,VoidReasonID,VoidManagerID,VoidQTY)
" & _
"Values(" & Forms!frmFXVoidItemReason!TxtVoidID & "," &
Forms!frmFXVoidItem!TxtSalesID & "," & Forms!frmFXVoidItem!TxtItemID &
"," & Forms!frmFXVoidItem!TxtAmount & "," &
Forms!frmFXVoidItemReason!TxtReasonID & "," &
Forms!frmFXVoidItem!TxtManID & "," & 1 & ")"
'

 

[DS]

Something like:

"INSERT INTO tblVoids
(VoidId, VoidCheckID,
VoidItemID,VoidAmount,VoidReasonID,VoidManagerID,VoidQTY)
SELECT tblCheckDetails.VoidId, tblCheckDetails.SalesId,
tblCheckDetails.ItemId, tblCheckDetails.Amount,
tblCheckDetails.ReasonId, tblCheckDetails.ManId, 1 FROM tblCheckDetails
WHERE tblCheckDetails.SalesId = " & Forms!frmFXVoidItem!txtSalesId

This works fine! Now to the next step!
Thanks
DS

 

[DS]

Dear DS.




Probably, you want something like this:
CurrentDb.Execute "INSERT INTO tblVoids (VoidID,VoidCheckID," & _
"VoidItemID,VoidAmount,VoidReasonID,VoidManagerID,VoidQTY)" & _
" SELECT VoidID,SalesID,ItemID,Amount,ReasonID,ManID,1 FROM" & _
" tblCheckDetails WHERE SalesID=" & Forms!frmFXVoidItem!TxtSalesID

Thanks Wolfgang
DS