Full Version: Sort records on a form
UtterAccess Forums > Microsoft® Access > Access Forms
k9huey
I just can not figure out how to make this happen. I found two fully working examples that are VERY similar to what I am trying to do and I have copied what they did but I can not get mine to work.
I have a form that I want to be able to sort a list of data using three combo boxes.
I use this Select statement as the record source for the form.
SELECT qryMachine.Machine, qryMachine.MachineOwner, qryMachine.CompanyName, qryMachine.City, qryMachine.State, qryMachine.Division, qryMachine.CustStatus FROM qryMachine WHERE 1=1;
I have this code in the form.
CODE
Option Compare Database
Option Explicit
'Set the default record source of form
Const strSQL = "SELECT qryMachine.Machine,qryMachine.MachineOwner,qryMachine.CompanyName,qryMachine.Cit
y,qryMachine.State,qryMachine.Division,qryMachine.CustStatus FROM qryMachine"
Private Sub cmdSort_Click()
On Error Resume Next
'Variable to hold filtered SQL string
Dim sCriteria As String
    sCriteria = " WHERE 1=1 "
    
    If Me![cboMachOwner] <> "" Then
        sCriteria = sCriteria & "AND qryMachine.MachineOwner = """ & cboMachOwner & """"
    End If
    
    If Me![cboDivision] <> "" Then
        sCriteria = sCriteria & "AND qryMachine.Division = """ & cboDivision & """"
    End If
    
    If Me![cboStatus] <> "" Then
        sCriteria = sCriteria & "AND qryMachine.CustStatus = """ & cboStatus & """"
    End If
    If Nz(DCount("*", "qryMachine", right(sCriteria, Len(sCriteria) - 14)), 0) > 0 Then
        Me.RecordSource = sCriteria
        Me.Requery
    Else
        MsgBox "The search failed to find any records" & vbCr & vbCr & _
        "that matches your search criteria?", vbOKOnly + vbQuestion, "Search Record"
    End If
End Sub

The form opens up displaying all of the records.
I make one choice from any combo box and all of my records become “?” marks.
Any idea what I am doing wrong.
k9huey
I am sorry! Not just a "?" mark but this
Name?
Ken
Tomolena
I'm guessing that your running that code in response to the combo box AfterUpdate events. Then it resets the form's recordsource property. That you're seeing "#Name" in the form's controls indicates that once the code runs, those controls no longer have valid ControlSources. The quickest way to debug this would be to add:

Debug.Print sCriteria 'at the last line line

That aside, I see problems with your code

Where is sCriteria being annexed to strSQL? Your code seems to be ignoring strSQL, and resetting the record source to a WHERE clause without any SELECT statement

The way you're using the quote marks seems a little dubious
sCriteria = sCriteria & "AND qryMachine.MachineOwner = """ & cboMachOwner & """"
Assuming that cboMachOwner is bound to a string, shouldn't it be:
sCriteria = sCriteria & "AND qryMachine.MachineOwner = '" & cboMachOwner & "'"

You may get better results by Declaring strSQL as a variable instead of a constant. Then:

strSQL = strSQL & " AND " & sCriteria
Me.RecordSource = strSQL
Me.Requery

at the end of the sub


Edited by: Tomolena on Thu May 18 12:56:50 EDT 2006.
k9huey
I am not running the code in response to a combo boxes AfterUpdate event I am using a click event of a command button. But that does not matter too much right now I just want to get the code to work first then I will set it up so the code fires with the AfterUpdate of the combo box.
kay I see what you are saying about my code and I made a few changes.
THere is the modified code
CODE
Private Sub cmdSort_Click()
On Error Resume Next
Dim sCriteria As String
Dim strSQL As String
    strSQL = "SELECT qryMachine.Machine,qryMachine.MachineOwner,qryMachine.CompanyName,qryMachine.Cit
y,qryMachine.State,qryMachine.Division,qryMachine.CustStatus FROM qryMachine"
    
    If Me![cboMachOwner] <> "" Then
        sCriteria = sCriteria & "AND qryMachine.MachineOwner = "" & cboMachOwner & """
    End If
    
    If Me![cboDivision] <> "" Then
        sCriteria = sCriteria & "AND qryMachine.Division = "" & cboDivision & """
    End If
    
    If Me![cboStatus] <> "" Then
        sCriteria = sCriteria & "AND qryMachine.CustStatus = "" & cboStatus & """
    End If
    strSQL = strSQL & " AND " & sCriteria
    
    If Nz(DCount("*", "qryMachine", right(sCriteria, Len(sCriteria) - 14)), 0) > 0 Then
        Me.RecordSource = strSQL
        Me.Requery
    Else
        MsgBox "The search failed to find any records" & vbCr & vbCr & _
        "that matches your search criteria?", vbOKOnly + vbQuestion, "Search Record"
    End If
End Sub

The code still does not work but now I do not get the #Name?.
Any other ideas.
Ken
Tomolena
I still don't believe that you're using the quotes correctly. The way you insert a string variable into an SQL string is:

"my sql sofar x='" & MyVariable & "' AND the rest of the string"
Notice the apostrophees in place of the quotes.
Your criteria string MUSt be preceeded with the WHERE word, NOT the AND word

sCriteria = " WHERE qryMachine.MachineOwner = '" & cboMachOwner & "'"

Then
sCriteria = sCriteria & " AND qryMachine.Division = "" & cboDivision & """

Interpolating SQL strings will seem a little tricky if you're not used to it. That's why I suggested testing the resultant string in a new query first. Just remember that the capitalized keyword ALWAYS get a single space before and after.
k9huey
Okay I have modified the code and I think it is correct but when I hit the button to run the code it appears to just not do anything. I know the code is running but nothing is changing. Here is the code. I put that debug.print line at the end and here is the SQL being generated.
SELECT * FROM qryMachine WHERE 1=1 AND qryMachine.MachineOwner = "Pending" AND qryMachine.Division = "Ice Cream";
CODE
Private Sub cmdSort_Click()
On Error Resume Next
Dim sCriteria As String
Dim strSQL As String
    strSQL = "SELECT * FROM qryMachine"
    sCriteria = " WHERE 1=1 "
    If Me![cboMachOwner] <> "" Then
        sCriteria = sCriteria & " AND qryMachine.MachineOwner = """ & cboMachOwner & """"
    End If
    If Me![cboDivision] <> "" Then
        sCriteria = sCriteria & " AND qryMachine.Division = """ & cboDivision & """"
    End If
    If Me![cboStatus] <> "" Then
        sCriteria = sCriteria & " AND qryMachine.CustStatus = """ & cboStatus & """"
    End If
    strSQL = strSQL & sCriteria & ";"
    If Nz(DCount("*", "qryMachine", right(sCriteria, Len(sCriteria) - 14)), 0) > 0 Then
        Me.RecordSource = strSQL
        Me.Requery
    Else
        MsgBox "The search failed to find any records" & vbCr & vbCr & _
        "that matches your search criteria?", vbOKOnly + vbQuestion, "Search Record"
    End If
Debug.Print strSQL 'at the last line line
End Sub

Any ideas as to why the form is not being updated.
Ken
Tomolena
I still see a problem with the string.

Why are you testing for 1=1? That seems pretty useless!

I'll give it to you again about the quotes. When you pass an SQL statement in VBA, string variables should
be enclosed with apostrophes, NOT quote marks. Play with your code until is spits out the following for strSQL:

SELECT * FROM qryMachine WHERE qryMachine.MachineOwner = 'Pending' AND qryMachine.Division = 'Ice Cream';

ALSO

There is a problem with this line:
If Nz(DCount("*", "qryMachine", right(sCriteria, Len(sCriteria) - 14)), 0) > 0 Then

You Don't need Nz here because DCount never results in a Null. I just noticed why you tested for 1=1; so that you can more easily parse the string, yes?
Try this code:
CODE
Private Sub cmdSort_Click()
On Error GoTo ErrorHandler
    Dim sCriteria As String
    Dim strSQL As String
    Dim msg As String
    
    strSQL = "SELECT * FROM qryMachine"
    sCriteria = " WHERE 1 = 1"
    
    If Me![cboMachOwner] <> "" Then
        sCriteria = sCriteria & " AND qryMachine.MachineOwner = '" & cboMachOwner & "'"
    End If
    
    If Me![cboDivision] <> "" Then
        sCriteria = sCriteria & " AND qryMachine.Division = '" & cboDivision & "'"
    End If
    
    If Me![cboStatus] <> "" Then
        sCriteria = sCriteria & " AND qryMachine.CustStatus = '" & cboStatus & "'"
    End If
    
    strSQL = strSQL & sCriteria & ";"
    
[color="green"] 'I RESET Right(sCriteria, Len(sCriteria) - 17) TO 17 INSTEAD OF 14...BUT I DIDN'T TEST IT! [/color]
    If DCount("*", "qryMachine", Right(sCriteria, Len(sCriteria) - 17)) > 0 Then
        Me.RecordSource = strSQL
        Me.Requery
    Else
    MsgBox "The search failed to find any records" & vbCr & vbCr & _
    "that matches your search criteria?", vbOKOnly + vbQuestion, "Search Record"
    End If
Exit_cmdSort_Click:
    Debug.Print strSQL
    Exit Sub
ErrorHandler:
    msg = Err.Number & " " & Err.Description & vbCrLf
    msg = msg & "sCriteria: " & sCriteria & vbCrLf
    msg = msg & "strSQL: " & strSQL
    MsgBox msg
    Resume Exit_cmdSort_Click
End Sub
k9huey
I like that Error Handler. That is one to remember.
changed my code to what you suggested and thanks to your errorhandler I know get this message when I hit the sort button.
3075 Syntax error (missing operator) in query expression '.MachineOwner = 'Pending' AND qryMachine.Division = 'Ice Cream''.
sCriteria: WHERE qryMachine.MachineOwner = 'Pending' AND qryMachine.Division = 'Ice Cream'
strSQL: SELECT * FROM qryMachine WHERE qryMachine.MachineOwner = 'Pending' AND qryMachine.Division = 'Ice Cream';
CODE
Private Sub cmdSort_Click()
On Error GoTo ErrorHandler
Dim sCriteria As String
Dim strSQL As String
Dim msg As String
    strSQL = "SELECT * FROM qryMachine"
  
    If Me![cboMachOwner] <> "" Then
        sCriteria = " WHERE qryMachine.MachineOwner = '" & cboMachOwner & "'"
    End If
    
    If Me![cboDivision] <> "" Then
        sCriteria = sCriteria & " AND qryMachine.Division = '" & cboDivision & "'"
    End If
    
    If Me![cboStatus] <> "" Then
        sCriteria = sCriteria & " AND qryMachine.CustStatus = '" & cboStatus & "'"
    End If
    
    strSQL = strSQL & sCriteria & ";"
    
    If DCount("*", "qryMachine", right(sCriteria, Len(sCriteria) - 17)) > 0 Then
        Me.RecordSource = strSQL
        Me.Requery
    Else
    MsgBox "The search failed to find any records" & vbCr & vbCr & _
    "that matches your search criteria?", vbOKOnly + vbQuestion, "Search Record"
    End If
Exit_cmdSort_Click:
    Debug.Print strSQL
    Exit Sub
ErrorHandler:
    msg = Err.Number & " " & Err.Description & vbCrLf
    msg = msg & "sCriteria: " & sCriteria & vbCrLf
    msg = msg & "strSQL: " & strSQL
    MsgBox msg
    Resume Exit_cmdSort_Click
End Sub

Ken
Jack Cowley
Hmmm. Are you trying to 'sort' (as in alphabetically) records or are you trying to 'filter' (show records that contain the items selected in the combo boxes)? If the latter then take a look at this article.
Just my 3 cents worth...
Jack
k9huey
Right now I am just trying to filter. I do want to add sorting but for now I just want to filter
Jack Cowley
Then the article I sent is what you are looking for. What I do is have a form with the combo boxes and a subform based on the Dynamic_Query. The user selects the criteria, clicks a button and the results are displayed in the subform.
ack
sredworb
I got lost.
et me see if I have this right, you want three combo boxes, 1st combo to select the "Topic" 2nd to filter combo box to meet 1st combo boxes selection, 3rd combo to meet 2nd combos selection...Do I have it right, if so I have something for you..
Jerry
k9huey
sredworb - are you talking about cascading combo boxes? That is not what this is.
en
sredworb
Sorry, that's what I thought...
Good Luck..
Maybe trim down and attach it so we all can have a shot at it...
Tomolena
It seems like you're close.

What happens if you drop the qryMachine. qualifier in ALL the strCriteria statements?

AND qryMachine.Division =

becomes:

To show as AND [Division] =

That may get you past the syntax issue.

This assumes that the MachineOwner, Division, and CustStatus comboboxes are giving STRING VALUES!!!!!!
k9huey
I have no idea what some of this stuff means with the DAO method but here is what I got.
The SQL ends up being - Select * from qryMachine where [MachineOwner] = 'Pending';
Othen get Run-time error ‘2001’: You canceled the previous operation
I click the Help button and the help file opens up but it is blank.
I click the Debug button and it takes me to the last line in the code.
THere is the code I used.
CODE
Private Sub cmdSortDAO_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant
Set db = CurrentDb()
where = Null
' Delete the existing dynamic query; trap the error if the query does not exist.
On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query")
On Error GoTo 0
    If Me![cboMachOwner] <> "" Then
        where = where & " AND [MachineOwner] = '" + Me![cboMachOwner] + "'"
    End If
    If Me![cboDivision] <> "" Then
        where = where & " AND [Division] = '" + Me![cboDivision] + "'"
    End If
    If Me![cboStatus] <> "" Then
        where = where & " AND [CustStatus] = '" + Me![cboStatus] + "'"
    End If
MsgBox "Select * from qryMachine " & (" where " + Mid(where, 6) & ";")
Set QD = db.CreateQueryDef("Dynamic_Query", "Select * from qryMachine " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query"
End Sub

Ken
sredworb
s why I suggested to strip it down and attach it, could have saved yourself some time and aggravation...
k9huey
If I just make the change you suggested then I get this error.
3075 Syntax error in query expression 'ner] = 'Pending' AND [Division] = 'Ice Cream''.
sCriteria: WHERE [MachineOwner] = 'Pending' AND [Division] = 'Ice Cream'
strSQL: SELECT * FROM qryMachine WHERE [MachineOwner] = 'Pending' AND [Division] = 'Ice Cream';
Ocreated one button two sort the DAO method and one button to sort the way I originally started.
Ken
Jack Cowley
Start by making sure that 'Microsoft DAO 3.6 Object Library" is checked in Reference. Next change the code below:
If Not IsNull(Me![cboMachOwner]) Then
where = where & " AND [MachineOwner] = '" + Me![cboMachOwner] + "'"
End If
If Not IsNull(Me![cboDivision]) Then
where = where & " AND [Division] = '" + Me![cboDivision] + "'"
End If
If Not IsNull(Me![cboStatus]) Then
where = where & " AND [CustStatus] = '" + Me![cboStatus] + "'"
End If
Is the query "qryMachine" a parameter query or just a query based on a table?
Jack
k9huey
The query is based off of three tables.
will post a very stripped down version of the database now.
Jack Cowley
Your problem lies in your the query "qryMachine". You have a related table that has a lookup for MachineOwner and it is a number NOT text. You are not looking for 'We' but the number 1 is what is stored in the table. I would suggest you remove all 'lookups' at table level and use combo boxes on your forms to prevent this time of problem in the future. I did not check the other 'lookups' in the query "qryMachine"
th,
Jack
k9huey
I see what you mean I will play with this on Monday. Have a great weekend.
opefully I can say I got it working then.
Thank You
Ken.
Jack Cowley
Ken -
You are welcome and once you fix those items you should be in good shape.. You have a good weekend as well!
Jack
Tomolena
Send an attachment...who'd a thunk?

After I was able to test the code against an actual working database, I found the problem. You violated the second commandment of Access!

Thou shalt abhor the use of "Lookup Fields" which art the creation of the Evil One

MachineOwner in tjxCustMach is a lookup field. SHAME UPON YOU!

To make your procedure work you will have to set the Bound Column for cboMachOwner to 1

Otested the following code. It works.

CODE

Private Sub cmdSort_Click()
On Error GoTo ErrorHandler
    Dim sCriteria As String
    Dim strSQL As String
    Dim msg As String
    
    strSQL = "SELECT * FROM qryMachine"
    If Not IsNull(Me.cboMachOwner) Then
        sCriteria = "((qryMachine.MachineOwner) = " & cboMachOwner & ")"
    End If
    If Not IsNull(Me.cboDivision) Then
        If Len(sCriteria) > 0 Then sCriteria = sCriteria & " AND "
        sCriteria = sCriteria & "((qryMachine.Division) = '" & cboDivision & "')"
    End If
    If Not IsNull(cboStatus) Then
        If Len(sCriteria) > 0 Then sCriteria = sCriteria & " AND "
        sCriteria = sCriteria & "((qryMachine.CustStatus) = '" & cboStatus & "')"
    End If
    If Len(sCriteria) = 0 Then
        msg = "You did not specify a criteria."
        MsgBox msg, vbOKOnly + vbInformation, "INVALID REQUEST"
    End If
    strSQL = strSQL & " WHERE (" & sCriteria & ");"
    If DCount("*", "qryMachine", sCriteria) > 0 Then
        Me.RecordSource = strSQL
        Me.Requery
    Else
    MsgBox "The search failed to find any records" & vbCr & vbCr & _
    "that matche your search criteria.", vbOKOnly, "Search Record"
    End If
Exit_cmdSort_Click:
   [color="green"]'Debug.Print strSQL  [/color]    
    Exit Sub
ErrorHandler:
    msg = Err.Number & " " & Err.Description & vbCrLf
    msg = msg & "sCriteria: " & sCriteria & vbCrLf
    msg = msg & "strSQL: " & strSQL
    MsgBox msg, vbCritical, "AN ERROR OCCURRED"
    Resume Exit_cmdSort_Click
End Sub

Edited by: Tomolena on Fri May 19 20:45:17 EDT 2006.
Tomolena
One other thing.
After:
MsgBox msg, vbOKOnly + vbInformation, "INVALID REQUEST"
Add another line:
Exit Sub
sredworb
See, sometimes it's better to send an attachment then to paste five million lines of code.
erry
k9huey
Tomolena - Your code worked great. I liked your commandment thing.
Thank you ALL for your help! Everything works now.
Ken
Tomolena
Glad to help. o!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.