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.
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
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.
Code:
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