Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Select Data for Report (Access 2002)

    Do you mean only for the record that is on the mainform?

  2. #2
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    How to Select Data for Report (Access 2002)

    I have a form that displays records from one table along with a sub-form that displays linked records from a second table. I would like to have a control on the main form that calls a report that prints some information from the selected record on the main form and the data in the sub-form. How do I set up the select criteria (query?) for the report?

    In a similar way I need a control that will append data from a temporary table to the table in the sub-form, but link all of the appended records to the main record that is currently selected on the form. That is, I need to append from one table, but add and ID field corresponding to the ID of the record selected on the main form.

    I hope this all makes some sense. I am clear as to what I need to do, but I"m not sure I'm presenting in a way others can understand.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to Select Data for Report (Access 2002)

    Yes. I am printing labels -- one label for each record in the sub-form. Also, on each label I repeat some of the fields on the main form.

    Here is the setup. The main form contains the address of a location. The sub-form contains a list of the names of persons at that location. So, I need to print a set of labels containing the person's name plus the common address.

  4. #4
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Select Data for Report (Access 2002)

    On your main form add code like the one below:
    Use the command button wizard to get the basic code, then add the line that starts with limit, using your own criteria. Be sure to add the ,, limit at the end of the docmd line.

    Private Sub Command21_Click()
    On Error GoTo Err_Command21_Click

    Dim stDocName As String, limit As String
    limit = "[CustNo]=[forms]![frmCustomer]![Custno]"
    stDocName = "rptCustomerMaster"
    DoCmd.OpenReport stDocName, acPreview, , limit

    Exit_Command21_Click:
    Exit Sub

    Err_Command21_Click:
    MsgBox Err.description
    Resume Exit_Command21_Click

    End Sub

    If you do this for a form instead of a report, you can actually have the command wizard write the code for you.

    As far as the second part of your question is concerned, if you have the tables linked correctly, the append to the table in the subform should be easy:
    the append query should have a criteria that refers to the main form primary key.
    e.g.
    [CustNo]=[forms]![frmCustomer]![Custno]

    By the way, you can use the same technique for the report instead of the code above.
    Let me know if this makes no sense.

    deutsch

  5. #5
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to Select Data for Report (Access 2002)

    It looks sensible to me -- I'll give it a try. Thanks.

  6. #6
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to Select Data for Report (Access 2002)

    Hi Deutsch,

    Both of your suggestions work like a champ! Thanks for the help. I would have never figured it out for myself.

  7. #7
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Select Data for Report (Access 2002)

    Glad it worked,
    deutsch

  8. #8
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to Select Data for Report (Access 2002)

    Hi Deutsch,

    Well, it almost works. When I attempt to run the append query it starts and then pops up a window saying that it is going to append 0 records. What I found so far is that I have to go to the table I'm appending to (tblNames) and create a blank record with the ID pointing to the appropriate record in the master file. Then when I run the append query it works just fine (except I have to go back and delete the blank record). I'm attaching the query design. The query appends a list of names from tblTest to tblNames along with the ID link from tblOffice (the master table). My form displays the selected Office and a sub-table (from tblNames) with the list of names in that office. A command button on the form runs the append query.

    Any suggestion as to how to get the query to work without first creating a blank record with the master ID?

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to Select Data for Report (Access 2002)

    Chuck,

    You're using an inner join between tblOffice and tblNames, which means that if the record doesn't already exist in tblNames, then no record is selected from tblOffice. That's why you have to create an empty record. Change the inner join to an outer join or drop tblNames from the query altogether unless you use it to filter for a null ID in tblNames, which I recommend.
    Charlotte

  10. #10
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Select Data for Report (Access 2002)

    Hi Chuck,
    There is something wrong with your query. It has unrelated tables in the query design. Remove the tblTest to begin with. Then look at the rest of the query:
    You should be appending from the tblNames table the field Name to tblTest. The Name should match the id in either tblOffice or tblNames. Since tblTest does not contain the ID, you cannot append to that field.
    Let me try to explain this differently:
    1. remove tblTest
    2.append from tblnames to tbltest into the name field the record that matches the id in tbloffice.
    3. try this without a command button by typing in a valid id when the parameter dialog box pos up and see if it works correctly, only then use the command button.
    4. when it all works right, remove the messages with
    docmd.setwarnings false before the query is run, then docmd.setwarnings true after the query is run.

    Can you explain to me a little more about your design? I see that the referential integrity is not turned on in the tbloffice to tblnames tables.Let me know if the query explanation does not make sense.
    Good luck,

    deutsch

  11. #11
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to Select Data for Report (Access 2002)

    Charlott's suggestion seems to work, i.e. eliminating tblNames from the query. Thanks a bunch. It's obvious that I have a lot more to learn about Append queries.

    I'm not sure what Deutsh is getting at, because I am appending from tblTest to tblNames, not the reverse.

  12. #12
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Select Data for Report (Access 2002)

    What Charlotte is saying makes a lot of sense also. What I was trying to tell was that the table that you append to has no business in the query design.

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to Select Data for Report (Access 2002)

    It does have a place if you are filtering to exclude records already in the target table with an outer join. Otherwise, no.
    Charlotte

  14. #14
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to Select Data for Report (Access 2002)

    Thanks to Deutsch and Charlotte. Both of your suggestions helped me immensly.

  15. #15
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to Select Data for Report (Access 2002)

    I'm afraid I was too quick on the trigger regarding the Append Query solution. I deleted the one table you suggested from the query and thought that worked, but I think what happend was that I had already run the query once and when I went back to the form and ran it again it worked. However, when I create a new record in the master table form (tblOffice) and then run the query it says it is about to append 0 records. If I go ahead and do that then run the query a second time the append works.


    I'm afraid I really don't understand Inside and Outside Joins. Here is the query in SQL

    INSERT INTO tblNames ( Name, ID )
    SELECT tblTest.Name, tblOffice.ID
    FROM tblTest, tblOffice
    WHERE (((tblOffice.ID)=forms!frmOffice!ID));

    Perhaps you can suggest a modification. I'm not using the query to filter and exclude any records -- I'm just trying to append the list of names in tblTest to tblNames along with the associated ID field from tblOffice.

    tblOffice and tblNames are in a one-to-many relationship linked by the ID fields with referential integrity so that when I delete a record in tblOffice it deletes all linked records in tblNames.

    I'm wondering if prior to running the query I should refresh or update the form -- would that cause the new record in tblOffice to be set into the table?

Page 1 of 2 12 LastLast

Posting Permissions

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