Updating Several Tables from form field

[Zonie32]

Hello. I have 3 tables. PEPM table, Market Table and State Avail Table. The PEPM table has PPOID and PEPM amount as fields. Each of the tables has PPOID in it.

When I change or update the PEPM amount in the PEPM table, I would like for the PEPM amount to change in the other 2 tables as well. Can I do this with the form?

So if I make a new form using PEPM table and update the PEPM amount text box in this form, I want the other 2 tables PEPM amount to change.

 

[Thenolos]

The only way I know of to do this is to use VBA... but then again I use VBA for all data entry purposes on forms--it gives you more control over what is going on...

Basically you would have a bunch of unbound textboxes with names that match the names of fields on your tables... put a command button on a form that says something like "Save Entry" Then you would need to put code like this behind the button:

Private Sub save_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset

If IsNull([PPOID]) Then GoTo allfilled_no
If IsNull([PEPM]) Then GoTo allfilled_no

Set db = CurrentDb
Set rs = db.OpenRecordset("PEPM Table")

With rs
    .AddNew
    
    !PEPM = [PEPM]
    !PPOID = [PPOID]
      
    .Update
End With
rs.close

Set rs = db.OpenRecordset("Market Table")

With rs
    .AddNew
    
    !PEPM = [PEPM]
      
    .Update
End With
rs.close

Set rs = db.OpenRecordset("State Avail Table")

With rs
    .AddNew
    
    !PEPM = [PEPM]
      
    .Update
End With
rs.close

Set rs = Nothing

Msgbox "You have added information to the tables."
DoCmd.close acForm, "frmUserAdd"
Exit Sub

allfilled_no:

Msgbox "Please fill in all required fields.", 16, "Wait a tic."

End Sub

Or something like that.... check out the Access help for more infomation on the DAO object.

 

[PHV]

The usual way is to not store derived/calculated values ...
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886