Update 2 tables via Check Box in Form

[StageRight]

I did not design this database, but it's in it's 4th generation update on
strict timeline.

I have two tables:
-tblMain containing 3 check boxes
-tblHistory, updated yearly appending only those records where a check was
indicated in one of the 3 check boxes in tblMain, and filling in a year value
in column related to check box

I would like to automate this process so that when a check box is populated
via the form, it automatically:
1) creates a record in tblHistory
2) populates check box's associated column with year value stored in another
table (or sumsuch)

I am not event procedure savvy but was thinking an event procedure in
'OnDirty' might work but I have no clue how to code to:
- add a new record to another table with data stored in a 3rd table

Is this possible?
Thanks

 

[Jack Leach]

I would recommend unsing the AfterUpdate event of the Form... this is after
the current record is saved. Execute an INSERT INTO query to append the
record to your history table.

Here is an example INSERT INTO taken from
http://www.blueclaw-db.com/accessquerysql/sql_insert_into.htm


Insert Into TableName (FieldName1, FieldName2) Values (Value1, Value2)


The tricky part about this is, for any string values, you must enclose the
string with quotes inside the SQL string.

Private Function Form_AfterUpdate()
Dim strSQL As String
If Me.checkbox = True Then
strSQL = _
"INSERT INTO tblHistory (" & _
"Fieldname, Fieldname, Fieldname) " & _
"VALUES (" & _
"""stringvalue"", numericvalue, ""stringvalue"")"
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
End If
End Sub


The Debug.Print line will print the value of strSQL to the immediate window.
For this example, you should see:

INSERT INTO tblHistory (Fieldname, Fieldname, Fieldname) VALUES
("stringvalue", numericvalue, "stringvalue")

You will then want to
a) verify the record was created in the history table
b) remove the record from the active table

This is hardly everything you need, but should get you started on the right
path at least.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)