Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    12

    Unanswered: Fields from two tables for a report

    Hi all. I have a form linked to a table where an user can enter 3 greeting notes for a quotation, say note 1, 2 and 3. The user can select to have either one of the 3 greeting notes to be used in the quotation.

    I have another form linked to a order details table where an user enter all order details. Finally, I would like to have a report that will generate the quotation displaying all order details of the current record being viewed; with the selected greeting note in the earlier table.

    I managed to get the report to display the order details of the current record but cannot figure out how to get the greeting note field on to the same report. This is because I could only use fields from one table when designing the report. How can I use fields from both tables?

    All comments appreciated.

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465

    Re: Fields from two tables for a report

    Originally posted by kschia
    Hi all. I have a form linked to a table where an user can enter 3 greeting notes for a quotation, say note 1, 2 and 3. The user can select to have either one of the 3 greeting notes to be used in the quotation.

    I have another form linked to a order details table where an user enter all order details. Finally, I would like to have a report that will generate the quotation displaying all order details of the current record being viewed; with the selected greeting note in the earlier table.

    I managed to get the report to display the order details of the current record but cannot figure out how to get the greeting note field on to the same report. This is because I could only use fields from one table when designing the report. How can I use fields from both tables?

    All comments appreciated.
    If you want insert in your report 3 note from first form do this.
    Define in the report 3 field and in the ControlSource of each one put this
    sentence:
    =[Forms]![NameFirstForm]![Note 1]
    =[Forms]![NameFirstForm]![Note 2]
    =[Forms]![NameFirstForm]![Note 3]

    ok
    Saludos
    Norberto

  3. #3
    Join Date
    Oct 2003
    Posts
    12
    Thanks for the reply. Although the user enter three notes in the greetings form but only one could be used at any one time. From your reply, I think I know how to insert the note field into the report now.

    However, since the user can only select one note at any one time, how can I "tell" the report which note is selected each time a report is printed?

    I hope my English is not confusing the problem am trying to relate. All comments appreciated.

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    You say that
    "However, since the user can only select one note at any one time, how can I "tell" the report which note is selected each time a report is printed? "
    How the user select the notes in the form....? Where are the notes in the form...?
    Saludos
    Norberto

  5. #5
    Join Date
    Oct 2003
    Posts
    12
    OK..please bear with me. Let me clarify my problems in details.

    I have frmNotes linked to tblNotes. In frmNotes, the user can enter 3 sets of notes but can only select one set of notes as the default in the quotation printout. This default set of notes will appear in all quotation printout regardless of which order. The notes are basically greet notes that can be the same for all reports.

    Then, I have frmOrders linked to tblOrders. This form takes in all details about a particular order. There is a print button on this form. When the button is clicked, I would like to have the default note from frmNotes to appear on the report together with the order details of the current record.

    My problems are :-

    1) How to relate the default note from frmNotes and order details from frmOrders in the same quotation report? When I click the field list button, I can only see fields from tblOrders only.

    2) The notes are entered into three textboxes in frmNotes. I wrote codes to enable one textbox at one time only, making it the default note. When I print the report, how would it know which set is selected as default ?

    Thank you for all assistance.

  6. #6
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    For the point 2):
    In the same code that you have add the the sentences to put "null" the other two fields of notes which are not select.
    Then in the Report, in the control Notes use the Control Source with and
    IIf sentence to return only the field that have not null value.
    Something like this:
    =IIf([Forms]![NameFirstForm]![Note 1]<> Null;[Forms]![NameFirstForm]![Note 1]; IIF([Forms]![NameFirstForm]![Note 2]<> Null;[Forms]![NameFirstForm]![Note 2];IIf([Forms]![NameFirstForm]![Note 3]<> Null;[Forms]![NameFirstForm]![Note 3]; Null)))

    For the point 1) if i understand the solution is the point 2).
    Saludos
    Norberto

Posting Permissions

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