Solved: Word Userform Combobox to get two columns from Access Database

[rbrewington]

I've got another issue in the project I'm working on for a medical note template. I would like to have a userform that uses comboboxes to allow a user to choose selections that are listed in an access database. I've got this going right now and there are other complexities that I have worked out right now with a single column setup. So, I basically need several things for this to work properly.

1. I need to know how to add data from a second access field from the same record (they are already in the access database and paired up properly.

2. I need to continue to allow the userform to get the prior data from the original document and use that as a default (works now with single column setup but I'm afraid it might not if I change to two columns). Of note, the second column only needs to be visible once the data is placed in the document itself and is not really needed in the combobox for selecting.

3. Finally, the format I would prefer is:
FIRSTCOLUMNDATA (SECONDCOLUMNDATA)


Anyway, here is the code I'm using right now (sorry if it's clunky and messy, but so far it's working for me with one column:

[vba]

Option Explicit
'Define Variables
Dim dbDatabase As Database
Dim rsAxisI As Recordset
Dim i As Integer
Dim aResults()
Dim currentdx As String

Private Sub cmbExecute_Click()

' This code is supposed to add the current text from each
' of the axis I diagnosis comboboxes to the appropriate
' text fields in the the document.

ActiveDocument.FormFields("AxisOne01").Result = AxisI01.Text
ActiveDocument.FormFields("AxisOne02").Result = AxisI02.Text
ActiveDocument.FormFields("AxisOne03").Result = AxisI03.Text
ActiveDocument.FormFields("AxisOne04").Result = AxisI04.Text
ActiveDocument.FormFields("AxisOne05").Result = AxisI05.Text

' Unprotect form
ToggleFormLock

' Unhide all 4 available "extra" Axis I lines
ActiveDocument.Bookmarks("AxisOneLine02").Select
With Selection.Font
.Hidden = False
End With
ActiveDocument.Bookmarks("AxisOneLine03").Select
With Selection.Font
.Hidden = False
End With
ActiveDocument.Bookmarks("AxisOneLine04").Select
With Selection.Font
.Hidden = False
End With
ActiveDocument.Bookmarks("AxisOneLine05").Select
With Selection.Font
.Hidden = False
End With

' Hide all the available "extra" Axis I lines that are blank
With ActiveDocument.FormFields("AxisOne02")
If .Result = "" Then
ActiveDocument.Bookmarks("AxisOneLine02").Select
With Selection.Font
.Hidden = True
End With
End If
End With

With ActiveDocument.FormFields("AxisOne03")
If .Result = "" Then
ActiveDocument.Bookmarks("AxisOneLine03").Select
With Selection.Font
.Hidden = True
End With
End If
End With

With ActiveDocument.FormFields("AxisOne04")
If .Result = "" Then
ActiveDocument.Bookmarks("AxisOneLine04").Select
With Selection.Font
.Hidden = True
End With
End If
End With

With ActiveDocument.FormFields("AxisOne05")
If .Result = "" Then
ActiveDocument.Bookmarks("AxisOneLine05").Select
With Selection.Font
.Hidden = True
End With
End If
End With

' Reprotect form
ToggleFormLock

' Unload userform.
Unload Me

' Select Axis Two Userform when done
ChooseAxisTwoDiagnosis
End Sub

Private Sub cmbNoChange_Click()

' This is just to exit and not change the Axis I diagnosis
' field at all.
Unload Me

' Select Axis Two Userform when done
ChooseAxisTwoDiagnosis
End Sub

Private Sub UserForm_Initialize()

' This code is supposed to add the current text from each
' of the axis I diagnosis text fields to the appropriate
' combobox as the default value.
currentdx = ActiveDocument.Bookmarks("AxisOne01").Range.Text
AxisI01.AddItem (currentdx)
currentdx = ActiveDocument.Bookmarks("AxisOne02").Range.Text
AxisI02.AddItem (currentdx)
currentdx = ActiveDocument.Bookmarks("AxisOne03").Range.Text
AxisI03.AddItem (currentdx)
currentdx = ActiveDocument.Bookmarks("AxisOne04").Range.Text
AxisI04.AddItem (currentdx)
currentdx = ActiveDocument.Bookmarks("AxisOne05").Range.Text
AxisI05.AddItem (currentdx)

' This code adds a blank to select for deleting diagnoses no
' longer needed to each of the comboboxes.
AxisI01.AddItem ("")
AxisI02.AddItem ("")
AxisI03.AddItem ("")
AxisI04.AddItem ("")
AxisI05.AddItem ("")

' This code activates the Database connection. Change
' the path to reflect your database.
Set dbDatabase = OpenDatabase(ActiveDocument.Path & "\Psychiatry.mdb")

' This code opens the AxisI table. Change the Table
' to reflect the desired table.
Set rsAxisI = dbDatabase.OpenRecordset("AxisI", dbOpenSnapshot)

i = 2

With rsAxisI
' This code populates the combo box with the values
' in the Diagnosis field.

Do Until .EOF
AxisI01.AddItem (i)
AxisI01.Column(0, i) = .Fields("Diagnosis")
AxisI02.AddItem (i)
AxisI02.Column(0, i) = .Fields("Diagnosis")
AxisI03.AddItem (i)
AxisI03.Column(0, i) = .Fields("Diagnosis")
AxisI04.AddItem (i)
AxisI04.Column(0, i) = .Fields("Diagnosis")
AxisI05.AddItem (i)
AxisI05.Column(0, i) = .Fields("Diagnosis")
.MoveNext
i = i + 1
Loop

End With

'This code should set the default combobox selection as the
'last diagnosis used on the prior note

AxisI01.ListIndex = 0
AxisI02.ListIndex = 0
AxisI03.ListIndex = 0
AxisI04.ListIndex = 0
AxisI05.ListIndex = 0

End Sub


[/vba]

[rbrewington]

I ended up just combining the two columns in excel to make it easier and then edited the database and just entered them both into one field. Would have been nice to have both columns, but I couldn't find anything about doing that with a combobox and getting the kind of formatting I needed. So, sort of solved for now.