At Wit's End with Recordset

[cgdrake]

I've uploaded a database attached here called PMBooks1. It coughs and spits at the line:

Set rstRehab = myDb.OpenRecordset(rehabQuery)

I get an error message to the effect that VBA "expected parameters," but evidently was disappointed.

I know that the SQL is valid because I tested it in RehabTestQuery using 1 as the pvmt_analysis_section_id value. I also know that rstSections seems to be OK. I used the Debug.Print statement and it shows j being equal to 1, which it should be on the first record of rstSections. At that point, the line above where the program sputters, I'm attempting to open the rstRehab (rehabs on section #1) so I can number the rehab jobs in date order. BTW this is pavement rehab, not celebrity rehab

I can't figure this out, no matter how hard I try. I've searched and Googled and Help menued to no avail. Can anyone help? I would greatly appreciate any assistance.

 

[Banana]

Try this:

Set rstRehab = myDb.OpenRecordset(rehabQuery, dbOpenDynaset)

Not sure if this will fix your problem, but I recall seeing that problem where I didn't specify how to open the recordset caused an error.

 

[cgdrake]

Unfortunately, that didn't work, but I appreciate the help.

 

[RuralGuy]

You had a few problems.

.The Subs in standard modules should be Public rather than Private.
.You had a Sub named the same as the Module it was in.
.Put Option Explicit at the top of your modules to catch typos.
.You have no query named RehabQuery.
.The OpenRecordset query/table needs to be in quotes. ("RehabQueryTest")

I think that is all but I could be wrong. Anyway, give this upload a try.

 

[boblarson]

Something I just recently learned is that if a function or sub is not marked Private then it is Public by default. So, even though I like to be explicit, if you don't say:

Private Sub MySub()

and use this

Sub MySub()

it is public. Very interesting.

 

[cgdrake]

OK, I have made most of the changes you recommended as far as my sub name, public, explicit, etc. I had declared a string variable of RehabQuery to run to create the recordset, then I have these two lines right under the Debug.Print statements:

DoCmd.RunSQL rehabQuery

Set rstRehab = myDb.OpenRecordset("rehabQuery", dbOpenDynaset)

This does not work. You're correct--I had not built a query for this because of the j variable. I thought I could construct SQL to do one on the fly to create a recordset.

I appreciate the feedback--keep it coming. I am new to this but determined to master it!

 

[cgdrake]

OK, I tweaked the RehabTestQuery query and now I have the following, but am still getting the Too Few Parameters error message on the line that opens the rstRehab (the long line--it's actually one one line in my module):

Option Explicit

Public Sub OverlayNo()

Dim myDb As DAO.Database
Set myDb = CurrentDb
Dim pvmt_analysis_section_id As Integer
Dim RehabRecCount As Integer, j As Integer
Dim i As Integer
Dim rstSections As DAO.Recordset
Dim rstRehab As DAO.Recordset
Dim rehabQuery As String
Dim rehabQueryTest As QueryDef
rehabQuery = "SELECT rehab_proj_join.pvmt_analysis_section_id, "
rehabQuery = rehabQuery + "rehab_proj_join.proj_nmbr , rehab_proj_join.proj_detail_nmbr, "
rehabQuery = rehabQuery + "rehab_proj_join.pvmt_proj_actl_end_date, rehab_proj_join.overlay_no "
rehabQuery = rehabQuery + "FROM rehab_proj_join where pvmt_analysis_section_id=j;"

Set rstSections = myDb.OpenRecordset("Select distinct pvmt_analysis_section_id as j from rehab_proj_join")

rstSections.MoveFirst

Do Until rstSections.EOF

Debug.Print rstSections!j
Debug.Print rehabQuery


Set rstRehab = myDb.OpenRecordset("Select * from rehab_proj_join
WHERE pvmt_analysis_section_id=j order by pvmt_proj_actl_end_date")

RehabRecCount = rstRehab.RecordCount

For i = 1 To RehabRecCount
rstRehab.Edit
rstRehab!Overlay_No = i
rstRehab.Update

Next i

Set rstRehab = Nothing
rstSections.MoveNext
Loop

rstSections.Close
Set rstSections = Nothing
Set myDb = Nothing

End Sub

 

[cgdrake]

It's frustrating to me that this should be a simple task. I can do this task in Visual FoxPro in my sleep. Thanks for your help.

 

[DCrake]

Simple Software Solutions

rehabQuery = "SELECT rehab_proj_join.pvmt_analysis_section_id, "
rehabQuery = rehabQuery + "rehab_proj_join.proj_nmbr , rehab_proj_join.proj_detail_nmbr, "
rehabQuery = rehabQuery + "rehab_proj_join.pvmt_proj_actl_end_date, rehab_proj_join.overlay_no "
rehabQuery = rehabQuery + "FROM rehab_proj_join where pvmt_analysis_section_id=j;"[/QUOTE]

The section in bold should read

pvmt_analysis_section_id=" & j & ";"

The sql cannot interpret what j is

Apart from doing the Debug.Print where are you using rehabQuery?

What is this line attempting to do?

Set rstSections = myDb.OpenRecordset("Select distinct pvmt_analysis_section_id as j from rehab_proj_join")

Again you need to recode this line

Set rstRehab = myDb.OpenRecordset("Select * from rehab_proj_join
WHERE pvmt_analysis_section_id=j order by pvmt_proj_actl_end_date")

to:

Set rstRehab = myDb.OpenRecordset("Select * from rehab_proj_join 
WHERE pvmt_analysis_section_id=" & j & " order by pvmt_proj_actl_end_date")

What does you debug.print lines produce?

CodeMaster:

 

[cgdrake]

Thanks, DCrake, I'll give that a try.

 

[cgdrake]

DCrake, that was it! The " & j & " was the last missing piece of my puzzle. I didn't find any explanation of that where I searched, so this is good info to have. I appreciate this so much!!

Thanks to everyone for your input and helping me solve this.