Checking for null fields before running query

[Juan Correa]

MS Access 2003
Here's my scenario:

Form --> frm_report
In that form: 3 cbo boxes, 2 buttons, 12 txt fields.

the cboboxes provide parameters for a query, and also criteria for 12 DSum
expressions that are applied to each of the 12 text boxes respectively.
The 12 text boxes display the total revenue for each month based on the
selections from the 3 combo boxes (area, year, service).
This all works perfectly as I have it.

The two buttons that I have do the following:
1. Display the detailed data related to the figures shown on the 12 text
boxes --> This button runs the parameter query based on the selections chosen
on the three combo boxes and displays it on Access.
2. Exports the dtailed data related to the summary figures. Same as above
button but instead of displaying the results of the query, it sends it to
excel.
This all works fine as well.
And now my question:
All I want is to prevent users from sending "Null" parameters to the query
when clicking on the buttons (so as to prevent the display of a blank query,
or the export of a blank spreadsheet).
How can achieve this? Can I set it up so that a user gets a warning messge
letting them know that all three fields must be populated before the buttons
can be used?

thanks
Juan Correa

 

[Klatuu]

That would be a good way to do it provided you can be sure the query will
return at least one record when all 3 controls have values.
(Note the word Controls. Forms do not have fields, they have controls.
Tables and queries have fields)

I would suggest you disable the command buttons until there is a vaule in
all three controls. You can do that by setting the Enabled property of the
buttons to No in design view. Then create a private sub in your form module
that will test for the correct conditiont. Call the sub in the After Update
event of each combo:

Private Sub CheckTheCombs()
If IsNull(Me.Combo1) Or IsNull(Comb2) Or IsNull(Combo3) Then
Me.Command1.Enabled = False
Me.Command2.Enabled = False
Else
Me.Command1.Enabled = True
Me.Command2.Enabled = True
End If
End Sub

 

[Juan Correa]

Thanks for the help Klatuu....
Disabling the buttons is a nice way of achieving what I need. Having said
that, I like the "Warning Message" way better. I'll explain why.... This
tool that I'm building is primarily for my own use right now, but at some
point... my boss(es) might want to take a look at it, so I want it to be
"Dummy" proof. Trust me... I've had people complain to me that I did not
send them the complete data they needed, simply because I sent it to them in
"Subtotals" form... they didn't even think of expanding the subtotals before
crying about the lack of detail. So having two disabled buttons on a form,
will surely wreack havock with them. I'd rather have them hit the button and
get a nice big window telling them what they did wrong.
Ok... so now on to the programming part.
I could use that sub and modify it so that it checks for Null in the
controls and fires up a warning if conditions are true, or lets the query run
if conditions are false. Right?

Thansk again.
Juan Correa

 

[Klatuu]

That is correct.

 

[Juan Correa]

I figured it out. Don't know if this is the most efficient way, but this is
how I did it.

1. Created a macro (mcr_warning) that launches my warning box.
2. Added the following code to the button's onClick() code... right after
the first line of code for the button and before it runs the maint button
function.

If IsNull(Me.cbo_area) Or IsNull(Me.cbo_year) Or IsNull(Me.cbo_activity)
Then
Dim stDocWarning As String
stDocWarning = "mcr_warning"
DoCmd.RunMacro stDocWarning

That did it.
Thanks again Klatuu.

 

[Klatuu]

Why involve a macro?
If IsNull(Me.cbo_area) Or IsNull(Me.cbo_year) Or IsNull(Me.cbo_activity)
Then
MsgBox "One of the combos has no data"
End if

 

[Juan Correa]

Well.. that is a lot better than my approach. I've changed my code to your
suggestion.

Thanks again.

 

[Klatuu]

Good, the more proficient you get with VBA, the less you will rely on Macros.
Not to say that macros have no value, but with VBA you have more control and
better error handling.
Good Luck to you, Juan