Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2008
    Posts
    1

    Drop Down Menus

    Good morning to all.

    Let me make this simple. I've created a few forms, with mainforms linked to subforms. I know how to display the mainform with a drop down menu and subform items.



    What I am having trouble doing is creating a drop down menu that will display on a particular customer number. For example, let's say I have created a document with 10 customers that have several things attached to that customer number. I want to be able to select the drop down menu for that customer (keep in mind, all customers are currently listed with several things associated with them, the list may have 100 items) and only that customer number shows up with associated items.

    How do I do this?

  2. #2
    Join Date
    Oct 2007
    Posts
    10

    Dependent combo?

    Hi,

    I think it's still morning here. After much practice in perfecting this super cool trick myself, I am happy to share it with you. There are several ways of approaching this, but I find that the easiest way is this:

    1. Create two tables
    TblA (Your independent combo box) should have a column called 'CustomerName' (or CustomerNumber).
    TblB (Your dependent combo box) will have your 'CustomerName' in one column, then the attribute you are looking up in another column, such as their 'ShipTo' addresses for instance. So table B can have the same value for the CustomerName listed multiple times, but each time it is listed, it has a different ShipTo address in the other column.

    2. Create a form named 'frmCustomer' with the independent combo box. Name the independent combo box 'cmbCustomerName' Once you drop the combo box on the form, the wizard should take you through the rest. In the end, you should have this in your row source:
    SELECT [tblA].[CustomerName] FROM tblA;

    3. Drop another combo box onto your form (this will be the dependent combo box). Name this combo box 'cmbShipTo' The wizard will come up again. This time you will want to point the combo to the 'ShipTo' address column in TblB. The row source should look like this after:
    SELECT [tblB].[ShipTo] FROM tblB;

    4. Now for the cool part. Change the row source in the dependent combo to this:
    SELECT [tblB].[ShipTo] FROM tblB WHERE ((([tblB].[CustomerName])=[Forms]![frmCustomer]![cmbCustomerName]));

    5. Lastly, you will need a bit of code in the after update event of the independent combo box. It should look like this:
    Private Sub cmbCustomer_AfterUpdate()
    Me.cmbShipTo.Requery

    Note: If you dependent combo is on a subform, but your independent is on a the main form, your coding will be slightly different.

    Regards,
    James C.
    EDI Analyst[/u]

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

Similar Threads

  1. Hidden all of Microsoft Access Menus.
    By todaytips in forum Access
    Replies: 0
    Last Post: 10-28-2008, 08:21 AM
  2. drop down issues
    By gromit95 in forum Access
    Replies: 3
    Last Post: 07-21-2008, 08:14 AM
  3. Drop down question.
    By Simon Sweet in forum Forms
    Replies: 4
    Last Post: 02-05-2008, 12:32 PM
  4. Replies: 0
    Last Post: 12-20-2007, 12:32 AM
  5. form drop down
    By arianhojat in forum Forms
    Replies: 0
    Last Post: 06-01-2006, 11:56 AM

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