Hey guys,
I have had a request to change a few features on this database I've been working on. I have been asked to
provide a way to add more time to a Maintenance Event record.
I have started with the click event below, it creates a record in the "tbl_AVUM_Scored_Data" the IETM_ID
has time assigned to it from a table called "tbl_AVUM_Test_BT", these are all fixed time that don't change.
As a Maintenance Event is reviewed a task from table tbl_AVUM_Test_BT is selected with the assigned times.
There are instances when a Maintenance Event may have taken longer or shorter depending on the situation,
the code below is an attempt to create a record for the Maintenance Event and to create a related record with
the added times for that instance of the task.
Essentially I am creating a record and an edit for the record at the same time. I am not clear how to
reference the Record_ID of the Maint Event before it gets created to use it in the tbl_SME_AVUM_Edit table.
Code:
tbl_AVUM_SCORED_Data
Record_ID AutoNumber
(Pk) EI_ID Text
(Pk) Event_Date date/time
(Pk) Event_No Number
(Pk) Sys_Code Text
IETM_ID Number
Code:
tbl_SME_AVUM_Edit
(Fk) AVUM_SD_Record_ID Number
MOS_ID Text
Time Number
Comments Text
Date Added date/time
Code:
Private Sub btnSubmitSMEUPDATE_Click()
Dim strItems As String
Dim intItems As Integer
Dim varItem As Variant
Dim var2Item As Variant
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
On Error GoTo ErrorHandler
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("tbl_AVUM_Scored_Data", dbOpenDynaset)
Set rs2 = db.OpenRecordset("tbl_SME_AVUM_Edit", dbOpenDynaset)
Set ctl = Forms!frm_TaskList_Popup!List2
For Each varItem In ctl.ItemsSelected
rs1.AddNew
rs1!EI_ID = Forms!frm_Acft_Score!EI_ID
rs1!EVENT_DATE = Forms!frm_Acft_Score!EVENT_DATE
rs1!EVENT_NO = Forms!frm_Acft_Score!EVENT_NO
rs1!SYS_CODE = Forms!frm_Acft_Score!SYS_CODE
rs1!IETM_ID = ctl.ItemData(varItem)
rs1.Update
Next varItem
Set ctl2 = Forms!frm_SME_Update!lboAVUMSMEUpdate
For Each var2Item In ctl2.ItemSelected
rs2.AddNew
rs2!Record_ID = rs1!Record_ID
rs2!MOS_ID = Forms!frm_SME_Update!cboSMEMOSo1
rs2!Time = Forms!frm_SME_Update!txboSMETimeo1
rs2!Comments = rs2!MOS_ID = Forms!frm_SME_Update!txboSMEAVUMComments
rs2.Update
Next var2Item
ExitHandler:
Set strItem = Nothing
Set intItem = Nothing
Set ctl = Nothing
Exit Sub
ErrorHandler:
Select Case Err
Case Else
MsgBox Err.Description
DoCmd.Hourglass False
Resume ExitHandler
End Select
End Sub
I hope this is clear. Please ask questions for clarity.