Error in "Order by Clause" 2

[DrillMonkey]

Hi,
I am using a form with a combo box and a text box to accept a string to search 2 tables. I recive the following error

********************************
run-time error 3138
Syntax error in ORDER BY clause.
********************************
Row source type: Field List
Row source: see code

 SELECT [tblWarehouse].[Date], [tblWarehouse].[Signature], [tblWarehouse].[Driver], [tblPurchData].[Purchase Order], [tblPurchData].[Requisition], [tblPurchData].[Stores Order], [tblPurchData].[Ticket], [tblPurchData].[Memo Number], [tblPurchData].[Discription], [tblPurchData].[Parcels] FROM tblWarehouse INNER JOIN tblPurchData ON [tblWarehouse].[WarehouseID]=[tblPurchData].[WarehouseID];

Private Sub cmdSearch_Click()

    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
        
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
        
    Else
    
        'Generate search criteria
        GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
        
        'Filter Form1 based on search criteria
        Form_ListFrm.RecordSource = "select * from tblWarehouse where " & GCriteria
        'Form_ListFrm.Caption = "tblWarehouse (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
        
        'Close frmSearch
        DoCmd.Close acForm, "frmSearch"
         'Open report
        DoCmd.OpenReport "rptWarehouse", acViewPreview, , GCriteria
        DoCmd.Maximize              
        'MsgBox "Results have been filtered."
        
    End If
    
End Sub

I think I am missing a ORDER BY Clause but am not sure which part of the row source it goes..Thank you

 

[dhookom]

It looks like you have spaces in field names :-(. If this is the case then you need to use []s in your code like:

 GCriteria = " [" & cboSearchField.Value & "] LIKE '*" & txtSearchString & "*'"

Your code also assumes are fields are text.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DrillMonkey]

Hi Duane,

Thanks for your help you hit it right on the money..just on small hitch just before the report opens the code asks for a parameter.. "Enter Parameter Value" this only occures in the child table side "tblPurchData" the parent table side goes right through. Thanks again..And your comment about the field names are noted ty.

 

[BrianLe]

Does your report have a Sorting/Grouping property set for the parameter named in the "Enter Parameter Value"? If it is misspelled or not in the query, you will get that error.

Brian

 

[DrillMonkey]

Hi BrianLe,

My row source for my report is set to:SELECT * FROM tblWarehouse, tblPurchdata; and I am not grouping or sorting yet. The thing that I do notice is the fields that are in the parent table do not get hung up.

Parent tbl
tblWarehouse
-------------
WarehouseID
Date
Signature
Driver
School

Child tbl
tblPurchData
------------
Purchase OrderID
WarehouseID
Purchase Order
Requisition
Stores Order
Ticket
Memo Number
Parcels
Discription

The PK's are autonumber and the FK is set to number
Parcels is set to number, date is short date. All the other fields are text because they are a mix of numbers and letters example might be 864r2020 or 64s0023

 

[BrianLe]

The "D" in tblPurchdata is not capitalized in "SELECT * FROM tblWarehouse, tblPurchdata" but it is in your table name tblPurchData.

 

[DrillMonkey]

I fixed that but still getting the "Enter Parameter Value"
error

 

[BrianLe]

Did you change the SELECT ...?

 

[BrianLe]

What is the name of the parameter in the "Enter Parameter Value" message? If the record source for a textbox in your report is misspelled or is not in your report's record source, it will give you the "Enter Parameter Value" message.

 

[DrillMonkey]

well it depends on the selection i make in the combo box anything from the child table gives the error

 

[DrillMonkey]

I think I see whats going on here, in the code for the combo box I refrence both "parent and child" tables. In the module I only have the "parent" there is no child table i.e. "tblPurchData". I don't write code but wouldn't it be better to use the PK and or FK in this situation? any thoughts.

 

[BrianLe]

Do you need to put "GCriteria" after "acViewPreview" in your
Private Sub cmdSearch_Click() ?

DoCmd.OpenReport "rptWarehouse", acViewPreview,GCriteria

 

[DrillMonkey]

Well to be honest I think my problem lies in this part of the code.

Form_ListFrm.RecordSource = "select * from tblWarehouse where " & GCriteria

Somehow I need to reffrence the second table also i.e. "tblPurchData" I have tried inserting a second line.

Form_ListFrm.RecordSource = "select * from tblWarehouse where " & GCriteria
Form_ListFrm.RecordSource = "select * from tblPurchData where " & GCriteria

both[/color red] tables need to be searched to make this search worth doing....

 

[dhookom]

If you want to be able to search both the parent and child table fields then both would need to be in the form's record source query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DrillMonkey]

Hi Duane,

is that with a select statement?

right now i have tblwarehouse in the forms record source

 

[PHV]

Perhaps this ?
Form_ListFrm.RecordSource = "SELECT * FROM tblWarehouse INNER JOIN tblPurchData ON tblWarehouse.WarehouseID=tblPurchData.WarehouseID WHERE " & GCriteria

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DrillMonkey]

Yes I believe that worked...You guys are scarey good..many thanks to Dhookom, BrianLe, and PHV

 

[DrillMonkey]

Hey guys,

I may have spoke to soon on this one, I have been running some search reports and found that I am getting mixed results from the search. While all error messages have gone, I'm getting a kind of truncated report. I get the information the user requests (together on one page) plus a truncated version that shares information from the parent table. Lets say I wanted to search by school "Lexington" I should get 4 records.But instead I get 8, 4 mixed from an unrelated records. Any thoughts?

 

[PHV]

Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

If your record source isn't returning the correct records, always provide the SQL view of your record source. I'm not sure how anyone can help fix something we can't see.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]