Viewing combobox values from textbox values Microsoft Access 2007

Question

I have a form ("Patient Complications") where users input data to a form using 2 cascading combo boxes ("catcombo" and "speccombo"). The combo boxes pull their values from a table ("Complications"). The table has a field for complication category (for example, infection, bleeding, mechanical). A second field lists specific complications (for example, if the complication category is "bleeding", the specific complication could be "GI" or "other"). The input from the combo boxes are concatenated and put into a text field on the form ("Complication"). That part works fine.

My form has several command buttons, including "edit" and "save" command buttons. Since I don't want users interacting with the "complication" field on the form, I have the field become not visible when the "edit" button is clicked. Instead, the 2 combo boxes become visible and allow users to input data. When "save" is selected, the reverse occurs. The two combo boxes become not visible, and the complication field becomes visible and locked.

Unfortunately, when "edit" is selected, the combo boxes are visible but show up blank (nothing is selected or displayed). I am trying to have the boxes display the inputs that were given to the text field. For example, if the text field displays "Bleeding, Other", I want the catcombo box to display "Bleeding" and the speccombo box display "Other". I have been unable to find anything to this effect. If anyone has any ideas it would be greatly appreciated.

The relevant code is included below. Please let me know if I can provide further clarification.

Private Sub catcombo_AfterUpdate()
Me.speccombo.Requery
End Sub

Private Sub speccombo_OnCurrent()
Dim strsql As String
strsql = "SELECT [Complications]![Specific Complication] FROM tblComplications" & _
"WHERE [Complication Category]=Forms![Patient Complications]![catcombo].value"
End Sub

Private Sub speccombo_AfterUpdate()
Forms![Patient Complications]![Complication] = Me.catcombo.Value & ", " & Me.speccombo.Value
End Sub

Private Sub save_Click()
    Me.recordcount.Caption = "Record " & Me.CurrentRecord & " of " & Me.Recordset.recordcount
    Me.Patient_Initials.Visible = False
    Date_of_Complication.Locked = True
    Complication.Visible = True
    Complication.Locked = True
    comments.Locked = True
    catcombo.Visible = False
    speccombo.Visible = False
    Me.edit.Visible = True
    Me.edit.SetFocus
    Me.help.Visible = False
    Me.save.Visible = False
    Me.first.Visible = True
    Me.next.Visible = True
    Me.previous.Visible = True
    Me.last.Visible = True
    Me.addnew.Visible = True
    Me.close.Visible = True
    Me.cancel.Visible = False
End Sub

Private Sub edit_Click()
    Me.recordcount.Caption = "Record " & Me.CurrentRecord & " of " & Me.Recordset.recordcount
    Me.Patient_Initials.Visible = False
    Date_of_Complication.Locked = False
    Complication.Visible = False
    comments.Locked = False
    catcombo.Visible = True
    catcombo.Locked = False
    catcombo.Enabled = True
    speccombo.Visible = True
    speccombo.Locked = False
    speccombo.Enabled = True
    Me.cancel.Visible = True
    Me.cancel.SetFocus
    Me.edit.Visible = False
    Me.help.Visible = True
    Me.save.Visible = True
    Me.first.Visible = False
    Me.next.Visible = False
    Me.previous.Visible = False
    Me.last.Visible = False
    Me.addnew.Visible = False
    Me.close.Visible = False
End Sub

Not sure how the code would even compile. This line strsql = "SELECT [Complications]![Specific Complication] FROM tblComplications" & _ "WHERE [Complication Category]=Forms![Patient Complications]![catcombo].value" does absolutely nothing. Your question needs more verbiage than code. Could you please give another shot at explaing the issue? — PaulFrancis
The Complications table has a column for the complication category and a column for specific complications. That line causes the speccombo box to only give values falling under the complication category (value selected in the catcombo box). Will edit the question to try explaining it more clearly. — Alisa

Alisa Alisa · Accepted Answer · 2015-05-04T17:46:04

I figured it out. I added a field to the "Complications" table called "Input". This field contains the concatenated values that were put into the patient record (in the example above, the input field would be "Bleeding, Other"). The values in the Input field are the exact values that would be recorded in the "Complication" field on the Patient Complications form. I added the vba code below to the "Edit" command button code.

If Not IsNull(Forms![Patient Complications]![Complication]) Then
Dim comptext As String
Dim spectext As String

comptext = DLookup("[Complication Category]", "Complications", "Input = Forms![Patient Complications]![Complication]")
catcombo.Value = comptext
spectext = DLookup("[Specific Complication]", "Complications", "Input=Forms![Patient Complications]![Complication]")
speccombo.Value = spectext
End If

That solved my initial problem, but I then had an issue with the speccombo box displaying the last value it was given. For example, if speccombo.value="GI" when I click "edit", it would continue to display "GI" until another selection was made. This isn't a huge deal, just inconvenient. I wanted to make the speccombo box essentially zero out if catcombo was changed. I added the code below to fix that problem.

Private Sub catcombo_AfterUpdate()
Me.speccombo.Requery
Me.speccombo.Value = Null
End Sub

Please let me know if I need to provide clarification for anything.

Viewing combobox values from textbox values Microsoft Access 2007

1 Answers