I’ve come up with another solution which might be of interest to others, as this allows me to capture both the integer value in the field as well as the associated name string (and other fields when I use for real); and display the current name string value within each instance of the form, but change the row source for the combo control for each instance without visually disturbing the other records. I did have to resort to using a text box control as well as the combo control, but for my purposes that's better than storing a text string instead of an integer. Here’s what I did;
I placed a combo control with the following key properties;
Name: cboActionID
Control Source: ActionID (in my tblData)
Row Source: qryComboSource
Column Count: 2
Column Widths: 0cm;4.602cm
List Width:4.6cm
Width: 0.487cm
Left: 4.286cm
Scroll Bar Align: Left
I added a textbox control with the following key properties;
Name: txtActionName
Control Source: ActionName (in my tblData)
Left: 0.2cm
Width: 4.053cm
This simulates an ordinary combo control, so that the list drops down to the left, below the text box control.
I added code to the combo control as follows;
- Option Compare Database
-
Option Explicit
-
-
Private Sub cboActionID_Enter()
-
-
Dim MySQL As String
-
Dim CurrAction As Integer
-
Dim CurrName As String
-
-
CurrName = Me.txtActionName
-
CurrAction = DLookup("[ActionID]", "tblActions", "[ActionName] = '" & CurrName & "'")
-
-
MySQL = "SELECT tblActions.ActionID, tblActions.ActionName " & _
-
"FROM tblActions " & _
-
"WHERE (((tblActions.ActionID)=34 Or (tblActions.ActionID)=35 " & _
-
"Or (tblActions.ActionID)=36 Or (tblActions.ActionID)=39)) " & _
-
"UNION SELECT """ & CurrAction & """ AS ActionID, """ & CurrName & """ AS ActionName " & _
-
"FROM tblData WHERE ([tblData]![TableID]=[Forms]![frmData]![TableID]);"
-
-
Me.cboActionID.RowSource = MySQL
-
-
End Sub
-
-
Private Sub cboActionID_Exit(Cancel As Integer)
-
-
Me.cboActionID.RowSource = "qryComboSource"
-
Me.cboActionID.Requery
-
-
End Sub
I’ll need to finesse the way users interact with the text box so that this is in keeping with a combo, and also control for a null value, but otherwise I think this will do the job. Thanks again for your suggestion FishVal, it made me relook at an SQL solution rather than purely VBA, and stop thinking this had to be terribly complicated.
68
