pass combo box value to another form's combo box

[hllary]

I have a combo box and a button on a form. I would like the go to the record selected in combo box on another form when the button is clicked. Also, the first form is closed when I click the button too.

Private Sub Edit_Click()
DoCmd.OpenForm "3rdfrm_AddEdit"
DoCmd.GoToRecord , acNormal, acGoTo, Me.SearchVariance
DoCmd.Close acForm, "2ndfrm_AddEditView"

End Sub

I'm getting an error on the GoToRecord line.

 

[June7]

Somewhat confused by thread title. Why would you pass a value to another combobox?

Definitely not using GoToRecord arguments correctly.

Instead of GoToRecord, why not open form filtered to specific record?

Use WHERE CONDITION argument of OpenForm.

 

[theDBguy]

Hi Hillary. I agree with June7. It might be simpler to open the second form filtered to a specific record rather than try to navigate to the one you want to show.

 

[hllary]

On the second form there is another combo box that is used to filter records.

So I would like to have the value of the combo box on form 1 passed the the combo box on form 2 when I click the button.

 

[June7]

Why? Requirement is not making sense. Is that second combobox UNBOUND?

Nothing in posted code has anything to do with setting value of combobox.

If you want to populate a field, perhaps should be using form/subform arrangement.

 

[hllary]

Okay, how do I filter it base on the combo box? The text box on the other form is called, VarianceNumber.

I tried:

Private Sub Edit_Click()
DoCmd.OpenForm "3rdfrm_AddEdit",acNormal,,,where "VarianceNumber" = me.SearchVariance,,
DoCmd.Close acForm, "2ndfrm_AddEditView"
End Sub

But I got an error, so I think I going in the wrong direction.

 

[June7]

Is VarianceNumber name of field?

DoCmd.OpenForm "3rdfrm_AddEdit", acNormal, , "VarianceNumber=" & Me.SearchVariance

That assumes VarianceNumber is a number type. If text:

DoCmd.OpenForm "3rdfrm_AddEdit", acNormal, , "VarianceNumber='" & Me.SearchVariance & "'"

 

[hllary]

I've updated the code to my form and combo box names but when I open the 2nd form it does not open on the correct record.

Private Sub Edit_Click()
If IsNull(Me.SearchVariance) Then
        MsgBox "Select something in the Combo Box"
        Me.SearchVariance.SetFocus
        Me.SearchVariance.Dropdown
        Exit Sub
    End If

        Dim strFrmName As String
        strFrmName = "3rdfrm_AddEdit"
            DoCmd.OpenForm strFrmName
            
                With Forms(strFrmName)
                    .SearchVar.DefaultValue = Me.SearchVariance
                End With
End Sub

 

[theDBguy]

I've updated the code to my form and combo box names but when I open the 2nd form it does not open on the correct record.

Private Sub Edit_Click()
If IsNull(Me.SearchVariance) Then
        MsgBox "Select something in the Combo Box"
        Me.SearchVariance.SetFocus
        Me.SearchVariance.Dropdown
        Exit Sub
    End If

        Dim strFrmName As String
        strFrmName = "3rdfrm_AddEdit"
            DoCmd.OpenForm strFrmName
            
                With Forms(strFrmName)
                    .SearchVar.DefaultValue = Me.SearchVariance
                End With
End Sub

Can you post a copy of your db?

 

[hllary]

Thank you!!!
I used a combination of both.

Private Sub Edit_Click()
If IsNull(Me.SearchVariance) Then
        MsgBox "Select something in the Combo Box"
        Me.SearchVariance.SetFocus
        Me.SearchVariance.Dropdown
        Exit Sub
    End If

DoCmd.OpenForm "3rdfrm_AddEdit", acNormal, , "VarianceNumber='" & Me.SearchVariance & "'"
DoCmd.Close acForm, "2ndfrm_AddEditView"

End Sub

 

[theDBguy]

Thank you!!!
I used a combination of both.

Private Sub Edit_Click()
If IsNull(Me.SearchVariance) Then
        MsgBox "Select something in the Combo Box"
        Me.SearchVariance.SetFocus
        Me.SearchVariance.Dropdown
        Exit Sub
    End If

DoCmd.OpenForm "3rdfrm_AddEdit", acNormal, , "VarianceNumber='" & Me.SearchVariance & "'"
DoCmd.Close acForm, "2ndfrm_AddEditView"

End Sub

Congratulations! Glad to hear you got it sorted out. Good luck with your project.