recordsource conditioned on multible parameter queries-action if no results returned

BabaYaga · April 29th, 2009, 09:30 AM

Hi
Been tearing my hair out on this one:
I have a search form with three controlled drop-downs in the header, one search button, one clear search button. The way I need to search is by using three different parameter queries populating the continous form showing results. Which queries are chosen is dependent on which comboboxes are selected. this all works fine, except, if any of the queries retuns no records, I want a text label in the detail of the form to become visible, saying that there were no results, or it could be a msgbox or something, am not that fussed. Here is my code:

Code:

Private Sub btnSearch_Click()
On Error GoTo Err_btnSearch_Click
'Dim dblRecCout As Double
    Me.Detail.Visible = True
    Me.lblNoResult.Visible = False
 
 
    If (((Nz(Me.Org.Value, 0)) <> 0) And ((Nz(Me.Cat1.Value, 0)) <> 0) And (Nz(Me.Cat2.Value, 0) <> 0)) Then
        Me.recordsource = "SELECT * FROM qrysearchCat1and2AllOrgNz"
    ElseIf (IsNull(Me.Cat1.Value) And IsNull(Me.Cat2.Value) And IsNull(Me.Org.Value)) Then
        Me.recordsource = "SELECT * FROM qrysearchCat1and2AllOrgNz"
    ElseIf (IsNull(Me.Cat1.Value) And IsNull(Me.Cat2.Value) And (Nz(Me.Org.Value, 0) <> 0)) Then
        Me.recordsource = "SELECT * FROM qrysearchCat1and2AllOrgNz"
    ElseIf (((Nz(Me.Cat2.Value, 0)) <> 0) And ((Nz(Me.Cat1.Value, 0)) <> 0) And IsNull(Me.Org.Value)) Then
        Me.recordsource = "SELECT * FROM qrysearchCat1and2AllOrgNz"
    ElseIf (IsNull(Me.Cat1.Value) And (Nz(Me.Cat2.Value, 0) <> 0) And IsNull(Me.Org.Value)) Then
        Me.recordsource = "SELECT * FROM qrysearchCat1or2andOrgNz"
    ElseIf ((Nz(Me.Cat1.Value, 0) <> 0) And IsNull(Me.Cat2.Value) And IsNull(Me.Org.Value)) Then
        Me.recordsource = "SELECT * FROM qrysearchCat1or2andOrgNz"
    ElseIf (IsNull(Me.Cat1.Value) And (Nz(Me.Cat2.Value, 0) <> 0) And (Nz(Me.Org.Value, 0) <> 0)) Then
        Me.recordsource = "SELECT * FROM qrysearchCat1or2Nz"
    ElseIf ((Nz(Me.Cat1.Value, 0) <> 0) And IsNull(Me.Cat2.Value) And (Nz(Me.Org.Value, 0) <> 0)) Then
        Me.recordsource = "SELECT * FROM qrysearchCat1or2Nz"
    End If
 
   ' dblRecCout = DCount(Me.txtOrgID, Me.recordsource)
 
   ' If dblRecCount = 0 Then
   '     Me.lblNoResult.Visible = True
   '  Else: Me.lblNoResult.Visible = False
   ' End If
 
    Me.Requery
    Me.Repaint
 
Exit_btnSearch_Click:
    Exit Sub
 
Err_btnSearch_Click:
    MsgBox  & Err.Description
    Resume Exit_btnSearch_Click
 
 
End Sub
 
Private Sub Command30_Click()
On Error GoTo Err_Command30_Click
 
    Me.Cat1 = Null
    Me.Cat2 = Null
    Me.Org = Null
    Me.Detail.Visible = False
    Me.Requery
    DoCmd.RepaintObject
 
Exit_Command30_Click:
    Exit Sub
Err_Command30_Click:
    MsgBox  & Err.Description
    Resume Exit_Command30_Click
 
End Sub

The bits behind apostrophes are what I've tried to do so far, but to no avail. Am still relatively new to vba-ing, so, am probably making some kind of fundamental conceptual mistake. Anyway, any help would be much appreciated. Thanx
B

mmcdonal · April 30th, 2009, 02:22 PM

This is an interesting problem. I think you were on the right track with trying to check the recordcount.

I have a similar issue with one of my apps, but I use one query, and build the string based on the values in the combo boxes, then use the WHERE clause in the DoCmd.OpenQuery command. Slightly different events here.

Since I use one query, and just pass parameters to it, I can do something like this:

sSQL = "Select * FROM MyTable WHERE ..."

Set rs = NEW ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, ...

If rs.EOF = True and rs.BOF = True Then 'no records
Me.Label.Visible = True
Exit Sub
Else
'return results
End If

Did any of that help?

BabaYaga · May 6th, 2009, 05:57 AM

Mhmm, yes it does. Thanx mmcdonal.
I have a feeling that the reason why the recordcount is not working is that I haven't created a record set - so that I basically ask it to count something which it doesn't know what it is, because the recordset is not 'visible' to the vba code but is only created after the query ran. Could this be the case?

The reason why I have three queries, is because I what a Cat1 AND Cat2 search if both of the catagory selector combo boxes <> null, but an Cat1 OR Cat2 = Cmbvalue search if only one of the category combos is selected, and this you can't put into one query as it's mutually exclusive. I also don't know enough about vba to get it to query on the fly, syntax's really confusing - otherwise I could get it is to populate a recordset using different queries depending on the combination of combo selections.

Anyhow, I was thinking that if I could create a recordset using my saved queries and then elsewhere in the code get it to be evaluted, this might do the trick, but I don't know how to do that. Seems to me that it should be in the after_update event of any of the combos. What do you think?
BY

mmcdonal · May 6th, 2009, 08:00 AM

I would do just what I did in the example, except you could put it in a public function, and then just pass a query string to it, and return a boolean. Like:

Code:

Public Function fCheck(sSQL As String)
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If rs.EOF = True And rs.BOF = True Then
    'No Records
    fCheck = False
Else
    'Records
    fCheck = True
End If
End Function

On a form, I created a button, and this is the code on the On Click event of the button:

Code:

Dim sSQL As String
Dim bCheck As Boolean
Dim sName As String
 
sName = "Sam"
 
bCheck = False
sSQL = "SELECT * FROM Table1 WHERE DataText = '" & sName & "'"
bCheck = fCheck(sSQL)
If bCheck = True Then
    MsgBox sName & " is found", vbInformation
Else
    MsgBox sName & " is not found", vbInformation
End If

This is looking up data in a table, that has a record with Sam. This code returns a message box that Sam was found. When I change the name to Dave, which is not in the data, it returns a message box that Dave was not found.

Did that help?

SerranoG · May 6th, 2009, 09:24 AM

I'm not sure the purpose of the Me.Requery and the Me.Repaint in your case. That's typically not necessary at all to get a label to disappear and reappear. Also, I am not a fan of ELSEIFs. They then to confuse logic because people tend to go all over the place with their ordering of true/false. Try this simplified version.

Note that your DCOUNT needed the name of the field in the query, not the textbox in the form. I assumed the source of txtOrgID is lngOrgID (number of type long, e.g. autonumber).

Code:

Private Sub btnSearch_Click()
 
    On Error GoTo Err_btnSearch_Click
 
    Dim dblRecCount As Double, strQuery as String
 
    If Nz(Me.Org, 0) = 0 Then
        If Nz(Me.Cat1, 0) = 0 Then
            If Nz(Me.Cat2, 0) = 0 Then
                strQuery = "qrysearchCat1and2AllOrgNz"
            Else
                strQuery = "qrysearchCat1or2andOrgNz"
            End If
        Else
            If Nz(Me.Cat2, 0) = 0 Then
                strQuery = "qrysearchCat1or2andOrgNz"            
            Else
                strQuery = "qrysearchCat1and2AllOrgNz"
            End If
        End If
    Else
        If Nz(Me.Cat1, 0) = 0 Then
            If Nz(Me.Cat2, 0) = 0 Then
                strQuery = "qrysearchCat1and2AllOrgNz"            
            Else
                strQuery = "qrysearchCat1or2Nz"
            End If
        Else
            If Nz(Me.Cat2, 0) = 0 Then
                strQuery = "qrysearchCat1or2Nz"            
            Else
                strQuery = "qrysearchCat1and2AllOrgNz"
            End If
        End If
    End If
 
    Me.RecordSource = "SELECT * FROM " & strQuery
 
    dblRecCount = Nz(DCount("[lngOrgID]", Me.Recordsource), 0)
    Me.lblNoResult.Visible = (dblRecCount = 0)
 
Exit_btnSearch_Click:
    Exit Sub
 
Err_btnSearch_Click:
    MsgBox Err.Description, vbCritical, "Error " & Err.Number
    Resume Exit_btnSearch_Click
 
End Sub

SerranoG · May 6th, 2009, 09:32 AM

Oops.. I made a minor correction that won't be visible in the e-mail you received previously. View it on here instead.

(That's the only drawback of e-mailed responses in web forums... mistakes can't be taken back from your IN boxes!)

BabaYaga · May 12th, 2009, 07:06 AM

Hey thanx for responding
The repaint, was supposed to get the continous form to update, requery seems to do this too, but it was a little slow sometimes, so I thought this may solve it. Maybe a bit overkill. Yes the source of the txtOrgID is the ID number field in the query, I thought because it is a text field (data type number), but true not very sensible naming. So changed it to lngOrgID.

Anyway, I tried your code and receive the following error:
Microsoft Jet database engine cannot find the input table or query "SELECT * FROM qyrsearchCat1or2andOrgNz". Make sure it exists and is spelled correctly.

This pops up on all different kind of search combinations - but the queries do exist and are spelled correctly, but its somehow failing to find them. I have no idea why this could be, do you?

Also, if you have a moment, I quite like to understand what you mean by:

Quote:

Also, I am not a fan of ELSEIFs. They then to confuse logic because people tend to go all over the place with their ordering of true/false.

Do you think this a personal preference or the difference between little programming experience and much more programming experience?

SerranoG · May 12th, 2009, 08:09 AM

I'm not crazy about the naming of the control either. Microsoft calls it a TEXTBOX on the form, but it can hold text, numbers, dates, etc. It really depends on the type of the control source in the table.

Quote:

Anyway, I tried your code and receive the following error:
Microsoft Jet database engine cannot find the input table or query "SELECT * FROM qyrsearchCat1or2andOrgNz". Make sure it exists and is spelled correctly.

You misspelled qyrsearchCat1or2andOrgNz on your post. You mean qrysearchCat1or2andOrgNz (swap the "R" and "Y"). Is that how you spelled it in your code as well?

I also misspelled in my comment. I meant TEND, not THEN. That is,

Quote:

Also, I am not a fan of ELSEIFs. They tend to confuse logic because people tend to go all over the place with their ordering of true/false.

It is my preference. I don't know if it has to do with experience or not. I just think that most people who write ELSEIF statements do so in a very confusing manner as far as logic. They're usually hard to follow.

It's much cleaner to keep it in this form:

Code:

If (statement is true) Then
    Do This.
Else
    Do That.
End If

If you have more than one thing to compare, this may take more lines, but is easier (logcially) to follow.

Code:

If (1st statement is true) Then
    If (2nd statement is true) Then
        Do This.
    Else
        Do That.
    End If
Else
    If (2nd statement is true) Then
        Do This.
    Else
        Do That.
    End If
End If

I don't like

Code:

If (1st statement is true) And (2nd statement is true) Then
    'Do This.
ElseIf (1st statement is true) And (2nd statement is false) Then
    'Do That.
ElseIf (1st statement is false) And (2nd statement is true) Then
    'Do This.
ElseIf (1st statement is false) And (2nd statement is false) Then
    'Do That.
End If

If you're wondering why that is confusing, remember that I'm typing it based on the logic of the first way, so it's making more sense. But generally, people type statements like above very "stream of conciousness" and often go all over the place with placement of the true/false logic. The way I wrote it first, guides your logic.

There are other techniques people use that I'm not fond of. It's preference. For example, the EXIT SUB statement is rare in my code except where there is error trapping.

BabaYaga · May 12th, 2009, 09:11 AM

I see, there practically is a silent 'and' between the first If ... then and the second if ... then line, that's what's been confusing me - but I can see your point ELSEIF is sort of backwards regrading logic flow. Thanx.

Quote:

You misspelled qyrsearchCat1or2andOrgNz on your post. You mean qrysearchCat1or2andOrgNz (swap the "R" and "Y"). Is that how you spelled it in your code as well?

Darn, my dyslexia got the better of me again, but I checked, I copied your code, so in the code it is spelled 'qrysearchCat1or2andOrgNz' and the underlying query is spelled like this, too.

SerranoG · May 13th, 2009, 09:49 AM

Quote:

Originally Posted by BabaYaga

... I checked, I copied your code, so in the code it is spelled 'qrysearchCat1or2andOrgNz' and the underlying query is spelled like this, too.

Hmmm... if all is spelled correctly, I'm at a loss as why Access cannot find the query without standing over your shoulder.