You should be able to use a simple INSERT INTO query:
Dim strSQL As String
strSQL = "INSERT INTO tblLog (MemberID, LogDate, LogNote) " & _
"SELECT MemberID, " & Format(Date(), "#\mm\/dd\/yyyy\#") & _
", '" & Me.MyComboBox " & "' FROM qrySelectMembers"
CurrentDb.Execute strSQL, dbFailOnError
Remember that given a choice between a simple SQL statement and a VBA loop,
the SQL statement will almost always be more efficient.
For the sake of completeness, though, I'll show a couple of ways in which
you could use VBA. One is to create a recordset based on qrySelectMembers,
loop through it, and run an INSERT INTO statement for each record:
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String
Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("qrySelectMembers")
Do Until rsCurr.EOF
strSQL = "INSERT INTO tblLog (MemberID, LogDate, LogNote) " & _
"VALUES(" & rsCurr!MemberID & ", " & _
Format(Date(), "\#mm\/dd\/yyyy\#") & ", '" & _
Me.MyComboBox & ")"
dbCurr.Execute strSQL, dbFailOnError
rsCurr.MoveNext
Loop
rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing
Another would open a recordset based on tblLog and add recordset to that
recordset:
Dim dbCurr As DAO.Database
Dim rsCurr1 As DAO.Recordset
Dim rsCurr2 As DAO.Recordset
Dim strSQL As String
Set dbCurr = CurrentDb()
Set rsCurr1 = dbCurr.OpenRecordset("qrySelectMembers")
strSQL = "SELECT MemberID, LogDate, LogNote FROM tblLog " & _
"WHERE False"
Set rsCurr2 = dbCurr.OpenRecordset(strSQL)
Do Until rsCurr1.EOF
rsCurr2.AddNew
rsCurr2!MemberID = rsCurr1!MemberID
rsCurr2!LogDate = Date()
rsCurr2!LogNote = Me.MyComboBox
rsCurr2!Update
rsCurr1.MoveNext
Loop
rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing
Note that I renamed two of the fields in tblLog in my sample code. Both Date
and Note are reserved words, and shouldn't be used for your own purposes.
See what Allen Browne has at
http://www.allenbrowne.com/Ap****ueBadWord.html
for more details.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Tom" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi
>
> I have a select query, qrySelectMembers based on a table tblMembers
> wherein 1 field is named MemberID.
>
> Within that query I am able select members by different criteria.
>
> A second table, tblLog has 1 field named MemberID, 1 field named date & 1
> field named Notes
>
> What I want to achieve is to loop through qrySelectMembers and for each
> MemberID to enter that MemberID, today's date, and in the Notes field the
> name of a document [which has been selected from a combo box on the
> current form]
>
> A code example on how to achieve this would be very much appreciated.
>
> TIA
>
> Tom
>
Similar Threads
