I set up a test db with the same object names as yours. The error message
you were getting must have come from a problem with the spelling of a field
name or something. I got the same error at first, then i rewrote the code and
it no longer came up. However, I was not able to find any way to put a Null
value in DateCompleted (I've never tried to insert a Null in a date field
before
but apparently it can't be done). Since you seem to want to leave that field
blank, I just decided to leave it out of the code altogether
Anyway, the following code is tested and does work;
Private Sub UpdateTrain_Click()
On Error GoTo Err_UpdateTrain_Click
Dim db As Database
Dim rs As DAO.Recordset
Dim UpdateTraining As String
Set db = CurrentDb
Set rs = Me.RecordsetClone
rs.MoveFirst
Do Until rs.EOF
MsgBox (rs!chkTrain)
If rs!chkTrain = True Then
MsgBox ("loop")
UpdateTraining = "Insert Into EmpDocStatus " & _
"(EmpName, EmpEmail, DocID, Revision, " & _
"DateAssigned, JobFunc) Values ('" & rs!EmpName & _
"', '" & rs!EmpEmail & "', '" & rs!DocID & _
"', '" & rs!Revision & "', #" & Date & "#, '" & rs!JobFunc & "')"
db.Execute UpdateTraining, dbFailOnError
End If
rs.MoveNext
Loop
Exit_UpdateTrain_Click:
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
Err_UpdateTrain_Click:
MsgBox Err.Description
Resume Exit_UpdateTrain_Click
End Sub
--
_________
Sean Bailey
tracktraining said:
in addition to the error message appear, i didn't get the msgbox(rs!chktrain)
or the msgbox("loop"). So it may not have gone thru the Do Until rs.EOF loop.
--
Learning
tracktraining said:
that is correct that DocID is a textfield.
I am still getting the same error message when i click on the updatetrain
button.
--
Learning
:
Try this;
Private Sub UpdateTrain_Click()
On Error GoTo Err_UpdateTrain_Click
Dim UpdatedRev As String
Dim db As Database
Dim rs As DAO.Recordset
Dim UpdateTraining As String
Set db = CurrentDb
Set rs = Me.RecordsetClone
rs.MoveFirst
Do Until rs.EOF
MsgBox (rs!chkTrain)
If chkTrain = True Then
MsgBox ("loop")
UpdateTraining = "INSERT INTO EmpDocStatus (" & _
"EmpName, " & _
"EmpEmail, " & _
"DocID, " & _
"Revision, " & _
"DateAssigned, " & _
"DateCompleted, " & _
"JobFunc)" & _
" VALUES ('" & _
rs!EmpName & "', '" & _
rs!EmpEmail & "', '" & _
rs!DocID & "', '" & _
rs!txtRev & "', #" & Date & "#, '" & NULL & "', '" & _
rs!JobFunc + "')"
db.Execute UpdateTraining, dbFailOnError
End If
rs.MoveNext
Loop
Exit_UpdateTrain_Click:
Exit Sub
Err_UpdateTrain_Click:
MsgBox Err.Description
Resume Exit_UpdateTrain_Click
End Sub
The above assumes DocID is a text field. If not you'll need to remove the
single quotes around rs!DocID
--
_________
Sean Bailey
:
not any particular line. the error occurred when i clicked the UpdateTrain
BUTTON (located in the form header).
--
Learning
:
On what line does the error occur?
--
_________
Sean Bailey
:
I get the following error with your code and mine:
Item not found in this collection.
Can you please let me know what that mean and how to fix it?
This is my code based on what you have:
Private Sub UpdateTrain_Click()
On Error GoTo Err_UpdateTrain_Click
Dim UpdatedRev As String
Dim db As Database
Dim rs As DAO.Recordset
Dim UpdateTraining As String
Set db = CurrentDb
Set rs = Me.RecordsetClone
rs.MoveFirst
Do Until rs.EOF
MsgBox (rs!chkTrain)
If chkTrain = True Then
MsgBox ("loop")
UpdateTraining = "INSERT INTO EmpDocStatus (" & _
"EmpName, " & _
"EmpEmail, " & _
"DocID, " & _
"Revision, " & _
"DateAssigned, " & _
"DateCompleted, " & _
"JobFunc)" & _
" VALUES ('" & _
rs!EmpName + "','" & _
rs!EmpEmail + "','" & _
rs!DocID + "','" & _
rs!txtRev + "', Date (), NULL, '" & _
rs!JobFunc + "')"
db.Execute (UpdateTraining)
End If
rs.MoveNext
Loop
Exit_UpdateTrain_Click:
Exit Sub
Err_UpdateTrain_Click:
MsgBox Err.Description
Resume Exit_UpdateTrain_Click
End Sub
--
Learning
:
Try this;
Dim rs As Recordset, strSQL As String
Set rs = Me.RecordsetClone
With rs
Do Until .EOF
If !chkTrain = True Then
strSQL = "INSERT INTO EmpDocStatus (EmpEmail, DocID, Revision," _
& " DateAssigned, DateCompleted, JobFunc) VALUES (""" _
& rs!EmpEmail & """, " & rs!DocID & ", """ & rs!Revision _
& """, #" & rs!DateAssigned & "#, #" & rs!DateCompleted _
& "#, """ & rs!JobFunc & """)"
Currentdb.Execute strSQL, dbFailOnError
End If
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
You may need to adjust for line wrap and I've made some assumptions
about the data types of your fields, i.e.-
DocID = Number
EmpEmail, Revision, JobFunc = Text
DateAssigned, DateCompleted = Date/Time
If that's not correct you'll have to adjust as needed.
--
_________
Sean Bailey
:
I meant to write: My problem is that i don't know how to [write] the insert
into value statement.
--
Learning
:
Hi All,
I am trying to write this code and i am in need of help.
In my UPDATE form I have a checkbox on each row. When an old document is
updated with a new revision then this is the user's process:
1) User type in the document with the old revision in the search fields
and filter the records.
2) List of employee will appear
3) The checkboxs are default to true (i.e. checked)
4) user enters the new revision code in a field in the form header
5) user uncheck the boxes in the row of the employee who doesn't need to
read the updated revision of the document
6) add other employees who need to read the document in the *(star) line
(empname, empemail, document#, revision)
7) user click UPDATE button
8) rows will be added to empdocstatus table where the box is checked from
the UPDATED form, and any other additional *(star) rows that were added.
My problem is that i don't know how to read the insert into value statement.
This is what I have so far:
Private Sub UpdateTrain_Click()
On Error GoTo Err_UpdateTrain_Click
Dim UpdatedRev As String
Dim db As Database
Dim rs As DAO.Recordset
Dim UpdateTraining As String
Set db = CurrentDb
rs = Me.RecordsetClone
Do Until rs.EOF
If chkTrain = True Then
UpdateTraining = "INSERT INTO EmpDocStatus (EmpEmail, DocID,
Revision, DateAssigned, DateCompleted, JobFunc)" & _'
VALUES ......................................
End If
