Results 1 to 12 of 12
  1. #1
    netguy is offline Novice
    Windows 8 Access 2007
    Join Date
    Sep 2014
    Posts
    16

    Execute based on event and value

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

  2. #2
    netguy is offline Novice
    Windows 8 Access 2007
    Join Date
    Sep 2014
    Posts
    16
    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

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    Try the after update event:

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

    and I would test with IsDate().
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    netguy is offline Novice
    Windows 8 Access 2007
    Join Date
    Sep 2014
    Posts
    16
    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

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    netguy is offline Novice
    Windows 8 Access 2007
    Join Date
    Sep 2014
    Posts
    16
    Sounds good. Thanks!

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    No problem and welcome to the site by the way! Post back if you still have trouble.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    netguy is offline Novice
    Windows 8 Access 2007
    Join Date
    Sep 2014
    Posts
    16
    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

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    Quote Originally Posted by pbaldy View Post
    I would test with IsDate().
    Like

    ElseIf Me.PROV_TERM = True And Not IsDate(PROV_TERM_DATE) Then
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    netguy is offline Novice
    Windows 8 Access 2007
    Join Date
    Sep 2014
    Posts
    16
    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!

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    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
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    netguy is offline Novice
    Windows 8 Access 2007
    Join Date
    Sep 2014
    Posts
    16
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 05-08-2014, 10:34 AM
  2. Replies: 1
    Last Post: 05-06-2014, 09:14 PM
  3. execute two queries in single CLICK event
    By jhargram in forum Queries
    Replies: 1
    Last Post: 07-25-2012, 06:17 AM
  4. Combo Box event based on selection
    By tobydobo in forum Access
    Replies: 52
    Last Post: 01-20-2012, 07:26 PM
  5. Replies: 1
    Last Post: 07-12-2010, 12:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums