Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Print Only the Invoice That I'm On (Access 2000)

    I'm just trying to fine-tune my program. I have an Invoices by Student form where you select the student you want to see. Then you click the Invoice Details button and it goes to the Invoice form screen for that student. If you select from the drop down at the top of the screen, you can see whatever year you select. There is one invoice per year.

    I have a Print Invoice button on the Invoice form. I want to be able to print the invoice that is on the Invoice form at the time. But it prints all the invoices for that student. The following code is attached to the Print Invoice button. I've tried a few different ways, but I just can't get it to print only one invoice.

    Private Sub cmdPrintInvoice_Click()
    On Error GoTo Err_cmdPrintInvoice_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Invoice"

    stLinkCriteria = "[tblStudent]![StudID]=" & "'" & Me![StudID] & "'"
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

    Exit_cmdPrintInvoice_Click:
    Exit Sub

    Err_cmdPrintInvoice_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrintInvoice_Click

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print Only the Invoice That I'm On (Access 2000)

    The Where-condition you pass to DoCmd.OpenReport filters on StudID, so you get all invoices for that particular StudID. You should use the unique identification for the Invoice instead of StudID - do you have an InvoiceID field or something like that?

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Only the Invoice That I'm On (Access 2000)

    Jennifer,

    You need to add an "And" clause to the filter (link) criteria for your Open Report statement. What you have now is:
    <font face="Georgia">
    stLinkCriteria = "[tblStudent]![StudID]=" & "'" & Me![StudID] & "'"
    </font face=georgia>
    What you need to add depends on where the year of interest is stored (it's field name, etc.). For example, if the dropdown is named cboSelectYear, something like
    <font face="Georgia">
    stLinkCriteria = "[tblStudent]![StudID]=" & "'" & Me![StudID] & "' And [tblStudent]![Year] = " & me!cboSelectYear
    </font face=georgia>
    might work. If you don't know the field names, etc. needed to get this filter to work, you might look at the VBA code for the drop down (it's AfterUpdate event) -- that should give you some clues.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Only the Invoice That I'm On (Access 2000)

    I'm still doing something wrong. I have the following code:

    stLinkCriteria = "[tblStudent]![StudID]=" & "'" & Me![StudID] & "' and [tblSchoolYear]![SchoolYear]=" & Me![ComboSchoolYear]
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

    But when I click the Print Invoice button, it keeps saying "data type mismatch in criteria expression". What does this mean?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print Only the Invoice That I'm On (Access 2000)

    What kind of field is SchoolYear? If it is a text field, you should enclose the value in single quotes, just like you did for StudID:
    <pre>stLinkCriteria = "[tblStudent]![StudID]='" & Me![StudID] & _
    "' And [tblSchoolYear]![SchoolYear]='" & Me![ComboSchoolYear] & "'"
    </pre>


  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Only the Invoice That I'm On (Access 2000)

    Now I'm getting a message "Syntax error in string in query expression '([tblStudent]![StudID]='09-126' And [tblSchoolYear]![SchoolYearID]=7')'.-----Okay, I figured it out...I forgot an apostrophe.

    But now I get an "Enter Parameter Value" for tblSchoolYear!SchoolYearID. This is where I keep going around in circles.

  7. #7
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Only the Invoice That I'm On (Access 200

    Jennifer,

    As earlier mentioned by Hans, you really need to have an InvoiceID field that is unique for every invoice you create. One way to do this is to define the AutoNumber attribute to the InvoiceID within the Invoice table. Once you have that ID available, it becomes a fairly simple mechanism to develop your report selection criteria on that InvoiceID, to the exclusion of all other invoices that may exist for the student.

    -- Jim

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Only the Invoice That I'm On (Access 200

    Okay, I finally got it to work using the InvID field! Thanks, Jim & Hans!

Posting Permissions

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