VBA SQL Query Runtime Error 3075 (1 Viewer)

Oreynolds

Member
Local time
Today, 16:03
Joined
Apr 11, 2020
Messages
157
Hi all, I am a new member to the forum having recently signed up. I've used the forum as a useful resource recently and keen to get involved! Initially I wonder if anyone can help.

I have a cmd button on a form called "Orders". When pressed I want it to run an SQL query to create a record set that ultimately provides a list of all products related to the master "Orders" record that are contained within multiple quotes that are linked to the master "Orders" record.

The query SQL copied directly from a query I have written which produces the exact recordset I need is as follows:

SELECT [Quote Details].QuoteID, [Quote Details].ProductID, Products.DatasheetPath, Quotations.OrderNumber
FROM ([Quote Details] LEFT JOIN Products ON [Quote Details].ProductID = Products.ProductID) LEFT JOIN Quotations ON [Quote Details].QuoteID = Quotations.QuoteID
GROUP BY [Quote Details].QuoteID, [Quote Details].ProductID, Products.DatasheetPath, Quotations.OrderNumber
HAVING (((Quotations.OrderNumber)=[Forms]![Orders]![txtOrderNumber]));

I have then converted this to VBA as follows:

strSQL = " SELECT [Quote Details].QuoteID, [Quote Details].ProductID, Products.DatasheetPath, Products.Test3, Quotations.OrderNumber " & _
" FROM ([Quote Details] LEFT JOIN Products ON [Quote Details].ProductID = Products.ProductID) LEFT JOIN Quotations ON [Quote Details].QuoteID = Quotations.QuoteID " & _
" GROUP BY [Quote Details].QuoteID, [Quote Details].ProductID, Products.DatasheetPath, Products.Test3, Quotations.OrderNumber " & _
" WHERE Quotations.OrderNumber ='" & Me.txtOrderNumber & "';"

The code runs but produces a runtime 3075 error as below:

1586623650803.png


The data in the Quotations.OrderNumber field is text and the value I'm trying to filter to is "J0032301". The data in the Orders form field txtOrderNumber is also text. I have tried so many things to try and resolve but to no avail........!

Can anyone offer any help?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:03
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF! Try doing a Debug.Print strSQL and then copy and paste the result in the query designer to see where the problem is.
 

Oreynolds

Member
Local time
Today, 16:03
Joined
Apr 11, 2020
Messages
157
Thanks for the suggestion. I did that and got the following in the immediate window:

Code Tags Added by UG
Please use Code Tags when posting VBA Code
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/
Code:
SELECT [Quote Details].QuoteID, [Quote Details].ProductID, Products.DatasheetPath, Products.Test3, Quotations.OrderNumber
FROM ([Quote Details] LEFT JOIN Products ON [Quote Details].ProductID = Products.ProductID) LEFT JOIN Quotations ON [Quote Details].QuoteID = Quotations.QuoteID 
GROUP BY [Quote Details].QuoteID, [Quote Details].ProductID, Products.DatasheetPath, Products.Test3, Quotations.OrderNumber
WHERE Quotations.OrderNumber ='J0032301';
I then pasted into the query SQL designer but still got the following error....?

1586624182042.png


Any ideas?
 
Last edited by a moderator:

plog

Banishment Pending
Local time
Today, 11:03
Joined
May 11, 2011
Messages
11,611
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

That's the order those 6 keywords have to appear in. Your WHERE is after your GROUP BY
 

Oreynolds

Member
Local time
Today, 16:03
Joined
Apr 11, 2020
Messages
157
Hi, thanks for that, I've changed that around and that has solved that, next question:

I then want to cycle through the records and use the file location URL in the DatasheetPath field to copy the file to a new location. I wrote the following:

Code Tags Added by UG
Please use Code Tags when posting VBA Code
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/
Code:
Dim FileSystemObject As String
Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
      If Not .BOF And Not .EOF Then    
        .MoveLast
        .MoveFirst
       
        While (Not .EOF)           
           
            FileSystemObject.CopyFile "[DatasheetPath]", "c:\tempfolder\Test.pdf"
           
            .MoveNext

        Wend
       
    End If
   
    .Close

But get the following error:

1586626153679.png


I've never tried and file copy operations before so this is new to me....!
 
Last edited by a moderator:

Gasman

Enthusiastic Amateur
Local time
Today, 16:03
Joined
Sep 21, 2011
Messages
14,044
I would expect you need to use the value of DatasheetPath and not the name DatasheetPath ?
Also I would expect you wish to copy to a folder?, at present it appears you are copying each file to Test.pdf?

Try
Code:
FileSystemObject.CopyFile rs!DatasheetPath, "c:\tempfolder\"

Does the DatasheetPath have spaces at all?

HTH
 

Oreynolds

Member
Local time
Today, 16:03
Joined
Apr 11, 2020
Messages
157
Thanks so much, with your input and a few other changes I have got it working to a fashion with the following code:

While (Not .EOF)

Dim fsObject As Object
Set fsObject = CreateObject("Scripting.FileSystemObject")
fsObject.CopyFile rs!DatasheetPath, "c:\tempfolderB\"
Set fsObject = Nothing

.MoveNext

Wend

In answer to your question no the test in the DatasheetPath does not have any spaces, example in my test data:

c:\tempfoldera\test.txt

The next issue I have is there will be products in the recordset which have NULL values in the DatasheetPath field. When there is a NULL the code fails but when I put values in them all it works. I cannot risk this so do you know how I would mitigate for a NULL in the looping code?

Thanks again for everyone's help!
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 12:03
Joined
Apr 27, 2015
Messages
6,281
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

That's the order those 6 keywords have to appear in. Your WHERE is after your GROUP BY
I learned this the hard way...just today!
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:03
Joined
Sep 21, 2011
Messages
14,044
Thanks so much, with your input and a few other changes I have got it working to a fashion with the following code:

While (Not .EOF)

Dim fsObject As Object
Set fsObject = CreateObject("Scripting.FileSystemObject")
fsObject.CopyFile rs!DatasheetPath, "c:\tempfolderB\"
Set fsObject = Nothing

.MoveNext

Wend

In answer to your question no the test in the DatasheetPath does not have any spaces, example in my test data:

c:\tempfoldera\test.txt

The next issue I have is there will be products in the recordset which have NULL values in the DatasheetPath field. When there is a NULL the code fails but when I put values in them all it works. I cannot risk this so do you know how I would mitigate for a NULL in the looping code?

Thanks again for everyone's help!
Yes.
1. I would ignore them in the first instance with code in the SQL to ignore them?
2. If not that way, look at the NZ() function using "" as a replacement, then test for length of DataSheetpath before trying to save anything.?

For me, option 1 would be the way I would approach it.?

I would not keep creating/destroying the FileSystemObject all the time.
Create it once before the loop and destroy after the loop.?
 

Oreynolds

Member
Local time
Today, 16:03
Joined
Apr 11, 2020
Messages
157
Nearly there, One last problem if anyone can help....?!?

I have got it half working as per the following code. If the response to the Folderexists function is false then it creates the directory and then copies all of the files as required.

However, if the response to Folderexists function is True despite it running identical code it returns an error as follows:

1586638073463.png


For clarity if the directory exists I DO want it to rerun the query, and then copy and potentially overcopy copy the files if its been run before.

Any assistance would be greatly appreciated! Thanks

CODE:

Private Sub cmdCreateDatasheetsFolder_Click()

'On Error GoTo ErrorHandler

Dim strSQL As String
'Dim FileSystemObject As String
Dim fsObject As Object
Dim rs As DAO.Recordset
Dim NewPath As String

Dim Cancel As Integer
Dim intStyle As String
Dim strTitle As String
Dim strMsg As String
Dim Foldername As String
Dim createpath As String
Dim X, I As Integer

Foldername = "F:\SFA 20" & Right(Me.OrderDate, 2) & "\Running projects" & "\" & Me.CustomerID.Column(1) & "\J" & Me.OrderID & " " & Me.SiteName.Column(1) & "\OM Manual\Datasheets"


If FolderExists(Foldername) = False Then

Foldername = "F:\SFA 20" & Right(Me.OrderDate, 2) & "\Running projects"
createpath = Foldername & "\" & Me.CustomerID.Column(1) & "\J" & Me.OrderID & " " & Me.SiteName.Column(1) & "\OM Manual\Datasheets\"

Call MakeDirectory(createpath)

'MkDir (createpath)

Foldername = createpath

strSQL = " SELECT [Quote Details].QuoteID, [Quote Details].ProductID, Products.DatasheetPath, Quotations.OrderNumber " & _
" FROM ([Quote Details] LEFT JOIN Products ON [Quote Details].ProductID = Products.ProductID) LEFT JOIN Quotations ON [Quote Details].QuoteID = Quotations.QuoteID " & _
" GROUP BY [Quote Details].QuoteID, [Quote Details].ProductID, Products.DatasheetPath, Quotations.OrderNumber " & _
" HAVING (((Quotations.OrderNumber)='" & Me.txtOrderNumber & "'));"

Set rs = CurrentDb.OpenRecordset(strSQL)
Set fsObject = CreateObject("Scripting.FileSystemObject")

NewPath = Foldername

With rs

If Not .BOF And Not .EOF Then

.MoveLast
.MoveFirst

While (Not .EOF)

If IsNull(rs!DatasheetPath) Then
.MoveNext
Else
'fsObject.CopyFile rs!DatasheetPath, "c:\tempfolderB\"
fsObject.CopyFile rs!DatasheetPath, NewPath
End If
.MoveNext

Wend

End If

.Close

End With

Shell "C:\WINDOWS\explorer.exe """ & Foldername & "", vbNormalFocus

Else

strSQL = " SELECT [Quote Details].QuoteID, [Quote Details].ProductID, Products.DatasheetPath, Quotations.OrderNumber " & _
" FROM ([Quote Details] LEFT JOIN Products ON [Quote Details].ProductID = Products.ProductID) LEFT JOIN Quotations ON [Quote Details].QuoteID = Quotations.QuoteID " & _
" GROUP BY [Quote Details].QuoteID, [Quote Details].ProductID, Products.DatasheetPath, Quotations.OrderNumber " & _
" HAVING (((Quotations.OrderNumber)='" & Me.txtOrderNumber & "'));"

Set rs = CurrentDb.OpenRecordset(strSQL)
Set fsObject = CreateObject("Scripting.FileSystemObject")

NewPath = Foldername

With rs

If Not .BOF And Not .EOF Then

.MoveLast
.MoveFirst

While (Not .EOF)

If IsNull(rs!DatasheetPath) Then
.MoveNext
Else
'fsObject.CopyFile rs!DatasheetPath, "c:\tempfolderB\"
fsObject.CopyFile rs!DatasheetPath, NewPath
End If
.MoveNext

Wend

End If

.Close

End With

End If

ExitSub:
Set rs = Nothing
Set fsObject = Nothing

Exit Sub
ErrorHandler:
MsgBox "Error"
Resume ExitSub
End Sub
 

Oreynolds

Member
Local time
Today, 16:03
Joined
Apr 11, 2020
Messages
157
Yes.
1. I would ignore them in the first instance with code in the SQL to ignore them?
2. If not that way, look at the NZ() function using "" as a replacement, then test for length of DataSheetpath before trying to save anything.?

For me, option 1 would be the way I would approach it.?

I would not keep creating/destroying the FileSystemObject all the time.
Create it once before the loop and destroy after the loop.?

Thanks for this, I took note and amended my code as per last post. Still one last issue if you have any ideas on it (see previous post), thanks
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:03
Joined
Sep 21, 2011
Messages
14,044
You will need to post your code within code tags and suitably indented for it to make any sense to me. :(

Plus there is no reason to duplicate code.?
You are either just copying, or creating a folder then copying.? So the create folder is within one IF statement, then just copy.?
 

Oreynolds

Member
Local time
Today, 16:03
Joined
Apr 11, 2020
Messages
157
Also some people put the code in a neat box how do you do that?
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:03
Joined
Sep 21, 2011
Messages
14,044
Sorry, new to this forum stuff! How do you do the code tags?
See my signature?

Also just where are you trying to create these folders?
I ask as it is likely you could be able to create a folder and add to it?, but if someone else created that folder you would not be, sort of Linux issue.?
I am not sure about permissions structure, but I am sure one could have folders under a main folder, but private from other users.?

Can you enter/copy to those folders manually?
 

onur_can

Active member
Local time
Today, 09:03
Joined
Oct 4, 2015
Messages
180
Code:
WHERE Quotations.OrderNumber ='J0032301';

' J0032301 '
not so
'J0032301'
there should be no space[/CODE]
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:03
Joined
Sep 21, 2011
Messages
14,044
Also some people put the code in a neat box how do you do that?
That would be using the code tags.
You can manually do the same using Code and /Code tags either side of your code ? However you also need to enclose them with [].
If I try that now, they disappear (as they should), so not sure how to show them without them being activated.

Use the .. option first and you will see the structure.
 

Oreynolds

Member
Local time
Today, 16:03
Joined
Apr 11, 2020
Messages
157
Hi sorry to sound thick but am new to forum and have been told to post code in a specific way which I am still not clear on.

Do I just put a:

<

followed by pasting code direct form VBA editor then followed by a:

>

?
 

Users who are viewing this thread

Top Bottom