MsgBox when query doesn't open

[Burton]

I probably am going about this wrong, but I think it will be the easiest way
if it is possible. I have a form with numerous combo boxes, list boxes, and
text boxes. It functions as a query for my table. Users can come in and
select parameters and it spits out a query and then that query is fed into a
report which is actually what shows up when you hit the "Execute Query"
button.

I am now trying to create Error messages based on a few different situations.
(1) If a user doesn't select anything on the form and then tries to press
"execute query", a message box will appear that says "Please select query
parameters".
(2) If a user selects "execute query" and the parameters they selected don't
return any results, I want the message box to say "No employees met these
parameters."
(3) If a user fails to place operators in between selections and then
selects "Execute Query" I want the message box to come up and say "Please
make sure all parameters are accompanied by appropriate operator (and/or)"

So I have gotten it so that the first message box works, but the second one
and third ones aren't entirely working. Sometimes the "No employees met these
parameters" pops up when the "Please make sure all parameters are accompanied
by appropriate operator (and/or)" should have popped up. I am really trying
to determine if there is a way to write a statement in VB that says "When MY
QUERY isn't created, then return the open message box "Please make sure all
parameters are accompanied by appropriate operator (and/or)". Otherwise, open
the message box saying "No employees met these parameters."

My logic is that if the query is created, it must have all of the operators
it needs, and so if the record count is 0 then is just meant that there were
no records that met the parameters. However, if the query isn't created at
all, it means that there was an operator problem and therefore the 3rd
message should apply.

Here is the code I am using right now that is occasionally causing message 2
to appear when message 3 should:

On Error Resume Next
Err.Clear
CurrentDb.CreateQueryDef "junk", "select * from staffing_matrix where "
& fields
If Err.Description = "" Then
If DCount("*", "junk") = 0 Then
MsgBox ("No Employees Meet These Parameters")
Else
Dim stDocName As String
stDocName = "Query Report"
DoCmd.OpenReport stDocName, acPreview
End If
Else
If Text74.Value = "" Then
MsgBox ("Please enter query paramaters")
Else
MsgBox ("Missing Operator(s) in Query Expression. Make sure that all
selected values" & vbNewLine & "have corresponding operators (and internal
operators where necessary)")
End If
End If
On Error GoTo 0

 

[Duane Hookom]

I'm not sure why you don't trap user errors when building the 'fields' value.
I would also be a little concerned about using 'fields' as a memory variable
name. I assume this is a WHERE CLAUSE so I would name the variable 'strWhere'
or similar.

 

[Duane Hookom]

The first item on my todo would be to update all of your control and variable
names so they were maintainable. IMO, Text74 and List132 are not acceptable
in any application.

Then, I would try to get rid of redundant code. For instance you can add a
public function to a general module to handle all of your multi-select list
boxes.

Function BuildIn(lboListBox As ListBox, _
strFieldName As String, strDelim As String) As String
'send in a list box control object
' strFieldName might be "EmployeeID"
' strDelim is "" for numbers, """" for text, or "#" for dates
Dim strIn As String
Dim varItem As Variant
If lboListBox.ItemsSelected.Count > 0 Then
strIn = " AND " & strFieldName & " In ("
For Each varItem In lboListBox.ItemsSelected
strIn = strIn & strDelim & lboListBox.ItemData(varItem) &
strDelim & ", "
Next
'remove the last ", " and add the ")"
strIn = Left(strIn, Len(strIn) - 2) & ") "
End If
BuildIn = strIn
End Function

--
Duane Hookom
Microsoft Access MVP

Duane,

I am quite a novice at writing code, but have put together a fairly complex
form just piecing things together. I know that I have not done things the
easiest or best way, just the way I could figure it out. I avoided writing
all of the numerous different ways that an error could occur, because I felt
it would be too complex and take a very long time. In my mind, it makes sense
to just say, "If the query isn't created, then open this message box". That
seems very simple and straight forward to me. If you know how to write this
simple code, I think it would work. However, going through and saying, if
this textbox has text in it and it doesn't have an operator between it and
this combo box has more than one thing selected and doesn't have operators in
between them, then return an error.

Here is my code for the "Run Query" button: Don't laugh at how ugly it is!

Private Sub Command2_Click()

On Error Resume Next
DoCmd.Close acReport, "Query Report", acSaveNo
CurrentProject.Connection.Execute ("drop view junk")
CurrentProject.Connection.Execute ("drop view Query Report")


If Text60.Value <> "" Then
If (fields <> "") Then
fields = fields & " and "
End If
If Frame62.Value = False Then
fields = fields & "[Years] = " & Text60.Value & ""
Else
If Frame62.Value = 1 Then
fields = fields & "[Years] >= " & Text60.Value & ""
Else
fields = fields & "[Years] <= " & Text60.Value & ""
End If
End If
End If


Dim counterC
Dim tempstringC
counterC = 0
For Each varItem In List54.ItemsSelected
If counterC = 0 Then
tempstringC = tempstringC & "Level='" & List54.ItemData(varItem)
& "'"
Else
tempstringC = tempstringC & "or Level='" &
List54.ItemData(varItem) & "'"
End If
counterC = counterC + 1
Next varItem

If tempstringC <> "" Then
If fields <> "" Then
fields = fields & Combo181.Value & " ( " & tempstringC & " )"
Else
fields = fields & " ( " & tempstringC & " )"
End If
End If

Combo0.SetFocus
If Combo0.Text <> "" Then
If (fields <> "") Then
fields = fields & Combo171.Value
End If
fields = fields & "[" & Combo0.Text & "]=-1"
End If


If Combo25.Value <> "" Then
If (fields <> "") Then
fields = fields & Combo175.Value
End If
fields = fields & "[Clearance]='" & Combo25.Value & "'"
End If

If Check169.Value = -1 Then
fields = fields & Combo203.Value & "[" & "DHS Suitability" & "]=-1"
End If

Dim counterB
Dim tempstringB
counterB = 0
For Each varItem In List30.ItemsSelected
If counterB = 0 Then
tempstringB = tempstringB & "City_O='" &
List30.ItemData(varItem) & "'"
Else
tempstringB = tempstringB & "or City_O='" &
List30.ItemData(varItem) & "'"
End If
counterB = counterB + 1
Next varItem

If tempstringB <> "" Then
If fields <> "" Then
fields = fields & Combo177.Value & " ( " & tempstringB & " )"
Else
fields = fields & " ( " & tempstringB & " )"
End If
End If


Dim counter
Dim tempstring
counter = 0
For Each varItem In List28.ItemsSelected
If counter = 0 Then
tempstring = tempstring & "City='" & List28.ItemData(varItem) &
"'"
Else
tempstring = tempstring & "or City='" & List28.ItemData(varItem)
& "'"
End If
counter = counter + 1
Next varItem

If tempstring <> "" Then
If fields <> "" Then
fields = fields & Combo179.Value & " ( " & tempstring & " )"
Else
fields = fields & " ( " & tempstring & " )"
End If
End If


Dim counterS
Dim tempstringS
counterS = 0
For Each varItem In List3.ItemsSelected
If counterS = 0 Then
tempstringS = tempstringS & "[" & List3.ItemData(varItem) & "]=-1"
Else
tempstringS = tempstringS & Combo36.Value & "[" &
List3.ItemData(varItem) & "]=-1"
End If
counterS = counterS + 1
Next varItem

If tempstringS <> "" Then
If fields <> "" Then
fields = fields & Combo185.Value & " ( " & tempstringS & " )"
Else
fields = fields & " ( " & tempstring & " )"
End If
End If


Dim counterU
Dim tempstringU
counterU = 0
For Each varItem In List5.ItemsSelected
If counterU = 0 Then
tempstringU = tempstringU & "[" & List5.ItemData(varItem) & "]=-1"
Else
tempstringU = tempstringU & Combo38.Value & "[" &
List5.ItemData(varItem) & "]=-1"
End If
counterU = counterU + 1
Next varItem

If tempstringU <> "" Then
If fields <> "" Then
fields = fields & Combo189.Value & " ( " & tempstringU & " )"
Else
fields = fields & " ( " & tempstringU & " )"
End If
End If

Dim counterV
Dim tempstringV
counterV = 0
For Each varItem In List10.ItemsSelected
If counterV = 0 Then
tempstringV = tempstringV & "[" & List10.ItemData(varItem) &
"]=-1"
Else
tempstringV = tempstringV & Combo40.Value & "[" &
List10.ItemData(varItem) & "]=-1"
End If
counterV = counterV + 1
Next varItem

If tempstringV <> "" Then
If fields <> "" Then
fields = fields & Combo191.Value & " (" & tempstringV & ") "
Else
fields = fields & " ( " & tempstringV & " )"
End If
End If

Dim counterW
Dim tempstringW
counterW = 0
For Each varItem In List20.ItemsSelected
If counterW = 0 Then
tempstringW = tempstringW & "[" & List20.ItemData(varItem) &
"]=-1"
Else
tempstringW = tempstringW & Combo46.Value & "[" &
List20.ItemData(varItem) & "]=-1"
End If
counterW = counterW + 1
Next varItem

If tempstringW <> "" Then
If fields <> "" Then
fields = fields & Combo193.Value & " ( " & tempstringW & " )"
Else
fields = fields & " ( " & tempstringW & " )"
End If
End If


Dim counterX
Dim tempstringX
counterX = 0
For Each varItem In List17.ItemsSelected
If counterX = 0 Then
tempstringX = tempstringX & "[" & List17.ItemData(varItem) &
"]=-1"
Else
tempstringX = tempstringX & Combo44.Value & "[" &
List17.ItemData(varItem) & "]=-1"
End If
counterX = counterX + 1
Next varItem

If tempstringX <> "" Then
If fields <> "" Then
fields = fields & Combo199.Value & "(" & tempstringX & ")"
Else
fields = fields & " ( " & tempstringX & " )"
End If
End If


Dim counterT
Dim tempstringT
counterT = 0
For Each varItem In List14.ItemsSelected
If counterT = 0 Then
tempstringT = tempstringT & "[" & List14.ItemData(varItem) &
"]=-1"
Else
tempstringT = tempstringT & Combo42.Value & "[" &
List14.ItemData(varItem) & "]=-1"
End If
counterT = counterT + 1
Next varItem

If tempstringT <> "" Then
If fields <> "" Then
fields = fields & Combo187.Value & " ( " & tempstringT & " )"
Else
fields = fields & " ( " & tempstringT & " )"
End If
End If


Dim counterR
Dim tempstringR
counterR = 0
For Each varItem In List132.ItemsSelected
If counterR = 0 Then
tempstringR = tempstringR & "[" & List132.ItemData(varItem) &
"]=-1"
Else
tempstringR = tempstringR & Combo134.Value & "[" &
List132.ItemData(varItem) & "]=-1"
End If
counterR = counterR + 1
Next varItem

If tempstringR <> "" Then
If fields <> "" Then
fields = fields & Combo183.Value & " ( " & tempstringR & " )"
Else
fields = fields & " ( " & tempstringR & " )"
End If
End If

MsgBox fields

On Error Resume Next
Err.Clear
CurrentDb.CreateQueryDef "junk", "select * from staffing_matrix where "
& fields
If Err.Description = "" Then
If DCount("*", "junk") = 0 Then
MsgBox ("No Employees Meet These Parameters")
Else
Dim stDocName As String
stDocName = "Query Report"
DoCmd.OpenReport stDocName, acPreview
End If
Else
If Text74.Value = "" Then