Default value for Combo box on a Subform

[trekgoes2malaysia]

I am having difficulty using VBA to set a value for a combo box
control that resides on a subform based on the value in a text box
control on a parent form. I know how to do this for textbox controls
but Access 2003 does not seem to allow me to do this with a combo box
using the same method. It returns the error "You can't assign a value
to this object". So how do I assign a value to a combo box then? Below
is my code:

Private Sub Form_Open(Cancel As Integer)

Dim strtable As String
Dim strtable2 As String
Dim getvenueid As Integer
Dim sql As String
Dim sql2 As String

strtable = "Venue"

getvenueid = [Forms]![track session]![venueid]

sql = "[venueid] = " & getvenueid
getsize = DLookup("[Size]", strtable, sql)

Me.cboTrackSize = getsize

With Gratitude,
Patrick

 

[Marshall Barton]

I am having difficulty using VBA to set a value for a combo box
control that resides on a subform based on the value in a text box
control on a parent form. I know how to do this for textbox controls
but Access 2003 does not seem to allow me to do this with a combo box
using the same method. It returns the error "You can't assign a value
to this object". So how do I assign a value to a combo box then? Below
is my code:

Private Sub Form_Open(Cancel As Integer)

Dim strtable As String
Dim strtable2 As String
Dim getvenueid As Integer
Dim sql As String
Dim sql2 As String

strtable = "Venue"

getvenueid = [Forms]![track session]![venueid]

sql = "[venueid] = " & getvenueid
getsize = DLookup("[Size]", strtable, sql)

Me.cboTrackSize = getsize

If the combo box is in the subform, then you have to refer
to it through the subform control (Me refers to the form
running the code).

Me.[name of subform control].Form.cboTrackSize = getsize

You should have gotten a compile error about cboTrackSize
being an undeclared variable.

Note that If I am correct about the combo box being in the
subform, that implies that you do not have the
OPTION EXPLICIT
statement at the top of the code module. You should make
sure **every** module has this statement. Set VBA Tools -
Options - Editor tab Require Variable Declaration
to make Access insert it automatically in any new modules.
Then you can use Debug - Compile to detect this kind of
error. (**Never** edit code unless the form is in design
view and always use compile after editing any module.)

The Open event is too soon to set/get a control's value.
Use the Load event instead. If getsize is a control on the
main form, then that is probably the line with the error.

If the combo box is bound to a field, then you should not
set the Value because it would dirty the record. In
general, you probably only want to do this for new records,
in which case you should set the combo box's DefaultValue
instead of its value.