select case

[sunshine076]

Been working on a new program that is working however when it reaches the case select it skips case 1 moves to case 2 checks it and skips the rest of the cases and exits the cycle. Variable myID will only stay on the first row of data and will not check the rest of the excel spreadsheet. What this is doing is a spreadsheet will update a database when a button is clicked. Case 2 does work I have tried this and it does update the DB.
Below is my code:

Option Explicit
Public Function BottomRow(ColumnNumber As Integer)
   BottomRow = Cells(Rows.Count, ColumnNumber).End(xlUp).Row
End Function
Private Sub UpdateDatabase()
    Dim x As Integer
    Dim Data As Integer
    Dim db As Database, rs As Recordset
    Set db = OpenDatabase("Y:\Quality\Databases\eims.mdb")
    Set rs = db.OpenRecordset("M15", dbOpenTable)
    x = 2
    Data = 2
    myID = Cells(x, 13).Value
    Do
        If rs.Fields("ID") = myID Then
            rs.Edit
            With rs
                Select Case Data
                Case Is = 1
                    .Fields("CharNum") = Cells(x, Data).Value
                    .Update
                    Exit Do
                Case Is = 2
                    .Fields("Desc") = Cells(x, Data).Value
                    .Update
                    Exit Do
                Case Is = 3
                    .Fields("OpNum") = Cells(x, Data).Value
                    .Update
                    Exit Do
                Case Is = 4
                    .Fields("Loc") = Cells(x, Data).Value
                    .Update
                    Exit Do
                Case Is = 5
                    .Fields("LSL") = Cells(x, Data).Value
                    .Update
                    Exit Do
                Case Is = 6
                    .Fields("USL") = Cells(x, Data).Value
                    .Update
                    Exit Do
                Case Is = 7
                    .Fields("GELSL") = Cells(x, Data).Value
                    .Update
                    Exit Do
                Case Is = 8
                    .Fields("GEUSL") = Cells(x, Data).Value
                    .Update
                    Exit Do
                Case Is = 9
                    .Fields("LESSA") = Cells(x, Data).Value
                    .Update
                    Exit Do
                Case Is = 10
                    .Fields("MOREA") = Cells(x, Data).Value
                    .Update
                    Exit Do
                Case Is = 11
                    .Fields("LESSB") = Cells(x, Data).Value
                    .Update
                    Exit Do
                Case Is = 12
                    .Fields("MOREB") = Cells(x, Data).Value
                    .Update
                    Exit Do
                End Select
            End With

        End If
        rs.MoveNext
    Loop Until rs.EOF
    Set rs = Nothing
    Set db = Nothing
End Sub

 

[boblarson]

You just use

Case 1

(not Case Is = 1)

 

[boblarson]

Oh, and you are setting

Data = 2

but then nowhere changing it so it will always be 2.

 

[sunshine076]

to change the row I would add +1 to it?

 

[boblarson]

to change the row I would add +1 to it?

That would work but if you want the actual row, why not just use

ActiveCell.Row

instead?

EDIT - Nevermind - you would have to move the activecell for that then. Yes, adding 1 to it each time would help.

 

[sunshine076]

Why does it skip case 1?

 

[boblarson]

Why does it skip case 1?

Because Data is never 1. I'm not sure why you are checking the value of DATA anyway. But that is why. You have set it to start at 2 and so Data will never be one so the case will never be 1, get it?

 

[sunshine076]

Data is the column maybe I should change some of this around