VBA Module Update Query, Null Values etc.. (1 Viewer)

[rsmonkey]

Howdy

I've got this module which is suppose to look all the tables listed within a table & find the customers within these tables and get the averages for each question of the customer surveys. However for some reason the sql statement is trying to find every customer in every table and if it cant find it, it flags an error. Heres the Code:

Dim db As DAO.Database
Dim varItem As Variant
Dim strSQL As String
Dim rs As Recordset
Dim I As Integer



Set db = CurrentDb()

strSQL = "SELECT Customer.Customer FROM Customer WHERE Customer.CustomerView='y'"

Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF Then
    Do While Not rs.EOF
    
    strCustomer = rs!Customer
  
    strSQL = "SELECT budata.bu, budata.BUnumberOfQuestions2, budata.BUpreSLAQuestions FROM budata"
    
    Set rs2 = db.OpenRecordset(strSQL)
    If Not rs2.EOF Then
        Do While Not rs2.EOF
        
        strBu = rs2!bu
        
        

    strSQL = "SELECT Round(avg([Score1]), 1) As Score1Avg, Round(avg([Score2]), 1) As Score2Avg, Round(avg([Score3]), 1) As Score3Avg, Round(avg([Score4]), 1) As Score4Avg, Round(avg([Score6]), 1) As Score6Avg, Round(avg([Score7]), 1) As Score7Avg FROM [" & strBu & "] WHERE [" & strBu & "].[Customer Business Unit / Group] = '" & strCustomer & "'"
        'MsgBox strSQL

Set rs3 = db.OpenRecordset(strSQL)
        If Not rs3.EOF Then
        Do While Not rs3.EOF
        
    strScore1 = rs3!Score1Avg
    strScore2 = rs3!Score2Avg
    strScore3 = rs3!Score3Avg
    strScore4 = rs3!Score4Avg
    strScore6 = rs3!Score6Avg
    strScore7 = rs3!Score7Avg

Set rs4 = db.OpenRecordset(strSQL)
        If Not rs4.EOF Then
        Do While Not rs4.EOF

        strSQL = "UPDATE [" & strCustomer & "] SET [Score1] = " & strScore1 & ", [Score2] = " & strScore2 & ", [Score3] = " & strScore3 & ", [Score4] = " & strScore4 & ", [Score6] = " & strScore6 & ", [Score7] = " & strScore7 & " WHERE (([" & strCustomer & "].BU)='" & strBu & "'" 
        MsgBox strSQL
        CurrentDb.Execute (strSQL)


        
        rs4.MoveNext
        Loop
        
        rs4.Close
        Set rs4 = Nothing
        Else
        MsgBox "Shiiit"
        End If
        
        rs3.MoveNext
        Loop
        
        rs3.Close
        Set rs3 = Nothing
    Else
        'no issue
    End If
    
    rs2.MoveNext
    Loop

Else
    MsgBox "Something is wrong2"
End If
rs2.Close
Set rs2 = Nothing
    
    
    rs.MoveNext
    Loop

Else
    MsgBox "Something is wrong"
End If
rs.Close
Set rs = Nothing

MsgBox "Data Analysis & Compile Complete, Please Open the Excel Spreadsheet and Refresh the Query"

Set db = Nothing
DoCmd.Close
   
End Sub

Access is flagging the Update statement saying it cant find a specific customer in a table, however this obviously means my initial select statement just isnt working!

If someone could take a look it would be much appreciated!

Cheers

 

[rsmonkey]

sorry resolved if i'm being a tard.. i'm ill

I put the whole update statement in its own recordset? dnt ask me what im on... anyways if any1 is interested the code is:

Dim db As DAO.Database
Dim varItem As Variant
Dim strSQL As String
Dim rs As Recordset
Dim I As Integer



Set db = CurrentDb()

strSQL = "SELECT Customer.Customer FROM Customer WHERE Customer.CustomerView='y'"

Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF Then
    Do While Not rs.EOF
    
    strCustomer = rs!Customer
  
    strSQL = "SELECT budata.bu, budata.BUnumberOfQuestions2, budata.BUpreSLAQuestions FROM budata"
    
    Set rs2 = db.OpenRecordset(strSQL)
    If Not rs2.EOF Then
        Do While Not rs2.EOF
        
        strBu = rs2!bu
        
        

    strSQL = "SELECT Round(avg([Score1]), 1) As Score1Avg, Round(avg([Score2]), 1) As Score2Avg, Round(avg([Score3]), 1) As Score3Avg, Round(avg([Score4]), 1) As Score4Avg, Round(avg([Score6]), 1) As Score6Avg, Round(avg([Score7]), 1) As Score7Avg FROM [" & strBu & "] WHERE [" & strBu & "].[Customer Business Unit / Group] = '" & strCustomer & "'"
        MsgBox strSQL

Set rs3 = db.OpenRecordset(strSQL)
        If rs3.EOF Then
        Do While Not rs3.EOF
        
    strScore1 = rs3!Score1Avg
    strScore2 = rs3!Score2Avg
    strScore3 = rs3!Score3Avg
    strScore4 = rs3!Score4Avg
    strScore6 = rs3!Score6Avg
    strScore7 = rs3!Score7Avg

        strSQL = "UPDATE [" & strCustomer & "] SET [Score1] = " & strScore1 & ", [Score2] = " & strScore2 & ", [Score3] = " & strScore3 & ", [Score4] = " & strScore4 & ", [Score6] = " & strScore6 & ", [Score7] = " & strScore7 & " WHERE (([" & strCustomer & "].BU)='" & strBu & "'"
        MsgBox strSQL
        CurrentDb.Execute (strSQL)

        
        rs3.MoveNext
        Loop
        
        rs3.Close
        Set rs3 = Nothing
    Else
        MsgBox "Shiiit"
    End If
    
    rs2.MoveNext
    Loop

Else
    MsgBox "Something is wrong2"
End If
rs2.Close
Set rs2 = Nothing
    
    
    rs.MoveNext
    Loop

Else
    MsgBox "Something is wrong"
End If
rs.Close
Set rs = Nothing

MsgBox "Data Analysis & Compile Complete, Please Open the Excel Spreadsheet and Refresh the Query"

Set db = Nothing
DoCmd.Close
   
End Sub

apologies for the abusive msgbox's in the code