Check variable control on forms with VBA

[RECrerar]

Hi,

I have a bespoke data validation system set up for one of my forms.

The data validation defines which controls must be popolated for a project dependant on the project status (which is itself a control). The controls to be checked are variable (i.e someone with admin permissiong could go into the database and modify the list of checked controls).

So what I end up with is a two tables:

tblStatusValidationLevels, which contains the validation levels assigned to each status

and

tblFieldValdationLevels that has the following fields
ControlName (the name of the control on the form that is to be checked)
DisplayName (A user friendly version of the name for error messages)
ValidationLevel (the validation level of that control)

What I want the code to do is
1. Check the status and look up the status security value
2. Check each record th the tblFieldValidation recordset and if the validation level is equal or less than that of the status check that a value is entered in the field
3. Display a message at the end to show the user the missing fields.

Below is what I have so far. I can't figure out how to use a control name in a variable to check the value of that control.

Dim DataValNum As Integer

'get the status validation level
DataValNum = DLookup("[ValidLevel]", "tblStatusValidationLevels", "[StatusID]=" & Me.Status)
 
msg = "The following Data is missing:" & vbcr
 
Dim rcd As DAO.recordset
Set rcd = CurrentDb.OpenRecordset("tblFieldValdationLevels", dbOpenDynaset)

rcd.MoveFirst
do until rcd.EOF
'   Check the security level of the fields (level 0 means do not check)
    If rcd("ValidationLevel").Value <= DataValNum And rcd ("ValidationLevel").Value <> 0 Then
'***********************************************
'Check the control.  How?
'*************************************************
msg = msg & rcd("DisplayName").value & vbcr
dataOK = false
End If
rcd.movenext
Loop
 
if DataOk = false then
..... etc

If anyone can help it would be hugely appreciated

 

[RECrerar]

Hey,

Thanks for the links. The first one was perfect and it is all working now.

I have the following code:

Function BidTasksOK() As Boolean
BidTasksOK = True
Dim DataValNum As Integer
Dim strControlNames As String
Dim msg As String
DataValNum = DLookup("[SecurityLevel]", "tblSecurityLevelStatus", "[StatusID]=" & Me.Status)
On Error GoTo 0
Dim rcd As DAO.recordset
Set rcd = CurrentDb.OpenRecordset("SELECT tblFieldSecurityLevels.FieldID, " & _
"tblFieldSecurityLevels.[Security Level], tblSecurityFields.ControlName FROM tblSecurityFields " &_
"INNER JOIN tblFieldSecurityLevels ON tblSecurityFields.ControlID = tblFieldSecurityLevels.FieldID;", dbOpenDynaset)
rcd.MoveFirst
    Do Until rcd.EOF
    '   Check the security level of the fields (prob don't need the second part)
        If rcd("Security Level").Value <= DataValNum And rcd("Security Level").Value <> 0 Then
    '   Create a string of control names
            strControlNames = strControlNames & rcd("ControlName").Value & " "
        End If
        rcd.MoveNext
    Loop
 
    msg = "The following fields are required:" & vbCr
 
    Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.Tag = "CheckMe" Then
         '   Debug.Print ctl.Name
            If InStr(1, strControlNames, ctl.Name, vbTextCompare) Then
                If IsNull(ctl.Value) Or ctl.Value = 0 Then
                    BidTasksOK = False
                    msg = msg & DLookup("[DisplayFieldName]", "tblSecurityFields", "[ControlName]='" & ctl.Name & "'")
                    msg = msg & vbCr
                End If
            End If
        End If
    Next ctl
 
    If Not BidTasksOK Then MsgBox msg, , "Missing Data"
 
End Function

Some of the field names are a bit different to the original post as I decided I didn't need to create a pre-defined query of the information.

Thanks