Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Nov 2003
    Location
    UK
    Posts
    20

    Unanswered: Help needed printing current record from query

    First off, Hi. I'm completely green and self-taught at MS Access so excuse my lack of knowledge.

    I have made a single-table database which fulfills an invoicing function and need some help with queries.

    My data entry is via on-screen form and I can print, save, review, etc. and output the current record to my chosen report with no problems.

    My query prompts the user for an invoice number, and then outputs the results to an on-screen form. I would like to put buttons on this to preview and print the current record in the query using my assigned report. I would also like to browse through the query results record by record using buttons on the on-screen form. (I think I can do that bit!)

    I have everything else working the way I need it, but I am completely stumped on the previewing and printing. On my previous attempts, I have ended up printing every single record in the query.

    I am using MSAccess XP. Thanx in advance

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    You can make a botton of preview with the wizard, or what is the problem.
    Saludos
    Norberto

  3. #3
    Join Date
    Nov 2003
    Location
    UK
    Posts
    20
    Thanks.

    Once the query has run, I need to be able to print the results on a per-record basis to my report. I can browse through the results one at a time, but cannot find a way to print them individually.

    I can do this from an on-screen form when I am entering data, but NOT when I have run the query and am displaying the results.

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Sorry but i can understand your problem, post an sample of that
    Saludos
    Norberto

  5. #5
    Join Date
    Nov 2003
    Location
    UK
    Posts
    20
    I'm sorry. I would do if I could, but I don't understand what you want to see. My knowledge of Access is so limited that I am having trouble explaining what I have done so far.

    I have a database that uses a form for input into a simple table. The table holds invoice data i.e. Invoice number, name, address, etc. and a Yes/No checkbox to indicate whether an invoice has been paid.

    I have a query that allows me to view all unpaid invoices. The results of this query are output to an on-screen form to view the data record by record and also to allow me to update the payment details. I need a button on the form to print the data in the record I am currently viewing to a report. Every time I try to do it, I end up printing all the unpaid records returned by the query instead of just the one I am looking at.

    I hope that makes things clearer.

  6. #6
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    I thing you must make and combo box in the form to selecc only the records that do you want to see and print one by one
    Saludos
    Norberto

  7. #7
    Join Date
    Nov 2003
    Location
    UK
    Posts
    20
    It's not an option that I had considered, and to be honest I'm not too sure how to go about it.

    I can't understand why I can't use a button to print the current record like I can on my data entry form.

  8. #8
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    You have a report for the form or use the print option direct.
    Saludos
    Norberto

  9. #9
    Join Date
    Nov 2003
    Location
    UK
    Posts
    20
    My data entry on-screen form has a print button to print the current record using a report.

    My query on-screen form is basically a copy of the data entry one and I want it to have a button to print only the record I am viewing using a report.

    I have the form set up and it works ok. I have the report set up and it works ok too. Only problem is it prints all the records that match the query, not just the one I am viewing.

  10. #10
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by Mordecai
    My data entry on-screen form has a print button to print the current record using a report.

    My query on-screen form is basically a copy of the data entry one and I want it to have a button to print only the record I am viewing using a report.

    I have the form set up and it works ok. I have the report set up and it works ok too. Only problem is it prints all the records that match the query, not just the one I am viewing.
    All you need is a where clause for the code that opens your report.

    If you want to preview /print only the currently displayed record then create a variable that stores the current Invoice number and one that stores a string for the where clause in the command button_click procedure. For instance:

    Dim lngInvoice as long, strWhere as string

    This assumes a long data type for the invoice No.

    lngInvoice = Me.InvoiceNo
    strWhere = "InvoiceNo = " & lngInvoice

    DoCmd.OpenReport "rptName",acViewPreview, ,strWhere

    I think I got the number of commas right?!

    This should do it.

    Gregg

  11. #11
    Join Date
    Nov 2003
    Location
    UK
    Posts
    20
    Thanks Gregg. I'll give that a try when I get into work tomorrow. As I said, I'm a complete know-nothing when it comes to Access, but I think I understand your code. It looks similar to the ME.ID thing I used on my data entry form but couldn't get working on my query. I take it the results from a query don't use the same ID thing as the records in the table, then?

    Thanks Norberto for having the patience to coax my problem out of me when I didn't understand what I was talking about!

  12. #12
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by Mordecai
    Thanks Gregg. I'll give that a try when I get into work tomorrow. As I said, I'm a complete know-nothing when it comes to Access, but I think I understand your code. It looks similar to the ME.ID thing I used on my data entry form but couldn't get working on my query. I take it the results from a query don't use the same ID thing as the records in the table, then?

    Thanks Norberto for having the patience to coax my problem out of me when I didn't understand what I was talking about!
    I'm not sure what you meant by the results from a query don't use the same ID thing as the records in the table. Your report I would imagine has the Invoice No in it and the currently displayed record on your form should have the Invoice No in it.

    Also, check and see what data type you have assigned to the Invoice No. If it is a text data type you will have to write the strWhere clause differently. strWhere = "Invoice No = '" & strInvoice & "'". You will also have to declare the invoice variable as a string instead of a long data type.

    I just reread your original post. Are you using a paremeter query for the report or just the second form? I don't want to confuse the issue too much. I usually use where clauses when I am viewing all the records in a form. In other words you would call the report from the first form.

    If it doesn't work for you just call back and I'm sure we'll get it straight.

    Gregg

  13. #13
    Join Date
    Nov 2003
    Location
    UK
    Posts
    20
    Ok. Got into work, and realised I didn't understand how to implement the code or even what a where clause is!

    Here is the code I am using to print the current record in the data entry form:
    -------------------------------------------
    Private Sub Print_Click()
    On Error GoTo Err_Print_Click

    Dim stDocName As String
    Dim strWhere As String

    stDocName = "rptINVOICE"
    strWhere = "[RunID]=" & Me!RunID
    DoCmd.OpenReport stDocName, acPreview, , strWhere

    Exit_Print_Click:
    Exit Sub

    Err_Print_Click:
    MsgBox Err.Description
    Resume Exit_Print_Click

    End Sub
    --------------------------------------------

    This code will not work on the other form which shows the results of the query.

    The invoice number is an autonumber field, formatted to give leading zero's. The query just looks for all unpaid invoices by checking the status of the Yes/No tickbox and outputting the results to an onscreen form called frminvUNPAIDqry. I can then browse the results one record at a time. The print button on the form needs to output the currently selected record from this form to the report rptinvUNPAIDqry and print it.

  14. #14
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by Mordecai
    Ok. Got into work, and realised I didn't understand how to implement the code or even what a where clause is!

    Here is the code I am using to print the current record in the data entry form:
    -------------------------------------------
    Private Sub Print_Click()
    On Error GoTo Err_Print_Click

    Dim stDocName As String
    Dim strWhere As String

    stDocName = "rptINVOICE"
    strWhere = "[RunID]=" & Me!RunID
    DoCmd.OpenReport stDocName, acPreview, , strWhere

    Exit_Print_Click:
    Exit Sub

    Err_Print_Click:
    MsgBox Err.Description
    Resume Exit_Print_Click

    End Sub
    --------------------------------------------

    This code will not work on the other form which shows the results of the query.

    The invoice number is an autonumber field, formatted to give leading zero's. The query just looks for all unpaid invoices by checking the status of the Yes/No tickbox and outputting the results to an onscreen form called frminvUNPAIDqry. I can then browse the results one record at a time. The print button on the form needs to output the currently selected record from this form to the report rptinvUNPAIDqry and print it.
    Is the RunID field present in the second form and in the report?

    If it is not, you could include it and it should work.

    Gregg

  15. #15
    Join Date
    Nov 2003
    Location
    UK
    Posts
    20
    No, the [RunID] field is not present, but it is also not present in the original form or report, yet it works. I think it is something to do with a pre-defined field or something. I got this piece of code from a book which I borrowed.

Posting Permissions

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