checking a checkbox appends/updates a record in another table

  • Thread starter Thread starter Chris Ryner
  • Start date Start date
C

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?
 
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
 
Back
Top