Can someone please assist with the VBA code Below - Error Message 3075 (4 Viewers)

[tucker61]

I am trying to run this code on a form open - that askes for the search criteria, then just brings up the specific records, but i am getting Error 3075
"Syntax error (Missing operator) in the query expression as per image below.
If i click on OK, the code runs but does not recognise the names of some of the join fields.

Private Sub Form_Open(Cancel As Integer)
Dim Search As String
Dim Strsql As String
On Error GoTo Handler

 Search = Trim(InputBox("Enter Line Number . . . ", "Search Line"))
    If StrPtr(Search) = 0 Then
        MsgBox ("User Canceled!")
    ElseIf Search = vbNullString Then
        MsgBox "No Data!", vbExclamation + vbOKOnly, "Cancelled"
    Else
   
Strsql = "SELECT DISTINCT tblWarehouseStock.Cat_No, tblproducts.Line_Description, Sum(tblWarehouseStock.No_Items) AS SumOfNo_Items, tblWarehouseStock.Status_ID, tblproducts.Size_Description " & vbCrLf & _
"FROM tblWarehouseStock LEFT JOIN tblproducts ON tblWarehouseStock.Cat_No = tblproducts.Cat_No " & vbCrLf & _
"GROUP BY tblWarehouseStock.Cat_No, tblproducts.Line_Description, tblWarehouseStock.cat_no, tblWarehouseStock.Status_ID, tblproducts.Size_Description " & vbCrLf & _
"WHERE tblWarehouseStock.cat_no='" & Trim(UCase(Nz(Search, ""))) & "' ;"
'Debug.Print Strsql
  End If
Me.RecordSource = Strsql


Exit Sub
Handler:
        Call LogError(Err.Number, Err.Description, "11", "")
        Exit Sub
End Sub

 

[arnelgp]

use Len() instead of StrPtr() function.
also you do not need the vbCrLf on your strSQL.

...
"WHERE tblWarehouseStock.cat_no='" & Trim(Search & "") & ";"

 

[Minty]

Your Where clause needs to go before the Group By
And I'm pretty sure the Distinct keyword is redundant because you are already grouping.

Edit : And Access isn't case sensitive, so no need to UCase() the search terms.

 

[Uncle Gizmo]

Chatty said:-

Private Sub Form_Open(Cancel As Integer)
    Dim Search As String
    Dim Strsql As String
    On Error GoTo Handler

    ' Prompt the user for input and trim leading/trailing spaces from the input
    Search = Trim(InputBox("Enter Line Number . . . ", "Search Line"))

    ' Check if the user canceled the InputBox (StrPtr(Search) returns 0 if canceled)
    ' Correction: Added "Cancel = True" to stop form opening if canceled
    If StrPtr(Search) = 0 Then
        MsgBox "User Canceled!", vbInformation, "Cancelled"
        Cancel = True ' This prevents the form from opening when canceled
        Exit Sub
    ' Check if the input is an empty string after trimming
    ElseIf Search = vbNullString Then
        MsgBox "No Data!", vbExclamation + vbOKOnly, "Cancelled"
        Cancel = True ' Prevent form from opening if no input is provided
        Exit Sub
    Else
        ' Construct SQL query with the user input
        ' Correction: Fixed GROUP BY clause by removing duplicate tblWarehouseStock.cat_no
        Strsql = "SELECT DISTINCT tblWarehouseStock.Cat_No, tblproducts.Line_Description, Sum(tblWarehouseStock.No_Items) AS SumOfNo_Items, tblWarehouseStock.Status_ID, tblproducts.Size_Description " & vbCrLf & _
                 "FROM tblWarehouseStock LEFT JOIN tblproducts ON tblWarehouseStock.Cat_No = tblproducts.Cat_No " & vbCrLf & _
                 "WHERE tblWarehouseStock.cat_no='" & Trim(UCase(Nz(Search, ""))) & "' " & vbCrLf & _
                 "GROUP BY tblWarehouseStock.Cat_No, tblproducts.Line_Description, tblWarehouseStock.Status_ID, tblproducts.Size_Description;"

        ' Set the form's RecordSource to the constructed SQL string
        Me.RecordSource = Strsql
    End If

    Exit Sub

Handler:
    ' Log the error with custom error handling procedure
    Call LogError(Err.Number, Err.Description, "11", "")
    Exit Sub
End Sub

Chatty's Comments:-

Key Corrections:

1. Handling Form Cancellation:
If the user cancels the input (StrPtr(Search) = 0), the form will no longer proceed to open (Cancel = True ensures this).

Similarly, if the input is empty (Search = vbNullString), the form is also prevented from opening.

2. GROUP BY Clause Fix:
Removed the redundant field tblWarehouseStock.cat_no in the GROUP BY clause since it was already included with tblWarehouseStock.Cat_No. Having duplicates in GROUP BY can cause SQL errors.

3. Improved User Messaging:
The MsgBox statements were formatted with the proper message styles (vbInformation, vbExclamation), enhancing the user experience when the action is canceled or invalid data is provided.

Answer created by ChatGPT through a specialized agent called MS Access Copilot, developed by OpenAI.

 

[Gasman]

Your quotes are all wrong. You need single quotes either side of your citeria.
You have a single at the front, and a double at the end?

 

[tucker61]

Chatty said:-

Private Sub Form_Open(Cancel As Integer)
    Dim Search As String
    Dim Strsql As String
    On Error GoTo Handler

    ' Prompt the user for input and trim leading/trailing spaces from the input
    Search = Trim(InputBox("Enter Line Number . . . ", "Search Line"))

    ' Check if the user canceled the InputBox (StrPtr(Search) returns 0 if canceled)
    ' Correction: Added "Cancel = True" to stop form opening if canceled
    If StrPtr(Search) = 0 Then
        MsgBox "User Canceled!", vbInformation, "Cancelled"
        Cancel = True ' This prevents the form from opening when canceled
        Exit Sub
    ' Check if the input is an empty string after trimming
    ElseIf Search = vbNullString Then
        MsgBox "No Data!", vbExclamation + vbOKOnly, "Cancelled"
        Cancel = True ' Prevent form from opening if no input is provided
        Exit Sub
    Else
        ' Construct SQL query with the user input
        ' Correction: Fixed GROUP BY clause by removing duplicate tblWarehouseStock.cat_no
        Strsql = "SELECT DISTINCT tblWarehouseStock.Cat_No, tblproducts.Line_Description, Sum(tblWarehouseStock.No_Items) AS SumOfNo_Items, tblWarehouseStock.Status_ID, tblproducts.Size_Description " & vbCrLf & _
                 "FROM tblWarehouseStock LEFT JOIN tblproducts ON tblWarehouseStock.Cat_No = tblproducts.Cat_No " & vbCrLf & _
                 "WHERE tblWarehouseStock.cat_no='" & Trim(UCase(Nz(Search, ""))) & "' " & vbCrLf & _
                 "GROUP BY tblWarehouseStock.Cat_No, tblproducts.Line_Description, tblWarehouseStock.Status_ID, tblproducts.Size_Description;"

        ' Set the form's RecordSource to the constructed SQL string
        Me.RecordSource = Strsql
    End If

    Exit Sub

Handler:
    ' Log the error with custom error handling procedure
    Call LogError(Err.Number, Err.Description, "11", "")
    Exit Sub
End Sub

Chatty's Comments:-

Key Corrections:

1. Handling Form Cancellation:
If the user cancels the input (StrPtr(Search) = 0), the form will no longer proceed to open (Cancel = True ensures this).

Similarly, if the input is empty (Search = vbNullString), the form is also prevented from opening.

2. GROUP BY Clause Fix:
Removed the redundant field tblWarehouseStock.cat_no in the GROUP BY clause since it was already included with tblWarehouseStock.Cat_No. Having duplicates in GROUP BY can cause SQL errors.

3. Improved User Messaging:
The MsgBox statements were formatted with the proper message styles (vbInformation, vbExclamation), enhancing the user experience when the action is canceled or invalid data is provided.

Answer created by ChatGPT through a specialized agent called MS Access Copilot, developed by OpenAI.

Thanks, that helped a lot.

I have never really used ChatGPT for Access coding - Can i ask What did you ask it to do - Was it something simple like Check this Code ?

 

[Uncle Gizmo]

Yes it was basically chat GPT but you also have the ability to create specialist agents within the chat GPT framework for aiding with a particular expertise.

The chat GPT owners also create some of these specialist agents and the one I used was called "MS Access co-pilot"

I used the following search term:-

Please correct this code, and comment the correction:- (your code)

I added the "comment the correction" part so that it would highlight the corrections it had made.

As usual it produced a load of other superfluous text that was not required!

This is a tendency of Chatty to be verbose, which is why I call it Chatty!!!

By the way, I have no idea if chatty has provided you with correct code, you need to feed it into your database and see what happens. Report back of there are any errors!

 

[tucker61]

Yes it was basically chat GPT but you also have the ability to create specialist agents within the chat GPT framework for aiding with a particular expertise.

The chat GPT owners also create some of these specialist agents and the one I used was called "MS Access co-pilot"

I used the following search term:-

Please correct this code, and comment the correction:- (your code)

I added the "comment the correction" part so that it would highlight the corrections it had made.

As usual it produced a load of other superfluous text that was not required!

This is a tendency of Chatty to be verbose, which is why I call it Chatty!!!

By the way, I have no idea if chatty has provided you with correct code, you need to feed it into your database and see what happens. Report back of there are any errors!

It did the job for me. Thanks

 

[GPGeorge]

Spoiler:

Yes it was basically chat GPT but you also have the ability to create specialist agents within the chat GPT framework for aiding with a particular expertise.

The chat GPT owners also create some of these specialist agents and the one I used was called "MS Access co-pilot"

I used the following search term:-

Please correct this code, and comment the correction:- (your code)

I added the "comment the correction" part so that it would highlight the corrections it had made.

As usual it produced a load of other superfluous text that was not required!

This is a tendency of Chatty to be verbose, which is why I call it Chatty!!!

By the way, I have no idea if chatty has provided you with correct code, you need to feed it into your database and see what happens. Report back of there are any errors!

This, is I suggest, is the essence of reasonable use for the AI assistants now available. Or, to quote a bit of wisdom from the 20th Century, "Trust, but verify."