checking a checkbox appends/updates a record in another table

[Chris Ryner]

Can anyone help me figure out the code to do the following.


I have a form frmNewStock that is bound to tblStock. The form has a
checkbox bound to tblStock.chk01
There is another table tblAd that is has this same field chk01. When a user
checks the box on frmNewStock, (afterupdate?) I want to execute code that
will append or update a record in the tblAd.

The two tables both have ItemNo as the primary key. and the data's that I
want to copy have the exact same field names. On the form checking chk01
box would look in tblAd and if the itemno exists it I would like to check
the chk01 box in tblAd. If it doesn't exist I need to create the record
(append?) it along with a checked chk01. If you uncheck the item in the
frmNewstock I would like uncheck the corresponding chk01 in tblAd.

I realize that I shouldn't be saving (redunant) data in table. But due to
the complexity of the way the data is stored in the system I must maintain
the two tables independently this way.

I am thinking what I need is an afterupdate event in the form on chk01 that
would do something like

UPDATE tblAd INNER JOIN tblNewStock ON tblAD.Itemno = tblNewStock.Itemno SET
tblAD.chk01 = tblNewStock.chk01

but I am not sure if I am thinking right, particulary with the possibility
that Itemno's may or may not already be in tblAd. Can anyone shed light on
this for me?

 

[arthurjr07]

You can use this approach.

Private Sub Form_AfterUpdate()
Dim rs As New ADODB.Recordset
rs.Open "SELECT * FROM tblAd where ItemNo = '" & ItemNo & "'"
If rs.RecordCount > 0 Then
CurrentProject.Connection.Execute "UPDATE SQL statement"
Else
CurrentProject.Connection.Execute "INSERT SQL statement"
End If
End Sub