I have a form that contains a data type field as Yes/No. All records currently are set to No i.e. The box has never been checked. Here's what I'd like to do.
Upon checking the box Yes the following needs to happen:
1) Execute SQL query to insert some of the data from the current record into a separate table. (Here is the data that needs to be used from the current record - Source Table - PROV_ID, and TERM_DATE). The destination table (tbl_Provider_Term) has these two fields however there is a third field MOD_TERM that I would like to auto generate the current Date/Time. For example SQL GETDATE()
2) I would also like to include some conditional logic which locks the check box PROV_TERM from entry if there is no value in the TERM_DATE field. If there is a value (in this case a date) then the user would be able to check the box and the cmd would fire off the SQL that would capture the two values from the current record and concatenate the DateTime for insert into tbl_Provider_Term
Upon checking the box No the following needs to happen:
1) The reverse would occur and a DELETE SQL command would fire off against the tbl_Provider_Term . I.E. DELETE FROM tbl_Provider_Term WHERE PROV_ID = '" & Me.PROV_ID & "'