Write data to two tables from form with button

[lysedi]

I have a form that when I print it out I want it to save a record to a table seperate from the form based on values that are on the form. when I tried to drag fields from the second table to include them in the onclick code but when I switched to form view it was blank except for a small line in the top left corner. I had to edit record source and remove the second table so that my form would show up like it was supposed to.



what I would really like is a way to "reference" the other table in vba to set values without actually adding the table and making relationships between the tables or whatever. Once the information in the second table is added it wont be changed and can be mostly unique from other tables, maybe some matching like customer name for example, but each customers may have multiple transactions.

[June7]

Not understanding. You are printing a form? How do you 'drag fields from table to include in onclick code'? Why are you copying data to two tables? Explain more just what you are trying to accomplish.

[lysedi]

Yeah, wow I shouldnt angry rant post at 3 am when access doesnt do what i want.

here we go.
so I have 2 relevent forms
Customer_Form which is used to add and edit customer information
Receipt which is used basically just to print a few fields from the table customer form saves to.

and then there are two tables
Information and trans_history

basically what i want to happen is when i click the button that prints the receipt, i want the event procedure to save like 5 of the values on receipt (from information) but when i "add existing field" from the other table it asks me how they relate and then when i go to form view its blank. I think that the problem is that A. the trans_history table is empty and B. the form cant figure out what record to display from "tras_history" based on the record from "infomation"

what I would like is a way, either to reference those fields in the event procedure of the button like Me.Date_Out = Date except I dont know what to replace the "me" with to tell it the table i want. OR a way that i can set the values i want to variables that i can pass to another form with only has the trans_history table on it. part of the point is that the values of the Trans_History table not be changed once they are set, which is why i dont want to link the tables.
any insight would be great.

[June7]

Just linking tables doesn't change values. Table trans_history has a field for customer ID? Why do you need to save data from information to trans_history? Concept of relational database is to not save duplicate data. Use query joins to retrieve related data. If you only want to display values on form and not allow edits, then make those controls Locked.

You are printing a form instead of a report as a receipt? How's that working out? Forms are not intended to be printed. They can be printed but since don't have ability to set print margins can cause issues in setup. Whereas reports are intended as a printable object.

[lysedi]

if i link the tables with customerID can i have multiple entries per customer or customerID. and actually the form prints nicely, and im using a form because i only want to print one record. and i know linking tables doenst change the values, i was goint to set the values with VBA, but when, from "add existing fields" i drag the fields to the form so that i can include them in the code, my form just shows up blank. basically each time i print a receipt i want an individual record of it, i would just add it to the customer information table but each customer might have multiple transactions. the values i want saved are pretty simple, just like name, date, amount, device and type. amount is calculated from a field in information table (adding tax) and type is derived from a combo box. i want to do it this way so that i can go back and make a report and look at how much was made in a day, week or month. I actually have it all in one table now but its not working because the customer information can be changed. Maybe if a customer comes back i should just make a new record with different spelling. john smith and john smithh? I figured that keeping the transactions different from the customer information would make it easier partly because not every customer will have a transaction. ALSO i wanted it seperate because some customers leave deposits and would have two receipts for the same transaction so I wasnt able to differentiate between a deposit trans and a payed in full trans, mostly because in the paid in full the deposit is subtracted from the total

[lysedi]

I tried linking CustomerID to a number field in trans_hist and it worked as far as saving the values, so far as i typed them in. now the problem is that on my customer form i have a button that opens receipt to the selected record, and it no longer works, im assuming because there is no entrys in the trans table. ughhh I might just have to find a way to organize everything on one table, but thats going to suck

[June7]

One table would not be appropriate solution, not if you want to take advantage of the features of a relational database. If you want to make project available I will look at. If too large for attachment to post can upload to a fileshare site such as box.net and post link to the file. Either way, run Compact & Repair first.

A form/subform arrangement might suit your situation.

Reports can print for only one record, just a matter of filtering the data. Just as must be done on the form to print one record.

Perhaps you need another field in trans_history for type of transaction (Deposit, ROA, Refund).

[lysedi]

ok so what i am trying to do is this. if you open it and go to the navigation form and click on add/edit customer information, theres a drop down menu at the top, click that and select random customer, from there i want to go to the button that says make reciept, which actually opens "copy of reciept". so that all works, C.O.receipt opens with the right record and will print like i want. then copy2 of receipt has the other table added, and kinda works for new records i type into that form, but when i change the "make receipt" button to open copy2 it opens a blank form.
the onlckick (on copy2) button event procedure has the code that i want but i want the tables to be pretty much independent, for example if something gets changed in customer info like the price, i dont want that to change the entry in trans history. and like i said before one customer may have multiple transactions. for ex they could leave a deposit and then come back and pay in full.

i know that it isnt all prettied up and some of the math is off and i deleted a bunch of stuff to try to lower the size, but i really need to find a way to make an independent record of a physical transaction, that wont change even if i change or delete customer records so that i can continue and make everything look and to what i want.

http://www.box.net/shared/refsxd56bu

[lysedi]

Oh and on the receipt forms there is a combo box at the top for type of transaction, that the calculated values depend on... so that should be selected first.

[June7]

Observations about the project:
1. Navigation form 'floats' and sits on top of other forms that are open because it is set as a pop-up. I would change this. Menu (navigation, switchboard) forms normally not pop-ups, especially the main one.
2. Customer Form recordsource is a query that selects virtually (if not all) every field of information table. Could shorten the query to SELECT * FROM information;. However, since there is no join or sorting/filtering, just simply information as the recordsource will work.
3. First record of information table has no customer name.
4. Information table has fields Estimate, Deposit, Total Owed. Should not save a value into Total Owed field, this should be calculated from the other two as needed. Access 2010 has a new datatype - Calculated. Suggest using this.
5. Only in extreme cases should records be deleted.
6. I would not set up the database anything like this. I find this unworkable and prone to issues and I foresee only aggravation in your future.

This appears to be database to track sales. Entities would be Customers, Orders, Transactions

At minimum, tables should be like:

Customers
ID (primary key)
Company
Address
Contact
Phone

Orders
ID (primary key)
DateOrder
CustomerID (foreign key)

OrderDetails
ID (primary key)
OrderID (foreign key)
Item
Quantity
Price

Transactions
ID (primary key)
OrderID (foreign key)
Type (Deposit, ROA, Refund)
Amount