Below is the code I wrote after looking at a book. I know, I know, but it's all I have since no Access gurus live nearby. What I'm trying to do is to loop through a recordset of unique pavement sections, then get the overlay/rehab table (rehab_proj_join), of course many projects to one section. Then I want to filter the rehabs/overlays to just that one section to count them and assign an overlay number.
I can't even get the recordset to work. The sectionSQL query works in query-land--I tested it, and this is a very basic SQL here. But when I try to debug, after the rstSections recordset is supposedly open, the immediate window shows 0 as the anssecid (all numbers in the sections table are of course >0). Then it doesn't like the opening of the rehab_proj_join table much either. I'm pulling my hair out here--help! Any advice would be greatly appreciated.
Private Sub overlay_no()
Dim dbsA As Database
Set dbsA = CurrentDb
Dim rstRehab As New ADODB.Recordset
Dim rstSections As New ADODB.Recordset
Dim rehab As String, sectionSQL As String, rehabcount As Integer, counter As Integer, anssecid As Integer
sectionSQL = "select distinct pvmt_analysis_section_id as anssecid from v_pvmt_anlss_sctn_new_with_miles"
Set rstRehab = New ADODB.Recordset
rehab = "rehab_proj_join"
rstSections.Open sectionSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
Do Until rstSections.EOF
rstRehab.Open rehab, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rstRehab.Filter = "pvmt_analysis_section_id=anssecid"
rehabcount = rstRehab.RecordCount
For counter = 1 To rehabcount
overlay_nmbr = counter
rstRehab.Update overlay_nmbr, counter
Next counter
Loop
GoTo Done
rstRehab.Close
rstSections.Close
Set rstRehab = Nothing
Set rstSections = Nothing
Done:
End Sub
I can't even get the recordset to work. The sectionSQL query works in query-land--I tested it, and this is a very basic SQL here. But when I try to debug, after the rstSections recordset is supposedly open, the immediate window shows 0 as the anssecid (all numbers in the sections table are of course >0). Then it doesn't like the opening of the rehab_proj_join table much either. I'm pulling my hair out here--help! Any advice would be greatly appreciated.
Private Sub overlay_no()
Dim dbsA As Database
Set dbsA = CurrentDb
Dim rstRehab As New ADODB.Recordset
Dim rstSections As New ADODB.Recordset
Dim rehab As String, sectionSQL As String, rehabcount As Integer, counter As Integer, anssecid As Integer
sectionSQL = "select distinct pvmt_analysis_section_id as anssecid from v_pvmt_anlss_sctn_new_with_miles"
Set rstRehab = New ADODB.Recordset
rehab = "rehab_proj_join"
rstSections.Open sectionSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
Do Until rstSections.EOF
rstRehab.Open rehab, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rstRehab.Filter = "pvmt_analysis_section_id=anssecid"
rehabcount = rstRehab.RecordCount
For counter = 1 To rehabcount
overlay_nmbr = counter
rstRehab.Update overlay_nmbr, counter
Next counter
Loop
GoTo Done
rstRehab.Close
rstSections.Close
Set rstRehab = Nothing
Set rstSections = Nothing
Done:
End Sub
