openrecordset problem, runtime error 3091

S

sleek

Private Sub Item_No_AfterUpdate()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String


strsql = "SELECT * FROM [Job Items]" _
& "HAVING [Project]= '" & [Forms]![NDrawing Entry]![Project] & "' AND
[Partition]= '" & [Forms]![NDrawing Entry]![Partition] & "' AND [Section]= '"
& [Forms]![NDrawing Entry]![Sect] & "' AND [Sub Section]= '" &
[Forms]![NDrawing Entry]![Sub Sect] & "' AND [Drawing No]= '" &
[Forms]![NDrawing Entry]![Drawing No] & "' AND [Type]= '" & [Forms]![NDrawing
Entry]![Type] & "' AND [Item No]= '" & [Forms]![NDrawing Entry]![Drawing
Items].[Form]![Item No] & "';"



Set db = CurrentDb()
*Set rs = db.OpenRecordSet(strsql, dbOpenDynaset)

If Not rs.EOF Then
rs.MoveFirst
Me.Supplier = rs![Supplier]
Me.Quantity = rs![Quantity]
Me.Status = rs![Status]
Me.Due_Date = rs![Due Date]
Me.Category = rs![Category]
Me.W_O_No = rs![W/O No]
Me.P_O_No = rs![P/O No]
Me.P_O_Line_No = rs![P/O Line No]
End If

rs.Close
End Sub

basicly now the code is stuck at the (*) openrecordset call. It is returning
a 3091 runtime error, something about no grouping or aggregation. if i add a
grouped by call to the sql



strsql = "SELECT * FROM [Job Items]" _
& "Grouped BY [Item No] " _
& "HAVING [Project]= '" & [Forms]![NDrawing Entry]![Project] & "' AND
[Partition]= '" & [Forms]![NDrawing Entry]![Partition] & "' AND [Section]= '"
& [Forms]![NDrawing Entry]![Sect] & "' AND [Sub Section]= '" &
[Forms]![NDrawing Entry]![Sub Sect] & "' AND [Drawing No]= '" &
[Forms]![NDrawing Entry]![Drawing No] & "' AND [Type]= '" & [Forms]![NDrawing
Entry]![Type] & "' AND [Item No]= '" & [Forms]![NDrawing Entry]![Drawing
Items].[Form]![Item No] & "';"

i get 3131 runtime error (syntax error in from clause)

Help please, this piece of code has me going round in circles. i know the
actual code works as i put in a simplified query and it gave the correct
result. I have done my best to simplify the query, but it must be this
complex or i can not get the correct data from the table.

Can someone help me, is there something fundamentally wrong with the way i
have constructed the strsql???
 
N

Nikos Yannacopoulos

Sleek,

To begin with, if this is a direct paste of your code, then I suppose
the whole expression of the strSQL building snippet after [Job Items]" _
is in one line, just wrapped in the NG, right? If not, then VBA cannot
read it as one expression; all lines from the third down would be read.

Secondly, you are not taking proper care of spaces between the SQL parts.
In your first attempt, your strSQL string will read:
SELECT * FROM [Job Items]HAVING [Project]...etc.
You need to insert a space right after the table name, or right before
the HAVING keyword, inside the quotes.
In your second attempt, the strSQL string will read:
SELECT * FROM [Job Items]Grouped BY [Item No] ...etc.
Again, you need a space right after the table name or before the Grouped
keyword.
Generally speaking, this kind of problem is quite common when building
SQL strings in code; it is very helpful to include a:
Debug.Print strSQL
right afterwards, and before you try to do anything with it, so you get
the SQL expression printed in the immediate window, where such problems
are easy to spot.

Finally, in your strSQL expression you are treating all your fields as
strings; is this correct? I would expect [Item No], for instance, to be
numeric, as the name suggests.

HTH,
Nikos
Private Sub Item_No_AfterUpdate()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String


strsql = "SELECT * FROM [Job Items]" _
& "HAVING [Project]= '" & [Forms]![NDrawing Entry]![Project] & "' AND
[Partition]= '" & [Forms]![NDrawing Entry]![Partition] & "' AND [Section]= '"
& [Forms]![NDrawing Entry]![Sect] & "' AND [Sub Section]= '" &
[Forms]![NDrawing Entry]![Sub Sect] & "' AND [Drawing No]= '" &
[Forms]![NDrawing Entry]![Drawing No] & "' AND [Type]= '" & [Forms]![NDrawing
Entry]![Type] & "' AND [Item No]= '" & [Forms]![NDrawing Entry]![Drawing
Items].[Form]![Item No] & "';"



Set db = CurrentDb()
*Set rs = db.OpenRecordSet(strsql, dbOpenDynaset)

If Not rs.EOF Then
rs.MoveFirst
Me.Supplier = rs![Supplier]
Me.Quantity = rs![Quantity]
Me.Status = rs![Status]
Me.Due_Date = rs![Due Date]
Me.Category = rs![Category]
Me.W_O_No = rs![W/O No]
Me.P_O_No = rs![P/O No]
Me.P_O_Line_No = rs![P/O Line No]
End If

rs.Close
End Sub

basicly now the code is stuck at the (*) openrecordset call. It is returning
a 3091 runtime error, something about no grouping or aggregation. if i add a
grouped by call to the sql



strsql = "SELECT * FROM [Job Items]" _
& "Grouped BY [Item No] " _
& "HAVING [Project]= '" & [Forms]![NDrawing Entry]![Project] & "' AND
[Partition]= '" & [Forms]![NDrawing Entry]![Partition] & "' AND [Section]= '"
& [Forms]![NDrawing Entry]![Sect] & "' AND [Sub Section]= '" &
[Forms]![NDrawing Entry]![Sub Sect] & "' AND [Drawing No]= '" &
[Forms]![NDrawing Entry]![Drawing No] & "' AND [Type]= '" & [Forms]![NDrawing
Entry]![Type] & "' AND [Item No]= '" & [Forms]![NDrawing Entry]![Drawing
Items].[Form]![Item No] & "';"

i get 3131 runtime error (syntax error in from clause)

Help please, this piece of code has me going round in circles. i know the
actual code works as i put in a simplified query and it gave the correct
result. I have done my best to simplify the query, but it must be this
complex or i can not get the correct data from the table.

Can someone help me, is there something fundamentally wrong with the way i
have constructed the strsql???
Click to expand...
 
S

sleek

please excuse the spacing of the code, it was cut and pasted from another
post i made on a different website which couldnt help me. the formatting is
bad because of the differences in format style from site to site. this is a
better copy of the code, all the spaces are as required and it only spans two
lines as you suggested. its is a word wrap issue that moves it to more than
one like when posting on this site.

strsql = "SELECT * FROM [Job Items]" _
& "Grouped BY [Item No] " _
& "HAVING [Project]= '" & [Forms]![NDrawing Entry]![Project] & "' AND
[Partition]= '" & [Forms]![NDrawing Entry]![Partition] & "' AND [Section]= '"
& [Forms]![NDrawing Entry]![Sect] & "' AND [Sub Section]= '" &
[Forms]![NDrawing Entry]![Sub Sect] & "' AND [Drawing No]= '" &
[Forms]![NDrawing Entry]![Drawing No] & "' AND [Type]= '" & [Forms]![NDrawing
Entry]![Type] & "' AND [Item No]= '" & [Forms]![NDrawing Entry]![Drawing
Items].[Form]![Item No] & "';"


Nikos Yannacopoulos said:
Sleek,

To begin with, if this is a direct paste of your code, then I suppose
the whole expression of the strSQL building snippet after [Job Items]" _
is in one line, just wrapped in the NG, right? If not, then VBA cannot
read it as one expression; all lines from the third down would be read.

Secondly, you are not taking proper care of spaces between the SQL parts.
In your first attempt, your strSQL string will read:
SELECT * FROM [Job Items]HAVING [Project]...etc.
You need to insert a space right after the table name, or right before
the HAVING keyword, inside the quotes.
In your second attempt, the strSQL string will read:
SELECT * FROM [Job Items]Grouped BY [Item No] ...etc.
Again, you need a space right after the table name or before the Grouped
keyword.
Generally speaking, this kind of problem is quite common when building
SQL strings in code; it is very helpful to include a:
Debug.Print strSQL
right afterwards, and before you try to do anything with it, so you get
the SQL expression printed in the immediate window, where such problems
are easy to spot.

Finally, in your strSQL expression you are treating all your fields as
strings; is this correct? I would expect [Item No], for instance, to be
numeric, as the name suggests.

HTH,
Nikos
Private Sub Item_No_AfterUpdate()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String


strsql = "SELECT * FROM [Job Items]" _
& "HAVING [Project]= '" & [Forms]![NDrawing Entry]![Project] & "' AND
[Partition]= '" & [Forms]![NDrawing Entry]![Partition] & "' AND [Section]= '"
& [Forms]![NDrawing Entry]![Sect] & "' AND [Sub Section]= '" &
[Forms]![NDrawing Entry]![Sub Sect] & "' AND [Drawing No]= '" &
[Forms]![NDrawing Entry]![Drawing No] & "' AND [Type]= '" & [Forms]![NDrawing
Entry]![Type] & "' AND [Item No]= '" & [Forms]![NDrawing Entry]![Drawing
Items].[Form]![Item No] & "';"



Set db = CurrentDb()
*Set rs = db.OpenRecordSet(strsql, dbOpenDynaset)

If Not rs.EOF Then
rs.MoveFirst
Me.Supplier = rs![Supplier]
Me.Quantity = rs![Quantity]
Me.Status = rs![Status]
Me.Due_Date = rs![Due Date]
Me.Category = rs![Category]
Me.W_O_No = rs![W/O No]
Me.P_O_No = rs![P/O No]
Me.P_O_Line_No = rs![P/O Line No]
End If

rs.Close
End Sub

basicly now the code is stuck at the (*) openrecordset call. It is returning
a 3091 runtime error, something about no grouping or aggregation. if i add a
grouped by call to the sql



strsql = "SELECT * FROM [Job Items]" _
& "Grouped BY [Item No] " _
& "HAVING [Project]= '" & [Forms]![NDrawing Entry]![Project] & "' AND
[Partition]= '" & [Forms]![NDrawing Entry]![Partition] & "' AND [Section]= '"
& [Forms]![NDrawing Entry]![Sect] & "' AND [Sub Section]= '" &
[Forms]![NDrawing Entry]![Sub Sect] & "' AND [Drawing No]= '" &
[Forms]![NDrawing Entry]![Drawing No] & "' AND [Type]= '" & [Forms]![NDrawing
Entry]![Type] & "' AND [Item No]= '" & [Forms]![NDrawing Entry]![Drawing
Items].[Form]![Item No] & "';"

i get 3131 runtime error (syntax error in from clause)

Help please, this piece of code has me going round in circles. i know the
actual code works as i put in a simplified query and it gave the correct
result. I have done my best to simplify the query, but it must be this
complex or i can not get the correct data from the table.

Can someone help me, is there something fundamentally wrong with the way i
have constructed the strsql???
Click to expand...
Click to expand...
 
S

sleek

Problem now solved

this is the final code, should anyone else want to see how it worked

Private Sub Item_No_AfterUpdate()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String


strsql = "SELECT * FROM [Job Items] " _
& "WHERE [Project]= '" & [Forms]![NDrawing Entry]![Project] & "' AND
[Partition]= '" & [Forms]![NDrawing Entry]![Partition] & "' AND [Section]= '"
& [Forms]![NDrawing Entry]![Sect] & "' AND [Sub Section]= '" &
[Forms]![NDrawing Entry]![Sub Sect] & "' AND [Drawing No]= '" &
[Forms]![NDrawing Entry]![Drawing No] & "' AND [Type]= '" & [Forms]![NDrawing
Entry]![Type] & "' AND [Item No]= '" & [Forms]![NDrawing Entry]![Drawing
Items].[Form]![Item No] & "' "


Set db = CurrentDb()
Set rs = db.OpenRecordSet(strsql, dbOpenDynaset)

If Not rs.EOF Then
rs.MoveFirst
Me.Job = rs![Job]
Me.[Item Name] = rs![Item Name]
Me.Supplier = rs![Supplier]
Me.Status = rs![Status]
Me.Quantity = rs![Quantity]
Me.Status = rs![Status]
Me.Due_Date = rs![Due Date]
Me.Category = rs![Category]
Me.W_O_No = rs![W/O No]
Me.P_O_No = rs![P/O No]
Me.P_O_Line_No = rs![P/O Line No]
Me.Description = rs![Description]
Me.Allocated = rs![Allocated]
Me.Complete = rs![Complete]
End If

rs.Close
End Sub

sleek said:
please excuse the spacing of the code, it was cut and pasted from another
post i made on a different website which couldnt help me. the formatting is
bad because of the differences in format style from site to site. this is a
better copy of the code, all the spaces are as required and it only spans two
lines as you suggested. its is a word wrap issue that moves it to more than
one like when posting on this site.

strsql = "SELECT * FROM [Job Items]" _
& "Grouped BY [Item No] " _
& "HAVING [Project]= '" & [Forms]![NDrawing Entry]![Project] & "' AND
[Partition]= '" & [Forms]![NDrawing Entry]![Partition] & "' AND [Section]= '"
& [Forms]![NDrawing Entry]![Sect] & "' AND [Sub Section]= '" &
[Forms]![NDrawing Entry]![Sub Sect] & "' AND [Drawing No]= '" &
[Forms]![NDrawing Entry]![Drawing No] & "' AND [Type]= '" & [Forms]![NDrawing
Entry]![Type] & "' AND [Item No]= '" & [Forms]![NDrawing Entry]![Drawing
Items].[Form]![Item No] & "';"


Nikos Yannacopoulos said:
Sleek,

To begin with, if this is a direct paste of your code, then I suppose
the whole expression of the strSQL building snippet after [Job Items]" _
is in one line, just wrapped in the NG, right? If not, then VBA cannot
read it as one expression; all lines from the third down would be read.

Secondly, you are not taking proper care of spaces between the SQL parts.
In your first attempt, your strSQL string will read:
SELECT * FROM [Job Items]HAVING [Project]...etc.
You need to insert a space right after the table name, or right before
the HAVING keyword, inside the quotes.
In your second attempt, the strSQL string will read:
SELECT * FROM [Job Items]Grouped BY [Item No] ...etc.
Again, you need a space right after the table name or before the Grouped
keyword.
Generally speaking, this kind of problem is quite common when building
SQL strings in code; it is very helpful to include a:
Debug.Print strSQL
right afterwards, and before you try to do anything with it, so you get
the SQL expression printed in the immediate window, where such problems
are easy to spot.

Finally, in your strSQL expression you are treating all your fields as
strings; is this correct? I would expect [Item No], for instance, to be
numeric, as the name suggests.

HTH,
Nikos
Private Sub Item_No_AfterUpdate()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String


strsql = "SELECT * FROM [Job Items]" _
& "HAVING [Project]= '" & [Forms]![NDrawing Entry]![Project] & "' AND
[Partition]= '" & [Forms]![NDrawing Entry]![Partition] & "' AND [Section]= '"
& [Forms]![NDrawing Entry]![Sect] & "' AND [Sub Section]= '" &
[Forms]![NDrawing Entry]![Sub Sect] & "' AND [Drawing No]= '" &
[Forms]![NDrawing Entry]![Drawing No] & "' AND [Type]= '" & [Forms]![NDrawing
Entry]![Type] & "' AND [Item No]= '" & [Forms]![NDrawing Entry]![Drawing
Items].[Form]![Item No] & "';"



Set db = CurrentDb()
*Set rs = db.OpenRecordSet(strsql, dbOpenDynaset)

If Not rs.EOF Then
rs.MoveFirst
Me.Supplier = rs![Supplier]
Me.Quantity = rs![Quantity]
Me.Status = rs![Status]
Me.Due_Date = rs![Due Date]
Me.Category = rs![Category]
Me.W_O_No = rs![W/O No]
Me.P_O_No = rs![P/O No]
Me.P_O_Line_No = rs![P/O Line No]
End If

rs.Close
End Sub

basicly now the code is stuck at the (*) openrecordset call. It is returning
a 3091 runtime error, something about no grouping or aggregation. if i add a
grouped by call to the sql



strsql = "SELECT * FROM [Job Items]" _
& "Grouped BY [Item No] " _
& "HAVING [Project]= '" & [Forms]![NDrawing Entry]![Project] & "' AND
[Partition]= '" & [Forms]![NDrawing Entry]![Partition] & "' AND [Section]= '"
& [Forms]![NDrawing Entry]![Sect] & "' AND [Sub Section]= '" &
[Forms]![NDrawing Entry]![Sub Sect] & "' AND [Drawing No]= '" &
[Forms]![NDrawing Entry]![Drawing No] & "' AND [Type]= '" & [Forms]![NDrawing
Entry]![Type] & "' AND [Item No]= '" & [Forms]![NDrawing Entry]![Drawing
Items].[Form]![Item No] & "';"

i get 3131 runtime error (syntax error in from clause)

Help please, this piece of code has me going round in circles. i know the
actual code works as i put in a simplified query and it gave the correct
result. I have done my best to simplify the query, but it must be this
complex or i can not get the correct data from the table.

Can someone help me, is there something fundamentally wrong with the way i
have constructed the strsql???
Click to expand...
Click to expand...
Click to expand...
 
S

sleek

no,

i was using having instead of where, that was the major issue. as i said the
spacing was not a problem, it was just a result of posting the code to this
discussion, i.e the site formated the code as such, it isnt actually written
as such

Nikos Yannacopoulos said:
So it was the missing space after [Job Items], right?
Problem now solved

this is the final code, should anyone else want to see how it worked

Private Sub Item_No_AfterUpdate()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String


strsql = "SELECT * FROM [Job Items] " _
& "WHERE [Project]= '" & [Forms]![NDrawing Entry]![Project] & "' AND
[Partition]= '" & [Forms]![NDrawing Entry]![Partition] & "' AND [Section]= '"
& [Forms]![NDrawing Entry]![Sect] & "' AND [Sub Section]= '" &
[Forms]![NDrawing Entry]![Sub Sect] & "' AND [Drawing No]= '" &
[Forms]![NDrawing Entry]![Drawing No] & "' AND [Type]= '" & [Forms]![NDrawing
Entry]![Type] & "' AND [Item No]= '" & [Forms]![NDrawing Entry]![Drawing
Items].[Form]![Item No] & "' "


Set db = CurrentDb()
Set rs = db.OpenRecordSet(strsql, dbOpenDynaset)

If Not rs.EOF Then
rs.MoveFirst
Me.Job = rs![Job]
Me.[Item Name] = rs![Item Name]
Me.Supplier = rs![Supplier]
Me.Status = rs![Status]
Me.Quantity = rs![Quantity]
Me.Status = rs![Status]
Me.Due_Date = rs![Due Date]
Me.Category = rs![Category]
Me.W_O_No = rs![W/O No]
Me.P_O_No = rs![P/O No]
Me.P_O_Line_No = rs![P/O Line No]
Me.Description = rs![Description]
Me.Allocated = rs![Allocated]
Me.Complete = rs![Complete]
End If

rs.Close
End Sub

:

please excuse the spacing of the code, it was cut and pasted from another
post i made on a different website which couldnt help me. the formatting is
bad because of the differences in format style from site to site. this is a
better copy of the code, all the spaces are as required and it only spans two
lines as you suggested. its is a word wrap issue that moves it to more than
one like when posting on this site.

strsql = "SELECT * FROM [Job Items]" _
& "Grouped BY [Item No] " _
& "HAVING [Project]= '" & [Forms]![NDrawing Entry]![Project] & "' AND
[Partition]= '" & [Forms]![NDrawing Entry]![Partition] & "' AND [Section]= '"
& [Forms]![NDrawing Entry]![Sect] & "' AND [Sub Section]= '" &
[Forms]![NDrawing Entry]![Sub Sect] & "' AND [Drawing No]= '" &
[Forms]![NDrawing Entry]![Drawing No] & "' AND [Type]= '" & [Forms]![NDrawing
Entry]![Type] & "' AND [Item No]= '" & [Forms]![NDrawing Entry]![Drawing
Items].[Form]![Item No] & "';"


:


Sleek,

To begin with, if this is a direct paste of your code, then I suppose
the whole expression of the strSQL building snippet after [Job Items]" _
is in one line, just wrapped in the NG, right? If not, then VBA cannot
read it as one expression; all lines from the third down would be read.

Secondly, you are not taking proper care of spaces between the SQL parts.
In your first attempt, your strSQL string will read:
SELECT * FROM [Job Items]HAVING [Project]...etc.
You need to insert a space right after the table name, or right before
the HAVING keyword, inside the quotes.
In your second attempt, the strSQL string will read:
SELECT * FROM [Job Items]Grouped BY [Item No] ...etc.
Again, you need a space right after the table name or before the Grouped
keyword.
Generally speaking, this kind of problem is quite common when building
SQL strings in code; it is very helpful to include a:
Debug.Print strSQL
right afterwards, and before you try to do anything with it, so you get
the SQL expression printed in the immediate window, where such problems
are easy to spot.

Finally, in your strSQL expression you are treating all your fields as
strings; is this correct? I would expect [Item No], for instance, to be
numeric, as the name suggests.

HTH,
Nikos

sleek wrote:

Private Sub Item_No_AfterUpdate()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String


strsql = "SELECT * FROM [Job Items]" _
& "HAVING [Project]= '" & [Forms]![NDrawing Entry]![Project] & "' AND
[Partition]= '" & [Forms]![NDrawing Entry]![Partition] & "' AND [Section]= '"
& [Forms]![NDrawing Entry]![Sect] & "' AND [Sub Section]= '" &
[Forms]![NDrawing Entry]![Sub Sect] & "' AND [Drawing No]= '" &
[Forms]![NDrawing Entry]![Drawing No] & "' AND [Type]= '" & [Forms]![NDrawing
Entry]![Type] & "' AND [Item No]= '" & [Forms]![NDrawing Entry]![Drawing
Items].[Form]![Item No] & "';"



Set db = CurrentDb()
*Set rs = db.OpenRecordSet(strsql, dbOpenDynaset)

If Not rs.EOF Then
rs.MoveFirst
Me.Supplier = rs![Supplier]
Me.Quantity = rs![Quantity]
Me.Status = rs![Status]
Me.Due_Date = rs![Due Date]
Me.Category = rs![Category]
Me.W_O_No = rs![W/O No]
Me.P_O_No = rs![P/O No]
Me.P_O_Line_No = rs![P/O Line No]
End If

rs.Close
End Sub

basicly now the code is stuck at the (*) openrecordset call. It is returning
a 3091 runtime error, something about no grouping or aggregation. if i add a
grouped by call to the sql



strsql = "SELECT * FROM [Job Items]" _
& "Grouped BY [Item No] " _
& "HAVING [Project]= '" & [Forms]![NDrawing Entry]![Project] & "' AND
[Partition]= '" & [Forms]![NDrawing Entry]![Partition] & "' AND [Section]= '"
& [Forms]![NDrawing Entry]![Sect] & "' AND [Sub Section]= '" &
[Forms]![NDrawing Entry]![Sub Sect] & "' AND [Drawing No]= '" &
[Forms]![NDrawing Entry]![Drawing No] & "' AND [Type]= '" & [Forms]![NDrawing
Entry]![Type] & "' AND [Item No]= '" & [Forms]![NDrawing Entry]![Drawing
Items].[Form]![Item No] & "';"

i get 3131 runtime error (syntax error in from clause)

Help please, this piece of code has me going round in circles. i know the
actual code works as i put in a simplified query and it gave the correct
result. I have done my best to simplify the query, but it must be this
complex or i can not get the correct data from the table.

Can someone help me, is there something fundamentally wrong with the way i
have constructed the strsql???
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

run time error 3075. 1
Help with run-time error '2424' and '3265' 4
user defined type not defined error 5
OpenRecordSet Error 3
Runtime Error 3070 7
Runtime error 2001 2
data base connection runtime error 3211 2
OpenRecordset 4

Top