Updating records using the results of another Recordset

[MarXman]

Morning All,

I hope someone can help me here...
I am trying to fill in the blanks within the records of a table using information already in that table.
i.e: I have five records with the same 'premise code' but only on has 'Tier' information - I want to be able to put this information in all 5.

I thought I had it with this code but when I run it Access just shuts down on me...program not responding.

Could someone have a look and maybe VET my code and tell what what simple thing I am missing.

---------------------------------------

Private Sub Tier_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strSQL2 As String

    strSQL = "SELECT DISTINCT [Premise Code], Tier "
    strSQL = strSQL & "FROM [Combined List] "
    strSQL = strSQL & "WHERE Tier Is Not Null;"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)

    Do Until rs.EOF
        strSQL2 = "UPDATE [Combined List] SET Tier='" 
        strSQL2 = strSQL2 & rs!Tier & "' WHERE [Premise Code]='"
        strSQL2 = strSQL2 & rs![Premise Code] & "';"
        db.Execute strSQL2
        rs.MoveNext
    Loop

End Sub

--------------------------------------

Any help would be much appreciated


-----------
Comrades, What we do in life echoes in eternity

 

[hermanlaksko]

Hi Marx

2 things you could try:

1. Insert an error handler to trap whatever is stopping you code.

2. Try using rs2.addnew (rs2.edit)/rs2.update in the loop, this last bit will, in debug, give you a responce to any err on insert of data into the rs2 recordset.

Herman

 

[MarXman]

Herman,

Thanks...am a little moth balled and rusty with VB can you give me a quick example of using rs2.update?

much appreciated

-----------
Comrades, What we do in life echoes in eternity

 

[hermanlaksko]

Sure

Dim Rs2 as dao.recordset

Do Until rs.EOF
rs2.addnew (rs2.edit)
rs2!Tier = rs!Tier
rs2.Update
rs.MoveNext
Loop
Herman