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.
2 2043 NeoPa 32,568
Recognized Expert Moderator MVP
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.Open Report(ReportNa me, 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.
acViewPivotChar t Not supported.
acViewPivotTabl e 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.
MMcCarthy 14,534
Recognized Expert Moderator MVP
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Sami |
last post by:
Could someone explain clearly how to go about doing this? I have
tried setting up the structure on numerous occasions, but it never
seems to work. Could someone please help me out? Thanks!
|
by: longtim |
last post by:
I have been having endless difficulty creating reports/queries that
set any relevent parameters from controls in forms.
I am creating an application under access 2003 but will target access
2000. The access file is in access 2000 format.
I have a form that will hold the relevent parameters for the
query/report that reports the statistics for all job records that
match a certain criteria. These are:
- A Customer Name.
|
by: Tony |
last post by:
G'day
My attempt to create an MDE file was unsuccessful. The attempt produced an
error that effectively said the (MDB) file was to big. The MDB consists of
about 30 tables, 50 forms and 3 Modules.
Have others encountered this problem and if so, have the found a work
around?
Tony
|
by: erick-flores |
last post by:
Hello all
I am creating an Expense Report. There are to types of expenses:
Company expenses and Reimb Expeneses. The company expenses is under
"Expense Details Co" table and Reimb Expenses is under "Expense
Details" table. So I am creating this final report to see both type of
expenses separately but I CANT.
I got this report design from a website, but it only came with one
"Expense Details" table, so I add the Expense Details Co. The...
|
by: Swans, What Swans? |
last post by:
In one of my tables, in addition to a text field that is a Description
field, there are two fields for color formatting the data in reports.
They are Longs and hold the foreground and background color values that
the Description in that record should be displayed in. For example, the
following records might exist...
Bass - 255 - 16777215 (The word BASS displayed red on white)
Carp - 65535 - 0 (The word CAR displayed yellow on black)...
| |
by: Argonath |
last post by:
Hi,
I'm afraid this will no doubt horrendously basic as I'm a complete newbie to VB and have dived in without learning to do much... Start in the deep end and all.
So if anyone could help, I'd appreciate it;
I've got this bit of code and I couldn't for the life of me tell you why it doesn't work, if anyone else can I'd appreciate it!
Cheers
|
by: sklett |
last post by:
I've developed an ERP application that we use internally and works quite
well. I receiving more and more requests from users to print various
transactions, order forms, search results, etc. I haven't decided what the
best way to do this is because I don't have much experience with generating
printable forms.
Early on I knew one of my modules would need to print a clear report so I
used the open source SharpPDF library to generate the...
|
by: Loogie |
last post by:
I want to create reports and I don't care if I use crystal reports or
vb.net reports. I am using vb.net 2005 and the app pulls data from
user created access db's and compact 2005 sdf's.
Here is where things get a bit tricky for me. I don't want to open any
datasets to create my reports. All of the data I need is staring at me
on the screen. Is there anyway to copy over the contents of textboxes
and listviews to the report easily?
I...
|
by: g diddy |
last post by:
Hi I'm relatively new to VBA and could really do with some help please!! This is going to sound really long winded i'm sorry but I hope it will paint a picture of what i'm trying to do.
Basically here is what I want to do: I want a form (Selector) to have 4 check boxes and a Run command button. When the user clicks run another form (ReportsMenu) will appear giving various options. Importantly though, when the user clicks OK (in the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |