Loop SQL

  • Thread starter Thread starter B. Edwards
  • Start date Start date
B

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
 
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
 
Dear DS.

DS said:
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 & ")"
Click to expand...

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
 
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 & ")"
'
 
B. Edwards said:
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
Click to expand...
This works fine! Now to the next step!
Thanks
DS
 
Wolfgang said:
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
Click to expand...
Thanks Wolfgang
DS
 
Back
Top