How to do this as a transaction

[Tailgun]

I have a VB frontend and an Access db.
The user enters info into 2 forms each is saved into a seperate table. No problem.

However now he wants to do a transaction so I have a form that has tabs for the info from each entry from the original 2 forms. The original forms saved have a number of rows but with a number of columns. After he makes changes I need somehow to save them as lets say transaction1 then he does another that will be transaction2. etc each transaction must be saved seperatatly so he can call them up as he wishes, that part I have. How do I save each transaction with the appropiate info since each one has many rows and columns???

I hope this makes sense to someone if not just ask and I will try to explain it better.

Thanks for any help

 

[BiggerBrother]

I think I know what you are trying to achieve, but here goes:

You have a form and it has data saved in columns, where there can be as many rows as there are items purchased. You want to be able to save the data from each transaction into a table of some description.

One way to achieve what you are after is, as long as you know the number of columns you are going to use, is to create a table called transactionItems. In this table, have the following columns:
TransactionID | ItemID | ItemPrice ItemQuantity | Discount

In a transaction table, you can then link the customer number to a transaction number. Also, if you are using standard sale items, each with there own uniqueID, then you can reference these with the ItemID. Personally, I would record the price in this table also, as the price next year may alter, and change the previous transactions, and making any accounting very difficult in the future.

Using this method, you then get a table (transaction) with one line per transaction. You also get a number (number of items per transaction) in the table - transactionItems. This also makes updating the tables much easier, as you can delete all items where transactionID = trans that was cancelled etc.

I hope this answers your problem, or at least gives you a start.

BB

 

[Tailgun]

Thanks for the help BiggerBrother. It is almost what I'm needing. Yes there are a fixed number of columns that never change. However each transaction will have a number of rows associated with it. Each of those rows need to have the same exact ID. So I can call that transaction up in a grid later. BTW it really isn't a customer ID it is actually a unique ID for each transaction. IE the first one will be transaction 1 second transaction 2 etc I have no problem storing the ID in the table but it is only shown in the first row of that transaction. How can I by code insert that ID number in each of those rows associated with that transaction?

Hope this makes some sense.

Thanks again you have me started in the right direction.

 

[chiph]

Tailgun -

I would add - be careful how you use the word "transaction". I know you mean it in a business sense, but if you use it to a database person, it means something else entirely (making sure that a unit of work gets completely written to the database).

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first