For Loop Not Working

T

TitaniaTiO2

Ok so I am spinning myself in circles.

Premise - I have a list of documents and a list of individuals. All
individuals are not trained to all documents. As a new history of a document
becomes effect, I need to document everyone's trainingcompletion dates for
that history.

I have the following tables:
tblDocument
DocumentNumber (key)
DocumentTitle

tblDocumentHistories
DocumentHistoryID (key)
DocumentNumber
DocumnetHistory

tblTrainingStatus
TrainingStatusID (key)
DocumentsForEachPersonID
DocumentHistoryID
TrainingDate
TrainingStatus

tblDocumentsForEachPerson
DocumentsForEachPersonID (key)
DocumentNumber
PersonID
DocumentActivity


I am working on one of my forms.
I want to be able to add a new document history to tblDocumentHistories (got
that) and add that same history to all the people who are trained to that
document (on tblTrainingStatus - here is where I am having problems)

Unfortunately the training date for all individuals for that document and
history are not the same.

Here is my code (no laughing at it... I know I probably went about things in
convoluted ways).


Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String
Dim DocumentHistoryID As String
Dim DocumentsForEachPersonID As Integer

MaxHistory = DMax("[DocumentHistory]", "[tblDocumentHistories]",
"[DocumentNumber] = '" & cmbDocumentNumber & "'")

strSQL = "SELECT tblDocumentHistories.DocumentNumber FROM
tblDocumentHistories"
strSQL = strSQL & " WHERE tblDocumentHistories.DocumentNumber = '" &
cmbDocumentNumber & "'"
strSQL = strSQL & " WHERE tblDocumentHistories.DocumentHistory = '"
& MaxHistory & "'"

OldDocumentHistoryID = DMax("[DocumentHistoryID]",
"[tblDocumentHistories]", "[DocumentNumber] = '" & cmbDocumentNumber & "'" &
" AND [DocumentHistory] = " & MaxHistory)

Set rs = CurrentDb.OpenRecordset("tblDocumentHistories", dbOpenTable,
dbAppendOnly)
rs.AddNew
rs!DocumentNumber = Me.cmbDocumentNumber
rs!DocumentHistory = Me.txtDocumentHistory
DocumentHistoryID = rs!DocumentHistoryID
rs.Update

Set rs = CurrentDb.OpenRecordset("tblTrainingStatus", dbOpenTable,
dbAppendOnly)

TotalRecords = DCount("[TrainingStatusID]", "tblTrainingStatus")
Text19 = TotalRecords

For i = 0 To TotalRecords
If rs!DocumentHistoryID = OldDocumentHistoryID Then
DocumentsForEachPersonID = rs!DocumentsForEachPersonID
rs.AddNew
rs!DocumentsForEachPersonID = DocumentForEachPersonID
rs!DocumentHistoryID = DocumentHistoryID
rs!TrainingStatus = "Training Document Issued"
End If
Next i


' stDocName = "rptNewSOPHistoryTrainingSheet"
' DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click
Resume
End Sub



The code executes but nothing in the For statement actually happens...no
additional lines are added to tblTrainingStatus.

What am I doing wrong?

Thanks!

Titania
 
G

Graham Mandeno

Hi Titania

I haven't been through your code with a fine-toothed comb, but the most
glaring error is that you have omitted rs.Update from you loop, so none of
your new TrainingStatus records will be saved.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

TitaniaTiO2 said:
Ok so I am spinning myself in circles.

Premise - I have a list of documents and a list of individuals. All
individuals are not trained to all documents. As a new history of a
document
becomes effect, I need to document everyone's trainingcompletion dates for
that history.

I have the following tables:
tblDocument
DocumentNumber (key)
DocumentTitle

tblDocumentHistories
DocumentHistoryID (key)
DocumentNumber
DocumnetHistory

tblTrainingStatus
TrainingStatusID (key)
DocumentsForEachPersonID
DocumentHistoryID
TrainingDate
TrainingStatus

tblDocumentsForEachPerson
DocumentsForEachPersonID (key)
DocumentNumber
PersonID
DocumentActivity


I am working on one of my forms.
I want to be able to add a new document history to tblDocumentHistories
(got
that) and add that same history to all the people who are trained to that
document (on tblTrainingStatus - here is where I am having problems)

Unfortunately the training date for all individuals for that document and
history are not the same.

Here is my code (no laughing at it... I know I probably went about things
in
convoluted ways).


Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String
Dim DocumentHistoryID As String
Dim DocumentsForEachPersonID As Integer

MaxHistory = DMax("[DocumentHistory]", "[tblDocumentHistories]",
"[DocumentNumber] = '" & cmbDocumentNumber & "'")

strSQL = "SELECT tblDocumentHistories.DocumentNumber FROM
tblDocumentHistories"
strSQL = strSQL & " WHERE tblDocumentHistories.DocumentNumber = '"
&
cmbDocumentNumber & "'"
strSQL = strSQL & " WHERE tblDocumentHistories.DocumentHistory = '"
& MaxHistory & "'"

OldDocumentHistoryID = DMax("[DocumentHistoryID]",
"[tblDocumentHistories]", "[DocumentNumber] = '" & cmbDocumentNumber & "'"
&
" AND [DocumentHistory] = " & MaxHistory)

Set rs = CurrentDb.OpenRecordset("tblDocumentHistories", dbOpenTable,
dbAppendOnly)
rs.AddNew
rs!DocumentNumber = Me.cmbDocumentNumber
rs!DocumentHistory = Me.txtDocumentHistory
DocumentHistoryID = rs!DocumentHistoryID
rs.Update

Set rs = CurrentDb.OpenRecordset("tblTrainingStatus", dbOpenTable,
dbAppendOnly)

TotalRecords = DCount("[TrainingStatusID]", "tblTrainingStatus")
Text19 = TotalRecords

For i = 0 To TotalRecords
If rs!DocumentHistoryID = OldDocumentHistoryID Then
DocumentsForEachPersonID = rs!DocumentsForEachPersonID
rs.AddNew
rs!DocumentsForEachPersonID = DocumentForEachPersonID
rs!DocumentHistoryID = DocumentHistoryID
rs!TrainingStatus = "Training Document Issued"
End If
Next i


' stDocName = "rptNewSOPHistoryTrainingSheet"
' DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click
Resume
End Sub



The code executes but nothing in the For statement actually happens...no
additional lines are added to tblTrainingStatus.

What am I doing wrong?

Thanks!

Titania
Click to expand...
 
Top