Need Help with Data Transformation, Recordsets (1 Viewer)

[Firepen]

Hi Guys,

Having a bit of trouble with this, I only know as much VBA as my Access book teaches and it's not the focus of the book.

My aim is to change the value of tblMakesTemp.NBody to the correct ID for that Value (tblBodyTypesTemp.BodyTypeID) as part of an import Mod. I need to check all the values in tblBodyTypesTemp.BodyName to find a match so have used recordsets and until loops. Not sure if the best thing to use, but this is the 'easy' bit of what I will need to do for the full import so I used these as I think i will need them down the line anyway.

My Code is below with plain english logic below that and access db attached.

Private Sub IfTest()

Dim db As Database
Dim rsMak As Recordset
Dim rsBod As Recordset

Set db = CurrentDb
Set rsMak = db.OpenRecordset("tblMakesTemp", dbOpenDynaset)
Set rsBod = db.OpenRecordset("tblBodyTypesTemp", dbOpenDynaset)

Do Until rsMak.EOF

    If rsMak!NBody = Not Null Then
        
                Do Until rsBod.EOF
                        
                            If rsBod!BodyName = rsMak!NBody Then
                
                                rsMak.Edit
                                rsMak!NBody = rsBod!BodyTypeID
                                rsMak.Update
                                rsBod.MoveLast
                                rsBod.MoveNext
                            Else
                            rsBod.MoveNext
                            End If
                Loop

    End If
                        
    rsMak.MoveNext
    rsBod.MoveFirst

Loop

End Sub

FYI used Make and Body in lou of full tbl names below
Ok, so the idea is first to check if Not Null on Make.NBody, the code will move next and loop until it gets to record 55, where the value of Make.NBody is "Coupe".
This being 'not null' fires the next loop, which checks the value of Body.BodyName, being the 1st record automatically.
This does not match so 'Else' moves the rsBod(y ) to the next record and loops back
Now it does match so it runs the 'Then' that should change the value of Make.NBody to the ID from Body.BodyTypeID from that record I.e. give the correct ID
It saves the change and moves rsBody to EOF as job is done.
The nested loop ends, and the main loop moves to rsMak(e) to record 56, rsBod goes back to the start and does all the above again.
All records should be worked through and all entires in Make.NBody should become ID numbers.

This doesn't happen, and I am unsure why.
Datatypes are not the same but the short string should be able to change value to the autonumber value without issue.
I have not seen 2 recordsets used at the same time in the book, but I guessed you could do that if seperately refined?

Not sure if i'm barking up the wrong tree entierly or how best to go about this, any assistance would be appreciated.

Many Thanks

 

[CJ_London]

not sure why you don't just do this with an update query?

Also struggling to understand what you are trying to do, Suggest take a simple example and take us through it

 

[jdraw]

I agree with CJ. Your post is not clear. Perhaps you could tell us in plain English (simple terms, no jargon) what is the issue. And, what the ideal solution/fix would look like.

 

[MajP]

At a minimum this will never be true

If rsMak!NBody = Not Null Then

should be; if not isnull(rsMak!body)

However no need to return records you do not need
Set rsMak = db.OpenRecordset("Select * from tblMakesTemp where not Body is Null", dbOpenDynaset)

However this should be done in an update query. Make a query Joining your tables by BodyName and NBody. If you cannot do a join because of datatypes then us a where condition (assume one is text and one is long) Ex. Where BodyName = cstr([NBody])

 

[arnelgp]

did you make the db?
it was intended that way so that your table will be Normalized.
if the missing info can be found on another table through join,
then there is no need to repeat the info in same table.

read up normalization.

 

[The_Doc_Man]

IF all of your data in that column you are searching is unique AND IF all the data you are importing has matching data that is also unique,

UPDATE TABLE1 INNER JOIN TABLE2 ON TABLE1.NAMEFIELD = TABLE2.NAMEFIELD
SET TABLE1.ITEMNUMBER = TABLE2.ITEMNUMBER ;

However, if a name in EITHER table is not unique, you can't do it this way.

Once you run that query, you will have null/unmatched cases left over. That would have to be fixed by hand.