Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Printing Reports (Access 2003)

    I have attached a database that I downloaded from this website; it has a main form that displays two subforms; I'm trying to print a report from the Product SubForm; I want the Report to only display the Products that are chosen from the Category SubForm. I get a Data type mismatch criteria expression; but not sure why this is happening.

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

    Re: Printing Reports (Access 2003)

    fkeyCategoryID is a number field, so you shouldn't put quotes around the value in stLinkCriteria: change
    <code>
    stlinkCriteria = " [fkeyCategoryID] =" & "'" & [Forms]![frmMain]![txtLink] & "'"
    </code>
    to
    <code>
    stlinkCriteria = " [fkeyCategoryID] =" & [Forms]![frmMain]![txtLink]</code>

  3. #3
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing Reports (Access 2003)

    Thanks; for the code and for the information between number fields and test fields. I have a new problem; I placed some labels on the subform called sbfProducts; I made the labels sort the Product ID and Product Name in ascending or descending order based on clicking on the labels. If I click on the Product Name label and sort the data by ascending order the form sorts the data; however when I click on the Preview Report command button the report displays the data that is displayed in the form; but not in the order that is displayed in the form. I have attached the database.

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

    Re: Printing Reports (Access 2003)

    Add the following lines below the DoCmdOpenReport line:

    If Me.OrderByOn = True Then
    Reports(stDocName).OrderBy = Me.OrderBy
    Reports(stDocName).OrderByOn = True
    End If

  5. #5
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing Reports (Access 2003)

    If you look at the Main Form I have included a Text Box that displays the current value of the SubForm's sbfProducts Order By value. When you open the attached database you will see that the SubForm's sbfProducts Order By value = strProductName DESC. When you click on the Report Preview Button the report Order By value is blank; look at the footer of the report or open the report in design view and you will notice that the Order By value does not = strProductName DESC. What I'm I missing here; why doesn't the report use the value from the form?

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

    Re: Printing Reports (Access 2003)

    Subforms are not part of the Forms collection. If you want to refer to a subform, you must do it like this:

    Forms!MainForm!SubForm

    where SubForm is the name of the subform as a control on the main form. And if you want to refer to a property of the subform as a form as opposed to the subform as a control on the mainform, you must do it like this:

    Forms!MainForm!SubForm.Form.OrderBy

    For the same reason, you can't use IsLoaded("sbfProducts"). Test for IsLoaded("frmMain") instead. So the code becomes
    <code>
    Private Sub Report_Open(Cancel As Integer)
    If IsFormLoaded("frmMain") = False Then
    Exit Sub
    End If

    If Forms!frmMain!sbfProducts.Form.FilterOn = True Then
    Me.Filter = Forms!frmMain!sbfProducts.Form.Filter
    Me.FilterOn = True
    End If

    If Forms!frmMain!sbfProducts.Form.OrderByOn = True Then
    Me.OrderBy = Forms!frmMain!sbfProducts.Form.OrderBy
    Me.OrderByOn = True
    End If
    End Sub
    </code>
    (I removed the part referring to a non-existent variable stDocName)

Posting Permissions

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