• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 647
  • Last Modified:

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.
0
Karen Schaefer
Asked:
Karen Schaefer
  • 4
  • 4
1 Solution
 
Dale FyeCommented:
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
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Dale,

I did not have any luck with your suggestion.

Missing Name
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

0
 
Dale FyeCommented:
Are you certain that the RowSource for cboContract in this second form is the same as in the first?  I was a bit surprised that your ContractID field is a string.  Is that the case?  if not, try:

 Me.ComboContract = gContractID

I would also make a recommendation regarding your DLOOKUP and the subsequent code.  If you change the NZ( ) function to return a "U" if the DLOOKUP value is NULL, then you can eliminate a whole set of your IF Then.  You could also avoid the If then altogether with code similar to:

    Dim x as String
    x = Nz(DLookup("Currency", "TblInvoice", "ContractNumber = " & Chr(34) & gContractID & Chr(34)), "U")

    'Make these controls visible if x = "U", hidden otherwise
    Me.lblMCFUS.Visible = (x = "U")
    Me.txtMCFUS.Visible = (x = "U")
    Me.txtMCFPaidUS.Visible = (x = "U")
    Me.txtMCFRemainingUS.Visible = (x = "U")

    'Make these controls visible if x = "C", hidden otherwise
    Me.lblMCFCDN.Visible = (x = "C")
    Me.txtMCFCDN.Visible = (x = "C")
    Me.txtMCFPaidCDN.Visible = (x = "C")
    Me.txtMCFRemainingCDN.Visible = (x = "C")

Open in new window

You might also want to consider moving that code to the Current event, so that if you change records, it would fire for each record.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Karen SchaeferBI ANALYSTAuthor Commented:
the variable is not a number it is alphanumeric, hence the string.

thanks for the suggestion - I am testing it now.
0
 
Dale FyeCommented:
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).
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
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
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
after I just posted they both seem to be working now thanks for the suggestions.
0
 
Dale FyeCommented:
glad I could help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now