how to sync forms subforms to combobox.value onLoad

[mdavid]

Hi, I have a main form with subforms, the main form has a combobox and in the form onload event I set its value by

Me.cmbMempSpecies = Me.cmbMempSpecies.Column(0, 0)

The problem is other fields on my form and subforms are only updated after I select a value from this combobox
How do I update the forms and subforms to the combobox value onLoad.

Thanks for any help
David

[pbaldy]

Is there code that runs in the after update event? If so, you need to run it from the load event too.

[mdavid]

Hi Paul, Thanks for your reply.
I'm a little confused, after you asked about after update I checked the combobox after update event and found a macro

SearchForRecord
     Object Type
      Object Name
      Record:  First
   Where Condition =="[SpeciesID] = " & Str(Nz([Screen].[ActiveControl],0))

No idea what this means - maybe you can enlighten me.

In the OnLoad event I already have vba code and don't know how to add this macro as well - what do you suggest?

Thanks
David

[mdavid]

I found the "convert Forms Macros to Visual Basic", and and after running placed :



Call cmbMempSpecies_AfterUpdate

into the Form_Load.

but I get an error:

"The expression you entered requires the controls to be in the active window"

Any ideas how to solve this?

Thanks
David

[pbaldy]

I would just copy the code produced by the conversion and add it to your load event.

[mdavid]

After copying I still get the same message!

Here's the form onLoad code:

Private Sub Form_Load()
     Me.cmbMempSpecies = Me.cmbMempSpecies.Column(0, 0)
     Me.TabCtl0.Value = 0
     TabCtl0_Change
' Copied code from cmbMempSpecies_AfterUpdate below - after conversion
' ====================================================
On Error GoTo cmbMempSpecies_AfterUpdate_Err


    DoCmd.SearchForRecord , "", acFirst, "[SpeciesID] = " & Str(Nz(Screen.ActiveControl, 0))




cmbMempSpecies_AfterUpdate_Exit:
    Exit Sub


cmbMempSpecies_AfterUpdate_Err:
    MsgBox Error$
    Resume cmbMempSpecies_AfterUpdate_Exit
End Sub

[pbaldy]

Oh, there probably is no active control yet. Change that to specify the combo.

[mdavid]

Hi Paul,
Excuse my ignorance, tried: Screen.ActiveControl = Me.cmbMempSpecies

getting run time error 2474 "the expression you entered requires the control to be in the active window".

Could you give me the code I need.

It appears that in the Form_Load where I'm trying to run this there are no active forms or controls

Thanks
David

[davegri]

DoCmd.SearchForRecord , "", acFirst, "[SpeciesID] = " & Str(Nz(Screen.ActiveControl, 0))

I don't know why macros love this sort of syntax. Just specify your control directly instead of that weird indirect method:

DoCmd.SearchForRecord , "", acFirst, "[SpeciesID] = " & cmbMempSpecies

[mdavid]

Hi Dave, Much appreciated, unbelievable how much time and energy I've wasted on this, and the solution's so simple.

At least now I know how to convert macros - don't click "Convert Form's Macros to Visual Basic"

Marked as Solved
Thanks
David

[pbaldy]

FYI, that's what I meant by specifying the combo. It's okay to use that conversion method, you just have to realize that the resulting code may need tweaking.

[mdavid]

Hi Paul, Appreciate your help, problem is I need to be spoon fed - 'cause I didn't know how to "specify the combo".

Thanks
David

[pbaldy]

Sorry, since you were doing it above I assumed you did.

Me.cmbMempSpecies = Me.cmbMempSpecies.Column(0, 0)

[mdavid]

Hi Paul, I did have that line, but the problems continued - as I said in reply #8.

This what worked in the end:

Private Sub Form_Load()
     Dim ctrl As ComboBox
     Dim frm As Form
     Me.cmbMempSpecies = Me.cmbMempSpecies.Column(0, 0)
     Me.TabCtl0.Value = 0
     TabCtl0_Change
     Me.Refresh
'  Following line sets all forms/subforms to the first record in the Combo list
    DoCmd.SearchForRecord , "", acFirst, "[SpeciesID] = " & cmbMempSpecies
End Sub

Thanks
David