I'm pretty new to Access and trying to learn on the fly here. Any help from the experts here would be very much appreciated.
I'm working on trying to auto-populate some parts of a form so that only need to change a handful of fields for their daily updates.
The database table called Master_Issue_List that consists of Issue_ID, Date_Added, Status, and Status_Index. There are other fields too, but not relevant for this question.
The form that updates that table has Issue_ID as a combo box and Status as a combo box. The items in the Status field are going to be the strings 'Green', 'Yellow', and 'Red'. I would like to create the logic so that once an Issue_ID has been selected by the user on the form, the default value for the Status field will be last known Status for that particular Issue_ID using MAX(Date_Added). Also, the Status field should NOT be filtered. 'Green', 'Yellow', and 'Red' should still all be options listed in the combo box drop down, but just that the default selected value will depend on prior records.
Here's what I have, but I keep getting a "Run-time error '13': Type mismatch" error message. I have this included as the 'After Update' event code for the Issue_ID
Private Sub Issue_ID_AfterUpdate()
Dim lastStatusIndex As Integer
lastStatusIndex = "SELECT Master_Issue_List.Status_Index FROM Master_Issue_List WHERE Master_Issue_List.Date_Added = (SELECT MAX(Master_Issue_List.Date_Added) FROM Master_Issue_List WHERE Master_Issue_List.Issue_ID = '" & Me.Issue_ID & "') "
Me.Status = Me.Status.ItemData(lastStatusIndex)
End Sub
Without any coding, the combo box for Status goes off a fixed Value List of 'Green', 'Yellow', 'Red'. The Status Index just correlates those strings to integers of 0, 1, 2 respectively.
Is there a better way to do this?
Issue_ID Date_Added Status Status_Index AAA 12/1/19 Red 2 BBB 12/2/19 Yellow 1 AAA 12/3/19 Yellow 1
In the above example, if I select AAA from the Issue_ID drop-down field on the form, the default Status selected should be Yellow, but still show the same Green, Yellow, and Red in the drop-down.