Unable to set variable as form

[AUGuy]

I've got a function that iterates through each control on the form and looks for any required fields that aren't filled in. The function worked great initially by doing the check in the module and the control iteration in the form. However, this results in the recordset (table) being opened every time the control iterates in the form and then calls the function (which opens the recordset). Here's the code below:

The form button is tied to this which starts the iteration and calls the function:

Private Sub cbNext_Click()
Dim strForm As String
Dim strField As String
Dim strMissing As String
Dim intMissing As Integer
strMissing = ""
intMissing = 0
strForm = Me.Name
For Each Control In Me.Controls
    strField = Control.Name
    Select Case ReqCheck(strForm, strField)
        Case Is = True
            strMissing = strMissing & strField & Chr(10)
            intMissing = intMissing + 1
    End Select
Next Control
If intMissing > 0 Then
    MsgBox "You need to fill in the following " & intMissing & " fields:" & Chr(10) & strMissing, vbCritical, "Missing Required Fields"
    End
End If

The original function ReqCheck is below:

Public Function ReqCheck(strForm As String, strField As String)
'This function evaluates whether a field passed to it is requred or not.
'It returns TRUE for a required field, and FALSE for a non-required field.
Dim rs As Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Required_Controls", dbOpenSnapshot).Clone
rs.MoveFirst
rs.FindFirst ("[RequiredControls] = '" & strField & "' AND [Form] = '" & strForm & "'")
Select Case rs.NoMatch
    Case Is = True
        ReqCheck = False
        Debug.Print "ReqCheck - " & strForm & " - " & strField & ": " & ReqCheck
        rs.Close
    Case Is = False
        ReqCheck = True
        Debug.Print "ReqCheck - " & strForm & " - " & strField & ": " & ReqCheck
        rs.Close
End Select
 
End Function

It seems that moving the form part of the iteration to the function would improve the speed since it would only open the recordset once, then do all the searches, then close the recordset.

My issue is if I change the function to be called like this:

ReqCheck(frm as Form)

and call it from the form like this:

ReqCheck(Me.Form.Name)

it errors out (type mismatch). Any idea what i'm doing wrong? How do you call a function with a variable as form?

Thanks.

 

[bob fitz]

Without much confidence, Try:
ReqCheck(Me)

 

[AUGuy]

Without much confidence, Try:
ReqCheck(Me)

tried that as well, no dice. Still gives a runtime error 13 - type mismatch

Thanks, though

 

[bob fitz]

Try this:
ReqCheck Me

 

[AUGuy]

Try this:
ReqCheck Me

that actually worked.... but...why....

 

[bob fitz]

that actually worked.... but...why....

I'm sorry, I have no idea. I had the same problem a few months ago but couldn't remember at first what the solution was.

 

[ChrisO]

How can it work?

If:-
ReqCheck(frm as Form)
is the Function which returns True or False and you call it with:-
ReqCheck Me
then the returned value is not used.


Chris.

 

[AUGuy]

How can it work?

If:-
ReqCheck(frm as Form)
is the Function which returns True or False and you call it with:-
ReqCheck Me
then the returned value is not used.


Chris.

Correct. In it's current state it's called from the form and fed the field/form names and returned whether that field on that form is required (true/false). I have to adjust it to work the way I mentioned above, but I first had to figure out why it wouldn't call properly.

 

[AUGuy]

How can it work?

If:-
ReqCheck(frm as Form)
is the Function which returns True or False and you call it with:-
ReqCheck Me
then the returned value is not used.


Chris.

Here's the adjusted function if you or anyone else is interested:

Public Function ReqCheck(frm As Form)
'This function iterates through all combo boxes, text boxes, and list boxes on a form
'and compares their control name to the table "tbl_Required_Controls".  After scanning the
'form it displays a message box informing the user of required fields they have not filled in.
Dim rs As Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Required_Controls", dbOpenSnapshot).Clone
strMissing = ""
intMissing = 0
strForm = frm.Name
For Each Control In frm.Controls
    If Control.ControlType = acComboBox Or Control.ControlType = acTextBox Or Control.ControlType = acListBox Then
        If IsNull(Control) = True Then
            strField = Control.Name
            rs.MoveFirst
            rs.FindFirst ("[RequiredControls] = '" & strField & "' AND [Form] = '" & strForm & "'")
            Select Case rs.NoMatch
                Case Is = True
                    Debug.Print "ReqCheck - " & strForm & " - " & strField & ": Not Required"
                Case Is = False
                    Debug.Print "ReqCheck - " & strForm & " - " & strField & ": Required"
                    strMissing = strMissing & strField & Chr(10)
                    intMissing = intMissing + 1
            End Select
        End If
    End If
Next Control
If intMissing > 0 Then
    MsgBox "You need to fill in the following " & intMissing & " fields:" & Chr(10) & strMissing, vbCritical, "Missing Required Fields"
    rs.Close
    End
rs.Close
End If
End Function