Results 1 to 7 of 7
  1. #1
    JPR is offline Novice
    Windows Vista Access 2000
    Join Date
    Jan 2013
    Posts
    4

    Unique SubForm for multiple tables or queries

    Hello,



    I would like to add a list box on my main form which lists names of Countries. When the user select the Country (double click), it should open a subform which should have it's record source to the table names as the Country selected in the list box.

    Is there a way I can do this so to avoid creating multiple sub forms?

    Thank you for any help.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the subform shows all record using query : qsAllRecs
    when user selects the country, swap query to qs1Country
    using sql: select * from table where country = forms!myForm!lstCountry

    swap it in afterupdate event:
    Code:
    sub lstCountry_afterupdate()
    me.subform.sourceObject = "qs1Country"
    end sub



    put a button to show all countries again
    Code:
    sub btnAll_Click()
    lstCountries = null    'clear the selection
    me.subform.sourceObject = "qsAllRecs"
    
    end sub



  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum......

    First terminology. A "subform" is a form embedded in another form. You don't "open" a subform (although a subform can be opened independently of the main form). It sounds like you just want to open a different form.


    You didn't give names of object so I am going to make up names.

    You have a table of countries named "tblCountries".
    You have a main form named "frmMainForm"
    On the main form you have a list box named "lstCountries".
    The row source is a query based on the table "tblCountries".
    The form you want to open after selecting a country is named "frmForm2"

    The question now is "What does the form "frmForm2" do/display? What is the record source? Is it to create a new record? Edit a record?
    Would you post an image of "frmForm2"?

    The basic code to open a form would be
    Code:
    Private Sub lstCountries_DblClick(Cancel As Integer)
        DoCmd.OpenForm "frmForm2"
    End Sub
    but this will change depending on what you want the form to do.


    Maybe you could post your dB with a few records.......????

  4. #4
    JPR is offline Novice
    Windows Vista Access 2000
    Join Date
    Jan 2013
    Posts
    4
    Hello Steve,
    thanks for your help. I think that my original idea was a bit confusing, as I did not realize that I could not have the same subform to open multiple tables as each table although has similar fields, refers to different information. Basically in my sub some fields are calculated fields, and the criteria changes for each country. At this point, I would like to try to have a Main Menu with the list of Country and on the double click event, open the respective form. Furthermore, I would like that when the db opens, the main menu only shows the listbox and that once a form related to a country is closes, the main menu should again only display the listbox. For your convenience I am attaching the sample db. Thanks
    Attached Files Attached Files

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I've got a little better idea now.

    For each country, will there be only one record (entry) or will there be many records?
    Can you show a couple of examples of the calculations/criteria for each country?

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    No answers yet, so I'll just post what I have so far.
    Attached Files Attached Files

  7. #7
    JPR is offline Novice
    Windows Vista Access 2000
    Join Date
    Jan 2013
    Posts
    4
    Thank you Steve and sorry for not replying earlier but was out for a few days.
    I have looked at your sample and works fine. I got the idea and I can now make the necessary changes. Appreciate your help.

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

Similar Threads

  1. Replies: 6
    Last Post: 09-10-2017, 08:13 PM
  2. Replies: 3
    Last Post: 11-22-2013, 04:22 PM
  3. Replies: 3
    Last Post: 10-18-2013, 07:29 AM
  4. Importing XML with XSLT to multiple tables without unique key
    By ObstructedBelt in forum Import/Export Data
    Replies: 3
    Last Post: 06-27-2012, 01:21 PM
  5. Unique queries - Unique Count
    By bigfish in forum Queries
    Replies: 7
    Last Post: 03-14-2012, 01:28 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