I need my form to update more than 1 table 2

[mgoliveira]

I have a big table with many products and prices for each of them. The form I am working on summarizes all costs of the products, on a "product family" level (does not list all the products, only totals). Since the prices change every month, I created another table to store only these totals by product family level.

My question is: in my form that shows the summarized costs, I created some unbound fields and I would like the values for these fields to be stored in this other table (only with the totals).

Let me show an example:
Table 1:
Product a - June - 100.00 - Family A
Product b - June - 200.00 - Family A
Product c - June - 250.00 - Family A

Form:

Products from family A
Total Cost: 550.00
Amount to be paid (unbound field, the user enters it) - 500.00 (supposing the user is receiving an invoice charging for the products, and didn't agree with one cost)

HOW DO I STORE THIS AMOUNT TO BE PAID UNBOUND FIELD(500.00) IN ANOTHER TABLE ? The table would be the one below:

Table 2
Product Family A - June - Amount to be paid (500.00)

Thanks

 

[jimmythegeek]

Kind of unorthodox, but the following should work.

You will need some sort of "WHERE" criteria to update the appropriate record in your "summary" table (ideally, a key field).

Something like the following should work, this is on a button click, you could put it on any event you want:

==========
Private Sub MyButton_Click()
Dim db as Database
Set db = Currentdb()
db.Execute "UPDATE YourTable SET YourField = " & Me.FieldName & "WHERE KeyField = " & Me.KeyField
db.Close
End Sub
==========

Obviously, you will need to change the field names to reflect both the fields in your table, and the fields on your form. You could also update multiple fields by adding "AND's" in the Update statement. Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[mgoliveira]

Jimmy

You asked me to use a key field as criteria in the WHERE statement of the VB code. What can I use, considering that every month the user of the database will be entering new values in the fields of my form and storing these values in the "summary" table ? Can I tell the code to store the values WHERE that same field (the one which value will be stored in the other table) is different from zero, or not null ? I am a little confused with what I should use as the criterion. Can you help me ?

Thank you very much

 

[jimmythegeek]

Will you be editing an existing record from this form, or adding a new record to the summary table? Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[mgoliveira]

This form is based off a query that reads a table with all these products, which prices change every month. So, every new month, when tue user finishes entering the new costs, he opens the form that shows summarized info (total cost for that product family). So, every new month I want this unbound field to store value in the summary table, adding a new record to that.

Does it make it easier ?

Thank you

 

[jimmythegeek]

If you can tell me the following, I can give you the right code:

Name of the summary table
Names of the fields in the table
Names of the corresponding fields on the form

You will more than likely need to use an INSERT statement, but if you give me the info, I can help you with it. Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[mgoliveira]

Jimmy

Thank you so much for your time and help. I am providing as much info as I can below, but I definitely don't expect you to take your time writting the code considering all 7 fields that I want to be copied from the form and appended to the second table. I would appreciate so much if you could only consider 2 fields in the codes (preferably 2 unbound fields, like Text57 and Text 59 in the form), and I will extend the code adding the other ones. There it goes:

The first table (the detailed one) is called Temp , and the main fields are:

- Ban (family level of the products. There is only one Ban in my Temp table for every time this "update" action will happen);
- Circuit (the product)
- Month (every new month this table gets new records but with a new month in this field)
- M_cost (one cost component)
- Fract_Chg (another cost component)
- Misc_Chg (another cost component)
- Instal (another cost component)
- Adjust (another cost component)

Att: There last 5 fields don't need to be exported anywhere

Form:

Fields which values need to be "carried over" / appended to the summary (Ban_details) table:

- Ban (is a bound field, based on the query that summarizes the records from Temp table) Will update field Ban in Ban_details table);
- Month (is a bound field, based on the query that summarizes the records from Temp table). Will update field Ban in Ban_details table);
- Total (total cost, is a calculated unbound field). Will update field Total_cost in Ban_details table);
- Text30 (is an unbound field where the user enters a value)Will update field Taxes in Ban_details table);
- Text 40 (is an unbound field where the user enters a value)Will update field Late_Chg in Ban_details table);
- Text57 (is an unbound field where the user enters a value)Will update field Amount_paid in Ban_details table);
- Text59 (is an unbound field where the user enters a value)Will update field Comment in Ban_details table);

The second table (summary) is called Ban_details , and the fields are:

- Ban (same as in the firt table) - will receive the value from the field Ban from the form;
- Month - will receive the value from the field Month from the form;
- Total_cost - sum of all cost components (M_cost, Fract_chg, Install, Adjust and Misc_chg) - will receive the amount from the calculated unbound field called "Total" in the form;
- Taxes - will receive the value entered manually by the user in the unbound field called "Text30" in the form;
- Late_Chg - will receive the value entered manually by the user in the unbound field called "Text40" in the form
- Amount_paid - will receive the value entered manually by the user in the unbound field called "Text57" in the form;
- Comments - will receive the value entered manually by the user in the unbound field called "Text59" in the form.

Thank you one more time

Miguel Oliveira

 

[jimmythegeek]

On the click of whatever button you are using to save the informatin on the form, try the following code:

====================
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Ban_details&quot

With rs
.AddNew
!Ban = Me.Ban
!month = Me.month
!Total_cost = Me.Total
!Taxes = Me.Text30
!Late_Chg = Me.Text40
!Amount_paid = Me.Text57
!Comments = Me.Text59
.update
End With

db.Close
rs.Close
====================

I highly suggest re-naming the fields on your form to give them understandable names. Text30, or Combo12 tells you nothing about the field. It is common practice to name all controls using naming convention standards. I did not change the names of anything, as we need to get it working before we worry about that.

Let me know if this doesn't work. Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[mgoliveira]

Jimmy

Thank you A LOT for your help. The code worked perfectly. The only thing I had to delete was the line Db.close . When I tried to run with it, although the exporte occured, an error message came up.

Thanks also for the feedback regarding the names of the fields. I'm just beginning working with Access, still need a lot of hard work to become good (what yo already are).

Tek-tips ROCKS !!!

Miguel Oliveira