Passing combobox value from one form to a combobox of another form

[MilaK]

Hello,

I have two forms, each has a combo-box “cb_samples” with the identical query. After a user selects an item from combobox on FormA and pressed a button “openFormB”, I would like FormA to close and FormB to open with the combobox showing the same item that was selected on FormA.
Here is what I’ve tried:

Private Sub cmb_FormB_open_Click()

Debug.Print Me.cb_samples.ListIndex ‘shows index of the selected item                
DoCmd.OpenForm "FormB", acNormal, , , , , Me.cb_samples.ListIndex
DoCmd.Close

End sub

Private Sub Form_Open(Cancel As Integer) ‘open FormB
If Not IsNull(Me.OpenArgs) Then
        Debug.Print Me.OpenArgs
        Me!cb_samples.ItemData (Me.OpenArgs)
    End If
End Sub

I get an error message “object doesn’t support this property of method”
How do I accomplish this?

Thanks, Mila

 

[sneuberg]

I think you want

Me.cb_samples.ListIndex = Me.OpenArgs

instead of

Me!cb_samples.ItemData (Me.OpenArgs)

 

[MilaK]

Doesn't work either. i get "Compile error: argument not option" Thanks

 

[MilaK]

I've tried the following but it failed as well:

Dim index_val As Integer

If Not IsNull(Me.OpenArgs) Then

Debug.Print Me.OpenArgs
index_val = val(Me.OpenArgs)

Me!frm_sample_cnv1.Form!cmb_samples = Me!frm_sample_cnv1.Form!cmb_samples.ItemData(idex_val)

End If

 

[MilaK]

The db is way too large. It will take me a long time to reduce it to 1MB.

 

[sneuberg]

Sorry I misread you post and assumed a listbox which make my suggestion in post #2 bogus. To set a combo box to a value in its list you need to set the combo box to the desired value in the bound column. I have a working example in the attached database. The code in the buttons is

DoCmd.OpenForm "FormB", acNormal, , , , , Nz(Me.cb_samples, 0)
DoCmd.Close acForm, "FORMA"

and the code in the forms' opens is

Me.cb_samples = Me.OpenArgs

There are a few things to note:

• In the button code you need to put the form name in the form close. A DoCmd.Close by itself will act on the form that has the focus so you end up closing the form you just opened.
  
• You can't pass a null through OpenArgs. When nothing is selected the value of the combo box is null. I chose to assign a value of 0 in this case as the bound column of the combo boxes is an autonumber. So 0 works as an automumber never has that value. You may need to do this differently depending on how your combo boxes are set up.
  
• The form's in the demo database both open the other. You can look at the code in either.

 

[MilaK]

Great! Thanks you.

 

[MilaK]

Sorry one more follow-up question:

One of the forms has a drop-downs with (All) choice that isn’t present in a combobox of the second form that I’m opening. I’ve included the following code in the Form_Open event of the second form, however, the combobox fails to populate with the first item if “All” choice is selected (combobox appears blank). Do you have ideas on how to fix this?

Private Sub Form_Open(Cancel As Integer)
If Me.OpenArgs <> "(All)" Then
        Debug.Print "The main form is" & (Me.OpenArgs)
     Me!frm_Review_Fusions_total.Form!cmb_samples = Me.OpenArgs
Else
  ‘populate combo-box with the first item if All was selected
     Me!frm_Review_Fusions_total.Form!cmb_samples = Me!frm_Review_Fusions_total.Form!cmb_samples.ItemData(0)
    
End If
    Call Form_frm_Review_Fusions_total.cmb_samples_AfterUpdate
    
End Sub

Thanks!

 

[sneuberg]

It looks like what you have should work. Are you sure the Else part of the If-Then-Else is being executed. If not I suggest putting in a stop and stepping through it or put

 Debug.Print "The main form is" & (Me.OpenArgs)

in the Else part to check this. Also

Debug.Print "The first item is " & Me!frm_Review_Fusions_total.Form!cmb_samples.ItemData(0)

 

[MilaK]

It looks like it executes but still the combobox is blank. thanks
I've tried just about anything but nothing works.

 

[sneuberg]

Sorry I understand you have a large database but I'd need to see this to figure this out. Could you create a new database, import the tables, queries and form relevant to this problem and upload that.

 

[MilaK]

it will be very difficult to get to 1MB. I will try.

 

[sneuberg]

If you strip down a copy please note that after you delete stuff you will still need to do a compact and repair to get the size down.

 

[MilaK]

I will get rid of the "All" choice from the combobox thus eliminating the problem. This will save a lot of time.

thanks