How to control the inputs created into a form using VBA

[Ricardo Caicedo]

Good morning

I am fairly newbie in Access VBA, and I cannot figure out the best way to validate a form. Let me explain: I got the following piece of code with an input form named as “frmEnterData” containing a list box named as “ListMketer,” a text box named as “HEC” and finally other list box named as “MonthBR”. These are input from my program.
When I call in debug mode the form, I can enter the values one by one; however in run mode the error:94 “invalid use of null” happened as the code does not wait until I enter the data. I tried to use the beforeUpdate event and the enter event in the form but it does not work. So I try to use a do while statement and error handler but fails as the program stays forever in the loop without leave the user post the options.
I need a way that the code stops, accept & validate the inputs and then continue into the program.

--------------------portion of my code
Sub Enterdata()



Dim Mketer As String, MonthBR As String
Dim HECAmount As Currency,

'Enter the HEC $ amoun billed , Marketer Group and month
DoCmd.OpenForm "frmEnterdata"

'Pass variables from the entryForm
Mketer = Forms("frmEnterdata")!ListMketer
HECAmount = Forms("frmEnterdata")!HEC
MonthBR = Forms("frmEnterdata")!MonthBR
MonthNum = Month(CDate(MonthBR & " 1," & Year(Date)))
'…
'…(MORE CODE)

End Sub

[JamesDeckert]

What kind of validation do you need to do? You can do some validation at the table level and not have to write any code (e.g. required field)

[Ricardo Caicedo]

Thank you James. I need to validate:

-both list box contains some selection from the user, so never be null. When the code runs, it does not stop in the form, so I guess the code assumes that the values are there.
-the text box was set as currency so the validation is be bigger than $0.0


Thank you

[June7]

Where's the code that does the validating? Why is procedure running - what triggers it?

[Ricardo Caicedo]

in the form "frmEnterData" I create the following code for the event Form_BeforeUpdate

Private Sub Form_BeforeUpdate(Cancel As Integer)

'Check that required fields are filled out
Dim strErrMsg As String
Dim strProjectName As String

strProjectName = "Municipalities HEC allocations for Franchise Fee & Property Tax"
If IsNull(Forms("frmEnterdata")!ListMketer) Then
strErrMsg = "A Marketer selection is requiered" & vbNewLine
End If

If (Forms("frmEnterdata")!HEC) <= 0 Then
strErrMsg = "Amount should be bigger than zero" & vbNewLine
End If

If IsNull(Forms("frmEnterdata")!MonthBR) Then
strErrMsg = strErrMsg & "A selection is required for the month of the Burn Report" & vbNewLine
End If


If strErrMsg & "" = "" Then
'No errors
Cancel = False
Else
'Errors found
Cancel = True
strErrMsg = strProjectName & vbNewLine & " could not save your record at this time for the following reasons:" & _
vbNewLine & vbNewLine & _
strErrMsg & _
vbNewLine & vbNewLine & _
"Please correct and try again"
MsgBox strErrMsg, vbOKOnly + vbInformation, strProjectName
End If

End Sub

However, never trigger this validation....

[June7]

Form BeforeUpdate triggers when attempt to commit record to table. Record is committed when close form, move to another record, or run code to save.

[Ricardo Caicedo]

thanks for your answer, however can you explain how I can keep the program asking correct input.....sorry I am new in this...

[June7]

I do not understand why this code is running when it shouldn't.

If you want to provide db for analysis, follow instructions at bottom of my post.

[JamesDeckert]

If you want to validate at the table level and not write code, you go into design view for the table and select the field. Look at the General tab for the properties Validation Rule, Validation Text, and Required. If you want to make sure that a field is not left blank, make it required. If you want a field greater than or equal to zero set Validation Rule to >=0 and put the error prompt into the Validation Text (e.g. Currency must be a positive number)

[Ricardo Caicedo]

Thank you James it is working now