Form question

[Skip Bisconer]

I haven't been in Access for almost a year and being 70 my memory is not like a steel trap anymore so I can't get a handle on this project. If you could help me get started again I would be eternally grateful.

I have a simple query that picks off order line items that have been billed. In the query I am able to look up all the lines of a specific order number using the filter and that's great and is the information I need but the person who is going to use this must find the lines of 20 or 30 orders and have a visual as well as written report with totaling of some of the fields to balance to a manually created tally generated from operations.

Some of the fields are entered manually by the delivery person and billing is suppose to pick up those manual entries during the invoicing process. The purpose here is to make sure the invoiced lines are the same as the incoming manual truck inventory to allow us to correct any transaction errors to keep the customer account accurate as we can get it.

I believe I want to get these lines added to a table then create a report from the table generating the reports needed. I am just having a brain cramp about going about this. Thank for reading my long explanation.

 

[Galaxiom]

When generating any kind of output one should avoid writing to a table wherever possible because this is storing derived values and denormalizes the data.

Use queries instead wherever possible.

The report can use the name of the query for its RecordSource property. Then you simply set the ControlSource property of each textbox, subform etc as the name of the field in the RecordSource query.

Calculations are performed on this recordset as required.

Sometimes it is necessary to write a temporary dataset but it often can be avoided, especially with with careful planning of the data construction.

 

[Skip Bisconer]

Thanks for the information. I have a query that isolates all the order information of orders that have been invoice as apposed to orders still open or unprocessed. I only want to see a few of those orders at one time. When I use the query filter I only have the option of viewing one particular order. I need to make a query of the query with just those orders? How do I get past this one at a time stuff? I was thinking of some kind of form to populate a table then delete the data when those particular orders were viewed and printed. Am I able to do that to a query? I hate being such a "wufo" and I hope I am being clear here.

 

[Galaxiom]

You are filtering by order number. Instead you could limit the orders showing using a date range, customer number range or customer name range for example.

Another way is to use a TOP modifier in the sql of the query, lke this:
SELECT TOP 10 field1, field2 etc FROM tablename.

Add a checkbox field to indicate printed and exclude these from the query results. Eventually there will be less than the number in the TOP modifier and ultimately all the invoices processed. Include a control on the report form to include or exclude the processed documents.

 

[Skip Bisconer]

Thanks, I will check that out and see if I get what I want.