Field won't update when using Do... Until Loop

[mplsdoors]

This is my first program. It is complicated and after months of work I am almost finished with it. I would appreciate any advice.

Problem: I expected a field in my table to be updated after the program executes and instead the field remains null.

Am I coding the "start" and "end" correctly?

"start"
tbl_commissions.MoveFirst
Do Until tbl_commissions.EOF
tbl_commissions.Edit
Select Case

<lots of code, If and Select Case statements>

&quot;end&quot;
tbl_commissions.Update
End Select
tbl_commissions.MoveNext
Loop
tbl_commissions.Close

Thanks,
Michelle

 

[Databaseguy]

You'll have to create a recordset. Besudes that, you're code looks proper.

Dim dbs as database
Dim rst as recordset
Set dbs = currentdb()
Set rst = dbs.Openrecordset(&quot;tblCommisions&quot
rst.movefirst
Do while not rst.eof
With rst
.edit
.fields(&quot;Your field name&quot = YourVariable
.update
.movenext
Loop

 

[mplsdoors]

Thanks Databaseguy for your quick response. Actually, I did put in Dim and Set code but didn't say so in my post to this forum. I thought my Dim and Set code was okay, but now I would like to rule it out as my problem. Does this look okay?

My code:
Dim db As Database
Dim tbl_commissions As Recordset
Set db = Currentdb()
Set tbl_commissions = db.OpenRecordset(&quot;tbl_commissions&quot

 

[Databaseguy]

Looks fine with one exception, I wouldn't give the reecordset the same name as the table. As a matter of fact, I don't know if Access will even allow it. Just call it rstCommisions or something. I usually keep them short because typing rstSomeReallyLongAndDescriptiveNameThatLetsMeKnowExactlyWhatItMeans can be a pain....

You're on the right track. Have fun !!

 

[Sameal]

Also, try using some Debug.Print statements and fine out at what point your lines of code are not firing correctly. This can be an easy way to pinpoint the exactly syntax error. Also set a breakpoint toward the beginning of the Sub and turn on your Locals and Immediate windows in the VBA environment. This way as you step through the code line by line you can watch every variable updating and changing.