form record bound to 2 tables?

[wshm]

is it possible to capture values from a form and send to 2 different table?
what i mean is...
I have a table PSCtx.
it's a transaction table where people can enter
contract amount and such.
I have a table PSC which has almost exact same fields
as PSCtx but has balances and etc.
(hard to explain why i need both tables).

is it possible to build a query or something to get the
data to PSC table from PSCtx?

when user inputs info into form "frm_PSC" (rec sourcesctx)
i need to update PSC table as well.

 

[fneily]

There's a variety of ways. One is to place code on your Save button - such as:
Sub YourButtonName_OnClick
Dim DB As Dao.Database
Dim RS As Dao.Recordset

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("PSC", dbOpenDynaset)
RS.AddNew
RS![TableFieldName] = Me![FormControlName]
Etc. Other fieldnames
RS.Update
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing
End Sub
Or you can set a condition to find a record in the PSC table and update a field.

 

[wshm]

thank you. that was a good approach for me; however,
I have one problem.

tbl_PSCtx can have duplicate contract numbers but
tbl_PSC should only have one contract number.
its like total

and i forgot to think that
before update, If 'contractNumber' already exists in
tbl_PSC, only one field needs to be updated.

so im thinking something like this.

If Contract Number exists in tbl_PSC(database)already
Then Update tbl_PSC.[UpdatedContAmt] =
frm_PSC_new.[TranxAmt] + tbl_PSCtx.[ContAmt]
Where 'contractNo' = 'contractNo'

Else
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("tbl_PSC", dbOpenDynaset)
RS.AddNew
RS![Bmonitor] = Me![cboBmonitor]
RS![ContractNo] = Me![txtContractNo]
RS![ContractName] = Me![txtcontractName]
RS![Description] = Me![TxtDescription]
RS![PaID] = Me![cboPaID]
RS![ContractAmt] = Me![txtTranxAmt]
RS![Bfsix] = Me![cboBfsix]
RS.Update
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing

im having hard time coming up /w the coding for the top part... any help?

 

[fneily]

Maybe this can partially help you:
Dim R As DAO.Recordset, RS As DAO.Recordset
Set R = CurrentDb.OpenRecordset("Select * From [tbl_PSC] Where [Contract Number] = " & Me![ContractNumberOnForm])
If R.RecordCount = 0 Then
Rs.Addnew
etc.
Rs.Update
Else
Rs.Edit
etc.
Rs.Update
End If