How can I update table data in a report?

[piperent]

Here's another simple, but I'm sure not unique question. I now have a unique 'Invoice' number that is generated from a 'report' itself. The report consists of information from two tables (one to many relationship) with one customer being associated with many transactions. It's a simple customer invoice that contains a detail list and summary of all the transactions for a customer. There must be a means of updating each transaction with 'that unique invoice' number when the report is produced. I have tried macros, event procedures, and just about everything else with no results. It appears as if the only place to locate the coding is in the 'detail' section for each invoice, but I keep getting a message that the "object doesn't contain the Automation object 'Transactions'". What is this telling me?

All I want to do is plug that invoice number into a field in the transactions table.

Access is new to me, but I've been programming for over 30 years, so I know there has to be a way to do this. It seems as if Access is a good candidate for what I need, but I keep tripping over these little things and that makes it a very frustrating effort.

If you could point an old-line developer in the right direction, I would sure appreciate it.

JP

 

[Remou]

Hi
This is what I was muttering about tables in a reply to your other post. I think you are approaching this from the wrong end. I think it is best to generate the invoices first, then build the report from that table, rather than trying to build an invoices table from the report.

 

[BoxHead]

JP,

I feel your pain, but I think Remou is right. The report is a view of your data. A report doesn't generate an invoice number any more than my monitor runs a program. If you're generating the invoice number when the report runs (whether it's input, selected or calculated), that's the time to update the table and have the report pull the data from that table.

Otherwise, when you look at the same report on the same data at a different time, you may have a new unique number.

A simple Update query would probably suffice depending on your data structure.





John

Use what you have,
Learn what you can,
Create what you need.

 

[piperent]

I'm not sure exactly what you are referring to when you say 'generate the invoices first'. I'm only creating a 'detail transactions' report that is summarized and paged by customer. When the customer break occurs, I increment the invoice number so the next customer has a different number.

Now all I want to do is take that invoice number and plug it into each detail transaction for later reference. Are you saying that I should create some kind of query first, then use that as input to a report program?

I'm not trying to create any tables. I already retain all the detail that is needed. This is a hardcopy report that goes to the customer (a monthly statement if you want to call it that).


JP

 

[piperent]

I just received Boxhead's post. I think both you and Remou are saying what I queried in my most recent post. Man, this stuff is really goofy. You can query data and update at the same time, but apparently can't report data and update. After all the talk about 'code re-use' spewed by every 'OOP' coder in the world, it apparently all comes down to doing a job twice anyway.

I'll take the 'query' first then report it approach. If I ever figure this one out, I think I'll call it quits.

thanks for the insight.
JP

 

[BoxHead]

summarized and paged by customer. When the customer break occurs, I increment the invoice number so the next customer has a different number.

I'm not sure what you mean by Invoice Number.

I would assume that an invoice is generated for a particular transaction with a particular customer and a unique number is assigned to that invoice. A summary report grouped by customer would therefore reference multiple invoices per customer and multiple invoice numbers.

Does each of your 'detail transaction' records (which I believe would be the equivalent of an invoice) have a unique number already? Is this to reference the actual report that was last viewed with that data?

I looked at your other thread and it looks like you could
> open the report at 10am to generate a set of numbers,
> invoice a new customer at 10:01am
> open the report at 10:02 and generate a group of numbers that would be 1 or 2 higher depending on where the new customer fell into the sort order.

Am I understanding this correctly or missing something?




John

Use what you have,
Learn what you can,
Create what you need.

 

[piperent]

Boxhead,

The detail transaction records are not equivalent to an invoice. They are part of the whole. Think of an 'object'. It might consist of several 'pieces' which fit together to form the entire object. That's what the 'detail transactions' do for this invoice. I might have 1, 2, or 30 detail transactions which constitute an entire invoice. It's like a gussied-up sales receipt. Or maybe your checking account. Each month the bank sends you a statement detailing your months activity (checks and deposits). If the bank were to reproduce that statement, they would need some means of identifying each item included on any particular statement. Therefore, they could assign a statement sequence number for the entire statement, then tie that number to each check and deposit associated with that statement. Now, to reproduce the statement, all they have to do is look for each check and deposit that has that unique sequence number associated with it.

See. It's simple. Anyone should be able to do it.

I probably muddied the water big time with that explanation, but that's all I really need to do. Produce a 'statement' (detail list of transaction items) with a unique number (invoice number) and tie that unique number to 'each' item (transaction) listed on the statement.

Whew!! Anything else I need to explain?

Can you update a tables (any tables) data at the time of reporting?

JP

 

[BoxHead]

JP,

Good explanation.

You can run an update query on the report's Open event, but the way you have it set up now, you have to go beyond the Open event to get your invoice numbers that you want to update to the table.

Catch 22!

Do you have a form you use to select which invoices will be posted or do you have to post all invoices whenever you want to post any invoices?

If you use a form, you can update the table as you select the customers to invoice. The report would then pull the invoice number from the table like it pulls the rest of the data.

If you're using the all-or-nothing approach, you could use vba to update the table on the open event but you cannot use the Page number as part of your calculation because that doesn't exist until after the Open event occurs.

HTH




John

Use what you have,
Learn what you can,
Create what you need.

 

[piperent]

Appreciate the rssponses. I finally used the 'shotgun' approach and kind of kinked it into submission. Since Access makes all the decisions as to when something should occur, you either figure out how they time things or you find another way to do it.

So, here is what I did. I took the 'Invoice' file idea and created a static invoice table that is only 1 row in size. In that I put the invoice number I want to use for the current 'statement' run. Then, to make a unique Invoice Number for each statement I take the Customer's ID number, convert it to string, then append the static invoice number saved in the invoice table which is already defined as 'text' type.

It's ugly, not very efficient, and certainly not the way I would prefer to do it, but it does the trick for now. As for the user, well, they're having to go thru three (3) table updates to complete the procedure. A PRE-print update that updates the 'statement print date' and then constructs and applies the new 'Invoice Number'. A POST-print update to reset a flag used during the extract/print phase. And finally an update to bump the 'invoice' number in the invoice table.

Some day I'm gonna have to buy a book. For now, though, you guys are life savers. Thanks for all your help.

JP