Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2015
    Posts
    9

    Unanswered: Command continuous form to show certain records

    Hello,

    I have a continuous subform used for data entry that contains just one combo box. The combo box rowsource comes from a table (tblStates) that contains a record for each of the US states and Canadian provinces (both are listed in the same field "State/Province"). A second field in tblStates shows the applicable country, either US or Canada.

    Ultimately, the subform works exactly like the one on this site: http://stackoverflow.com/questions/1...ccess-database (about half way down the page).

    I am hoping to add two button commands, that when executed will pre-populate the subform with either all of the US records or all of the Canada records. (So for example, the US command would result in 50 combo boxes, each with the name of a different US state).

    Is this possible? How would I go about it?

    Many thanks in advance.

    Ian

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You could use an append query that got its records from the states table with a criteria on country.

    CurrentDb.Execute "INSERT INTO TableName(FieldName) SELECT [State/Province] FROM tblStates WHERE Country = 'US'"
    Paul

  3. #3
    Join Date
    Nov 2015
    Posts
    9
    Paul, thanks for your response. How would I get my combo box to select all of the records appended to that table?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use a filter on the combo box
    use a country designation in the state/province table
    add a radio button that decides if its Canada or the US (Actually you may be better of using cascading combo boxes )one selects the country, which when selected populates the combo box for state/province..


    incidentally coudl I point out that saying soemthign along the lines of
    Ultimately, the subform works exactly like the one on this site: http://stackoverflow.com/questions/1...ccess-database (about half way down the page).
    is, I'd argue feck all use. just because you are adapting code form elsewhere and it isn't working in your form/report is useless information. we dont' knwo what code runs behind that form nor your form... and its your form that matters
    ...just saying
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2015
    Posts
    9
    Thanks for the suggestions. That makes sense about the form I linked to, thought it would help visualize. Instead I am posting my database here for you all to take a look at. I'm still having trouble implementing the combo box, so hopefully this will allow you to give specific suggestions.

    On frmSourceEntry, you will see the combo box I'm referring to ("State/Province"). Some of these sources (the primary records I'm tracking) apply to more then one or all states or provinces. For a source that applies to all states, I need to allow the user to automatically populate all of the US states in that subform, rather then selecting each state one-by-one. All of the states must be connected with the record, rather than simply connecting the record to country = United States, because when a user filters by state on frmSourceSearch, all sources that have records for that state must display, including those with national coverage.

    I am now realizing that this is spawning a new problem. My search form is displaying a "duplicate" record for the same source because it is attached to multiple states. For example, I have the record "Accurint" associated with four states (for testing) and the country United States. When you filter by sources that cover the United States on my search form, it shows four records for Accurint (one for each state). Is this avoidable?

    As I'm writing this, I am realizing I probably need more help than I thought. Any pointers you all can give are massively appreciated.

    Many thanks

    Sources Database.zip

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    with in reason anythign is possible with a db
    ..it just depends on how its been designed and implemented.

    granted that not a helpful answer but inj the first instance the answer to virtually ANY system question is YES it can, but the beggar is int he detail
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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