Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2015
    Posts
    16

    Question Tables, Combo-box and auto population based on a different combo selection on form

    Hi everyone,
    What is the best practice of storing lists of Subdivisions/Complexes for each town of a state. I would like on the Prospect entry form for the subdivisions to be populated in a combo based only on the selected township in a different combo on that form.

    Example -
    User selects Township and the Subdivision Combo should populate with only subdivisions in that particular town.


    Notes
    Township combo on form is sourced from a query displaying for example - Howell, NJ 07731.
    Not every Street is in a subdivision so I also need to have a None option for each subdivision in each town.


    Would I create a table with a column for each town with subdivisions listed under that town then use code to have the Subdivision combo populate by column or is there a better way? I would definitely need help with the VBA but I know there is a different section of the forum for VBA. At this point it is purely design.

    I am currently using a combo on the Prospecting form and adding new subdivisions as needed but this is very impractical, the list is getting long and I can't sort the subdivisions.

    Thanks for all your help in advance.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    No vba needed..
    User pick a town in combo. QsTowns.
    SubDivision cbo has the qsSubDiv query. this query has cboTown in the criteria. It automatically shows subDivs othe 1 town in the cboTown.
    No vba.

  3. #3
    Join Date
    Apr 2015
    Posts
    16
    Ranman56 thank you for your time in helping me. Would you mind simplifying how I can structure the Queries and the criteria to make your answer work. I have just started learning SQL and how to write queries but I think your answer is too advanced at this point. Thanks so much, I really appreciate it.

    Darren.

  4. #4
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    in query design, pick your table states. bring down the State abbrev., save query as qsStates.
    this would be in cboStates.rowsource = qsStates

    new query
    for towns, bring down fields: [town], [state], save query as qsTowns (this would show all records)
    modify it further to filter on a state save as qsTowns1State
    select [town], [state] where [state] = forms!frmMain!cboState

    cboTowns.rowsource = qsTowns1State

    After the state has been picked, in the event AFTERUPDATE of cboState, you must refresh the list so qsTowns1State and get the change of state..


    Code:
    private sub cboState_Afterupdate()
       cboTowns.requery     'this will refresh the data in the list to get the new state
    end sub

  5. #5
    Join Date
    Apr 2015
    Posts
    16

    Question Still Stuck

    Quote Originally Posted by ranman256 View Post
    in query design, pick your table states. bring down the State abbrev., save query as qsStates.
    this would be in cboStates.rowsource = qsStates

    new query
    for towns, bring down fields: [town], [state], save query as qsTowns (this would show all records)
    modify it further to filter on a state save as qsTowns1State
    select [town], [state] where [state] = forms!frmMain!cboState

    cboTowns.rowsource = qsTowns1State

    After the state has been picked, in the event AFTERUPDATE of cboState, you must refresh the list so qsTowns1State and get the change of state..


    Code:
    private sub cboState_Afterupdate()
       cboTowns.requery     'this will refresh the data in the list to get the new state
    end sub
    Dear Ranman 256,
    I am so sorry but I am struggling with the reply you gave. I kind of understand it but not enough to execute the instructions.
    I think part of it might be my fault. I only work in one state - NJ. I have a table called SubDivisions with columns as Twp and SubDivision.

    On the Main form(Prospecting) I have two combos - Town and Subdivision.

    I know the answer will not be much different from the last one, but I think [State] references in your reply are making it a bit tricky for me to fully comprehend what I need to do. I am really sorry. I do not know if this will help but the Township values in the Subdivision table are related to a table storing Townships and Zips.

    I really appreciate the patience you must need to do this and thank you very very much for helping me. i have attached some screenshots to help you understand the current Main Form Prospecting, Subdivision Table and the start of a query. Hoping they help even a bit.Click image for larger version. 

Name:	CurrentQueryFor ComboPopulation.JPG 
Views:	1 
Size:	23.0 KB 
ID:	16370Click image for larger version. 

Name:	Prospecting Table.JPG 
Views:	1 
Size:	23.7 KB 
ID:	16371Click image for larger version. 

Name:	SubDivisions.JPG 
Views:	0 
Size:	58.3 KB 
ID:	16372

    Darrren

Tags for this Thread

Posting Permissions

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