Execute based on event and value

[netguy]

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 & "'

[netguy]

Folks I was able to figure this based on the following code however the only thing I have not been able to do was to create the conditional logic.

Private Sub PROV_TERM_Click()

Dim strSQL As String
If Me.PROV_TERM = True Then

strSQL = "INSERT INTO tbl_Provider_Term (PROV_ID, TERM_DATE, MOD_TERM)" & vbCrLf & _
"VALUES ('" & Me.PROV_ID & "', '" & Me.PROV_TERM_DATE & "', '" & Date & "');"
CurrentDb.Execute strSQL, dbFailOnError

Else


strSQL = "DELETE FROM tbl_Provider_Term WHERE PROV_ID = " & Me.PROV_ID & ""
CurrentDb.Execute strSQL, dbFailOnError

End If

End Sub


I did try this for the Conditional Code however i get an error. Not sure the event is correct "_Enter"


Private Sub PROV_TERM_Enter()
If Me.PROV_TERM_DATE = "" Then
Me.PROV_TERM.Locked = True
Else
Me.PROV_TERM.Locked = False
End If

[pbaldy]

Try the after update event:

http://www.baldyweb.com/ConditionalVisibility.htm

and I would test with IsDate().

[netguy]

Gotcha....
to go back to the SQL, the first time I ran it it worked now the actually SQL statement is displaying "Date" as apposed to "Date()" . Every time I add the Parenthesis and click off the line they automatically disappear. I simply need to add the current date into the the SQL string. The problem code

strSQL = "INSERT INTO tbl_Provider_Term (PROV_ID, TERM_DATE, MOD_TERM)" & vbCrLf & _
"VALUES ('" & Me.PROV_ID & "','" & Me.PROV_TERM_DATE & "','" & Date & "');"
CurrentDb.Execute strSQL, dbFailOnError

FYI The field that I'm entering into is of type DateTime, So technically I really don't need the single quotes outside of the double quotes Date()

Not sure I've tried a bunch of different things

[pbaldy]

I wouldn't worry about the parentheses; Access will sometimes drop them off. For a date/time field, you want # rather than ' as the delimiter.

[netguy]

Sounds good. Thanks!

[pbaldy]

No problem and welcome to the site by the way! Post back if you still have trouble.

[netguy]

Thanks for the welcome its a wonderful resource and I've heard a lot about.
Well Got everything working and then began creating the logic for when to insert versus when not to based on IF statements. Here's the issue:

If there is a value in PROD_TERM_DATE and a user selects the box then the insert SQL runs and MsgBox displays added This WORKS fine
If there is no value in PROD_TERM_DATE and a user selects the box then do not run SQL rather display MsgBox This DOES NOT WORK. No msg displays and no records entered

Private Sub PROV_TERM_Click()


Dim strSQL As String
If Me.PROV_TERM = True And PROV_TERM_DATE <> 0 Then


strSQL = "INSERT INTO tbl_Provider_Term (PROV_ID, TERM_DATE, MOD_TERM)" & vbCrLf & _
"VALUES ('" & Me.PROV_ID & "', '" & Me.PROV_TERM_DATE & "', '" & Date & "');"
CurrentDb.Execute strSQL, dbFailOnError


MsgBox ("Record added to Termed Table")


ElseIf Me.PROV_TERM = True And PROV_TERM_DATE = "" Then


MsgBox ("ACTION NOT COMPLETED: Note - You Must Enter a Term Date First!")


Else


strSQL = "DELETE FROM tbl_Provider_Term WHERE PROV_ID = " & Me.PROV_ID & ""
CurrentDb.Execute strSQL, dbFailOnError


MsgBox ("Record deleted from Termed Table")


End If


End Sub

[pbaldy]

I would test with IsDate().

Like

ElseIf Me.PROV_TERM = True And Not IsDate(PROV_TERM_DATE) Then

[netguy]

That works the only issue I ran into is if this is runs ElseIf Me.PROV_TERM = True And Not IsDate(PROV_TERM_DATE) Then

I have to have a way to prevent the checkbox PROV_TERM from actually being checked. We only want the check box checked if in fact there is a value in PROV_TERM_DATE

Thank you!

[pbaldy]

I thought you were going to modify the conditional code above to keep it locked when there was no date. In any case, you can use the before update event of the checkbox to stop it from being checked. Same method as here, which could also be used:

http://www.baldyweb.com/BeforeUpdate.htm

[netguy]

Yes I need to keep it locked when there is no date however I reviewed your example and could not get it to work. Sorry Very New. Ok I have included two scenarios on how this should work along with a mish-mosh of code to get started with.

Scenario 1
Form Loads
PROV_TERM is Not Checked AND
NO date has been entered into PROV_TERM_DATE
THEN
User tries checking the PROV_TERM box but error displays and indicates that a PROV_TERM_DATE must first be entered. The focus is now on the PROV_TERM_DATE dateField, where the user selects a date. After the date has been inserted the user can now select the PROV_TERM check box. When clicking on the check box, a SQL script is executed that then uses other fields in the form to INSERT INTO a table DONE

Scenario 2
Form Loads
PROV_TERM is Checked AND
a date has already been entered into PROV_TERM_DATE
THEN
User un-checks the PROV_TERM box which automatically updates the record and deletes the current value in the PROV_TERM_DATE field
ALSO
A SQL script runs and deletes an associated record from another table based on fields from the form. At which point the form reloads to now display scenario 1

Here is code so far:



Private Sub PROV_TERM_Click()

Dim strSQL As String
If Me.PROV_TERM = True And PROV_TERM_DATE <> 0 Then

strSQL = "INSERT INTO tbl_Provider_Term (PROV_ID, TERM_DATE, MOD_TERM)" & vbCrLf & _
"VALUES ('" & Me.PROV_ID & "', '" & Me.PROV_TERM_DATE & "', '" & Date & "');"
CurrentDb.Execute strSQL, dbFailOnError

MsgBox ("Record added to Termed Table")

ElseIf Me.PROV_TERM = True And Not IsDate(PROV_TERM_DATE) Then

MsgBox ("ACTION NOT COMPLETED: Note - You Must Enter a Term Date First!")

Else

strSQL = "DELETE FROM tbl_Provider_Term WHERE PROV_ID = " & Me.PROV_ID & ""
CurrentDb.Execute strSQL, dbFailOnError

MsgBox ("Record deleted from Termed Table")

End If

End Sub


Private Sub PROV_TERM_BeforeUpdate(Cancel As Integer)

If Not IsDate(Me.PROV_TERM_DATE) Then

Me.PROV_TERM.Locked = True
MsgBox ("Please Include a Term Date first!")
Cancel = True
Me.PROV_TERM_DATE.SetFocus

Else

Me.PROV_TERM.Locked = False

End If


End Sub