Printing Reports (Access 2003)

Topic

New Reply

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.

Reply | Quote

Replies

fkeyCategoryID is a number field, so you shouldn’t put quotes around the value in stLinkCriteria: change

stlinkCriteria = " [fkeyCategoryID] =" & "'" & [Forms]![frmMain]![txtLink] & "'"

to

stlinkCriteria = " [fkeyCategoryID] =" & [Forms]![frmMain]![txtLink]

Reply | Quote

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.

Reply | Quote

Add the following lines below the DoCmdOpenReport line:

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

Reply | Quote

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?

Reply | Quote

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

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

(I removed the part referring to a non-existent variable stDocName)

Reply | Quote