Help with Crazy Loop and Query in VBA (1 Viewer)

[ice051505]

Hey guys! I almost raise my white flag with my project, I am a so bad programmer....... My code gives me bugs all the time and I hardly can fix it... Can any of you genius have a look and give me some advise or fix for me...PLEASE...THANKS.....

Private Sub Zone_slc_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim AreaArray() As String
Dim Sql1 As String
Dim Sql2 As String
Dim strSql As String
Dim i As Long
Dim j As Long
Dim rst As DAO.Recordset
Dim RecordCnt As Long
strSql = "SELECT [AreaCode],[Zone],[TotalArea],[SmallArea],[SmallerArea],[SmallestArea] FROM AreaQuery1 INNER JOIN AreaQuery1ON AreaQuery2.AreaCode= AreaQuery1.AreaCode;"
Set rst = db.OpenRecordset(strSql, dbOpenSnapshot)
Set db = CurrentDb()
RecordCnt = DCount("AreaCode", "AreaQuery1")
For i = 4 To 6
 For j = 0 To RecordCnt
    ReDim AreaArray(i, j)
 
rs.MoveFirst
Do While rst.Fields(i) And Not rs.EOF
Sql1 = "SELECT " & rst.Fields(i) & " From [AreaQuery1] WHERE ( " & rst.Fields(i) & " <>'NA');"
Set rs = db.OpenRecordset(Sql1, dbOpenSnapshot)
If rs.RecordCount > 0 Then
Sql1 = "UPDATE [AreaQuery2]SET[AreaQuery2]." & rst.Fields(i) & " =" & Sql1 & "WHERE [AreaQuery1].[AreaCode]=[AreaQuery2].[AreaCode];"
db.Execute Sql1, dbFailOnError
End If
Set rs = Nothing
Set db = Nothing
Sql2 = "SELECT " & rst.Fields(i) & rst.Fields(i - 1) & [AreaCode] & " FROM [AreaQuery1]WHERE( " & rst.Fields(i) & "='NA');"
Set rs = db.OpenRecordset(Sql2, dbOpenSnapshot)
If rs.RecordCount > 0 Then
Sql2 = "SELECT Count([AreaQuery1]." & rst.Fields(i - 1) & ") AS CountOfArea, AreaQuery2." & rst.Fields(i - 1) & " " & _
     "FROM AreaQuery2 INNER JOIN AreaQuery1 ON AreaQuery2.AreaCode= AreaQuery1.AreaCode" & _
     "GROUP BY AreaQuery2." & rst.Fields(i - 1) & ";"
db.Execute Sql2, dbfilonerror
   If ALLCountOfArea = 1 Then
      Sql2 = "SELECT " & rst.Fields(i - 1) & " FROM AreaQuery1INNER JOIN AreaQuery2 WHERE ( " & rst.Fields(i) & " ='NA');"
      Set rs = db.OpenRecordset(Sql2, dbOpenSnapshot)
      Sql2 = "UPDATE [AreaQuery2] SET [AreaQuery2]." & rst.Fields(i) & "=" & Sql2 & " WHERE [AreaQuery1].[AreaCode]=[AreaQuery2].[AreaCode];"
      db.Execute Sql2, dbFailOnError
 
   Else
      Select Case CountOfArea
      Case CountOfArea = 1
      Sql2 = "SELECT " & rst.Fields(i - 1) & " FROM AreaQuery1INNER JOIN AreaQuery2 WHERE ( " & rst.Fields(i) & " ='NA');"
      Set rs = db.OpenRecordset(Sql2, dbOpenSnapshot)
      Sql2 = "Update [AreaQuery2]SET[AreaQuery2]." & rst.Fields(i) & " = [AreaQuery1]." & rst.Fields(i - 1) & "WHERE [AreaQuery1].[AreaCode]=[AreaQuery2].[AreaCode];"
      db.Execute Sql2, dbFailOnError
 
      Case CountOfArea > 1
      Sql2 = "SELECT " & rst.Fields(i - 1) & " FROM AreaQuery1INNER JOIN AreaQuery2 WHERE ( " & rst.Fields(i) & " ='NA');"
      Set rs = db.OpenRecordset(Sql2, dbOpenSnapshot)
      Sql2 = "Update [AreaQuery2] SET [AreaQuery2]." & rst.Fields(i) & "=[AreaQuery1]." & rst.Fields(i - 1) & " WHERE [AreaQuery1].[AreaCode]=[AreaQuery2].[AreaCode];"
      db.Execute Sql2, dbFailOnError
      End Select
   End If
End If
rs.MoveNext
Loop
  Next j
Next i
End Sub

Please write me if you have any confusion, I will try my best to explain...Thanks
BTW, I think I have huge error on obtaining the field column numbers in Sql (rst.Fields(i)), but I don't know how to fix it... many many people give me help while I was doing this loop, Thanks a lot for all your patient, and I really feel stupid now.......

 

[thechazm]

Can you post a sample db and I'll help you out?

 

[ice051505]

Of course, Thanks for your reply!!!

I have table <Area>, <Species>, <CatchNum>,<weeks>
my query AreaQuery1 in the code represent the selection query by user enter weeks, species and gives catchNum and primary area
My <Area> table contains 6 column: the attachment is the database of my area table
Zone_slc is the one of the area selection on my form

After the Zone(one of the field in my area table) selection, I wish to select the level of area base on my code

my theory is to use the loop update the area to my AreaQuery2 in the code:
1, if the selection of the area does not contian 'NA', update to the AreaQuery2 directly,
2, if there is a 'NA' in the selection, check for the count number of the bigger area, if all count is 1, then update the bigger area value to the current level, if not all count is 1, for the count<>1, update the whole current level (include not "NA") to the bigger area value

Also since there is No "NA" value in the AreaCode, TotalArea and Zone fields, so I already put them in my AreaQuery2, and the loop is start in column4(smallArea)

Hope my explaination won't cause more confusion T,T

Can you post a sample db and I'll help you out?

 

[thechazm]

Ok I'm going to try and work this out but its really hard to since I don't have a db with example data in it to step through. I did however notice you are setting your recordset rst to use the variable db before db is declared. This would be one fix and I'll post others as I get time to find them. Below is changed code in red:

Private Sub Zone_slc_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim AreaArray() As String
Dim Sql1 As String
Dim Sql2 As String
Dim strSql As String
Dim i As Long
Dim j As Long
Dim rst As DAO.Recordset
Dim RecordCnt As Long
strSql = "SELECT [AreaCode],[Zone],[TotalArea],[SmallArea],[SmallerArea],[SmallestArea] FROM AreaQuery1 INNER JOIN AreaQuery1ON AreaQuery2.AreaCode= AreaQuery1.AreaCode;"
[COLOR=red]Set db = CurrentDb() ' This needs to be first before opening any recordsets with it.[/COLOR]
[COLOR=red]Set rst = db.OpenRecordset(strSql, dbOpenSnapshot)[/COLOR]
RecordCnt = DCount("AreaCode", "AreaQuery1")
For i = 4 To 6
 For j = 0 To RecordCnt
    ReDim AreaArray(i, j)
 
rs.MoveFirst
Do While rst.Fields(i) And Not rs.EOF
Sql1 = "SELECT " & rst.Fields(i) & " From [AreaQuery1] WHERE ( " & rst.Fields(i) & " <>'NA');"
Set rs = db.OpenRecordset(Sql1, dbOpenSnapshot)
If rs.RecordCount > 0 Then
Sql1 = "UPDATE [AreaQuery2]SET[AreaQuery2]." & rst.Fields(i) & " =" & Sql1 & "WHERE [AreaQuery1].[AreaCode]=[AreaQuery2].[AreaCode];"
db.Execute Sql1, dbFailOnError
End If
[COLOR=red]'Set rs = Nothing[/COLOR]
[COLOR=red]'Set db = Nothing[/COLOR]
 
Sql2 = "SELECT " & rst.Fields(i) & rst.Fields(i - 1) & [AreaCode] & " FROM [AreaQuery1]WHERE( " & rst.Fields(i) & "='NA');"
Set rs = db.OpenRecordset(Sql2, dbOpenSnapshot)
If rs.RecordCount > 0 Then
Sql2 = "SELECT Count([AreaQuery1]." & rst.Fields(i - 1) & ") AS CountOfArea, AreaQuery2." & rst.Fields(i - 1) & " " & _
     "FROM AreaQuery2 INNER JOIN AreaQuery1 ON AreaQuery2.AreaCode= AreaQuery1.AreaCode" & _
     "GROUP BY AreaQuery2." & rst.Fields(i - 1) & ";"
db.Execute Sql2, dbfilonerror
   If ALLCountOfArea = 1 Then
      Sql2 = "SELECT " & rst.Fields(i - 1) & " FROM AreaQuery1INNER JOIN AreaQuery2 WHERE ( " & rst.Fields(i) & " ='NA');"
      Set rs = db.OpenRecordset(Sql2, dbOpenSnapshot)
      Sql2 = "UPDATE [AreaQuery2] SET [AreaQuery2]." & rst.Fields(i) & "=" & Sql2 & " WHERE [AreaQuery1].[AreaCode]=[AreaQuery2].[AreaCode];"
      db.Execute Sql2, dbFailOnError
 
   Else
      Select Case CountOfArea
      Case CountOfArea = 1
      Sql2 = "SELECT " & rst.Fields(i - 1) & " FROM AreaQuery1INNER JOIN AreaQuery2 WHERE ( " & rst.Fields(i) & " ='NA');"
      Set rs = db.OpenRecordset(Sql2, dbOpenSnapshot)
      Sql2 = "Update [AreaQuery2]SET[AreaQuery2]." & rst.Fields(i) & " = [AreaQuery1]." & rst.Fields(i - 1) & "WHERE [AreaQuery1].[AreaCode]=[AreaQuery2].[AreaCode];"
      db.Execute Sql2, dbFailOnError
 
      Case CountOfArea > 1
      Sql2 = "SELECT " & rst.Fields(i - 1) & " FROM AreaQuery1INNER JOIN AreaQuery2 WHERE ( " & rst.Fields(i) & " ='NA');"
      Set rs = db.OpenRecordset(Sql2, dbOpenSnapshot)
      Sql2 = "Update [AreaQuery2] SET [AreaQuery2]." & rst.Fields(i) & "=[AreaQuery1]." & rst.Fields(i - 1) & " WHERE [AreaQuery1].[AreaCode]=[AreaQuery2].[AreaCode];"
      db.Execute Sql2, dbFailOnError
      End Select
   End If
End If
rs.MoveNext
Loop
  Next j
Next i
End Sub

 

[thechazm]

Umm now that I am looking at it quite a bit closer their is a huge problem with your do while loop. The reason is your loop is counting on rs.eof as part of it's criteria but inside of your loop you close the recordset and open it to other criteria then you tell it to move next in that recordset that you just blew away and now it's looking at the wrong data.

The code is pretty jacked but it is fixable but I really need you to post either a sample database or the real deal so I can run through and fix the code by debugging.

 

[ice051505]

Yes! It is super complex.....and Thank you a lot for your patient and help!!!

Ok I'm going to try and work this out but its really hard to since I don't have a db with example data in it to step through. I did however notice you are setting your recordset rst to use the variable db before db is declared. This would be one fix and I'll post others as I get time to find them.