Creating reports for table column from form

[ryanwales]

Hi everyone. This is my first forum post so bare with me!

I'm creating an adaptation of the Microsoft sample goods database that must include a quote option.


while i have the data entered in the form to save to a quote table, the data must be able to be sent to a 'quote report' to include the details from the table.

In a nutshell, the user enters the product, currency and quantity that then gives a calculated price with tax and shipping included. When the user presses a 'print report' button, a template report should appear with the details from the form entered in the correct place i.e customer name on form(and table) = customer name in report

Can anyone enlighten me on this? I'm sure there must be similar posts?

Thanks for reading and I hope you can help!

[June7]

Assuming data input on form is saved to table.

The report must have a RecordSource. RecordSource can be a table or a query or a table joined to a query. Then need to filter the report's RecordSource to show only the desired records. Several ways to pass filter criteria to a report or form RecordSource.

Can manually input the criteria in the query designer.

Query input parameter can prompt user for the criteria.

Query input paramater can read the input from a form control.

Use VBA code construct filter criteria from user input on form and pass it to the form/report with the WHERE argument of DoCmd.OpenReport or OpenForm.

[ryanwales]

Hi June7, you helping must be fate as my birthday is the 7th June!

The record source would be the 'Quote' table that has:

QuoteID, CustomerName (fullname in customer table), ProductName (ProductName in product table), Quantity, Discount, Listprice (ListPrice in product table), SubTotal (expression), Tax, Shipping, Salesperson (fullname in salesperson table), TotalPrice (expression), Notes.

Should I be looking at using the SQL designer in record source and then making the button save and create the report to the latest record?

As for the SQL I'm not 100% on what should be written. At the moment I have something along the lines of:

SELECT Quote.*, Quote.QuoteID, Quote.CustomerName, Quote.ProductName, Quote.Quantity, Quote.Discount, Quote.Tax, Quote.Shipping, Quote.Salesperson, Quote.SubTotal, Quote.ListPrice, Quote.TotalPrice
FROM Quote;


I'm sure however it must be more along the lines of:

SELECT Quote.ID AS QuoteID, ect, ect?

Thanks

[June7]

RecordSource could be simple as just referencing the table or SELECT * FROM Quote ORDER BY CustomerName;

The * wildcard means 'get all'.

Only need the As alias for fields constructed with expression, such as: Quantity * ListPrice As ExtPrice. Or you want to display a different name in Datasheet View, which is all the Quote.ID As QuoteID accomplishes.

Your RecordSource will have to include a joins with the Products and Salesperson tables so as to get related info. Can build the query in the RecordSource property with the query designer.

Not sure what you mean by 'save and create the report to the latest record'. A button would open the report filtered to the desired records. My usual practice is to have users enter criteria in unbound controls and reference the controls to get filter criteria for the WHERE argument. Like:

DoCmd.OpenReport "reportname", , , "CustomerID=" & Me.txtCustID

[ryanwales]

RecordSource could be simple as just referencing the table or SELECT * FROM Quote ORDER BY CustomerName;

I understand this, I have done basic access and SQL before, though nothing in Access 2010 or creating single record reports

Your RecordSource will have to include a joins with the Products and Salesperson tables so as to get related info. Can build the query in the RecordSource property with the query designer.

If by this you mean relationships between the tables, there are relationships.

Not sure what you mean by 'save and create the report to the latest record'.

What I'm trying to get at is the user presses a 'save&print' button on the form and then the report appears with the information in the allocated area of the report. The report will have a print button underneath

A button would open the report filtered to the desired records. My usual practice is to have users enter criteria in unbound controls and reference the controls to get filter criteria for the WHERE argument. Like:

DoCmd.OpenReport "reportname", , , "CustomerID=" & Me.txtCustID

This has confused me - are you saying that an embedded macro within the button should be what i need? For instance, if i just choose Quote* to select all fields in the table, is it a macro within the button on the 'create a quote' form that then adds the fields from the most recent Quote? If so, do you have any pointers or sample code?

Thank you

[June7]

If by this you mean relationships between the tables, there are relationships.
No, I mean join tables in a query. Setting up relationships can control data integrity and help with building queries but tables must be joined on key fields in query in order for related data to be viewed. Access Help has guidelines on building queries with the designer and wizards.

This has confused me - are you saying that an embedded macro within the button should be what i need? For instance, if i just choose Quote* to select all fields in the table, is it a macro within the button on the 'create a quote' form that then adds the fields from the most recent Quote? If so, do you have any pointers or sample code?
I use only VBA, no macros. I am suggesting data entered into controls on form are referenced as filter criteria passed to the report when it opens. The Save & Print button Click event would execute the VBA code I suggested. In the Click event property, select [Event Procedure] then double click the ellipses (...) to go to the VBA procedure. Type code there. Check out the VBA Editor Help for guidelines

[ryanwales]

Is there a step-by-step method you could help me with? I'm missing something valuable along the line here. I'm so close to cracking it I just need something step-by-step to make sure I'm going in the right direction!!

Thank you

[June7]

First, do you know how to build queries with table joins? This is the heart of relational database concepts and elementary Access functionality. Access Help has step-by-step guidelines on using the query designer and wizards.

[ryanwales]

if you would have asked me that a week or so ago i would be confident but now..not so!
My tables have relationships, though the terminology 'joins' is a bit beyond me

Thanks

[ryanwales]

also - I found this how to guide, though the ability to go to [event procedure] is not available in the goods database

source: http://allenbrowne.com/casu-15.html

edit# I can see that the main problem is not being able to use [event procedure]. The goods database allows custom macros already made by microsoft.
Oh! I have just made a form using the Client forms. Am i onto something?

Thanks

[seek1]

Sorry..I entered wrong text..

[ryanwales]

I have data passing from the form to the report!

Now (inevitably) for another problem : The user must be able to select multiple products that should appear in the report. In the form, the products should also add up to a total price
I have a subform inside the Quote form that enables multiple records to be created only whatever is chosen creates a new quote (i.e. 3 products chosen, 3 new records (QuoteID's) created)

Thanks

[June7]

Going in circles, sometimes seeing is a big help. Can you provide the project for analysis?

[ryanwales]

Going in circles, sometimes seeing is a big help. Can you provide the project for analysis?

Unfortunately some customer details have been added :\

The user selects multiple items that so that the products must appear on the report as well as a total price:
Sub Form
.................................................. .............................
Product Name, Quantity, ListPrice, Discount, Total Price

xxxxx xxxxxx xxxxxxx xxxxxxx £100

xxxxx xxxxxx xxxxxxx xxxxxxx £100
.................................................. ..............................
Subtotal:£200
Tax: £20
Shipping: £20

order total: £240

So far in the form i have not been able to create function that adds both total prices to show in 'Subtotal'!

[June7]

You could make copy, remove confidential data, run Compact & Repair, zip if still large, attach to post.

Forms are not really intended to present summary (aggregate) data, however, it can be done. One way is if form is in Datasheet view, click the Totals button from the ribbon. This will present a row at bottom of the records where you can select aggregate functions for each column. Any thing else will required VBA code or DLookup function.

Reports are designed for presenting summary calcs.