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

 

[Zion7]

Where do you declare, your variables?

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

 

[GabberGod]

I just added those two lines

the code now stops at the * with the error user defined type not defined

*Private Sub Item_No_AfterUpdate()

Dim db As DAO.Database
Dim rs As DAO.Recordset
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]

You've got to got to a record in the recordset first, usually the first one, but before you do that you have to check that the recordset is not empty. And don't forget to close your recordset.

If Not rs.EOF Then
   rs.MoveFirst
   Me.Supplier = rs![Supplier]
   .
   .
   .
End If
rs.Close

 

[Edski]

Oh and the error occurs because you haven't referenced the DAO Object library.
(Tools...References...)

 

[GabberGod]

Ok so Ive now got this code as per your instructions.

It still produces the same user defined type not defined error as above stopping at the *

*Private Sub Item_No_AfterUpdate()

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

Set db = CurrentDb()
Set rs = db.OpenRecordSet("item_incrementer")

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

 

[Zion7]

Did you set your references for the DAO Library, as Edski pointed out?


BTW, don't forget
rs.Close: Set rs = Nothing

 

[GabberGod]

I dont know what that means???

ive never worked in vb or vba before

 

[Edski]

Hi GabberGod,
In the VBE window (<ALT><F11>) on the File menu click Tools...References... and select the DAO 3.6 Object Library from the long list. Push it up as far to the top as you can.

 

[GabberGod]

ok i figured that step out, for the record it

microsoft dao 3.6 object library

that one word would have helped me find it out of the list of like infinty choices so much faster

anyway I did that and now its back to the run time error 3061 error with the 7 parameters as mentioned in the original post. does anyone have any further help with this?

as it really seems i didnt need to add in the dao stuff explicitly.

 

[Edski]

Gabber,
It took you 4 days to find it???

Can you run the query from the database window instead of VBA? If not then post the SQL code from the query so we can see what's wrong.
All the best.

 

[GabberGod]

no it was a public holiday in australia, for easter

i havent been at work for 4 days, i refuse to do work at home, hence i found it this this afternoon when i finished my all morning meeting

how would i run the query from the db window considering what i am trying to do?

there is nothing actually wrong with the sql code. i can run the sql no problems and it gives me the results i am asking for, i just cant get the form to accept the info from the query, which is what i am trying to do if you read the original post.

here is the sql

SELECT [Job Items].Project, [Job Items].Partition, [Job Items].Section, [Job Items].[Sub Section], [Job Items].[Drawing No], Max([Job Items].Revision) AS MaxOfRevision, [Job Items].Type, [Job Items].Job, [Job Items].[Item No], [Job Items].[Item Name], [Job Items].Quantity, [Job Items].Supplier, [Job Items].Status, [Job Items].[Due Date], [Job Items].Category, [Job Items].[W/O No], [Job Items].[P/O No], [Job Items].[P/O Line No], [Job Items].Description, [Job Items].Allocated
FROM [Job Items]
GROUP BY [Job Items].Project, [Job Items].Partition, [Job Items].Section, [Job Items].[Sub Section], [Job Items].[Drawing No], [Job Items].Type, [Job Items].Job, [Job Items].[Item No], [Job Items].[Item Name], [Job Items].Quantity, [Job Items].Supplier, [Job Items].Status, [Job Items].[Due Date], [Job Items].Category, [Job Items].[W/O No], [Job Items].[P/O No], [Job Items].[P/O Line No], [Job Items].Description, [Job Items].Allocated
HAVING ((([Job Items].Project)=[Forms]![NDrawing Entry]![Project]) AND (([Job Items].Partition)=[Forms]![NDrawing Entry]![Partition]) AND (([Job Items].Section)=[Forms]![NDrawing Entry]![Sect]) AND (([Job Items].[Sub Section])=[Forms]![NDrawing Entry]![Sub Sect]) AND (([Job Items].[Drawing No])=[Forms]![NDrawing Entry]![Drawing No]) AND (([Job Items].Type)=[Forms]![NDrawing Entry]![Type]) AND (([Job Items].[Item No])=[Forms]![NDrawing Entry]![Drawing Items].[Form]![Item No]));

 

[Edski]

Hi GabberGod,
HAPPY EASTER!!

i can run the sql no problems and it gives me the results i am asking for,

Really?

How are you running this query?

HAVING ((([Job Items].Project)=[Forms]![NDrawing Entry]![Project]) AND

I don't think the the recordset is giong to know what [Forms]![NDrawing Entry]![Project] etc.. is.

I'm not really sure what you are trying to do here but if I was doing this I'd create a String variable called StrSQL and define it to be the SQL code above. By the way, you can remove the [Job Items] prefix for every field because it is redundant and get rid of some unneeded round brackets as well.

StrSQL = "SELECT Project, Partition, Section, ..."

For the HAVING clause you would need to modify it a little so that it looks like this:

... HAVING (Project=" & [Forms]![NDrawing Entry]![Project] & " AND Partition=" & [Forms]![NDrawing Entry]![Partition]) & " AND ...

Note that for String variables in your 'Job Items' table (if you ahve any), you would need to enclose these in single quotes (' ').

Then open the recordset...

Set rs = db.OpenRecordSet(StrSQL)

Try that. Good luck!

 

[GabberGod]

i didnt write that code, i used the query builder to build it. seems pointless to write something that can be automaticly written for you.

thanks for your help, i thought it might be something along those lines. i will give i a whirl tomorrow.

happy Easter to you too

for my reference, does access use the same sql interpreter if the query is run via vba and via opening an access query?

It just seems strange that a query that works from the db will now work when called via vba if it uses the same sql interpreter.

 

[Zion7]

GabberGod, I think Edski's point was simply, you need to have [Forms]![NDrawing Entry open, in order to reference any controls on it. Otherwise the query is receiving null values.
...assuming I've been following correctly?

Good luck either way!

 

[Edski]

Yep, that's exactly what I wanted to say.

As far as I'm aware Access uses the same SQL compiler (the JET engine) no matter where the query comes from (VBA or otherwise). A nice overview is located here:

http://www.frick-cpa.com/ss7/Theory_SQL_Overview.asp

i can run the sql no problems and it gives me the results i am asking for

You still haven't said where this 'working' query is run from. Is it the Row Source for a control on the form or something like that?

 

[GabberGod]

the form is open, im calling the vba from a subform of the form in quetion, the form is open and the required data is in the fields.

if i go to the queries window and double click the query in question i recieve the results in looking for.

also if i use vba to open the query, that works too.

but i cant get it to work with recordset, so i can get the data without having to show up the window (so that my program looks seamless)

 

[Miquella]

Ummm... Not sure if I'm just missing something, but I think the "item_incrementer" in your:

Set rs = db.OpenRecordSet("item_incrementer")

just needs to be changed to an SQL statement that will return the proper query...

Set rs = db.OpenRecordSet("SELECT * FROM [Job Items] HAVING (Project=" & [Forms]![NDrawing Entry]![Project] & " AND Partition=" & [Forms]![NDrawing Entry]![Partition]) & " AND ...
")

or something similar to that, or perhaps I misunderstood, my apologies if I did.

-Bean
"Everything should be made as simple as possible, but not simpler." -Albert Einstein

 

[Edski]

GabberGod,
I think we need to see your code again. Can you post your code and tell us where the error occurs now? Please use the [ code ] [ /code ] tags so it comes out clearer.

 

[GabberGod]

this is the vba code

Private Sub Item_No_AfterUpdate()

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


Set strsql = "SELECT Project, Partition, Section, Sub Section, Drawing No, Max(Revision) AS Rev, Type, Job, Item No, Item Name, Quantity, Supplier, Status, Due Date, Category, W/O No, P/O No, P/O Line No, Description, Allocated"
From [Job Items]
GROUP BY Project, Partition, Section, Sub Section, Drawing No, Type, Job, Item No, Item Name, Quantity, Supplier, Status, Due Date, Category, W/O No, P/O No, P/O Line No, Description, Allocated
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)

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

as per everyones suggestions, it now contains the sql as a string (strsql) Currently I am having issues getting the syntax on the sql string correct.

Currently Im getting an object required error, the code execution stops at the function call. But it highlights the strsql= section