Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
 
 
Access Search Seminar
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   10 years ago


Back to Access Search Seminar
 

Search with Combo Box Upload Images   Link  
Ina van Rooyen 
10 months ago
Hi Richard
I have three combo boxes on a continuous form with approximately 600 records in total. I use the exact same code you used in the Genealogy seminar with different field names, obviously.
All three combo boxes get their values from a table.
When I select an option from the combo box I get a Run-time error '3138': Syntax error in ORDER BY clause.
This line is yellow when I press debug.          
    Me.RecordSource = SQL

This is my code:

Private Sub RequeryForm()

    Dim SQL As String, WhereStr As String, OrderBy As String
    
    SQL = "SELECT DogT.DogID, DogT.BirthDate, DogT.Mchip, DogT.CallName, GenderT.Gender, DogT.Status, ColoursT.Colour " & _
        "FROM (DogT INNER JOIN GenderT ON DogT.GenderID = GenderT.GenderID) INNER JOIN ColoursT " & _
            "ON DogT.ColourID = ColoursT.ColourID " & Wh
            
          
    ' WHERE -----------------------------------------------
    WhereStr = ""
    
    If CallNameSearch <> "" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "CallName LIKE ""*" & CallNameSearch & "*"" "
    End If
    
    If Not IsNull(GenderCombo) Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Gender = " & GenderCombo & " "
    End If
    
    If Not IsNull(StatusCombo) Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Colour = " & ColourCombo & " "
    End If
    
    If WhereStr <> "" Then
        WhereStr = "WHERE " & WhereStr
    End If
    
    SQL = SQL & WhereStr
    
    ' ORDER BY -----------------------------------------------
    Select Case SortCombo
        Case 1: OrderBy = "Status, Gender, CallName"
        Case 2: OrderBy = "Gender, CallName"
        Case 3: OrderBy = "BirthDate DESC, Gender, CallName"
        Case 4: OrderBy = "CallName"
        Case 5: OrderBy = "MChip ASC"
    End Select
    
    SQL = SQL & "ORDER BY " & OrderBy
        
    Me.RecordSource = SQL

End Sub
Richard Rost
10 months ago
Your code looks solid. Usually this problem is because people forget the space before "ORDER BY" but you've got that covered at the end of the WhereStr. Only time you'd have a problem would be if there was no WhereStr. So you might want to add a space in there. Let's see what your final SQL statement looks like. MsgBox it and post a screen shot.
Kevin Robertson
10 months ago
What is the "& wh" at the end of sql?
Your WhereStr appears to go straight after with no space.
Richard Rost
10 months ago
Good catch. As the code is written, it would evaluate to an empty string, unless it's a form field. Probably shouldn't be there, but I don't see that causing the problem. That's why I'd like to see the final SQL as compiled.
Alex Hedley
10 months ago
Debug.Print SQL
Ina van Rooyen
10 months ago
Thank you for the quick response, guys. My WhereStr appears to have spaces except where it appears at the end of a line.  I've put spaces in there but the space disappears again.  Not quite sure what you mean by 'WhereStr appears to go straight after with no space'.
I'm not quite sure which SQL Statement you want to see and how do I run it.  My Record Source in my form is a Query and this is the SQL Statement
SELECT DogT.DogID, DogT.BirthDate, DogT.Mchip, DogT.CallName, GenderT.Gender, DogT.Status, ColoursT.Colour
FROM (DogT LEFT JOIN GenderT ON DogT.GenderID = GenderT.GenderID) LEFT JOIN ColoursT ON DogT.ColourID = ColoursT.ColourID
WHERE (((DogT.DogID)<988) AND ((DogT.Status)="Breeding"))
ORDER BY DogT.CallName;
I'm also not sure what Alex means with 'Debug.Print SQL'
Ina van Rooyen
10 months ago

Ina van Rooyen
10 months ago

Ina van Rooyen
10 months ago

Ina van Rooyen
10 months ago
Thank you for the quick response, guys. My WhereStr appears to have spaces except where it appears at the end of a line.  I've put spaces in there but the space disappears again.  Not quite sure what you mean by 'WhereStr appears to go straight after with no space'.
Scott Axton
10 months ago
All -
Fresh eyes here.  I think Kevin Robertson was on to something.  It appears to me that you are mixing up 2 different   code variations.
In the code in the first message you have:
"ON DogT.ColourID = ColoursT.ColourID " & Wh (last line of the SQL statement)

You did your dim as WhereStr As String, but used Wh in the SQL statement.
In the screen captures I see Wh As String.  Which ever you decide to use they all need to be the same - Either WH or WhereStr.

I'm also not sure what Alex means with 'Debug.Print SQL'
What Alex was telling you was to use the Immediate window to display the SQL statement after it is built.
Comment out "Me.RecordSource = SQL" and put the "Debug.Print SQL" above it.  When you run the code it will put the full SQL statement in the immediate Window that you should be able to copy and paste here so we can look at it.
Scott Axton
10 months ago
Access Developer 15 Lesson 2 covers Debugging more in depth including the use if Immediate window & Debug.Print as well as the Watch Window.
Richard Rost
10 months ago
Is it just me or do you have an ORDER BY at the end of the line all by itself? You need to put a field after it. ORDER BY FirstName, for example.

Debug.Print just prints a string to the Immediate Window instead of using MsgBox or my StatusBox thing from the Blank template.
Richard Rost
10 months ago
Yeah, it's possible for your SortCombo to have no value. Make sure you set a Case Else with a default ORDER BY value.
Ina van Rooyen
10 months ago
Hi Guys
This is my SQL Statement.  According to me, it looks fine

SELECT DogT.DogID, DogT.BirthDate, DogT.Mchip, DogT.CallName, GenderT.Gender, DogT.Status, ColoursT.Colour FROM (DogT INNER JOIN GenderT ON DogT.GenderID = GenderT.GenderID) INNER JOIN ColoursT ON DogT.ColourID = ColoursT.ColourID WHERE Gender = -1 ORDER BY

I might just say, my CallNameSearch is working, so does the Select Case SortCombo.

I'm posting a copy of my code again in a different Comment
Ina van Rooyen
10 months ago
Private Sub RequeryForm()

    Dim SQL As String, WhereStr As String, OrderBy As String
    
    SQL = "SELECT DogT.DogID, DogT.BirthDate, DogT.Mchip, DogT.CallName, GenderT.Gender, DogT.Status, ColoursT.Colour " & _
        "FROM (DogT INNER JOIN GenderT ON DogT.GenderID = GenderT.GenderID) INNER JOIN ColoursT " & _
            "ON DogT.ColourID = ColoursT.ColourID " & WhereStr
              
      WhereStr = ""
    
    If CallNameSearch <> "" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "CallName LIKE ""*" & CallNameSearch & "*"" "
    End If
    
    If Not IsNull(GenderCombo) Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Gender = " & GenderCombo & " "
    End If
    
    If Not IsNull(StatusCombo) Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Status = " & StatusCombo & " "

Ina van Rooyen
10 months ago
End If
    If Not IsNull(ColourCombo) Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Colour = " & ColourCombo & " "
    End If
    If WhereStr <> "" Then
        WhereStr = "WHERE " & WhereStr
    End If
    
    SQL = SQL & WhereStr
      
    ' ORDER BY -----------------------------------------------
    Select Case SortCombo
        Case 1: OrderBy = "Status, Gender, CallName"
        Case 2: OrderBy = "Gender, CallName"
        Case 3: OrderBy = "BirthDate DESC, Gender, CallName"
        Case 4: OrderBy = "CallName"
        Case 5: OrderBy = "MChip ASC"
    End Select
    
    SQL = SQL & "ORDER BY " & OrderBy
      
    Me.RecordSource = SQL

End Sub
Alex Hedley
10 months ago
You could try creating a new Query
Change to SQL View
Paste it in
Run to see if there are any errors
Scott Axton
10 months ago
Grasping at straws here,  I can't recall which video it was in, but Richard was doing lots of editing, saving, running the query with both the form open and the code window open.  I also forget the exact scenario of what caused it but the "bad" Order By ended up being "hard coded" by Access into the form properties Order By.

You might check that in your form just to make sure it hasn't happened to you.
Scott Axton
10 months ago

Richard Rost
10 months ago
You've got nothing after the ORDER BY in the final SQL you posted above. That's no bueno. Gotta have something there or remove the ORDER BY altogether.
Ina van Rooyen
10 months ago
Richard, I totally agree with you.  It doesn't make a difference if I remove it. I will remove it though.
I have it there because I've tried to mimic your code from the Genealogy seminar LOL.
Kevin Robertson
10 months ago
How is your ComboBox set up? Is the bound column numeric or text?
Ina van Rooyen
10 months ago
The bound column is numeric.
Ina van Rooyen
10 months ago
I'm very confused.  I've just run the Genealogy database and the Gender Combo runs exactly how it should.  All my code is exactly what it is there.  I then copied the Gender Combo and rename it with an after update event running the exact same procedure and I get two different results. The second combobox doesn't sort because there's not a Where clause in the SQL. However, I don't get the Run Time Error &#129300;&#128527;
Original GenderCombo:
SELECT * FROM PersonListQ WHERE Gender = -1 ORDER BY LastName, FirstName;
The GenderCombo copy with a different name (it doesn't pick up the Where Clause):
SELECT * FROM PersonListQ ORDER BY LastName, FirstName;
Ina van Rooyen
10 months ago
I did a fresh SQL statement and I moved the SQL statement inside the Select Case Statement. The Select Case Statement needs the "ORDER BY "
I'm now one step further, the Run Time Error doesn't pop up anymore.  
The problem I have is that the Where Clause doesn't end up showing in the SQL Statement.
Looks like a problem somewhere in my WhereStr ??

Select Case SortCombo
        Case 1: OrderBy = "Status, Gender, CallName"
        Case 2: OrderBy = "Gender, CallName"
        Case 3: OrderBy = "BirthDate DESC, Gender, CallName"
        Case 4: OrderBy = "CallName"
        Case 5: OrderBy = "MChip ASC"
    SQL = SQL & "ORDER BY " & OrderBy
    End Select
    
    'SQL = SQL & "ORDER BY " & OrderBy
      
    Debug.Print SQL
    Me.RecordSource = SQL

SELECT DogT.DogID, DogT.BirthDate, DogT.Mchip, DogT.CallName, GenderT.Gender, DogT.Status, ColoursT.Colour FROM (DogT INNER JOIN GenderT ON DogT.GenderID = GenderT.GenderID) INNER JOIN ColoursT ON DogT.ColourID = ColoursT.ColourID
Richard Rost
10 months ago
I don't see your WhereStr there anywhere. Get it? AnyWHERE? LOL
Scott Axton
10 months ago
Oooo...  I think Kevin may be on to it.
If you place the actual text at the end of the SQL instead of the results of the SortCombo does the SQL run correctly?
so
SQL = SQL & "ORDER BY " & CallName

if the above works, try placing a stop on the Select statement and step through.  I'm betting rather than 1 the case is returning "1" etc.

Also - You are checking for Null everywhere else but the SortCombo
I would add a Case Else to the select as a fall through.

Case else: OrderBy = "YourID"
Ina van Rooyen
10 months ago
I might just add that the WhereStr works with the CallName Search

WhereStr = ""
    
    If CallNameSearch <> "" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "CallName LIKE ""*" & CallNameSearch & "*"" "
    End If
    
    If Not IsNull(GenderCombo) Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Gender = " & GenderCombo & " "
    End If
     If WhereStr <> "" Then
        WhereStr = "WHERE " & WhereStr
    End If
    
    SQL = SQL & WhereStr
  
SELECT DogT.DogID, DogT.BirthDate, DogT.Mchip, DogT.CallName, GenderT.Gender, DogT.Status, ColoursT.Colour FROM (DogT INNER JOIN GenderT ON DogT.GenderID = GenderT.GenderID) INNER JOIN ColoursT ON DogT.ColourID = ColoursT.ColourID WHERE CallName LIKE "*lil*"
Ina van Rooyen
10 months ago
Richard, that was just part of the code. This is it all:
Private Sub RequeryForm()

    Dim SQL As String, WhereStr As String, OrderBy As String
  
    DoCmd.SetWarnings True
    
    SQL = "SELECT DogT.DogID, DogT.BirthDate, DogT.Mchip, DogT.CallName, " & _
        "GenderT.Gender, DogT.Status, ColoursT.Colour " & _
        "FROM (DogT INNER JOIN GenderT ON DogT.GenderID = GenderT.GenderID) " & _
        "INNER JOIN ColoursT ON DogT.ColourID = ColoursT.ColourID " & WhereStr
  
    DoCmd.SetWarnings False
              
    ' WHERE -----------------------------------------------
    WhereStr = ""
    
    If CallNameSearch <> "" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "CallName LIKE ""*" & CallNameSearch & "*"" "
    End If
    
    If Not IsNull(GenderCombo) Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Gender = " & GenderCombo & " "
    End If
    
Ina van Rooyen
10 months ago
If WhereStr <> "" Then
        WhereStr = "WHERE " & WhereStr
    End If
    
    SQL = SQL & WhereStr

      
    ' ORDER BY -----------------------------------------------
    Select Case SortCombo
        Case 1: OrderBy = "Status, Gender, CallName"
        Case 2: OrderBy = "Gender, CallName"
        Case 3: OrderBy = "BirthDate DESC, Gender, CallName"
        Case 4: OrderBy = "CallName"
        Case 5: OrderBy = "MChip ASC"
    SQL = SQL & "ORDER BY " & OrderBy
    End Select
    
    'SQL = SQL & "ORDER BY " & OrderBy
      
    Debug.Print SQL
    Me.RecordSource = SQL

End Sub
Kevin Robertson
10 months ago
The ComboBoxes that filter your results need to be a single column (No ID) They will return a string value so need to be enclosed in double quotes. I built a quick DB just to test it would work. Below is the modified code:
Kevin Robertson
10 months ago

Ina van Rooyen
10 months ago
Thank you, Kevin, it worked but it resulted in my SortCombo not working the way it should.
May I ask why the Gender selection won't work if it's selected by the gender ID, the same with the StatusCombo and the ColourCombo, which get its values from different tables.
I figured that the WhereStr variable is defined as a string and those comboboxes display numeric values which are the id fields in the tables.
I tried:  Dim WhereStr As Integer

And replaced the
WhereStr = ""
with
WhereStr = Null

It doesn't like the "Null" - what should it be then?

My code is based on the code Richard use in the Genealogy Seminar in the PersonListF.
Kevin Robertson
10 months ago
Maybe we set our forms up differently. This is how I set mine up. Let me know if yours is different.
Kevin Robertson
10 months ago

Ina van Rooyen
10 months ago
Mine looks different but I can do it your way.  My issue is that I don't want to create text values again just for the purpose of searching.  I already have those values set up in separate tables, eg. GenderT, StatusT and ColourT.  My RowSource for my DogF form is a Query that gets data from the DogT, GenderT, StatusT and ColourT. That is how I differentiate the dogs.  I use that Query on other forms as well.
So your Sort By ComboBox gives you the option of sorting by any of those fields?
And your Filters ComboBoxex are on each individual field?


Ina van Rooyen
10 months ago
This is my form
Ina van Rooyen
10 months ago

This thread is now closed. If you wish to comment, start a NEW discussion, below.
 


Back to Access Search Seminar Comments
 

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

You may want to read these articles from the 599CD News:

5/27/2022Add Anything
5/26/2022List Items Edit Form
5/25/2022Column Colors
5/24/2022Multi-Table Forms
5/23/2022Variables
5/21/2022Access Developer 39
5/21/2022Access Developer 39 Lessons
5/21/2022Access Developer 39
5/21/2022Select Items in Related Table
5/21/2022Clear List Box
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn