Three Recordsets to Loop through

[pvsmith5]

Here's a problem I've spent almost a week trying to crack, and I'll raise the flag here. I have three recordsets to loop through. One of the recordsets, rst1, may have only one record in it but it needs to change the fields each time it loops through the code, if there are records.

The rst1 table looks like this:
ElementarySchool SumOf2004_Total SumOf2005_total......
School A 100 220

This code does the first loop great, but I can't figure out how to write it so it will come back to this rst1 recordset and change fields. That's my problem. Can anybody help??

-----------

Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset

Set dbs = DBEngine.OpenDatabase("C:\My Documents\School District Project\BSSD_Student_Estimation.mdb")

Set rst1 = dbs.OpenRecordset("tblTotal_Schools_New_Construction", dbOpenDynaset)
Set rst2 = dbs.OpenRecordset("tblGrade", dbOpenDynaset)
Set rst3 = dbs.OpenRecordset(comboElemen & "_Enrollment_New ", dbOpenDynaset)


If rst1.EOF = True And rst1.BOF = True Then
Call MsgBox("There are no records")
Else
rst1.MoveFirst
rst2.MoveFirst
rst3.MoveFirst

Do
Do Until rst1.EOF = True

If rst1.Fields("ElementarySchool") = comboElemen And rst2.Fields("Grade") = "Kindergarten" Then
rst3.Edit
rst3.Fields("2004_New_Construction") = [rst1.Fields("Sumof2004_Total") * rst2.Fields("New_Construction_Calculation")]
rst3.Update

rst1.MoveNext
Exit Do
End If
Loop

 

[RoyVidar]

I don't understand the question " but I can't figure out how to write it so it will come back to this rst1 recordset and change fields."

You don't change any fields in rst1 at all.

You have two do loops, where you only finish one within the code you've pasted. There's an unfinished If statement. Is there something outside what you've pasted that changes things in rst1?

Back to the logic here, within the loop, you have an if test. If this test evaluates to true, you change one field in rst3. And only if the test is true, you issue a movenext on rst1 (shouldn't that be outside the if test?), and the important part, if the condition is true, you issue an exit do, thus skipping any further looping thru rst1.

I don't understand this logic, but if you try removing the exit do, and place the rst1.movenext outside the if test, perhaps that's a bit closer?

Roy-Vidar

 

[TheAceMan1]

How are ya pvsmith5 . . . .

[blue]Roy Vidar[/blue] is right! You said:

[blue]One of the recordsets, rst1, may have only one record in it but it needs to change the fields each time it loops through the code, if there are records.[/blue]

You need to be more specific about this! As it stands, its totally ambiguous.

I'm confused about [blue]rst2[/blue]. You only use the first record.

I also query the source for rst3 [blue](comboElemen & "_Enrollment_New ")[/blue] . . . . . . . Just what is that? It certainly does't look like a table or query . . .

In either case here's an initial reconstruction of some of the code that might make better sense:

[blue]'[purple]ES[/purple] = ElementarySchool
'[purple]cE[/purple] = comboElemen
'[purple]NC[/purple] = 2004_New_Construction
'[purple]ST[/purple] = Sumof2004_Total
'[purple]NCC[/purple] = New_Construction_Calculation

Do
   If rst1![purple]ES[/purple] = [purple]cE[/purple] And rst2!Grade = "Kindergarten" Then
      rst3.Edit
      rst3![purple]NC[/purple] = rst1![purple]ST[/purple] * rst2![purple]NCC[/purple]
      rst3.Update
   End If
   
   rst1.MoveNext
Loop Until rst1.EOF[/blue]

See Ya! . . . . . .

 

[pvsmith5]

The rst2 and rst3 tables look like this:

rst2
ID Grade New Construction_Calculation
1 Kindergarten 1.43%
2 1st Grade 1.57%
..
13 12th Grade 1.72%

rst3
ID Grade 2004_New_Construc 2005_New_Construction....
1 Kindergarten 2.86 3.146
2 1st Grade
..
7 6th Grade
END

I have written a query to do this, but I don't want to hard-code a query for each update in the table that has to occur. Is there a better way? Am I explaining this OK?

I will try the code that you have above, AceMan1, but what I meant about changing the fields is that in the rst1 table the fields need to change from 2004 to 2005 to 2014--about 10 fields. There might be only one record in the table but different numbers under each field.

Thanks for your help. I really appreciate it.

 

[TheAceMan1]

OK pvsmith5 . . . .

The code was only meant to be a model for proper structure (it actually entails the suggestions by [blue]Roy Vidar[/blue] which were excellent). However, since you've indicated you need to scan the fields of rst1, it wont work as you require.

Since you already have a query to do the job (apparently its an append query), I don't know why you don't just run the query? You could replace all the code with the following single line:

[blue]DoCmd.OpenQuery "[purple]YoutrQueryName[/purple]"[/blue]

If ya still wanna fix this code then, if I have this right:

[blue]You want to append a record to rst3, for each [purple]Date Field[/purple] of each Record of rst1[/blue]

Is this correct?

There's still the function of [blue]rst2[/blue] thats not clear. As it is, your only using the first record. Is this rst2's only requirement? You do not need to scan its records?

And now that a I think about it . . . . . I'd really like to see that query! Could ya post it for curiosities sake?

See Ya! . . . . . .

 

[pvsmith5]

Here's the query.

I have some recordset code that is close to working. If I test it through some more tonight, I think I can get it to go.
---------


'sql2 = "UPDATE tblTotal_Schools_New_Construction, " _
'& "" & comboElemen & "_Enrollment_New """ _
'& "INNER JOIN tblGRADE ON " _
'& "" & comboElemen & "_Enrollment_New.ID = tblGRADE.Grade_Sort " _
'& "SET " & comboElemen & "_Enrollment_New.[2004_New_Construction] " _
'& "= [tblTotal_Schools_New_Construction]![SumOf2004_Total]*[tblGRADE]![Grade_Projection_Factor] " _
'& "WHERE (((tblTotal_Schools_New_Construction.ElementarySchool)='" & comboElemen & "')) " _
'& "OR (((tblGRADE.Grade_Sort)=1));"