Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / July 2007

Tip: Looking for answers? Try searching our database.

Loop SQL

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
DS - 25 Jul 2007 20:55 GMT
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 - 25 Jul 2007 21:14 GMT
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

>I need to Loop this statement until all records in the tblCheckDetails that
>have this SalesID (Forms!frmFXVoidItem!TxtSalesID) table are added to the
[quoted text clipped - 11 lines]
> & "," & 1 & ")"
>     '
DS - 25 Jul 2007 23:43 GMT
> Something like:
>
[quoted text clipped - 21 lines]
>>& "," & 1 & ")"
>>    '

This works fine!  Now to the next step!
Thanks
DS
George Nicholson - 25 Jul 2007 21:16 GMT
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

>I need to Loop this statement until all records in the tblCheckDetails that
>have this SalesID (Forms!frmFXVoidItem!TxtSalesID) table are added to the
[quoted text clipped - 11 lines]
> & "," & 1 & ")"
>     '
Wolfgang Kais - 25 Jul 2007 21:36 GMT
Dear DS.

> I need to Loop this statement until all records in the tblCheckDetails
> that have this SalesID (Forms!frmFXVoidItem!TxtSalesID) table are
[quoted text clipped - 8 lines]
> 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

Signature

Regards,
Wolfgang

DS - 26 Jul 2007 03:02 GMT
> Dear DS.
>
[quoted text clipped - 16 lines]
> " SELECT VoidID,SalesID,ItemID,Amount,ReasonID,ManID,1 FROM" & _
> " tblCheckDetails WHERE SalesID=" & Forms!frmFXVoidItem!TxtSalesID

Thanks Wolfgang
DS
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.