IS Null Fields (1 Viewer)

OnlyTD · Aug 29, 2018

Hi

I have an input form that collects attendee details and need to make sure that each field is filled in. I have used the code below on the before update event of the form but when I try to close the form, the message box works and when I click ok I have the you cannot save this record at this time message, what is my code missing?

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me![At_Date_of_birth]) Then
MsgBox "You must enter a 'Date od Birth' before this Record can be saved"
Cancel = True: Me!Date_of_birth.SetFocus

ElseIf IsNull(Me![At_Gender]) Then
MsgBox "You must enter 'Gender' before this Record can be saved"
Cancel = True: Me!Gender.SetFocus

ElseIf IsNull(Me![At_Tel Number]) Then
MsgBox "You must enter a Preferred telehone number before this Record can be saved"
Cancel = True: Me!Telephone__Number.SetFocus
ElseIf IsNull(Me![At_Address 1]) Then
MsgBox "You must enter address line 1 before this Record can be saved"
Cancel = True: Me!Address_1.SetFocus

End If

End Sub

Thank you for your help

Minty · Aug 29, 2018

You'll find it better to build a single message box containing all the missing details and a single cancel event - something like

Code:

Dim sMsg As String

If IsNull(Me.[At_Date_of_birth]) Then
	sMsg = sMsg & "Date Of Birth " & vbcrlf
end if

If IsNull(Me.[At_Gender]) Then
	sMsg = sMsg & "Gender " & vbcrlf
end if

If IsNull(Me.[At_Tel Number]) Then
	sMsg = sMsg & "Phone No " & vbcrlf
end if

If IsNull(Me.[At_Address 1]) Then	
	sMsg = sMsg & "Address 1 " & vbcrlf
end if 

If len(sMsg & "") > 1 then 
	sMsg = "You are missing the following items, and the record cannot be saved yet; " & vbcrlf & sMsg & "Please add the missing details"
	MsgBox sMsg, vbok
	cancel = true
	exit sub
end if

OnlyTD · Aug 29, 2018

Hi Minty

Thank your code works better but after the initial message about missing information I am still getting the 'You cant save this record at this time - do you want to proceed anyway option'

I need to stop this and just return to the initial form to complete data entry.

Minty · Aug 29, 2018

How are you exiting the form ?
Are you using the Close [X] Button on the form?

If so you can disable that, and put your own close button on the form. That will stop the form trying to close, and producing the secondary message.

OnlyTD · Aug 29, 2018

Hi Minty

I did have a button before and I removed it. If I put an on click DOCmd.close event on the button will the Cancel = true then cancel the event?

Sorry I cant try it now I have left work but I will try it first thing.

Thank so much for your help, I have to have it up and running by Friday and I have tried loads of code!!!!

Minty · Aug 30, 2018

OnlyTD said:
If I put an on click DOCmd.close event on the button will the Cancel = true then cancel the event?

That's the theory...

It's not foolproof, as someone could simply close the database completely, but that requires a reasonably hard targeted exit.

moke123 · Aug 30, 2018

I use something similar to what Minty suggested but it is a public function that returns a boolean.

Code:

Public Function IsValidForm(frm As Form) As Boolean

    Dim ctl As Control
    Dim strMsg As String

    IsValidForm = True

    For Each ctl In frm.Controls

        If ctl.Tag = "REQ" Then

            If Nz(ctl, "") = "" Then
                strMsg = strMsg & Space(5) & "*" & ctl.Controls.Item(0).Caption & vbNewLine
                ctl.BorderColor = vbRed
                IsValidForm = False

            Else
                ctl.BorderColor = vbBlack
            End If

        End If

    Next

    If IsValidForm = False Then

        MsgBox "The following Field(s) are required:" & vbNewLine & strMsg

    End If

End Function

For each control that you want required put REQ in the tag property.
The code loops through the controls and returns true if all the fields are entered. If somethings missing it returns false, and constructs a string of missing items (using the label caption for the control) and fires a message box with the list. It also highlights the missing field with a red border.

I dont use it with the cancel argument but you could easily switch the boolean values returned to use it with the cancel arguement.
HTH

edit: You would call it like

Code:

if IsValidForm(me) then ...

OnlyTD · Aug 30, 2018

Hi Moke123

thank you for your input. I have never used functions in modules before but I have put your code into a module I just not sure how to call it.

so I put something in the Before update event of the form properties?

Sorry to be a pain but I am gaining very slowly!!

moke123 · Aug 31, 2018

Like I said, I don't usually use it with BeforeUpdate event, I usually use it under a command button such as a save or close button.

for instance a close button could be

Code:

If IsValidForm(me) then
    docmd.close acform, me.name
End if

OnlyTD · Sep 3, 2018

Hi Moke123

I'm sorry I am missing something.
I added the following to an ON Click event and I have a Compile error - Expected variable or procedure not Module.

I have created a module called IsValidForm and added the code you gave me

Sorry

moke123 · Sep 3, 2018

Change the name of the module. A module and a procedure cannot have the same name

OnlyTD · Sep 3, 2018

Hi

I changed the name and its now saying Sub of Function not defined???

Minty · Sep 3, 2018

Did you rename the Module or the function ?.

Sometimes it's easier to create a new module, paste the function code in to the new module, then delete the old one, then do a compact and repair.
The Access inner gremlins can get a bit confused by renamed modules sometimes.

moke123 · Sep 3, 2018

If you look at your original error

Expected variable or procedure not Module.

when you called IsValidForm(me) it first looked at the module you named "IsValidForm" and thus the message "Expected variable or procedure not Module" The bold part being the relevant part of the message.

Its a good idea to adopt a naming practice and stick to it. I usually name all my modules with a mod prefix (modMyModuleName), subs with an s (sMySub) or f for functions (fMyFunction). That said, I didnt follow mine by naming my function IsValidForm without an f prefix

IS Null Fields (1 Viewer)

OnlyTD

Registered User.

Minty

AWF VIP

OnlyTD

Registered User.

Minty

AWF VIP

OnlyTD

Registered User.

Minty

AWF VIP

moke123

AWF VIP

OnlyTD

Registered User.

moke123

AWF VIP

OnlyTD

Registered User.

moke123

AWF VIP

OnlyTD

Registered User.

Minty

AWF VIP

moke123

AWF VIP

Similar threads

Users who are viewing this thread