Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Question Unanswered: Cascading Info in dropdowns

    Hi Guys,

    I've been searching through the examples and help here but i can't seem to get my head around something I THINK should be very simple!?... basically i have the following:

    Table with all Store addresses i.e.

    Customer Name -> Branch Name -> Store Number -> Postcode
    (Currys) -> (Birmingham) -> (2228) -> (B1 9NS)

    and in a seperate table where reports are filled out i need to have 4 dropdown boxes that can filter out the other 3 fields until you have the correct 'combination' e.g. if I select Currys in the first dropdown box it will only list all the locations of Branches/store numbers/postcodes from the main datavase of all stores. But i also would like the other dropdowns (perhaps not the postcode one) so you can select from branch name and that will still filter the reamaining 3.... if it's easier then i'd be MORE than happy if someone could just spell out in lamens terms how i could just cascade from start to finish and end up with a result that can be stored in 4 new fields in a different table (along with all the other info on that particular store.)

    sorry if thats not clear but if i'm having problems with explaining what i need then you can see why i can't do it!!

    Any help is MUCH appreciated!!

    Thanks

    Hyperboy!

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Hyperboy--

    So which part is giving you the problem? Cascading the drop down lists or then inserting the 4 fields into a new table?

    Here's basically what you need to do for cascading the drop down lists:

    In the control source for Customer Name put in a query that returns all Customer Names. In the control source for Branch Name put in a query that limits the records based on the value in the Customer Name combo box. In the control source for Store Number put in a query that limits the records based on the values in the combo boxes for Customer Name and Branch name. And finally, (if you want to) in the control source for Postcode put in a query that limits the records based on the values in the combo boxes for Customer, Branch and Store.

    Then you need to put some code in the After Update Event for the combo boxes that will fire off when one of the combo boxes is updated. In the Customer Name combo box's After Update event you need to requery the combo box for Branch Name. In Branch Name's combo box you need to requery Store Numbers combo box. Etc.

    Hope that makes some sense and points you in the right direction.

    C

  3. #3
    Join Date
    Sep 2009
    Posts
    3
    I get what you're saying in ENGLISH.... but transferring that into Access terminology is giving me a headache... i hadn't even thought about using queries and when i have used them they never seem to do what i want... what code will i need to put in the AfterUpdate box? i would be extremely grateful if someone could post a basic example db!?... i think its the AfterUpdate thing i'm having an issue with, i can limit the values int he dropdown to the queries but can't get the 'next' dropdown to only display the values of the previous values info... hope you don't think i'm being lazy and all help is much appreciated!
    Last edited by Hyperboy; 09-28-09 at 06:52.

  4. #4
    Join Date
    Sep 2006
    Posts
    265
    There would appear to be three main criteria as it is unlikely that once your choose the branch there is only going to one outlet. That aside, set up a can set up a all encompassing search criteria by creating a search form with 4 fields:

    Code:
    Private Function SearchClientsCriteria()
        With CodeContextObject
                SearchClientsCriteria = "[Client Company Name] like '" & .[Field1] & "*" & "' and [Client Branch Name] like '" & .[Field2] & "*" & "' and [Client Store Number] like '" & .[Field3] & "*" & "' and [Client Postcode] like '" & .[Field4] & "*" & "''"
    Then on a Command Button On Click another Function:

    Code:
    Function SearchClients()
    
        DoCmd.OpenForm "Clients", , "", SearchClients, , acWindowNormal
    
    End Function
    The advantage of this approach is that you can get all any outlet fitting the criteria.

    Alternatively using Functions again:

    On Field2:

    Code:
    Function LookupClientsBranchName()
    
    Dim MyControl As Control
            Set MyControl = Screen.ActiveControl
    
        With CodeContextObject
            MyControl.RowSource = "SELECT Clients.[Client Branch Name], Clients.[Client Company], FROM ClientsQuery WHERE .Clients.[Client Company]= '" & .[Feild1] & "' ORDER BY ClientsQuery.[Company Branch Name];"
        End With
    
    End Function
    on Field3

    Code:
    Function LookupClientsStoreNumber()
    
    Dim MyControl As Control
            Set MyControl = Screen.ActiveControl
    
        With CodeContextObject
            MyControl.RowSource = "SELECT Clients.[Client Store Number],Clients.[Client Branch Name], Clients.[Client Company], FROM ClientsQuery WHERE .Clients.[Client Client Branch Name]= '" & .[Feild2] & "' ORDER BY ClientsQuery.[Company Branch Name], Clients.[Client Store Name];"
        End With
    
    End Function
    As you can see you gradually restrict the number of Clients until you end up with the outlet.

    Simon

  5. #5
    Join Date
    Sep 2009
    Posts
    3
    Hi Simon,

    thanks very much for this code but i am not sure exactly wehre I should paste this info perhaps it would be easier with my structure below?

    DATABASE CLEAN (table that has all the stores addresses and info etc..)
    fields: ID , Account Name, Branch Name, Store Name, etc....

    and then the table that i need to be cascaded/populated with the info from the above table:

    REPORTS
    fields: ID, Account Name, Branch Name, Store Name, etc...

    do i need to create a seperate form or can this be included in the form that is used to populate the REPORTS table

    i'm really sorry but this is doing my head in as i do not understand any code, i am only used to using wizards and inbuilt help from microsoft!!

  6. #6
    Join Date
    Sep 2006
    Posts
    265
    Yes create something like this:

    Gallery Solutions Clients

    Simon

Posting Permissions

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