asked on

Open form based on combo selection

i have code that opens a form based on a combo selection, however, the form contains another combo (same list of vendors from form1), how do I get the combo on form 2 to display the vendor name, form1.Combo opens the Form2 correctly using the filter method, but the VendorName is not displayed.

Private Sub cmdSingleCodingSlip_Click()
Dim strSQL As String
   
   On Error GoTo cmdSingleCodingSlip_Click_Error

    strSQL = "Select * from tblContracts Where ContractNumber = " & Chr(34) & gContractID & Chr(34)
    DoCmd.OpenForm "formcodingslip", acNormal, strSQL, , acFormEdit, acDialog
    Forms![FormCodingSlip]![ComboContract] = " & Chr(34) & gContractID & Chr(34)"
   
   On Error GoTo 0
   Exit Sub

cmdSingleCodingSlip_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdSingleCodingSlip_Click of VBA Document Form_FrmStart"

End Sub

Open in new window

Note:
Forms![FormCodingSlip]![ComboContract] is an unbound field

As you see I tried setting the value on open of the form.

Forms![FormCodingSlip]![ComboContract] = " & Chr(34) & gContractID & Chr(34)"

but this does not display the vendor name within the Form2,comboContract.

Dale Fye

The reason is that because you use the acDialog as the argument for the WindowMode in the Open Form method. Using acDialog will open the form up in Dialog mode and will pause execution of all subsequent code until that form is closed or hidden.

Where are you setting the value for your variable gContractID? If gContractID is actually a global variable, then you could use the Load event of your form "formcodingslip" to set the value of the combo on that form.

Private Sub Form_Load

me.ComboContract = chr$(34) & gContractID & chr$(34)

End Sub

Karen Schaefer

ASKER

Dale,

I did not have any luck with your suggestion.

the name displayed above is a separate field, that I am currently layer in front of the combo in an attempt to get the name displayed. the gcontractID is a globale variable and the Combocontract contains multiple columns(3). I even tried setting the column value with out luck (ComboContract.column(0).

Here is the code

Private Sub Form_Load()
  Dim x As String
    x = Nz(DLookup("Currency", "TblInvoice", "ContractNumber = " & Chr(34) & gContractID & Chr(34)))
 Me.ComboContract = Chr$(34) & gContractID & Chr$(34)
  If IsNull(x) Or x = "" Then
        Me.lblMCFUS.Visible = True
        Me.txtMCFUS.Visible = True
        Me.txtMCFPaidUS.Visible = True
        Me.txtMCFRemainingUS.Visible = True

        Me.lblMCFCDN.Visible = False
        Me.txtMCFCDN.Visible = False
        Me.txtMCFPaidCDN.Visible = False
        Me.txtMCFRemainingCDN.Visible = False
    ElseIf x = "U" Then
        Me.lblMCFUS.Visible = True
        Me.txtMCFUS.Visible = True
        Me.txtMCFPaidUS.Visible = True
        Me.txtMCFRemainingUS.Visible = True

        Me.lblMCFCDN.Visible = False
        Me.txtMCFCDN.Visible = False
        Me.txtMCFPaidCDN.Visible = False
        Me.txtMCFRemainingCDN.Visible = False

   ElseIf x = "C" Then
        Me.lblMCFCDN.Visible = True
        Me.txtMCFCDN.Visible = True
        Me.txtMCFPaidCDN.Visible = True
        Me.txtMCFRemainingCDN.Visible = True

        Me.lblMCFUS.Visible = False
        Me.txtMCFUS.Visible = False
        Me.txtMCFPaidUS.Visible = False
        Me.txtMCFRemainingUS.Visible = False

     End If

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Karen Schaefer

ASKER

the variable is not a number it is alphanumeric, hence the string.

thanks for the suggestion - I am testing it now.

Dale Fye

Are you sure that the combo in the 2nd form has the fields in the same order and that the bound column is the one for ContractID (usually the bound column will be 1, but not always).

Karen Schaefer

ASKER

ok I check the recordsource for both combos and they were not insync, however, when I changed them to match it did not solve the issue.

does it matter if they are bound or unbound?

k

Karen Schaefer

ASKER

after I just posted they both seem to be working now thanks for the suggestions.

Dale Fye

glad I could help.