Solved Update recordset based on values in second recordset.

[I_Macleod]

I have 2 tables - PPMExportTBL (PPME) and PPMVisits (PPMV).
In PPME there are records for apartments with and AssetID against them.
For example record 1 to 4 is for Apartment 1 and therefore has 4 separate AssetIDs.

I need to loop through PPME and update a single record in PPMV with the various AssetIDs.

The first record updates OK. Then it all goes wrong.

This is the code I am using.

My first time posting here so I hope I have got this correct.
I would appreciate any help.
thank you

Private Sub Command0_Click()

Dim dbs As DAO.Database, stdocname As String, rpt As Report, ppmv As DAO.Recordset, fcud As DAO.Recordset, myCopies As Integer, Apnum As Variant, fcunum As Variant


Set dbs = CurrentDb
Set ppmv = dbs.OpenRecordset("ppmvisits", dbOpenDynaset)
Set ppme = dbs.OpenRecordset("ppmexportTBL", dbOpenDynaset)


ppmv.MoveFirst
Do Until ppmv.EOF

    ppme.MoveFirst
    Do Until ppme.EOF

 
        If ppmv!aptid = ppme!aptid Then
            ppmv.Edit
            ppmv!AssetID1 = ppme!assetid
            ppmv!visitdate = ppme!visitdate
            ppmv.Update
            ppme.MoveNext
        Else
            ppmv.MoveNext
        End If
        

        
        If ppmv!aptid = ppme!aptid Then
            ppmv.Edit
            ppmv!AssetID2 = ppme!assetid
            ppmv.Update
            ppme.MoveNext
        Else
            ppmv.MoveNext
        End If
        
          
        
        If ppmv!aptid = ppme!aptid Then
            ppmv.Edit
            ppmv!AssetID3 = ppme!assetid
            ppmv.Update
            ppme.MoveNext
        Else
            ppmv.MoveNext
        End If

        
        If ppmv!aptid = ppme!aptid Then
            ppmv.Edit
            ppmv!AssetID4 = ppme!assetid
            ppmv.Update
            ppme.MoveNext
        Else
            ppmv.MoveNext
        End If
      

        
        If ppmv!aptid = ppme!aptid Then
            ppmv.Edit
            ppmv!AssetID5 = ppme!assetid
            ppmv.Update
            ppme.MoveNext
        Else
            ppmv.MoveNext
        End If
        

        
        If ppmv!aptid = ppme!aptid Then
            ppmv.Edit
            ppmv!AssetID6 = ppme!assetid
            ppmv.Update
            ppme.MoveNext
        Else
            ppmv.MoveNext
        End If
        
 
        
        If ppmv!aptid = ppme!aptid Then
            ppmv.Edit
            ppmv!AssetID7 = ppme!assetid
            ppmv.Update
            ppme.MoveNext
        Else
            ppmv.MoveNext
        End If
        

     Loop
        ppmv.MoveNext
    
Loop



End Sub

 

[Gasman]

I am not going to even try and understand that, as the PPMV is not normalized?
If you want to see all the assetid's with the aptid, then just concatenate them?

Just from the pics, it looks like you are not resetting your counter?. you appear to put in 4 items for the first record, skip a field and then start on the next field with the next aptid? then repeat for each aptid.

If I had to do that, I would probably try getting unique aptid's then get all records for that apt and then start at field one.
Easier logic to understand as only dealing with one aptid at a time.
Might not be as efficient, but easier to maintain?

If I wrote that and it was working, and I came back to it 6 months later, it would take me a while to work out what I did.

However, as I always keep saying, walk through your code line by line to see what it is actually doing, not what you think it is doing.

 

[I_Macleod]

I am not going to even try and understand that, as the PPMV is not normalized?
If you want to see all the assetid's with the aptid, then just concatenate them?

Just from the pics, it looks like you are not resetting your counter?. you appear to put in 4 items for the first record, skip a field and then start on the next field with the next aptid? then repeat for each aptid.

If I had to do that, I would probably try getting unique aptid's then get all records for that apt and then start at field one.
Easier logic to understand as only dealing with one aptid at a time.
Might not be as efficient, but easier to maintain?

If I wrote that and it was working, and I came back to it 6 months later, it would take me a while to work out what I did.

However, as I always keep saying, walk through your code line by line to see what it is actually doing, not what you think it is doing.

Thank you for your reply. I have certainly got myself lost with this. It is a one off procedure to create an import for another system. I will go through again. Cheers

 

[moke123]

It is a one off procedure to create an import for another system.

That explains the non-normalized structure.
Definitely doable.
Probably easier if you uploaded copies of your tables with some dummy data in them.

 

[Gasman]

Store the first apptid on open of recordset of PPME
Create a loop comparing that against current apptid, moving to next records after you have processed the data.
Once you exit that loop, store the aptid of the current record 9as it will have changed, as you exited the loop.
Repeat until end of PPE.
You will need an additional counter for the PPMV field index.

I had to do something similar for an outlook email in that I wished to put all the details for each client into one email.

See if you can understand the logic and adapt.
Really all you need is a loop within a loop, and exit the inner loop at the correct time, and keep track of changed aptid.
I have included all of the sub, just in case I take out something that is needed. Too big to post, so attached.

 

[I_Macleod]

These are the 2 tables in question. Thank you

 

[arnelgp]

this is not a Normalized db.
even then, you only have 13 AssetID columns so i will only work on those 13.
run code on Module1

 

[I_Macleod]

this is not a Normalized db.
even then, you only have 13 AssetID columns so i will only work on those 13.
run code on Module1

Hi, The 13 AssetID is fine, there are a maximum of 13 on the site. This worked a treat. Thank you very much you have saved my skin...

 

[arnelgp]

the id's are not saving in "sequence" so i added a "custom" sort function.
run the code again.

 

[Pat Hartman]

What was wrong with a crosstab query?