I posted this same post in the forms discussion forum but didn't get much of a responce, so I thought I'd try here as well.
I am trying to set up a report menu similar to the one found in the Northwind database. A difference between my database and Northwind is there is not an option group on the reports menu. This is what I have done;
I have a table called “Product Types” that has a list of “Product Descriptions” and a product ID key field.
On the Reports Menu, which I have called “Reports Menu,” there is an unbound list box called ProductSelect. It has the following row source:
SELECT DISTINCTROW [Product Types].[Product Description] FROM [Product Types] ORDER BY [Product Types].[Product Description];
Below the List Box is a button called “Preview” that has the following code behind it (this code was copied from the Northwind Database and modified slightly to meet my table/field/report names and to do away with the Option group information):
Private Sub Preview_Click()
Dim strWhereProduct As String
strWhereProduct = "Product Description = Forms![Reports Menu]!ProductSelect"
If IsNull(Forms![Reports Menu]!ProductSelect) Then
DoCmd.OpenReport "GenericAddressBook", acViewPreview
Else
DoCmd.OpenReport "GenericAddressBook", acViewPreview, , strWhereProduct
End If
'DoCmd.Close acForm, "Reports Menu"
Exit_Preview_Click:
Exit Sub
Err_Preview_Click:
Resume Exit_Preview_Click
End Sub
The Sub Report “GenericAddressBook” is based off of a query similar to the one that the report generated by Northwind is. I am encountering the following problems:
The first part works great. If nothing is selected by the list box the resulting report is a complete list of product purchasers. I am encountering the following problems:
If I select a product from the list box and press the button I receive this error:
Run-time error ‘3075’ Syntax error (missing operator) in query expression ‘Product Description = Forms![Reports Menu]!ProductSelect’
If I select DEBUG I am taken to this line in my code:
DoCmd.OpenReport "GenericAddressBook", acViewPreview, , strWhereProduct
After studying this error I thought that perhaps it may be caused by ‘Product Description’ being two words. I placed an underscore between them in the code so that it was ‘Product_Description’ (I made no changes elsewhere to tables or queries).
However, with this change I am then prompted to enter a Product_Description. If I do this I am the report then opens, but the fields say Error rather than having data in them.
If anyone has any idea what I have done wrong I would appreciate suggestions.
Sorry this post is so long winded but I thought I had better include as much information as possible to give you the best idea of my problem.
P.S. I know that a report of this nature can be generated similarly by inserting the prompt [enter product description] in the criteria field of the query but I hope to avoid that as there will be hundreds of products and I though it would be a pain to keep having to refer back to another table before running the query.
I am trying to set up a report menu similar to the one found in the Northwind database. A difference between my database and Northwind is there is not an option group on the reports menu. This is what I have done;
I have a table called “Product Types” that has a list of “Product Descriptions” and a product ID key field.
On the Reports Menu, which I have called “Reports Menu,” there is an unbound list box called ProductSelect. It has the following row source:
SELECT DISTINCTROW [Product Types].[Product Description] FROM [Product Types] ORDER BY [Product Types].[Product Description];
Below the List Box is a button called “Preview” that has the following code behind it (this code was copied from the Northwind Database and modified slightly to meet my table/field/report names and to do away with the Option group information):
Private Sub Preview_Click()
Dim strWhereProduct As String
strWhereProduct = "Product Description = Forms![Reports Menu]!ProductSelect"
If IsNull(Forms![Reports Menu]!ProductSelect) Then
DoCmd.OpenReport "GenericAddressBook", acViewPreview
Else
DoCmd.OpenReport "GenericAddressBook", acViewPreview, , strWhereProduct
End If
'DoCmd.Close acForm, "Reports Menu"
Exit_Preview_Click:
Exit Sub
Err_Preview_Click:
Resume Exit_Preview_Click
End Sub
The Sub Report “GenericAddressBook” is based off of a query similar to the one that the report generated by Northwind is. I am encountering the following problems:
The first part works great. If nothing is selected by the list box the resulting report is a complete list of product purchasers. I am encountering the following problems:
If I select a product from the list box and press the button I receive this error:
Run-time error ‘3075’ Syntax error (missing operator) in query expression ‘Product Description = Forms![Reports Menu]!ProductSelect’
If I select DEBUG I am taken to this line in my code:
DoCmd.OpenReport "GenericAddressBook", acViewPreview, , strWhereProduct
After studying this error I thought that perhaps it may be caused by ‘Product Description’ being two words. I placed an underscore between them in the code so that it was ‘Product_Description’ (I made no changes elsewhere to tables or queries).
However, with this change I am then prompted to enter a Product_Description. If I do this I am the report then opens, but the fields say Error rather than having data in them.
If anyone has any idea what I have done wrong I would appreciate suggestions.
Sorry this post is so long winded but I thought I had better include as much information as possible to give you the best idea of my problem.
P.S. I know that a report of this nature can be generated similarly by inserting the prompt [enter product description] in the criteria field of the query but I hope to avoid that as there will be hundreds of products and I though it would be a pain to keep having to refer back to another table before running the query.
