By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,825 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,825 IT Pros & Developers. It's quick & easy.

Creating reports from a form. Please help.

blyxx86
100+
P: 256
I am very new to Access, and have only been using it for a month now. I have learned how to do a lot of things, but have been having problems making a form update a query that then is sent to a report.

Here is what I would like to accomplish...

I have a table that has customers, and another table that has salesperson. I want to link certain salespersons to an individual customer. Also, each salesperson may have more than one customer. I then would like to create a form that will create a report of all the units that customer has under that salesperson.

For example, Joe has three customers. He has Cust1, Cust3, and Cust7. I want to create a combo box that has all the sales members and then will create a report that shows all the units that Cust1, Cust3, and Cust7 have bought. This way I can send the reports to each individual salesperson. I know I can setup a piece of code that will also send them an e-mail with the attached report.

I would also like to be able to filter the query by a date (or date range) on the form as well.

I normally wouldn't ask, but I have no idea how to make a form update a query and then generate the report. I also am not sure how to link the Salesperson to the customers.

I have tried making a form that updated a query, but when I went to open a report based on the query, it would fail. I don't have the error code because I have just been using manual date selection for the past week (ie, going in to the query and using BETWEEN date() AND date()-6, or actually entering the dates for the query)
Any help would be appreciated.

I am running MS Access 2000 on Windows XP Pro.
Nov 14 '06 #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,299
A report runs using a datasource (usually a query or table) but when you run the report, you have the option of passing a filter parameter.
What you need to do (as a general principle) is design the report to run on the whole dataset, then call it with your particular requirements from the form.

OpenReport Method
The OpenReport method carries out the OpenReport action in Visual Basic.

expression.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)
expression Required. An expression that returns a DoCmd object.

ReportName Required Variant. A string expression that's the valid name of a report in the current database. If you execute Visual Basic code containing the OpenReport method in a library database, Microsoft Access looks for the report with this name, first in the library database, then in the current database.

View Optional AcView. The view to apply to the specified report.

AcView can be one of these AcView constants.
acViewDesign
acViewNormal default Prints the report immediately.
acViewPivotChart Not supported.
acViewPivotTable Not supported.
acViewPreview

FilterName Optional Variant. A string expression that's the valid name of a query in the current database.

WhereCondition Optional Variant. A string expression that's a valid SQL WHERE clause without the word WHERE.

WindowMode Optional AcWindowMode. The mode in which the form opens.

AcWindowMode can be one of these AcWindowMode constants.
acDialog The form's Modal and PopUp properties are set to Yes.
acHidden The form is hidden.
acIcon The form opens minimized in the Windows taskbar.
acWindowNormal default The form is in the mode set by its properties.

OpenArgs Optional Variant. Sets the OpenArgs property.
Nov 14 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
If both your forms are working fine as they are

Create a query based on the Customers and Salespersons tables joining the tables on (I presume) SalespersonID as 1 salesperson can have many customers but (I hope) a customer can have only one salesperson. If your tables don't follow this structure you have a problem. Post a list of the fields in each table and I'll help you adjust it.

Once you have your query use the report wizard to create a report on this query grouping by SalespersonID. You may have to make some adjustments but this will give you a good report template to start with.


I am very new to Access, and have only been using it for a month now. I have learned how to do a lot of things, but have been having problems making a form update a query that then is sent to a report.

Here is what I would like to accomplish...

I have a table that has customers, and another table that has salesperson. I want to link certain salespersons to an individual customer. Also, each salesperson may have more than one customer. I then would like to create a form that will create a report of all the units that customer has under that salesperson.

For example, Joe has three customers. He has Cust1, Cust3, and Cust7. I want to create a combo box that has all the sales members and then will create a report that shows all the units that Cust1, Cust3, and Cust7 have bought. This way I can send the reports to each individual salesperson. I know I can setup a piece of code that will also send them an e-mail with the attached report.

I would also like to be able to filter the query by a date (or date range) on the form as well.

I normally wouldn't ask, but I have no idea how to make a form update a query and then generate the report. I also am not sure how to link the Salesperson to the customers.

I have tried making a form that updated a query, but when I went to open a report based on the query, it would fail. I don't have the error code because I have just been using manual date selection for the past week (ie, going in to the query and using BETWEEN date() AND date()-6, or actually entering the dates for the query)
Any help would be appreciated.

I am running MS Access 2000 on Windows XP Pro.
Nov 15 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.