Can someone please assist with the VBA code Below - Error Message 3075

[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."

 

[Minty]

But more importantly do you know and/or have learned what your error was, so you can avoid it in the future?

I notice Chatty didn't mention the real cause of your problem, which was the position of your where clause, it simply repositioned it in the sample output.

 

[Uncle Gizmo]

I notice Chatty didn't mention the real cause of your problem

I challenged Chatty:-
You didn't mention the real cause of the problem, which was the position of the where clause. - Please explain

Response:-
SQL Query Execution:
The WHERE clause filters the rows before the aggregation happens. If you place it after the GROUP BY, the SQL engine doesn't know what to filter, as grouping has already occurred.

Error Avoidance:
SQL would throw an error if the WHERE clause is misplaced, preventing the query from running properly.

Thank you for pointing this out, and I hope this explanation clears up the issue!

 

[Uncle Gizmo]

, "Trust, but verify."

Or, in my experience "Don't Trust" - VERIFY!!!

 

[Uncle Gizmo]

I've noticed that if you ask chatty to help you with a problem, and you want chatty to write some code for you, it will often default to producing python code because, I understand Chatty is very good with python.

So in your preliminary discussions with chatty, ask broad questions like I want to do so and so, which programming language would be the best for this type of task.

It's unlikely to specify a particular language, it will come back at you with for & against for several.

It's best to do this at the beginning than find out later after you are probability using the wrong language!

Experience is a blessing and a curse....

 

[NauticalGent]

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."

ChatGPT has worked wonders for me and has really simplified some coding tasks...but it is often wrong and I have to ask better questions.

But more importantly do you know and/or have learned what your error was, so you can avoid it in the future?

And there is the rub. For me, I personally ask for explanations because I really want to know, but I there have been a few times I was just glad to have something that worked and got on with my day. I'm not sure if this qualifies as a moral hazard, but when I simply take what is given and do not take the time to understand the response, I do myself a disservice.

 

[GPGeorge]

ChatGPT has worked wonders for me and has really simplified some coding tasks...but it is often wrong and I have to ask better questions.


And there is the rub. For me, I personally ask for explanations because I really want to know, but I there have been a few times I was just glad to have something that worked and got on with my day. I'm not sure if this qualifies as a moral hazard, but when I simply take what is given and do not take the time to understand the response, I do myself a disservice.

I think that as a threshold criteria, "it works" is fine for many things.

 

[tucker61]

Thanks for all the advice.

Over the last 2 weeks I have spent plenty of time asking chatgpt to optimise access vba code, check error handling and add comments to my code. Of which it has done 95% of the time without any errors.

Thanks All.

 

[CJ_London]

I do myself a disservice.

So it works this time - what about next time?