selecting a row in a combo box fails if bound column is hidden

Question

Using Access 2010 I have an unbound combo on my form, it looks like this...

RowSource: SELECT EventID, DocRef FROM Events
BoundColumn: 1 
ColumnCount: 2
ColumnWidths: 0cm;2cm

setting the combo value in vba (MyCombo="23") only works if the bound column is visible. When i set columnWidth="0cm;2cm" the assignment no longer works.

Sub Form_Open()
   evt = Me.OpenArgs
   If evt > "" Then
      MyCombo = evt
      ' MyCombo.Value = evt
      Call MyCombo_AfterUpdate
   End If
End Sub

How can i select the combo row without showing the eventIDs ?

Answer 1

I avoid dealing with controls in Form Open. Try this from Form Load.

Private Sub Form_Load()
    If Not IsNull(Me.OpenArgs) Then
        Me.MyCombo = Me.OpenArgs
        Call MyCombo_AfterUpdate
        'Call DoSomething
    End If
End Sub

The bound column can have 0 width, and this should still work.

Note, if your form is already open with a value selected in the combo when you do something like this ...

DoCmd.OpenForm "frmJaybob", OpenArgs:= 4

... the combo's value will not be changed to 4.

You may find Call MyCombo_AfterUpdate doesn't actually do anything from Form Load. If so, move your MyCombo_AfterUpdate code to a separate procedure and call the new procedure from MyCombo_AfterUpdate and from Form Load.

Private Sub DoSomething()
    ' move your MyCombo_AfterUpdate code here
    MsgBox "DoSomething"
End Sub

Answer 2

Try this instead of the current line

MyCombo = evt

Try the below if evt is a numeric value

MyCombo = DLookup("DocRef","Events","EventID=" & evt)

Use below expression if evt is a string

MyCombo = DLookup("DocRef","Events","EventID='" & evt & "'")

Answer 3

I don't think the issue is related to having the ID hidden. I can make it work just fine hidden or not.

I see a few things I would change. You Sub is not declared with the proper event parameters, and I also see you are not checking against null values. This tested fine for me setting the combobox to an hidden ID field as you described. (In fact it would accept either field as proper input.)

Private Sub Form_Open(Cancel As Integer)
    Dim evt As String
    If Not IsNull(Me.OpenArgs) Then
        evt = Me.OpenArgs
        If Len(evt) Then
            Me.Combo0.Value = evt
        End If
    End If
End Sub

I tested it by going to the VB-Editor, pressing control-G and running this from the immediate window:

Docmd.OpenForm "Form1",acNormal,,,,,5329

Obviously the form name and ID will need to be corrected for your setup.