Getting a compile error

[alansidman]

I am trying to run a select case scenario to update a recordset based upon the value in a particular field.

Private Sub Command4_Enter()
Dim mydb As DAO.Database
Dim myset As DAO.Recordset
Dim mycategory As String

Set mydb = CurrentDb
Set myset = mydb.OpenRecordset("Table1")
Do Until myset.EOF

Select Case [myset]![Mileage]
    Case 0 To 24000
    [mycategory] = "24,000"
    Case 24001 To 36000
    [mycategory] = "36,000"
    Case 36001 To 50000
    [mycategory] = "50,000"
    Case 50001 To 60000
    [mycategory] = "60,000"
    Case Else
    [mycategory] = "Greater than 60K"
    End Select
myset!Category = mycategory
myset.MoveNext
Loop
End Sub

It errors out on the line highlighted. Can't seem to see what I've done wrong here.

Thanks

[pbaldy]

Don't you need

myset.Edit

first?

[pbaldy]

Unrelated, but I don't think you want mycategory bracketed inside the Select/Case, do you? Or myset for that matter.

[orange]

I agree with Paul and would also suggest

myset.edit 'to set up for update
....
myset.update ' to do/commit the update

[alansidman]

Thanks guys. Got that included. Still bombing out on the myset.category=mycategory line. Somehow I need to tell Access to update the Category Field in Table1. Where am I going wrong on that.

Private Sub Command4_Enter()
Dim mydb As DAO.Database
Dim myset As DAO.Recordset
Dim mycategory As String

Set mydb = CurrentDb
Set myset = mydb.OpenRecordset("Table1")
Do Until myset.EOF

myset.Edit
Select Case myset!Mileage
    Case 0 To 24000
    [mycategory] = "24,000"
    Case 24001 To 36000
    [mycategory] = "36,000"
    Case 36001 To 50000
    [mycategory] = "50,000"
    Case 50001 To 60000
    [mycategory] = "60,000"
    Case Else
    [mycategory] = "Greater than 60K"
    End Select
myset.Update
myset!Category = mycategory

myset.MoveNext
Loop
End Sub

[pbaldy]

You'd want that line before the Update line. It's

rs.Edit
rs!Field = whatever
rs.Update

[alansidman]

Thanks, just figured that out but now it is only updating the first record and not any of the others. When I F8 through the code, it doesn't error out but it doesn't update the other records. Am posting db for examination.

[pbaldy]

It updated all records for me (though it took me a minute to realize you had the code in the enter event rather than the click event as I'd assumed).

[alansidman]

Thanks Paul. That was a mistake on my part. I thought I had put it in the On Click event. Fat Fingers do me in regularly. I will change it and see what happens. Thanks for looking at it and pointing that out to me.

[alansidman]

Runs fine. Thanks Paul and Orange.

[pbaldy]

Happy to help.

[alansidman]

A new wrinkle to the issue has arisen. Added an additional condition to the code and now the compile error is: Compile Error Loop without Do

Private Sub Command4_Click()
Dim mydb As DAO.Database
Dim myset As DAO.Recordset
Dim mycategory As String

Set mydb = CurrentDb
Set myset = mydb.OpenRecordset("Table1")
Do Until myset.EOF

myset.Edit
If myset!Coverage = "CCPLAT" Then
Select Case myset!Mileage
Case 0 To 24000
[mycategory] = "24,000"
Case 24001 To 36000
[mycategory] = "36,000"
Case 36001 To 50000
[mycategory] = "50,000"
Case 50001 To 60000
[mycategory] = "60,000"
Case Else
[mycategory] = "N/A"
End Select
Else
If myset!Coverage = "CCDIAM" Then
Select Case myset!Mileage
Case 0 To 50000
[mycategory] = "50,000"
Case 50001 To 75000
[mycategory] = "75,000"
Case 75001 To 100000
[mycategory] = "100,000"
Case 100001 To 125000
[mycategory] = "125,000"
Case 125001 To 150000
[mycategory] = "150,000"
Case Else
[mycategory] = "N/A"
End Select
End If
myset("Category").Value = mycategory
myset.Update

myset.MoveNext
Loop
End Sub

Not sure if this requires two do loops to work and if so, would it also require two edits and updates. I'm not sure of the correct syntax when using multiple loops.

[orange]

Alan,
Just a guess, but I think it isn't finding the Enf If, and outputs the Do--Loop error.

I'd try bringing your Else and If together

ElseIf myset!Coverage = "CCDIAM" Then

again just a quick look and guess.

[pbaldy]

These are actually fairly easy to figure out if you copy/paste out to Word or something and start deleting stuff that's okay. Yours gets down to this:

If myset!Coverage = "CCPLAT" Then
Else
If myset!Coverage = "CCDIAM" Then
End If


You never end the first If/Then.

[alansidman]

Seems to be working now. Thank you both for your help.

Alan