Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57

    Unanswered: Open a form based on selected combo box value

    Hello. I have a combo box of companies that a user selects from. I was wondering how I get a certain form to open that pertains to just the selected company. If I have 5 companies and 5 individual forms that deal with each company, how do I get Company1 form to open when I select Company1 from the combo box?

    Any help would be appreciated.

    Thanks!

  2. #2
    Join Date
    Mar 2002
    Posts
    192
    It can be done using either macros or VBA. You then need to tie the code or macro to the After_update events of the combo box.

    If you are using code this sample should help.

    public sub MyComboBox_AfterUpdate()
    docmd.openform MyComboBox.Value
    End Sub

    Assumes that the name of the form is the first column specified (regardless of whether its visible or invisible) otherwise you'll need to look into the column property.

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it can also be done using a query to drive your second form.

    your firstForm has a combo that selects a company.

    now make a query that can feed all the boxes you want on secondForm (include company even if you dont want it displayed).
    save the query.
    still in query desgn view, click once in the criteria box for Company and then on the magic-wand picture in the tool bar: navigate forms¦allForms¦firstForm and double-click the name of the combo. secondForm now has the value of the firstForm combo as criteria. save.

    switch the recordsource of your second form to the query.

    in the afterupdate event of the combo on firstForm, add:
    forms!secondForm.requery
    (or if secondform is a subform of firstForm: Me!secondForm.requery)

    izy

  4. #4
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132

    Re: Open a form based on selected combo box value

    [QUOTE][SIZE=1]Originally posted by decad1
    'Name of combo box is Type-Of_Invoice
    'Placed this code under AfterUpdate Event
    'Copied and pasted this code from my Project
    '
    Private Sub Type_of_Invoice_AfterUpdate()
    If ErrorTrapping = True Then
    On Error GoTo Invoice_Type_AfterUpdate_Err
    End If
    Dim formname As Form
    Set formname = Forms![Sales Order]
    'open subform based on value selected in ComboBox
    Select Case formname![Type of Invoice]
    Case "Simple"
    formname![zSalesOrderLineitems].SourceObject = "zSalesOrderLineitemsSimple"
    Case "Product Detail"
    formname![zSalesOrderLineitems].SourceObject = "zSalesOrderLineitemsDetail"
    If ItemCats = -1 Then
    formname![zSalesOrderLineitems].Form![Item Categories].Visible = True
    Else
    formname![zSalesOrderLineitems].Form![Item Categories Text].Visible = False
    formname![zSalesOrderLineitems].Form![Qty Sh Text].Width = 988.992
    formname![zSalesOrderLineitems].Form![Item Categories].Visible = False
    formname![zSalesOrderLineitems].Form![Ship Qty].Width = 960.048
    End If
    Case "Professional"
    Me![Add Inventory].Visible = False
    formname![zSalesOrderLineitems].SourceObject = "zSalesOrderLineitemsProf"
    Case "Service"
    formname![zSalesOrderLineitems].SourceObject = "zSalesOrderLineitemsServ"
    If ItemCats = -1 Then
    formname![zSalesOrderLineitems].Form![Item Categories].Visible = True
    Else
    formname![zSalesOrderLineitems].Form![Item Categories Text].Visible = False
    formname![zSalesOrderLineitems].Form![Qty Sh Text].Width = 988.992
    formname![zSalesOrderLineitems].Form![Item Categories].Visible = False
    formname![zSalesOrderLineitems].Form![Ship Qty].Width = 960.048
    End If

    End Select
    formname![Subtotal].ControlSource = "=IIf(IsNull([Forms]![Sales Order]![zSalesOrderLineItems].[Form]![Subtotal]),0,[Forms]![Sales Order]![zSalesOrderLineItems].[Form]![Subtotal])"
    formname.Recalc
    Exit Sub

  5. #5
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57

    Thumbs up

    Thank you all. I appreciate your help. It works great now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •