Results 1 to 2 of 2
  1. #1
    comteck is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011

    Selecting records based on 2 combo boxes

    First of all, thank you in advance for any help anybody can give me on this. I will try to explain my problem the best I can.
    I am designing a databse to track maintenance we do for various companies.

    When I click on a button on the main menu, it opens up a form with 2 combo boxes. I don't want any selections from the second box to occur until I've made a selection from the first box. The code for this button is:

    Private Sub ViewFiltered_Click()
    DoCmd.OpenForm ("frmAllRecords")
    [Forms]![frmAllRecords]![Label29].Caption = "View/Modify Records"
    [Forms]![frmAllRecords]![CompanyName].RowSource = "SELECT [tblCompanies].[Company] FROM [tblCompanies];"

    End Sub

    The table "tblCompanies" has a list of the companies, and the first combo box selects the company name from a drop down list. The second combo box selects the company number.

    The first combo box has thed following code on Change:
    Private Sub CompanyName_Change()
    [Forms]![frmAllRecords]![CompanyNumber].RowSource = "SELECT [tblMaintenanceLog].[CompanyNumber] FROM [tblMaintenanceLog] WHERE [tblMaintenanceLog].[CompanyName]=[Forms]![frmAllRecords]![CompanyName];"
    End Sub

    The second box has the following on Change:
    Private Sub CompanyNumber_Change()
    [Forms]![frmAllRecords]![Address].ControlSource = "Address"
    [Forms]![frmAllRecords]![Province].ControlSource = "Province"
    [Forms]![frmAllRecords]![City].ControlSource = "City"
    [Forms]![frmAllRecords]![PostalCode].ControlSource = "PostalCode"
    End Sub

    I am able to select data from each combo box, but the issue is it always goes to the first record in the table. I want it to go to whatever record has the information selected for Company Name and Number.

    If this is not enough information, please let me know. I really appreciate any help.

    Thank you.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    Concatenate variables. Reference to the first combobox is a variable.
    Me.CompanyNumber.RowSource = "SELECT [CompanyNumber] FROM [tblMaintenanceLog] WHERE [CompanyName]='" & Me.CompanyName & "';"

    Alternatively, set the second combobox RowSource property to:
    SELECT [CompanyNumber] FROM [tblMaintenanceLog] WHERE [CompanyName]=[CompanyName];

    Then code in the first combo AfterUpdate (I don't use Change) event would be:

    All you are doing is setting RowSource property of the two comboboxes. You are not filtering the form RecordSource. There are several methods to accomplish that.
    1. set the form RecordSource property
    2. set the form Filter property and set FilterOn = True
    3. use RecordsetClone and bookmarks to move to record of interest

    Why are you using code to set the ControlSource property of textboxes? This should not be necessary with a bound form.
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 06-04-2014, 10:45 AM
  2. Selecting records based on criteria from user input
    By shabbaranks in forum Programming
    Replies: 1
    Last Post: 01-04-2012, 09:06 AM
  3. Selecting records based on particular value
    By usman400 in forum Queries
    Replies: 4
    Last Post: 09-30-2011, 07:58 AM
  4. Filter based on two combo boxes.
    By jakeao in forum Programming
    Replies: 1
    Last Post: 05-22-2011, 10:56 AM
  5. populate boxes based on combo box choice
    By Mattm1958 in forum Forms
    Replies: 13
    Last Post: 08-30-2010, 02:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums