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:
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?
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:
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?