VBA Help in Validating Fields

[iuianj07]

Hello Guys,

I have a Form named "New Transaction Form", that have multiple fields and a SubForm Named "ASR Requested Form" that only has a Checkbox named "ASR_Requested" that is set as LOCKED field.

What I am trying to do though is that for the ASR_Requested checkbox to be UNLOCKED, certain fields from the New Transaction Form should be filled up. They are

Requested_By
Client_Sponsor
Property_Loan_Name
Portfolio
Date_Requested
File_Source
Specific_File_Instructions

So I created a VBA to try to validate these fields before unlocking the checkbox:

Private Sub ASR_Requested_BeforeUpdate(Cancel As Integer)

Dim A As [Form_New Transaction Form]

If IsNull(A.Requested_By) Then
 MsgBox "Please Enter Requested By", vbCritical, "Error Message"
 A.Requested_By.SetFocus
Else
If IsNull(A.Client_Sponsor) Then
 MsgBox "Please Enter Client Sponsor", vbCritical, "Error Message"
 A.Client_Sponsor.SetFocus
Else
If IsNull(A.Property_Loan_Name) Then
 MsgBox "Please Enter Property Loan Name", vbCritical, "Error Message"
 A.Property_Loan_Name.SetFocus
Else
If IsNull(A.Portfolio) Then
 MsgBox "Please Enter Portfolio", vbCritical, "Error Message"
 A.Portfolio.SetFocus
Else
If IsNull(A.Date_Requested) Then
  MsgBox "Please Enter Date Requested", vbCritical, "Error Message"
  A.Date_Requested.SetFocus
Else
If IsNull(A.File_Source) Then
 MsgBox "Please Enter File Source", vbCritical, "Error Message"
 A.File_Source.SetFocus
Else
If IsNull(Specific_File_Instructions) Then
 MsgBox "Please Enter Specific File Instructions", vbCritical, "Error Message"
 A.Specific_File_Instructions.SetFocus
Else
ASR_Requested.Locked = False
 
End If

End Sub

However, whenever I try to create a new record in the New Transaction Form, and only have one field entered, then try to click the checkbox. It gives a compile error:

"User defined type not defined"

Is there anyway you could help me with debugging this code? I appreciate any help.

Thank you,

 

[Andrzejek]

When you get this error, which line of your code is highlighted?

Just a guess here....:

Else
If IsNull([red]A.[/red]Specific_File_Instructions) Then
 MsgBox "Please Enter Specific File Instructions", vbCritical, "Error Message"
 A.Specific_File_Instructions.SetFocus
Else
ASR_Requested.Locked = False
 
End If

Have fun.

---- Andy

 

[PHV]

Replace this:
Dim A As [Form_New Transaction Form]
with this:
Dim A As Access.Form
Set A = Forms![New Transaction Form]


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Although a minor point, the most correct way to reference a main form from the subform is through the parent property. This ensures you are referncing the specific main form not another form instance with the same name.

dim A as access.form
set A = me.parent

 

[iuianj07]

Hello,

I tried replacing this:
Dim A As [Form_New Transaction Form]
with this:
Dim A As Access.Form
Set A = Forms![New Transaction Form]

And though it is giving the correct msgbox if the field is not entered. The problem though is that the checkbox still gets checked.

Is there a way that when for example Portfolio wasn't filled up, then when you try to check the checkbox, it'll give the Msgbox for portfolio THEN the checkbox WON'T be checked?

Thank you, I hope I wasn't confusing.

 

[dhookom]

Is your check box named "Locked"? Do you know this is also a property of check boxes?

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

@dhookom

I know that "Locked" is a property of checboxes.

The name of the Check box is "ASR_Requested