Check mandatory fields using VBA not working properly

I

Iram

Hello,
I have a "Save and Close" button on a form that is supposed to verify that
13 fields have been filled out befoe closing the data entry form (below is in
the On Click Event Procedure. The problem is that the code doesn't work like
its supposed to some times. For instance if I enter data into 10 of the
fields and click "Save and Close" I get a prompt for each of the fields that
I did not fill in and at the same time it tabs through each of them like its
supposed to HOWEVER if I just fill in one of the remaining 3 fields (leaving
2 fields blank) I get two prompts and after clicking Ok to both of them then
the form closes without giving me a chance to add data to those fields. Why
doesn't it let me add data before closing the form? Can you help me fix this?
I am using Access 2003.



Private Sub Save_and_Close_Click()

Dim frm As Form
Dim strMsgBoxTitle As String

Set frm = Forms!frmQuestionnaireAdd!

strMsgBoxTitle = "Mandatory Field"

' Check mandatory field Calwin Number
If IsNull(frm.CalwinNumb.Value) Then
MsgBox "Please fill in the Calwin Number field.", vbOKOnly,
strMsgBoxTitle
frm.CalwinNumb.SetFocus
End If

' Check mandatory Multiple referrals
If IsNull(frm.MultipleRfrrl_YN.Value) Then
MsgBox "Please select Yes or No for multiple referrals.", vbOKOnly,
strMsgBoxTitle
frm.MultipleRfrrl_YN.SetFocus
End If

' Check mandatory field CP First Name
If IsNull(frm.CP_NameF.Value) Then
MsgBox "Please fill in the CP's First Name.", vbOKOnly, strMsgBoxTitle
frm.CP_NameF.SetFocus
End If

' Check mandatory field CP Last Name
If IsNull(frm.CP_NameL.Value) Then
MsgBox "Please fill in the CP's Last Name.", vbOKOnly, strMsgBoxTitle
frm.CP_NameL.SetFocus
End If

' Check mandatory field CP Address Line 1
If IsNull(frm.CP_AddLine1.Value) Then
MsgBox "Please fill in the CP's Address Line 1.", vbOKOnly,
strMsgBoxTitle
frm.CP_AddLine1.SetFocus
End If

' Check mandatory field CP's City
If IsNull(frm.CP_AddCity.Value) Then
MsgBox "Please fill in the CP's City.", vbOKOnly, strMsgBoxTitle
frm.CP_AddCity.SetFocus
End If

' Check mandatory field CP's State
If IsNull(frm.CP_AddState.Value) Then
MsgBox "Please fill in the CP's State.", vbOKOnly, strMsgBoxTitle
frm.CP_AddState.SetFocus
End If

' Check mandatory field CP's zip
If IsNull(frm.CP_AddZip.Value) Then
MsgBox "Please fill in the CP's zip code.", vbOKOnly, strMsgBoxTitle
frm.CP_AddZip.SetFocus
End If

' Check mandatory field NCP's First Name
If IsNull(frm.NP_NameF.Value) Then
MsgBox "Please fill in the NCP's First Name.", vbOKOnly,
strMsgBoxTitle
frm.NP_NameF.SetFocus
End If

' Check mandatory field NCP's Last Name
If IsNull(frm.NP_NameL.Value) Then
MsgBox "Please fill in the NCP's Last Name.", vbOKOnly, strMsgBoxTitle
frm.NP_NameL.SetFocus
End If

' Check mandatory field Children of this relationship
If IsNull(frm.ChildrenOfThisRelationship.Value) Then
MsgBox "Please fill in the Children of this relationship info.",
vbOKOnly, strMsgBoxTitle
frm.ChildrenOfThisRelationship.SetFocus
End If

' Check mandatory field Interview Notes
If IsNull(frm.InterviewNotes.Value) Then
MsgBox "Please fill in the Interview Notes.", vbOKOnly, strMsgBoxTitle
Me.InterviewNotes.SetFocus

Else

DoCmd.Close

End If


End Sub
 
D

Dale Fye

Iram,

I usually do this by putting the error checking code in the forms
BeforeUpdate event. Since that event has a Cancel parameter which allows me
to cancel the update if all of the fields are not filled in. Assuming that
the command button is on the form you are trying to update, my command buton
code would look like:

Private Sub Save_and_Close_Click

if me.dirty then me.dirty = false

End sub

Then, the forms BeforeUpdate code would look something like the following.
I use the If ... ElseIf structure so that it cancels the update as soon as
it finds the first field that fails to pass your checks.

Private Sub Form_BeforeUpdate(Cancel as integer)

Dim strMsg as string
Dim ctrl as control
'Assumes that something will fail (avoids repetitive use of that line)
Cancel = true

'Check each of the required fields
If IsNull(me.CalwinNumb.Value) Then
strMsg = "Please fill in the Calwin Number field."
set ctrl = me.CalwinNumb
Elseif IsNull(frm.MultipleRfrrl_YN.Value) Then
strMsg = "Please select Yes or No for multiple referrals."
set ctrl = me.MultipleRfrrl_YN
Elseif IsNull(frm.CP_NameF.Value) Then
strMsg = "Please fill in the CP's First Name."
set ctrl = me.CP_NameF.SetFocus
Else
Cancel = False
endif

If Cancel = True then
msgbox strMsg, vbOKOnly
ctrl.setfocus
endif

End Sub

The down side of your methodology is the ISNULL() does not test for an empty
string (""), so if a value was entered in one of these fields, and then
subsequently deleted, it is likely that the value of the field will be "",
not NULL. In which case, this code would not work. For that reason, I
usually either use one of the following techniques

IF LEN(me.CalwinNumb & "") = 0

This method can get tedious, so I have a function IsNullOrBlank that I use
that uses the same code but is easier to read when debugging:

Public Function IsNullOrBlank(SomeValue as Variant) as Boolean

IsNullOrBlank = (Len(SomeValue & "") = 0)

End function

Then I would just replace all of the IsNull( ) function calls in the
BeforeUpdate code with IsNullOrBlank( ).

HTH
Dale
 
I

Iram

Thank you for the code Dale! I performed the following steps:
****Created a module called "IsNullOrBlank" and pasted the below into it...

Public Function IsNullOrBlank()
IsNullOrBlank = (Len(SomeValue & "") = 0)
End Function

****Then I added the below code to the Save and Close button on the form
Private Sub Save_and_Close_Click()
If Me.Dirty Then Me.Dirty = False
End Sub

****Then on the Before Update of the form in question I created the Event
Procedure with the below code however once I put some data in some of the
fields and intentionaly leave others blank I get the End or Debug prompt then
the code shows up and Private Sub Form_BeforeUpdate(Cancel As Integer) turns
into a yellow highlight and IsNullOrBlank turns into a grey highlight*****
Can you help me figure out this problem?

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim ctrl As Control
'Assumes that something will fail (avoids repetitive use of that line)
Cancel = True

'Check each of the required fields
If IsNullOrBlank(Me.CalwinNumb.Value) Then
strMsg = "Please fill in the Calwin Number field."
Set ctrl = Me.CalwinNumb.SetFocus
ElseIf IsNullOrBlank(frm.MultipleRfrrl_YN.Value) Then
strMsg = "Please select Yes or No for multiple referrals."
Set ctrl = Me.MultipleRfrrl_YN.SetFocus
ElseIf IsNullOrBlank(frm.CP_NameF.Value) Then
strMsg = "Please fill in the CP's First Name."
Set ctrl = Me.CP_NameF.SetFocus
ElseIf IsNullOrBlank(frm.CP_NameL.Value) Then
strMsg = "Please fill in the CP's Last Name."
Set ctrl = Me.CP_NameL.SetFocus
ElseIf IsNullOrBlank(frm.CP_AddLine1.Value) Then
strMsg = "Please fill in the CP's Address Line 1."
Set ctrl = Me.CP_AddLine1.SetFocus
ElseIf IsNullOrBlank(frm.CP_AddCity.Value) Then
strMsg = "Please fill in the CP's City."
Set ctrl = Me.CP_AddCity.SetFocus
ElseIf IsNullOrBlank(frm.CP_AddState.Value) Then
strMsg = "Please fill in the CP's State."
Set ctrl = Me.CP_AddState.SetFocus
ElseIf IsNullOrBlank(frm.CP_AddZip.Value) Then
strMsg = "Please fill in the CP's Zip Code."
Set ctrl = Me.CP_AddZip.SetFocus
ElseIf IsNullOrBlank(frm.CP_PhoneNumb.Value) Then
strMsg = "Please fill in the CP's Phone Number."
Set ctrl = Me.CP_PhoneNumb.SetFocus
ElseIf IsNullOrBlank(frm.NP_NameF.Value) Then
strMsg = "Please fill in the NP's First Name."
Set ctrl = Me.NP_NameF.SetFocus
ElseIf IsNullOrBlank(frm.NP_NameL.Value) Then
strMsg = "Please fill in the NP's Last Name."
Set ctrl = Me.NP_NameL.SetFocus
ElseIf IsNullOrBlank(frm.Estb_Marriage_YN.Value) Then
strMsg = "Please select Yes or No for Is There a Marriage."
Set ctrl = Me.Estb_Marriage_YN.SetFocus
ElseIf IsNullOrBlank(frm.ChildrenOfThisRelationship.Value) Then
strMsg = "Please fill in the Children of this relationship field."
Set ctrl = Me.ChildrenOfThisRelationship.SetFocus
ElseIf IsNullOrBlank(frm.InterviewNotes.Value) Then
strMsg = "Please fill in the Interview Notes."
Set ctrl = Me.InterviewNotes.SetFocus

Else

Cancel = False
End If

If Cancel = True Then
MsgBox strMsg, vbOKOnly
ctrl.SetFocus
End If



End Sub
 
D

Dale Fye

It is in the function. You forgot to declare the variable (SomeValue) and
the data type of the return value.

The opening line of that should look like:

Public Function IsNullOrBlank(SomeValue as Variant) as Boolean

HTH
Dale
 
I

Iram

Hello Dale, sorry about the delays...
Once I changed the function according to how you said I get the error
"Complie Error, Expected variable or procedure, not module".
How do I fix this?

Iram
 
I

Iram

Also, with that error in the previous message the following turns yellow
highlighted... "Private Sub Form_BeforeUpdate(Cancel As Integer)"

And

IsNullOrBlank in the following statement becomes grey highlighted...
If IsNullOrBlank(Me.CalwinNumb.Value) Then
strMsg = "Please fill in the Calwin Number field."
Set ctrl = Me.CalwinNumb.SetFocus



Iram
 
D

Dale Fye

2 thoughts:

1. Where did you put the IsNullOrBlank( ) function? Is it in a code
module, or in the code behind your form? I usually put it in a code module
that can be accessed from anywhere in my application. If you do this, then
you should be able to go to the immediate window and type:

?IsNullOrBlank(Null)
and what gets printed will be:
True

?IsNullOrBlank("A")
will result in False

It that doesn't work, then there is a problem with the function (post the
entire set of code for the function).

2. When you created the Form_BeforeUpdate code, did you just cut and paste
from my reply, or did you open the form in design view, select the Event tab
in the properties window and then select the BeforeUpdate event of the form,
set it to Expression, and click on the "..." to get to the code window? I
would copy the code between the event declaration and the End Sub, then
delete the Form_Update event, and try it again, using the above procedure.

Dale
 
I

Iram

1. I created a Module called "IsNullOrBlank" and copied the below into it...
Option Compare Database
Option Explicit
Public Function IsNullOrBlank(SomeValue As Variant) As Boolean
IsNullOrBlank = (Len(SomeValue & "") = 0)
End Function

I am not sure how to do what you said as far as checking the code but what I
did was I went to the form in question, right clicked the "CalwinNumb" field,
properties, On Click, Event Procedure, typed- " ?IsNullOrBlank(Null)",
closed the coding page, clicked on the CalwinNumb field and I got the same
error "Compile error: Expected variable or procedure, not module".

1. Below is all of the code in the Form...
Option Compare Database
Option Explicit



Private Sub cmdSR_Review_Comp_Click()
' When SR Review Complete button is clicked

Dim frm As Form

Set frm = Forms!frmQuestionnaireAdd!

With frm

If IsDate(.SRReviewCompDate.Value) Then
MsgBox "This record has already been marked as SR Review
Completed."
Else
.SRReviewCompDate.Value = Now
.SRReviewCompBy.Value = fOSUserName()
End If

End With
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String
Dim ctrl As Control
'Assumes that something will fail (avoids repetitive use of that line)
Cancel = True

'Check each of the required fields
If IsNullOrBlank(Me.CalwinNumb.Value) Then
strMsg = "Please fill in the Calwin Number field."
Set ctrl = Me.CalwinNumb.SetFocus
ElseIf IsNullOrBlank(frm.MultipleRfrrl_YN.Value) Then
strMsg = "Please select Yes or No for multiple referrals."
Set ctrl = Me.MultipleRfrrl_YN.SetFocus
ElseIf IsNullOrBlank(frm.CP_NameF.Value) Then
strMsg = "Please fill in the CP's First Name."
Set ctrl = Me.CP_NameF.SetFocus
ElseIf IsNullOrBlank(frm.CP_NameL.Value) Then
strMsg = "Please fill in the CP's Last Name."
Set ctrl = Me.CP_NameL.SetFocus
ElseIf IsNullOrBlank(frm.CP_AddLine1.Value) Then
strMsg = "Please fill in the CP's Address Line 1."
Set ctrl = Me.CP_AddLine1.SetFocus
ElseIf IsNullOrBlank(frm.CP_AddCity.Value) Then
strMsg = "Please fill in the CP's City."
Set ctrl = Me.CP_AddCity.SetFocus
ElseIf IsNullOrBlank(frm.CP_AddState.Value) Then
strMsg = "Please fill in the CP's State."
Set ctrl = Me.CP_AddState.SetFocus
ElseIf IsNullOrBlank(frm.CP_AddZip.Value) Then
strMsg = "Please fill in the CP's Zip Code."
Set ctrl = Me.CP_AddZip.SetFocus
ElseIf IsNullOrBlank(frm.CP_PhoneNumb.Value) Then
strMsg = "Please fill in the CP's Phone Number."
Set ctrl = Me.CP_PhoneNumb.SetFocus
ElseIf IsNullOrBlank(frm.NP_NameF.Value) Then
strMsg = "Please fill in the NP's First Name."
Set ctrl = Me.NP_NameF.SetFocus
ElseIf IsNullOrBlank(frm.NP_NameL.Value) Then
strMsg = "Please fill in the NP's Last Name."
Set ctrl = Me.NP_NameL.SetFocus
ElseIf IsNullOrBlank(frm.Estb_Marriage_YN.Value) Then
strMsg = "Please select Yes or No for Is There a Marriage."
Set ctrl = Me.Estb_Marriage_YN.SetFocus
ElseIf IsNullOrBlank(frm.ChildrenOfThisRelationship.Value) Then
strMsg = "Please fill in the Children of this relationship field."
Set ctrl = Me.ChildrenOfThisRelationship.SetFocus
ElseIf IsNullOrBlank(frm.InterviewNotes.Value) Then
strMsg = "Please fill in the Interview Notes."
Set ctrl = Me.InterviewNotes.SetFocus

Else

Cancel = False
End If

If Cancel = True Then
MsgBox strMsg, vbOKOnly
ctrl.SetFocus
End If
End Sub

Private Sub Frame_CO_YN_AfterUpdate()

Dim frm As Form
Set frm = Forms!frmQuestionnaireAdd!

With frm
If .Frame_CO_YN.Value = 1 Then
.CO_CountyState.Enabled = True
.CO_Order_.Enabled = True
Else
.CO_CountyState.Enabled = False
.CO_Order_.Enabled = False
End If
End With

End Sub

Private Sub Frame_DCT_ChildOther_YN_AfterUpdate()

Dim frm As Form
Set frm = Forms!frmQuestionnaireAdd!

With frm
If .Frame_DCT_ChildOther_YN.Value = 1 Then
.DCT_ChildOther_CountyState.Enabled = True
.DCT_ChildTimePeriod.Enabled = True
Else
.DCT_ChildOther_CountyState.Enabled = False
.DCT_ChildTimePeriod.Enabled = False
End If
End With

End Sub




Private Sub Frame_Estb_Marriage_YN_Click()

Dim frm As Form
Set frm = Forms!frmQuestionnaireAdd!

With frm
If .Frame_Estb_Marriage_YN.Value = 1 Then
.Estb_MarriageWhen.Enabled = True
.Estb_MarriageWhere.Enabled = True
Else
.Estb_MarriageWhen.Enabled = False
.Estb_MarriageWhere.Enabled = False
End If
End With
End Sub

Private Sub Frame_MultipleRfrrl_YN_AfterUpdate()

Dim frm As Form
Set frm = Forms!frmQuestionnaireAdd!

With frm
If .Frame_MultipleRfrrl_YN.Value = 1 Then
.MultipleRfrrl_HowMany_Item.Enabled = True
.MultipleRfrrl_HowMany_Total.Enabled = True
Else
.MultipleRfrrl_HowMany_Item.Enabled = False
.MultipleRfrrl_HowMany_Total.Enabled = False
End If
End With

End Sub

Private Sub Frame_NP_OtherIncome_AfterUpdate()

Dim frm As Form
Set frm = Forms!frmQuestionnaireAdd!

With frm
If .Frame_NP_OtherIncome.Value = 1 Then
.NP_OtherInsCo.Enabled = True
Else
.NP_OtherInsCo.Enabled = False
End If
End With

End Sub

Private Sub Frame_Unkn_MoreThanOne_YN_AfterUpdate()
Dim frm As Form
Set frm = Forms!frmQuestionnaireAdd!

With frm
If .Frame_Unkn_MoreThanOne_YN.Value = 1 Then
.Unkn_NamePutative.Enabled = True
Else
.Unkn_NamePutative.Enabled = False
End If
End With

End Sub

Private Sub MultipleRfrrl_YN_AfterUpdate()

Dim frm As Form
Set frm = Forms!frmQuestionnaireAdd!

With frm
If .MultipleRfrrl_YN.Value = 1 Then
.MultipleRfrrl_HowMany_Item.Enabled = True
.MultipleRfrrl_HowMany_Total.Enabled = True
Else
.MultipleRfrrl_HowMany_Item.Enabled = False
.MultipleRfrrl_HowMany_Total.Enabled = False
End If
End With

End Sub

Private Sub Save_and_Close_Click()
If Me.Dirty Then
Me.Dirty = False
End If
DoCmd.Close acForm, Me.Name

End Sub
Private Sub Command557_Click()
On Error GoTo Err_Command557_Click


DoCmd.Close

Exit_Command557_Click:
Exit Sub

Err_Command557_Click:
MsgBox Err.Description
Resume Exit_Command557_Click

End Sub

2. I opened the form in design mode, clicked Before Update, Event Procedure
and pasted the above code into the Event Procedure.
I think I see where the problem is now. I have the above code in the Form
code area instead of a module. I will move the above code into a module then
perform the Expression steps as you explained in the previous message. Am I
right?





Iram
 
I

Iram

Dale,
Can I just email this db to you and call you?
I am pretty stressed... I can't get anything straight
I have to leave at 5pm PST and will be back in on Monday at 7am...


Iram



Iram
 
J

John W. Vinson

1. I created a Module called "IsNullOrBlank" and copied the below into it...
Option Compare Database
Option Explicit
Public Function IsNullOrBlank(SomeValue As Variant) As Boolean
IsNullOrBlank = (Len(SomeValue & "") = 0)
End Function
Click to expand...

That's the problem. You've run afoul of a irksome feature of the VBA compiler:
Modules and Procedures (within those modules) share the same namespace. If you
have a module with the same name as a Sub you'll get this error.

Change the name of the Module to something else - basIsNullOrBlank say - and
your code should work.
 
D

Dale Fye

As John mentioned, rename the module. I usually keep this code in a module
called mod_Text_Code.

Dale
 
I

Iram

I keep getting a Run-Time error '2473': The expression Before Update you
entered as the event property setting produced the following error: The
expression you entered has a function name that Microsoft Office Access can't
find.

If you can please help again I would appreciate. Below is a breakdown
summary...

On a data entry form I have about 40 fields, 11 of which are Text and Memo
fields which are required.

On this same data entry form I have a "Save and Close button" that is
supposed to save and close the form with the following code in the On Click
[Event Procedure]...

Private Sub Save_and_Close_Click()
If Me.Dirty Then Me.Dirty = False

End Sub

On the "Before Update" of this same data entry form I have an expression of
=Form_BeforeUpdate() which takes me to the code in a Module called
"mdl_Is_Null_Or_Blank" that has the following code...

Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)


Dim strMsg As String
Dim ctrl As Control
'Assumes that something will fail (avoids repetitive use of that line)
Cancel = True

'Check each of the required fields
If IsNullOrBlank(Me.CalwinNumb.Value) Then
strMsg = "Please fill in the Calwin Number field."
Set ctrl = Me.CalwinNumb.SetFocus

ElseIf IsNullOrBlank(frm.CP_NameF.Value) Then
strMsg = "Please fill in the CP's First Name."
Set ctrl = Me.CP_NameF.SetFocus

ElseIf IsNullOrBlank(frm.CP_NameL.Value) Then
strMsg = "Please fill in the CP's Last Name."
Set ctrl = Me.CP_NameL.SetFocus

ElseIf IsNullOrBlank(frm.CP_AddLine1.Value) Then
strMsg = "Please fill in the CP's Address Line 1."
Set ctrl = Me.CP_AddLine1.SetFocus

ElseIf IsNullOrBlank(frm.CP_AddCity.Value) Then
strMsg = "Please fill in the CP's City."
Set ctrl = Me.CP_AddCity.SetFocus

ElseIf IsNullOrBlank(frm.CP_AddState.Value) Then
strMsg = "Please fill in the CP's State."
Set ctrl = Me.CP_AddState.SetFocus

ElseIf IsNullOrBlank(frm.CP_AddZip.Value) Then
strMsg = "Please fill in the CP's Zip Code."
Set ctrl = Me.CP_AddZip.SetFocus

ElseIf IsNullOrBlank(frm.CP_PhoneNumb.Value) Then
strMsg = "Please fill in the CP's Phone Number."
Set ctrl = Me.CP_PhoneNumb.SetFocus

ElseIf IsNullOrBlank(frm.NP_NameF.Value) Then
strMsg = "Please fill in the NP's First Name."
Set ctrl = Me.NP_NameF.SetFocus

ElseIf IsNullOrBlank(frm.NP_NameL.Value) Then
strMsg = "Please fill in the NP's Last Name."
Set ctrl = Me.NP_NameL.SetFocus

ElseIf IsNullOrBlank(frm.ChildrenOfThisRelationship.Value) Then
strMsg = "Please fill in the Children of this relationship field."
Set ctrl = Me.ChildrenOfThisRelationship.SetFocus

ElseIf IsNullOrBlank(frm.InterviewNotes.Value) Then
strMsg = "Please fill in the Interview Notes."
Set ctrl = Me.InterviewNotes.SetFocus

Else

Cancel = False
End If

If Cancel = True Then
MsgBox strMsg, vbOKOnly
ctrl.SetFocus
End If



I have another moduel called "mdl_Is_Null_Or_BlankCalculation" that verifies
text fields if they are null with the following code...

Option Compare Database
Option Explicit


Public Function IsNullOrBlankFied(SomeValue As Variant) As Boolean

IsNullOrBlank = (Len(SomeValue & "") = 0)

End Function



Aprarently everything looks ok to my eyes however I keep getting a "Run-Time
error '2473': The expression Before Update you entered as the event property
setting produced the following error: The expression you entered has a
function name that Microsoft Office Access can't find."

Then whe I click debug "Me.Dirty = False" turns yellow in the below code

Private Sub Save_and_Close_Click()
If Me.Dirty Then Me.Dirty = False

End Sub



Can you help me fix this? I have already renamed the module names (see above).


Thanks.
Iram
 
D

Dale Fye

Iram,

I think the problem is with your BeforeUpdate event.

1. Open the form in design view.
2. Click the square in the upper left corner that selects the form
3. Open the forms Properties dialog window
4. Go to the Event Tab
5. Click in the space next to BeforeUpdate, then expand the drop down list
and select [Event Procedure]
6. Click the "..." button to the right of the dropdown list. This will
take you to the forms BeforeUpdate event.

In your BeforeUpdate event, why are you refering to the frm object? You
have never declared that object. But I always use the me.controlname syntax
to refer to controls that exist on the form where the code is running. If
you have a code module, and want to refer to a particular form, then you can
declare the frm as a form, then set the form equal to the form, then refer
to the controls or properties of the form using the frm.whatever syntax.
But in this case, I'd just use the me.controlname syntax.

HTH
Dale

Iram said:
I keep getting a Run-Time error '2473': The expression Before Update you
entered as the event property setting produced the following error: The
expression you entered has a function name that Microsoft Office Access
can't
find.

If you can please help again I would appreciate. Below is a breakdown
summary...

On a data entry form I have about 40 fields, 11 of which are Text and Memo
fields which are required.

On this same data entry form I have a "Save and Close button" that is
supposed to save and close the form with the following code in the On
Click
[Event Procedure]...

Private Sub Save_and_Close_Click()
If Me.Dirty Then Me.Dirty = False

End Sub

On the "Before Update" of this same data entry form I have an expression
of
=Form_BeforeUpdate() which takes me to the code in a Module called
"mdl_Is_Null_Or_Blank" that has the following code...

Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)


Dim strMsg As String
Dim ctrl As Control
'Assumes that something will fail (avoids repetitive use of that line)
Cancel = True

'Check each of the required fields
If IsNullOrBlank(Me.CalwinNumb.Value) Then
strMsg = "Please fill in the Calwin Number field."
Set ctrl = Me.CalwinNumb.SetFocus

ElseIf IsNullOrBlank(frm.CP_NameF.Value) Then
strMsg = "Please fill in the CP's First Name."
Set ctrl = Me.CP_NameF.SetFocus

ElseIf IsNullOrBlank(frm.CP_NameL.Value) Then
strMsg = "Please fill in the CP's Last Name."
Set ctrl = Me.CP_NameL.SetFocus

ElseIf IsNullOrBlank(frm.CP_AddLine1.Value) Then
strMsg = "Please fill in the CP's Address Line 1."
Set ctrl = Me.CP_AddLine1.SetFocus

ElseIf IsNullOrBlank(frm.CP_AddCity.Value) Then
strMsg = "Please fill in the CP's City."
Set ctrl = Me.CP_AddCity.SetFocus

ElseIf IsNullOrBlank(frm.CP_AddState.Value) Then
strMsg = "Please fill in the CP's State."
Set ctrl = Me.CP_AddState.SetFocus

ElseIf IsNullOrBlank(frm.CP_AddZip.Value) Then
strMsg = "Please fill in the CP's Zip Code."
Set ctrl = Me.CP_AddZip.SetFocus

ElseIf IsNullOrBlank(frm.CP_PhoneNumb.Value) Then
strMsg = "Please fill in the CP's Phone Number."
Set ctrl = Me.CP_PhoneNumb.SetFocus

ElseIf IsNullOrBlank(frm.NP_NameF.Value) Then
strMsg = "Please fill in the NP's First Name."
Set ctrl = Me.NP_NameF.SetFocus

ElseIf IsNullOrBlank(frm.NP_NameL.Value) Then
strMsg = "Please fill in the NP's Last Name."
Set ctrl = Me.NP_NameL.SetFocus

ElseIf IsNullOrBlank(frm.ChildrenOfThisRelationship.Value) Then
strMsg = "Please fill in the Children of this relationship field."
Set ctrl = Me.ChildrenOfThisRelationship.SetFocus

ElseIf IsNullOrBlank(frm.InterviewNotes.Value) Then
strMsg = "Please fill in the Interview Notes."
Set ctrl = Me.InterviewNotes.SetFocus

Else

Cancel = False
End If

If Cancel = True Then
MsgBox strMsg, vbOKOnly
ctrl.SetFocus
End If



I have another moduel called "mdl_Is_Null_Or_BlankCalculation" that
verifies
text fields if they are null with the following code...

Option Compare Database
Option Explicit


Public Function IsNullOrBlankFied(SomeValue As Variant) As Boolean

IsNullOrBlank = (Len(SomeValue & "") = 0)

End Function



Aprarently everything looks ok to my eyes however I keep getting a
"Run-Time
error '2473': The expression Before Update you entered as the event
property
setting produced the following error: The expression you entered has a
function name that Microsoft Office Access can't find."

Then whe I click debug "Me.Dirty = False" turns yellow in the below code

Private Sub Save_and_Close_Click()
If Me.Dirty Then Me.Dirty = False

End Sub



Can you help me fix this? I have already renamed the module names (see
above).


Thanks.
Iram




Dale Fye said:
As John mentioned, rename the module. I usually keep this code in a
module
called mod_Text_Code.

Dale
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Checking a forms fields for null 1
Data Check not checking 14
.adp Project - Data check not checking 1
how to do this bit of code better? 2
Replace MS Access Error with Custom Error 2
Validate Required 2
Access How to check that the value of a field in a new record is not present in 2 fields of the table? 0
Code to require fields on Before_Update not working 1

Top