Jet CHECK Constraint

[Khalil Handal]

Hi
Check constraints are only at the table level. The example at the site:
example: https://mskb.pkisolutions.com/kb/201888
Shows how to create a new table with a check constraint and how to add a new record to the table that validate a constraint.

1- Is it possible to UPDATE a (Yes/No) field in a table using a CHECK Constraint? Default value of the field is No as follows:


Check for a Yes/No field, If Yes then Error Message if No then run some VBA code and after that update the value of the field from No to Yes ?

2- What is the difference between having the code as a check constraint in a module or having the code as an Event Procedure?

Thank you
Khalil

[orange]

See this info from W3Schools re Check Constraint.

Here is an example to limit 7 records in table tblTestMaxRecs.

Currentproject.connection.execute "ALTER TABLE tblTestMaxRecs ADD CONSTRAINT MaxRecs CHECK ((SELECT Count(*) FROM tblTestMaxRecs) <=7);"

Sample to remove the constraint

Currentproject.connection.execute "ALTER TABLE tblTestMaxRecs DROP CONSTRAINT MaxRecs;"

I am not sure about your #1. You can use the w3schools info and try to construct the CONSTRAINT on a yes/no column???
But, my guess is that a standard sub could accomplish your intended logic. Caution: Once you change the value to YES, that record would receive ERROR MESSAGE in subsequent processing (suggest you clarify the logic before proceeding).

A CHECK constraint on a Table/column is applied/tested/executed by the database system "automatically" when there is an action against the table/column.
An Event Procedure is run when the specific event is encountered/actioned based on the logic of your program/code.


Sample code to demo your #2 using tblTestMaxRecs

id	pname	checkcol
19	record2	No
23	record6	No
24	record1	Yes
25	hhhhh	No
26	ddd	No

' ----------------------------------------------------------------
' Procedure Name: changeCheck
' Purpose: demo routine to check value of CheckCol
'             if YES then error message
'             if NO  then run some vba, then update CheckCol to True/Yes
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 30-May-24
' related to https://www.accessforums.net/showthread.php?t=89784&p=524794#post524794
' ----------------------------------------------------------------
Sub changeCheck()
    Dim rs As DAO.Recordset, i As Integer
    Set rs = CurrentDb.OpenRecordset("tblTestMaxRecs")
    Do While Not rs.EOF
        If rs!CHECKCOL Then
            MsgBox "Error --was Yes " & rs!id
        Else                     'is NO/FALSE
            For i = 1 To 2       ' run some vba
                Debug.Print i & "  " & rs!id
            Next i
            rs.Edit
            rs!CHECKCOL = True  'update the NO value to Yes
            rs.Update
        End If
        rs.MoveNext
    Loop
End Sub

[Khalil Handal]

Hi,
Thank you for the reply.
Related to your reply about :Sub changeCheck()
I will be selecting one single record for the table based on a value from a combo box, check the value and apply the update based on the checkcol field value.

Khalil