Run Time Error 3061

[GabberGod]

Help

im currently trying to create a piece of vba code that will open a query get the single record returned by the query and copy the record from the query to the fields of a form which is a sub-form on another form. Basicly when i try and run the code (see below) it stops at the * with a run time error 3061, basicly i was able to pin point it back to the query. The query needs 6 fields of info from the main form and 1 field from the subform in question.

Basicly the error is telling me code is expecting 7 parameters (the 7 required fields in the query) and is getting 0. do i need to pass the fields from the forms into the function as you would in say c++, if so how? otherwise what am i missing?

Private Sub Item_No_AfterUpdate()
Set db = CurrentDb()
* Set rs = db.OpenRecordSet("item_incrementer")
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 Sub

 

[Edski]

Hi Gabber,
Few things:

1. Don't say Set strsql = "...
Just say strSQL = "...

2. Don't worry about the trailing ';'. It's a dumb quirk of Access.

3. I think you need to enclose every variable that has a space within square brackets [].

4. Same for variables with a slash '/' (not 100% sure bout this though).

5. If you are selecting EVERY variable from the table then just say strSQL = "SELECT * FROM ...

6. Make sure every '&' has a space before and after.

Kepp trying I think you're nearly there.

 

[GabberGod]

im not selecting every field, or i would have use select *.

Thanks so much for your help im booked in to do a course on this stuff in a few weeks, but i still need to get this done before then.

 

[Zion7]

GabberGod, Edski hit the nail on the head.
The "Set" statement is for objects, string data types need only an equal sign, Thus the "Object required" error....

 

[GabberGod]

Private Sub Item_No_AfterUpdate()

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


strsql = "SELECT * 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] & " AND Rev= " & [Forms]![NDrawing Entry]![Revision] & ";"

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

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

Ok Now im getting a new error.

Now im getting a run time error 3075.

Syntax error (missing operator) in query expression. It then goes on to list the 7 variables along with their values in sql format, but for some reason the word select is omitted from the from of the code

i.e

* having project =wbf AND partition = A AND...



* having

 

[Zion7]

In your "Having" clauses, are your criteria properly syntaxed?

For Strings, you need to surround with quotes, Dates must have hash marks, integers nothing..

If a string...

HAVING Project= '" & [Forms]![NDrawing Entry]![Project] & "' AND Partition

(notice the 2, single quotes)

 

[GabberGod]

is there somewhere i can read about the syntax of all this stuff. your help is great and greatly appreciated but its not really helping me understand all this any better.

why single and double quotes? what is the effect? where do i use each?

 

[Zion7]

It depends on your data type. I personally, don't know how VBA, or Access processes the information, but, am aware that basically, there are three conditions, depending on the data type being analysed.

"Having txtDateStarted =#" & dDate & "# And ....

"WHERE pkCountryID =" & intID & " And ....

"WHERE txtCountry ='" & strCountry & "' And ...

..sometimes, where there might be an unexpected punctuation in your STRING, 2 double quotes are used...

eg; Johnson's

"WHERE txtStoreName =""" & strStore & """ And...

...if this is confusing you, replace the original brackets.

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]))

 

[GabberGod]

but it didnt work at all with the original brackets, said it was missing all 7 parameters required to run the query.

This time it actually shows me the parameters in the error message and they are the correct parameters.

 

[Zion7]

Sorry GabberGod, my mistake. I got a bit confused when & where brackets are acceptable.

..thus back to the quotes. determine the arguments, whet date type they are (numbers, dates or strings), surround with either single quotes, or hash marks, or leave as is.

...if you like, send me the actual values, of each criterion (for one record), & I'll show you the syntax

you should be sending me 7 different values, (with which field in the SQL, they represent)

 

[Edski]

Gabber,
Post your code again with Zion's suggestions. I'm sure you need to use either single quotes or double quotes for most of your variables.

It should look something like this:

strsql = "SELECT * HAVING Project = '" & [Forms]![NDrawing Entry]![Project] & "' AND Partition = '" & [Forms]![NDrawing Entry]![Partition] & "' And ...

That is, every Text variable must be surrounded by either a single quote or, if you believe that some of the variables may contain a single quote, use the double quote (or Chr(34), which is the ANSI string code for "").

 

[GabberGod]

Ok so i think i have the sql sorted out now.

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

 

[traingamer]

Unless you want Sums and Averages and whatnot, you probably want to use ORDER BY rather than GROUP BY.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PHV]

Something like this ?
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] & "' " _
& "GROUP BY [Item No]"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GabberGod]

My hero

Thanks PHV

what was i doing wrong?

p.s for the record you cant group by when selecting *

thats why i was having such trouble understanding my problem, how could no grouping be the error when its not possible to do so in this situation???

 

[Edski]

Because you used the 'HAVING' clause, which only works in combination with fields that you've GROUPed BY.

 

[GabberGod]

I see, I will keep that in mind for the future, Thank YOU