Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Oct 2002
    Location
    Charlotte, NC
    Posts
    45

    Red face Unanswered: Problem with Subform

    I'm new to using Subforms. Maybe someone can give me some quick advice

    I have attached a picture of the form (Generate_Test) which includes the subform (Gen_Subform).

    I have a combobox (Combo21) which contains the names of different table names.

    After I select a tablename from Combo21.. I want it to display the table in the subform.

    Can anyone help? Thanks.
    brian
    -----

    "How much deeper would the ocean be without sponges?"

  2. #2
    Join Date
    Oct 2002
    Location
    Charlotte, NC
    Posts
    45

    Here is the Attachment

    Posting the visual
    Attached Thumbnails Attached Thumbnails pic1.jpg  
    -----

    "How much deeper would the ocean be without sponges?"

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Im not sure exatly what you need or the true set up of the subform. If the subform is powered by a query and the query has the combo as a condition say
    select tablename from table_tables where tablename like combo1
    then what you need to do is in the after update event of combo1
    put
    DoCmd.Requery "Subform_Name"

    That will refresh the subform and the new data will be shown.

    If this doesnt help then tell us how the subform is fed its info
    Jim

  4. #4
    Join Date
    Oct 2002
    Location
    Charlotte, NC
    Posts
    45

    Red face So Far, So Good, But Still Not There

    I think I have followed the above directions, but I'm obviously missing something. Let me fill you in:

    My subform has the following RecordSource:
    SELECT [Query2].[Test_Name] FROM Query2;

    Query Two is this:
    SELECT [Test_Name]
    FROM Test_Database
    WHERE Test_Name=[Forms]![Generate_Test].[combo21];

    Test_Database is a one column table with the databases's tables listed. EX: Test_1, Test_2, Mid_Term, Test_3, Test_4, Final...

    This Query does work. It outputs "Test_1" or "Test_3" or whichever test is selected in the combo box... which are the names of my various tables.

    I made sure to put the AFTERUPDATE function given in an above posting into my code. which is to requery the subform and reshow it.

    Everything seems to work, except when I select something from the Combo box, the subform is still blank.

    I know this is a long shot.. but if anyone happens to have any ideas.. they would be much appreciated.

    brian
    -----

    "How much deeper would the ocean be without sponges?"

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: So Far, So Good, But Still Not There

    Originally posted by stratego16
    I think I have followed the above directions, but I'm obviously missing something. Let me fill you in:

    My subform has the following RecordSource:
    SELECT [Query2].[Test_Name] FROM Query2;

    Query Two is this:
    SELECT [Test_Name]
    FROM Test_Database
    WHERE Test_Name=[Forms]![Generate_Test].[combo21];

    Test_Database is a one column table with the databases's tables listed. EX: Test_1, Test_2, Mid_Term, Test_3, Test_4, Final...

    This Query does work. It outputs "Test_1" or "Test_3" or whichever test is selected in the combo box... which are the names of my various tables.

    I made sure to put the AFTERUPDATE function given in an above posting into my code. which is to requery the subform and reshow it.

    Everything seems to work, except when I select something from the Combo box, the subform is still blank.

    I know this is a long shot.. but if anyone happens to have any ideas.. they would be much appreciated.

    brian
    the way that I do this is instead of after update of the combo I put in a button, but thats becuase I have multiple combos that all need to be evaluated. The major thing is that not only do you need a docmand.requery but you also need a docmd.runquery that runs the query before you to the refresh. That way the refresh will have uptodate information. Try that
    Jim

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try a different afterUpdate event:
    Me!subFormName.requery

    why complicate life with:
    My subform has the following RecordSource:
    SELECT [Query2].[Test_Name] FROM Query2;

    you could equally use [Test_Name] directly from Query2

    izy

  7. #7
    Join Date
    Oct 2002
    Location
    Charlotte, NC
    Posts
    45

    Angry I feel we are on the right track

    Thank you for the suggestions guys.

    First, I can't find a DoCmd.RunQuery command. I think when I refresh the subform.. it runs the Query again... (since it set as it's RecordSource).

    Next, I tried a different afterUpdate event:
    Me!subFormName.requery
    but I think this tries to reference a field.. and did not work


    This is what my new idea is... I know if I just run the subform.. it asks me what is in the combo box.. I type it Test_1 (which should work).. and it still comes up with no information. But if I type in Test_1 in the subform's RecordSource.. it works.

    I am thinking the query returns an invalid data type to the subform's RecordSource... which it can't understand.

    Any clues?
    -----

    "How much deeper would the ocean be without sponges?"

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...just wanted to check:
    you did replace subFormName in Me!subFormName.requery with the real name of your subform, didn't you?

    to get the real name, open the main form in design view and click just once on the subform and check Properties-Other-Name

    i promise you it works!


    izy

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    do you have any spaces in your real subFormName?

    in general - avoid spaces in names of forms/tables/fields/... like the plague. they only complicate your life.

    izy

  10. #10
    Join Date
    Oct 2002
    Location
    Charlotte, NC
    Posts
    45

    Question I am Going to Try another Approach

    I made sure there were no spaces in my Subform name.. and I did change subFormName in Me!subFormName.requery with the real name. Still nothing.

    I am going to try something new..

    I am going to try and change the SourceObject of my subform to point directly to my table.

    Can you tell me...

    Dim strNewRecord As String
    strNewRecord = "SELECT [Query2].[Test_Name] FROM Query2; "
    Me.Subform1.SourceObject = ???????

    How do I get the SourceObject to equal Table.strNewRecord

    I do not know the correct syntax

    Brian - You do not know how much I appreciate this help
    -----

    "How much deeper would the ocean be without sponges?"

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    lets go back to the beginning:

    --you have a query qryMine

    --you have a form frmMine that contains a combo cboMine which allows you to select [Test_Name] from tbl.Test_Database

    --you have experimented: after selecting in cboMine, you successfully (manually) ran qryMine and it returned the single record that you want to display in your subform.

    --you have built a new form willBeSub with data source qryMine.

    --you have opened frmMine and selected from the combo and then opened willBeSub and it shows the correct record.

    if YES so far:

    ...then drag willBeSub into (design view of) frmMine and in the afterUpdate event of cboMine put:
    me!willbesub.requery

    ...it works!

    izy

  12. #12
    Join Date
    Oct 2002
    Location
    Charlotte, NC
    Posts
    45

    Lightbulb #3.. this is different

    Izy,

    correction

    --you have experimented: after selecting in cboMine, you successfully (manually) ran qryMine and it returned the single record that you want to display in your subform.

    After selecting in cboMine (which lists table names).. I want it to successfully return ALL the records in the SELECTED table.. and display them in my subform.

    everything else is correct...
    -----

    "How much deeper would the ocean be without sponges?"

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ok: my case was single record returned - singleForm to display.
    no big deal if multiple records returned and you have multiple records display capability in the subform (continuousForms or datasheet)

    do you get the anticipated records when you (manually) run qryMine?

    no you have a query problem, not a subform problem

    yes then me!theSub.requery works



    izy

  14. #14
    Join Date
    Oct 2002
    Location
    Charlotte, NC
    Posts
    45

    Thumbs up Thanks for your help

    We've narrowed it down far enough..

    I know the Subform reads the query (which gets the table name from the combo box...).. I just need to learn how to grant control source to my fields in the subform BASED on the returned table name (from the query)

    Gimme a few more days to work on it...

    Thanks for all the help.

    brian
    -----

    "How much deeper would the ocean be without sponges?"

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    Unhappy

    duhhhh!
    did you just say table names are selected in the combo?

    can you post the sql of and the (simplified if you like) structure of any table involved in "qryMine" so an old guy like me doesn't have to stay up too late tonight.

    izy

Posting Permissions

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