Greetings
I am using the code below to try to update some tables and data. My problem is the code seem to only work on one line in the recordset the last line. I have put up to 10 records in the recordset and it still only does the last record.
Any help would be great as it is the last bit of the project and I am behind my deadline;
'============================
'Public Procedures
Public Sub UpdateQuestion()
Dim UpdateAnswer As Byte
Dim db1 As DAO.Database
Dim rst, R As DAO.Recordset
Dim volval As String
Dim rcount As String
UpdateAnswer = MsgBox("Is this " & _
"work note ready to update and complete Press" & vbCrLf & _
"YES to update and complete OR " & vbCrLf & "NO just " & _
"to update and close", vbQuestion + vbYesNo + _
vbDefaultButton1, "Update and Complete")
If UpdateAnswer = vbYes Then ' (Default)
If Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![StatusTransfer].Value = -1 Then 'do nothing
Else
Set db1 = CurrentDb
Set R = Me.Form.RecordsetClone 'create a recordset of the data
R.MoveFirst 'got to start somewhere
Do Until R.EOF ' start loop
Set rst = db1.OpenRecordset("tbl_Data_Vessels")
rst.Index = "PrimaryKey"
rst.Seek "=", Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![VesselID]
rst.Edit
volval = rst![Volume]
rst![Volume] = Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![FinalVolume]
If rst![Volume] < 0 Then
MsgBox ("Problem restoring original value"), vbCritical, gstrAppTitle
rst![Volume] = volval
rst.Update
rst.Close
Exit Sub
End If
rst![Status] = Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![Status]
rst![Updated] = [Forms]![frm_Data_JobOrder_Note]![EnterDate]
rst![LastOp] = [Forms]![frm_Data_JobOrder_Note]![JobNumber]
rst![ContentsOwner] = Forms![frm_Data_JobOrder_Note]![CompanyID]
rst.Update
rst.Close
R.MoveNext
Loop
Set db1 = Nothing
Set rst = Nothing
End Sub
Thanks
rbinder
I am using the code below to try to update some tables and data. My problem is the code seem to only work on one line in the recordset the last line. I have put up to 10 records in the recordset and it still only does the last record.
Any help would be great as it is the last bit of the project and I am behind my deadline;
'============================
'Public Procedures
Public Sub UpdateQuestion()
Dim UpdateAnswer As Byte
Dim db1 As DAO.Database
Dim rst, R As DAO.Recordset
Dim volval As String
Dim rcount As String
UpdateAnswer = MsgBox("Is this " & _
"work note ready to update and complete Press" & vbCrLf & _
"YES to update and complete OR " & vbCrLf & "NO just " & _
"to update and close", vbQuestion + vbYesNo + _
vbDefaultButton1, "Update and Complete")
If UpdateAnswer = vbYes Then ' (Default)
If Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![StatusTransfer].Value = -1 Then 'do nothing
Else
Set db1 = CurrentDb
Set R = Me.Form.RecordsetClone 'create a recordset of the data
R.MoveFirst 'got to start somewhere
Do Until R.EOF ' start loop
Set rst = db1.OpenRecordset("tbl_Data_Vessels")
rst.Index = "PrimaryKey"
rst.Seek "=", Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![VesselID]
rst.Edit
volval = rst![Volume]
rst![Volume] = Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![FinalVolume]
If rst![Volume] < 0 Then
MsgBox ("Problem restoring original value"), vbCritical, gstrAppTitle
rst![Volume] = volval
rst.Update
rst.Close
Exit Sub
End If
rst![Status] = Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![Status]
rst![Updated] = [Forms]![frm_Data_JobOrder_Note]![EnterDate]
rst![LastOp] = [Forms]![frm_Data_JobOrder_Note]![JobNumber]
rst![ContentsOwner] = Forms![frm_Data_JobOrder_Note]![CompanyID]
rst.Update
rst.Close
R.MoveNext
Loop
Set db1 = Nothing
Set rst = Nothing
End Sub
Thanks
rbinder
