If Then Else Syntax

[Guest]

Hi all,
Here is what I try to do:
I have three fields in a form, 2 of them are text boxes and the third is a
ComboBox. I was trying to write a code that kicks in on form close that
checks if any of these 3 fields is blank, then pops-up a msg box in this
case. I know there is something wrong with my code. Can someone please help.
The code I wrote is the following:-

Dim msg As Integer
Dim StrMessage As String
Dim StrTittle As String
StrMessage = "You haven't entered a required field. This is not a
recommended practice. Please enter all required fields"
StrTittle = "Required Field Missing"

If Me.TenantName Is Null or Me.From_Date Is Null Or Me.End_Date Is Null Then
msg = MsgBox(StrMessage, vbOKOnly, StrTittle)
DoCmd.Close
Else
DoCmd.Close

End If

 

[Graham Mandeno]

Hi TS

"Is Null" is a contruction that is valid only for the SQL language, not VBA.

Instead you should use the IsNull( ) function:

If IsNull(Me.TenantName) or IsNull(Me.From_Date) Or IsNull(Me.End_Date) Then

I think you also want to remove the first DoCmd.Close, otherwise you will
get the message and then the form will close anyway.

 

[John Vinson]

I have three fields in a form, 2 of them are text boxes and the third is a
ComboBox. I was trying to write a code that kicks in on form close that
checks if any of these 3 fields is blank, then pops-up a msg box in this
case. I know there is something wrong with my code. Can someone please help.
The code I wrote is the following:-

The Is Null syntax is valid for SQL statements, but not VBA code.
There is an IsNull() *function* which takes its place - but if you're
checking a textbox, bear in mind that the user might have typed
something in, and backspaced over it. This leaves the control
containing a zero length string which is *not* the same as Null.

Try

If Me.TenantName & "" = "" or Me.From_Date & "" = "" Or Me.End_Date &
"" = "" Then


John W. Vinson[MVP]

 

[Guest]

I am just learning about this sort of thing, and don't wish to contradict the
MVPs, from both of whom I have learned much, but if it was me I think I would
put the code into the form's Before Update event. After the message box line
of code I would set the focus to somewhere on the form, then Cancel = True;
and I would not close the form in the Before Update code. Also, I'm not sure
what you gain by Dim msg As Integer. The message box line of code could
simply be (vbOKOnly is the default):
MsgBox StrMessage, , StrTittle
If I am incorrect in my assumption about Dim msg As Integer (or anything
else I have written here) I would be interested in learning more.

 

[Guest]

Declaring a variable as integer and use it to generate a msgbox is the way I
used to if I want to generate a msgbox. I put the code in an event that opens
another from. Thank you all for your great help with the IsNull() function.

 

[Guest]

I have look at other data base templets and was able to peace this together
and it worked form me
****************************************************
step 1: open your form in design view.
step 2: create a command button that preforms the next step you want.
step 3: open the property form the command button.
step 4: on the event tab, on click, click on the code maker
step 5: insert somthing similar to the following:

Private Sub search_Click()
On Error GoTo Err_Command13_Click
If IsNull([field1]) or IsNull([field2]) or IsNull([field3]) Then
MsgBox "what you want your message box to say."
DoCmd.GoToControl "field1"
Else
DoCmd.Close
End If
Exit_Command13_Click:
Exit Sub

Err_Command13_Click:
MsgBox Err.Description
Resume Exit_Command13_Click

End Sub
*********************************************
change the thing that you need ie the name of the command button "search" to
your key name, IsNull(["field1"])... to IsNull(["your own field names"]), and
what you want your error message to say.

if this doesn't work correct i would look at some other codes in templet
data bases and you should be able to figure it out "contact manegment" is the
data base that i utilized to figure my syntax out.