I have a subform linked to a main form in access that updates a main table based on the H_ID.
See Code Below:
Everything works flawless EXCEPT, when I am done updating and click my button the code is behind (cmdAddReason_Click) it works.
The issue is before the code starts and I click from my main form to my subform a new record a blank line is added and assigned a record ID (Example we will say I clicked new member and record ID 123 is created).
The code as it functions below updates each record id as a selection is made, however, the original record ID (Record ID 123) is blank.
Any suggestions on how to fix this?
My table will look like this:
Date Record ID H ID Reason Type Reason
1/11/18 123 M12
1/11/18 124 M12 Other Other
1/11/18 125 M12 Consult Med Review
1/11/18 126 M12 Follow-up Reschedule
Private Sub cmdAddReason_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
Set db = CurrentDb()
Set rs = db.OpenRecordset("DSBP Member Case Information", dbOpenDynaset, dbAppendOnly)
'Make sure a selection has been made
If Me.lstReason.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Reason"
'Add selected value(s) to table
Set ctl = Me.lstReason
For Each varItem In ctl.ItemsSelected
rs!Reason_ID = ctl.ItemData(varItem)
rs!Type_ID = Me.cboTypeID
rs!DSBP_Date = Me.DSBP_Date
rs!H_ID = Me.H_ID
rs!DSBP_ID = Me.DSBP_ID
rs!Pharmacist_ID = Me.Pharmacist_ID
rs!Care_Manager_ID = Me.Care_Manager_ID
rs!Reason = ctl.Column(0)
If ctl.ItemData(varItem) = 8 Then
rs!Other = Me.txtOther
ctl.Selected(varItem) = False
For Each varItm In Me.lstReason.ItemsSelected
Me.lstReason.Selected(varItm) = False
Me.lstReason.Selected(0) = True
Set rs = Nothing
Set db = Nothing