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/AppIssueBadWord.html for more details.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
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
