Openrecordset invalid operation

[jaegertw2]

Hi guys,
I've seen a few other similar posts, but i still cant figure out exactly what I'm doing wrong or what i need to do to fix it.
I'm trying to find the ID number of the last record in a stored query. This method worked perfectly for a stored table, but with this query reulsts in an invalid operation error.
If I remove the DB_OPEN_TABLE parameter it says "Too few parameters. Expected 34." The criteria for this query has some complicated logic that I would rather not enter in manually, even if I could.
Is there another way to do this?

Here is my code:

Dim db As Database
Dim rs As Recordset
Dim lastn As Integer
Set db = CurrentDb()
Set rs = db.OpenRecordse t("Data Query", DB_OPEN_TABLE)
Do While Not rs.EOF
rs.Move (1)
Loop
lastn = rs![ID]
Me.DataField.Va lue

Any help will save me from a growing headache
Thanks,
Thomas

[puppydogbuddy]

Hi guys,
I've seen a few other similar posts, but i still cant figure out exactly what I'm doing wrong or what i need to do to fix it.
I'm trying to find the ID number of the last record in a stored query. This method worked perfectly for a stored table, but with this query reulsts in an invalid operation error.
If I remove the DB_OPEN_TABLE parameter it says "Too few parameters. Expected 34." The criteria for this query has some complicated logic that I would rather not enter in manually, even if I could.
Is there another way to do this?

Here is my code:

Dim db As Database
Dim rs As Recordset
Dim lastn As Integer
Set db = CurrentDb()
Set rs = db.OpenRecordse t("Data Query", DB_OPEN_TABLE)
Do While Not rs.EOF
rs.Move (1)
Loop
lastn = rs![ID]
Me.DataField.Va lue

Any help will save me from a growing headache
Thanks,
Thomas

Thomas,

The default recordset type for a query is a dynaset, so your code will probably work if you set your recordset as follows:

Set rs = db.OpenRecordse t("Data Query", dbOpenDynaset)


However, it is recommended that you declare the library (DAO or ADO) whose recordset methods you are accessing (as shown below) or you will have unpredictable results. See this link: http://allenbrowne.com/ser-29.html

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lastn As Integer
Set db = CurrentDb()
Set rs = db.OpenRecordset("Data Query", dbOpenDynaset)
If Not (rs.BOF or rs.EOF) Then
	rs.MoveFirst
End If
Do While Not rs.EOF
rs.MoveNext
Loop
lastn = rs![ID]
Me.DataField.Value

rs.Close
Set rs = Nothing

[jaegertw2]

Richard,
Thanks for taking the time to look at my code. I inserted the code you suggested, unfortunately, its still giving me the error: "Too few parameters. Expected 34"
This is access 97 with DAO 3.51. I have read several places that 3.6 needs to be included, but when I tried it, it didnt make a difference here.
I will continue to try whatever I can think of, but if you have any other suggestions please let me know.
Thanks again,
Thomas

[puppydogbuddy]

Richard,
Thanks for taking the time to look at my code. I inserted the code you suggested, unfortunately, its still giving me the error: "Too few parameters. Expected 34"
This is access 97 with DAO 3.51. I have read several places that 3.6 needs to be included, but when I tried it, it didnt make a difference here.
I will continue to try whatever I can think of, but if you have any other suggestions please let me know.
Thanks again,
Thomas

Does your query contain misspelled object references, or are you using any parameters in your query? Post the sql of your query.

[jaegertw2]

Does your query contain misspelled object references, or are you using any parameters in your query? Post the sql of your query.

Here is the query:

Status Criteria:
[Forms]![Input Search]![OpenY] Or <>[Forms]![Input Search]![OpenN]

Impact Criteria:
[Forms]![Input Search]![ImpactY] Or <>[Forms]![Input Search]![ImpactN]

Priority Criteria:
0 or (1 and [Forms]![Input Search]![Priority1] = true) or ( 2 and [Forms]![Input Search]![Priority2] = true) or (3 and [Forms]![Input Search]![Priority3] = true)

[puppydogbuddy]

Here is the query:

Status Criteria:
[Forms]![Input Search]![OpenY] Or <>[Forms]![Input Search]![OpenN]

Impact Criteria:
[Forms]![Input Search]![ImpactY] Or <>[Forms]![Input Search]![ImpactN]

Priority Criteria:
0 or (1 and [Forms]![Input Search]![Priority1] = true) or ( 2 and [Forms]![Input Search]![Priority2] = true) or (3 and [Forms]![Input Search]![Priority3] = true)

The above parameters you entered in the criteria row of your query are most likely the cause of your error message. Parameters work, provided the parameter definition does not conflict with the field name among the query's tables.

To better define the parameters so Access will know how to handle them, you should define them in the list of parameters. This is an optional step, but there are good reasons to do so, right mouse click on the top part of the query and choose Parameters. A parameter declaration form appears to let you list each parameter name and its type:

[jaegertw2]

The above parameters you entered in the criteria row of your query are most likely the cause of your error message. Parameters work, provided the parameter definition does not conflict with the field name among the query's tables.

To better define the parameters so Access will know how to handle them, you should define them in the list of parameters. This is an optional step, but there are good reasons to do so, right mouse click on the top part of the query and choose Parameters. A parameter declaration form appears to let you list each parameter name and its type:

The one thing I left out before is that my query was built on a series of other queries that have a lot of parameters in them, because access was crashing with them all together.
(See http://forums.microsof t.com/TechNet/ShowPost.aspx?P ostID=606327&Si teID=17) for details)

But after specifying all 34 parameter values programatically in my function, it works perfectly. Thanks a lot for your help. I've been working on this database since February, and it is being released for use this week. This was the last kink I had to work out, so I really appreciate the time you put into this.

Thomas

[puppydogbuddy]

The one thing I left out before is that my query was built on a series of other queries that have a lot of parameters in them, because access was crashing with them all together.
(See http://forums.microsof t.com/TechNet/ShowPost.aspx?P ostID=606327&Si teID=17) for details)

But after specifying all 34 parameter values programatically in my function, it works perfectly. Thanks a lot for your help. I've been working on this database since February, and it is being released for use this week. This was the last kink I had to work out, so I really appreciate the time you put into this.

Thomas

Thomas,

You are most welcome. I am glad I could help, but it was your unyielding determination to get it right that got you over the top. You have set a good example for others. Best wishes for continued success on your project.