If Then Statement

N

neenmarie

I'm trying to use the following code with a button on a form to open a
report. If the user has not selected or filled in fields on the form, I'd
like a message box to remind them. Only if they've selected 1 item and
filled in 2 dates should the report open. My code appears to do nothing at
all. Pls help with my code. Here's my bad code:
*************************
Private Sub Command35_Click()
On Error GoTo Err_Command35_Click

Dim stDocName As String

If ([Team1] Is Null) Then
MsgBox "You must select a Team"
ElseIf ([BegDate] Is Null) Then
MsgBox "You must enter a Beginning Date"
ElseIf ([EndDate] Is Null) Then
MsgBox "You must enter an Ending Date"
End If

stDocName = "rptTeamActivity"
DoCmd.OpenReport stDocName, acPreview

Exit_Command35_Click:
Exit Sub

Err_Command35_Click:
MsgBox Err.Description
Resume Exit_Command35_Click
**********************************
 
A

Al Campagna

neenmarie,
It appears that your Null statements are incorrect...
If IsNull([Team1]) Then
MsgBox "You must select a Team"
ElseIf Is Null([BegDate]) Then
MsgBox "You must enter a Beginning Date"
ElseIf Is Null([EndDate]) Then
MsgBox "You must enter an Ending Date"
End If
Click to expand...
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

neenmarie said:
I'm trying to use the following code with a button on a form to open a
report. If the user has not selected or filled in fields on the form, I'd
like a message box to remind them. Only if they've selected 1 item and
filled in 2 dates should the report open. My code appears to do nothing at
all. Pls help with my code. Here's my bad code:
*************************
Private Sub Command35_Click()
On Error GoTo Err_Command35_Click

Dim stDocName As String

If ([Team1] Is Null) Then
MsgBox "You must select a Team"
ElseIf ([BegDate] Is Null) Then
MsgBox "You must enter a Beginning Date"
ElseIf ([EndDate] Is Null) Then
MsgBox "You must enter an Ending Date"
End If

stDocName = "rptTeamActivity"
DoCmd.OpenReport stDocName, acPreview

Exit_Command35_Click:
Exit Sub

Err_Command35_Click:
MsgBox Err.Description
Resume Exit_Command35_Click
**********************************
Click to expand...
 
M

Mac

Also see the posting below yours. I was having a similar problem related to
using IsNUll. But my problem was related to the fact that IsNull does not
check for all conditions where the text box is empty. The text box could be
an empty string and the IsNull will not detect it. I found a nice function
that checks for IsNull AND string length and returns a 0 if either are true.
Here it is:

Function StrLen(AVariant) As Integer
' Returns the length of a variant or string, treating Null as a
zero-length string.
If IsNull(AVariant) Then
StrLen = 0
Else
StrLen = Len(AVariant)
End If
End Function
 
N

neenmarie

Thank you for the response. I changed the code as suggested. But, it still
just opens the report with blank data instead of showing any message boxes.
Here's the revised code:
******************************
Private Sub Command35_Click()
On Error GoTo Err_Command35_Click

Dim stDocName As String
stDocName = "rptTeamActivity"

If IsNull([Team1]) Then
MsgBox "You must select a Team"
ElseIf IsNull([BegDate]) Then
MsgBox "You must enter a Beginning Date"
ElseIf IsNull([EndDate]) Then
MsgBox "You must enter an Ending Date"
Else: DoCmd.OpenReport stDocName, acPreview
Exit_Command35_Click:
Exit Sub

Err_Command35_Click:
MsgBox Err.Description
Resume Exit_Command35_Click

End If

End Sub
**********************************

Al Campagna said:
neenmarie,
It appears that your Null statements are incorrect...
If IsNull([Team1]) Then
MsgBox "You must select a Team"
ElseIf Is Null([BegDate]) Then
MsgBox "You must enter a Beginning Date"
ElseIf Is Null([EndDate]) Then
MsgBox "You must enter an Ending Date"
End If
Click to expand...
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

neenmarie said:
I'm trying to use the following code with a button on a form to open a
report. If the user has not selected or filled in fields on the form, I'd
like a message box to remind them. Only if they've selected 1 item and
filled in 2 dates should the report open. My code appears to do nothing at
all. Pls help with my code. Here's my bad code:
*************************
Private Sub Command35_Click()
On Error GoTo Err_Command35_Click

Dim stDocName As String

If ([Team1] Is Null) Then
MsgBox "You must select a Team"
ElseIf ([BegDate] Is Null) Then
MsgBox "You must enter a Beginning Date"
ElseIf ([EndDate] Is Null) Then
MsgBox "You must enter an Ending Date"
End If

stDocName = "rptTeamActivity"
DoCmd.OpenReport stDocName, acPreview

Exit_Command35_Click:
Exit Sub

Err_Command35_Click:
MsgBox Err.Description
Resume Exit_Command35_Click
**********************************
Click to expand...
Click to expand...
 
A

Al Campagna

neenmarie,
For now, let's skip the Error coding, and just stick with the basic code to check the
fields and open the form.
Private Sub Command35_Click()
Dim stDocName As String
stDocName = "rptTeamActivity"
If IsNull([Team1]) Then
MsgBox "You must select a Team"
Exit Sub
ElseIf IsNull([BegDate]) Then
MsgBox "You must enter a Beginning Date"
Exit Sub
ElseIf IsNull([EndDate]) Then
MsgBox "You must enter an Ending Date"
Exit Sub
Else
DoCmd.OpenReport stDocName, acPreview
End If
End Sub

If any of the fields is null, the Sub Exits, and let's you correct those values, and
you can then re-Click the button to try again.. Only when all 3 fields are legitimate,
will the report open.
If all 3 fields contain "legitimate" values (that WILL return record/s), and the
report opens blank... then the problem would be in the report.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


neenmarie said:
Thank you for the response. I changed the code as suggested. But, it still
just opens the report with blank data instead of showing any message boxes.
Here's the revised code:
******************************
Private Sub Command35_Click()
On Error GoTo Err_Command35_Click

Dim stDocName As String
stDocName = "rptTeamActivity"

If IsNull([Team1]) Then
MsgBox "You must select a Team"
ElseIf IsNull([BegDate]) Then
MsgBox "You must enter a Beginning Date"
ElseIf IsNull([EndDate]) Then
MsgBox "You must enter an Ending Date"
Else: DoCmd.OpenReport stDocName, acPreview
Exit_Command35_Click:
Exit Sub

Err_Command35_Click:
MsgBox Err.Description
Resume Exit_Command35_Click

End If

End Sub
**********************************

Al Campagna said:
neenmarie,
It appears that your Null statements are incorrect...
If IsNull([Team1]) Then
MsgBox "You must select a Team"
ElseIf Is Null([BegDate]) Then
MsgBox "You must enter a Beginning Date"
ElseIf Is Null([EndDate]) Then
MsgBox "You must enter an Ending Date"
End If
Click to expand...
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

neenmarie said:
I'm trying to use the following code with a button on a form to open a
report. If the user has not selected or filled in fields on the form, I'd
like a message box to remind them. Only if they've selected 1 item and
filled in 2 dates should the report open. My code appears to do nothing at
all. Pls help with my code. Here's my bad code:
*************************
Private Sub Command35_Click()
On Error GoTo Err_Command35_Click

Dim stDocName As String

If ([Team1] Is Null) Then
MsgBox "You must select a Team"
ElseIf ([BegDate] Is Null) Then
MsgBox "You must enter a Beginning Date"
ElseIf ([EndDate] Is Null) Then
MsgBox "You must enter an Ending Date"
End If

stDocName = "rptTeamActivity"
DoCmd.OpenReport stDocName, acPreview

Exit_Command35_Click:
Exit Sub

Err_Command35_Click:
MsgBox Err.Description
Resume Exit_Command35_Click
**********************************
Click to expand...
Click to expand...
Click to expand...
 
N

neenmarie

Thank you so much.
I copied your code with the 'exit sub' and it's working now.
Thanks again

Al Campagna said:
neenmarie,
For now, let's skip the Error coding, and just stick with the basic code to check the
fields and open the form.
Private Sub Command35_Click()
Dim stDocName As String
stDocName = "rptTeamActivity"
If IsNull([Team1]) Then
MsgBox "You must select a Team"
Exit Sub
ElseIf IsNull([BegDate]) Then
MsgBox "You must enter a Beginning Date"
Exit Sub
ElseIf IsNull([EndDate]) Then
MsgBox "You must enter an Ending Date"
Exit Sub
Else
DoCmd.OpenReport stDocName, acPreview
End If
End Sub

If any of the fields is null, the Sub Exits, and let's you correct those values, and
you can then re-Click the button to try again.. Only when all 3 fields are legitimate,
will the report open.
If all 3 fields contain "legitimate" values (that WILL return record/s), and the
report opens blank... then the problem would be in the report.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


neenmarie said:
Thank you for the response. I changed the code as suggested. But, it still
just opens the report with blank data instead of showing any message boxes.
Here's the revised code:
******************************
Private Sub Command35_Click()
On Error GoTo Err_Command35_Click

Dim stDocName As String
stDocName = "rptTeamActivity"

If IsNull([Team1]) Then
MsgBox "You must select a Team"
ElseIf IsNull([BegDate]) Then
MsgBox "You must enter a Beginning Date"
ElseIf IsNull([EndDate]) Then
MsgBox "You must enter an Ending Date"
Else: DoCmd.OpenReport stDocName, acPreview
Exit_Command35_Click:
Exit Sub

Err_Command35_Click:
MsgBox Err.Description
Resume Exit_Command35_Click

End If

End Sub
**********************************

Al Campagna said:
neenmarie,
It appears that your Null statements are incorrect...
If IsNull([Team1]) Then
MsgBox "You must select a Team"
ElseIf Is Null([BegDate]) Then
MsgBox "You must enter a Beginning Date"
ElseIf Is Null([EndDate]) Then
MsgBox "You must enter an Ending Date"
End If
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I'm trying to use the following code with a button on a form to open a
report. If the user has not selected or filled in fields on the form, I'd
like a message box to remind them. Only if they've selected 1 item and
filled in 2 dates should the report open. My code appears to do nothing at
all. Pls help with my code. Here's my bad code:
*************************
Private Sub Command35_Click()
On Error GoTo Err_Command35_Click

Dim stDocName As String

If ([Team1] Is Null) Then
MsgBox "You must select a Team"
ElseIf ([BegDate] Is Null) Then
MsgBox "You must enter a Beginning Date"
ElseIf ([EndDate] Is Null) Then
MsgBox "You must enter an Ending Date"
End If

stDocName = "rptTeamActivity"
DoCmd.OpenReport stDocName, acPreview

Exit_Command35_Click:
Exit Sub

Err_Command35_Click:
MsgBox Err.Description
Resume Exit_Command35_Click
**********************************
Click to expand...
Click to expand...
Click to expand...
 
M

Mac

I tried your error checking code and it is Ok ONLY if your text control is
NULL. It is definately that your text control is an "" empty string and the
IsNull does not detect anything wrong (your code is lacking error checking
content). Try this (be sure to include the StrLen Function (below) in your
code):

Private Sub Command35_Click()
If StrLen([Team1]) = 0 Then
MsgBox "You must select a Team"
Exit Sub
ElseIf StrLen([BegDate]) = 0 Then
MsgBox "You must enter a Beginning Date"
Exit Sub
ElseIf StrLen([EndDate]) = 0 Then
MsgBox "You must enter an Ending Date"
Exit Sub
Else
'Open your form here
End If
End Sub
______________________________________________

Function StrLen(AVariant) As Integer
' Returns the length of a variant or string, treating Null as a
zero-length string.
If IsNull(AVariant) Then
StrLen = 0
Else
StrLen = Len(AVariant)
End If
End Function
 
N

neenmarie

Thank you for your help. My code is slowly getting better.

Mac said:
I tried your error checking code and it is Ok ONLY if your text control is
NULL. It is definately that your text control is an "" empty string and the
IsNull does not detect anything wrong (your code is lacking error checking
content). Try this (be sure to include the StrLen Function (below) in your
code):

Private Sub Command35_Click()
If StrLen([Team1]) = 0 Then
MsgBox "You must select a Team"
Exit Sub
ElseIf StrLen([BegDate]) = 0 Then
MsgBox "You must enter a Beginning Date"
Exit Sub
ElseIf StrLen([EndDate]) = 0 Then
MsgBox "You must enter an Ending Date"
Exit Sub
Else
'Open your form here
End If
End Sub
______________________________________________

Function StrLen(AVariant) As Integer
' Returns the length of a variant or string, treating Null as a
zero-length string.
If IsNull(AVariant) Then
StrLen = 0
Else
StrLen = Len(AVariant)
End If
End Function






--
Regards, Michael


neenmarie said:
I'm trying to use the following code with a button on a form to open a
report. If the user has not selected or filled in fields on the form, I'd
like a message box to remind them. Only if they've selected 1 item and
filled in 2 dates should the report open. My code appears to do nothing at
all. Pls help with my code. Here's my bad code:
*************************
Private Sub Command35_Click()
On Error GoTo Err_Command35_Click

Dim stDocName As String

If ([Team1] Is Null) Then
MsgBox "You must select a Team"
ElseIf ([BegDate] Is Null) Then
MsgBox "You must enter a Beginning Date"
ElseIf ([EndDate] Is Null) Then
MsgBox "You must enter an Ending Date"
End If

stDocName = "rptTeamActivity"
DoCmd.OpenReport stDocName, acPreview

Exit_Command35_Click:
Exit Sub

Err_Command35_Click:
MsgBox Err.Description
Resume Exit_Command35_Click
**********************************
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

Code will work on one table and form but not on another? 19
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
E-mail Code 2
OutputTo 3
Prompt For Replace in Selection And More... 0
Remove Identical words 0
If Statements 4
Validate/Verify entry in MsgBox 5

Top