Having trouble updating a recordset

[Wazza0161]

The code will not accept the top line inside the Do While loop. I am open to suggestions. I would like the user to type in a collection of 1 or more numbers that will need a common index applied. (forming part of a one-many relationship).

Public Sub ValveLink_Click()

    Dim db As Database
    Dim rs As Recordset

    Dim IndexFocus  As Long
    Dim Valve_No As Long
    Dim i As Integer
    Dim Answer As String
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("NameplateIndexUpdate") 'Fields: ValveNo and Index + others
    
    i = 0
    IndexFocus = Me.[Valve Index]             'transferred from a form, proven to be ok.
    Valve_No = InputBox("Please enter the valve number you want to link to this model", "Valve Link", "Enter Valve")
    
    Do While (Not rs.EOF) And (i < 15)
        If rs!ValveNo = Valve_No Then                               '***** only works when I change it to =i *****
            MsgBox ("This works, valve No is " & Valve_No)          'works ok when tested
            rs.Edit
            rs!Index = IndexFocus                                   'works ok when tested
            MsgBox ("This works as well, Index No is " & rs![Index])  'works ok when tested
            rs.Update
            Debug.Print rs!Index                                    'works ok when tested
        End If
        Answer = MsgBox("Do you have another valve to link?", vbQuestion + vbYesNo + vbDefaultButton2, "Another Valve to Link") 'Works OK
        If Answer = vbNo Then                                       'works ok
            Exit Do                                                 'works ok when tested, drops out of loop
        Else
            Valve_No = InputBox("Please enter the valve number you want to link to this model", "Valve Link", "Enter Valve") 'works ok
        End If
        rs.MoveNext
        i = i + 1
        Loop
    rs.Close
    Set rs = Nothing
    db.Close
       
End Sub

[NeoPa]

Hi Wazza.

Please try to pay more attention when posting. A VBA question is not appropriate in the Lounge and code must always be posted within the [ CODE ] tags provided.

The reason your code won't compile is because the If statement comes in two versions - single-line and multi-line. It's clear you want the multi-line version but if you add a comment after the Then part it causes it to be treated as a single-line instead. It sort of sucks in a way - I get that - but that's the rules we have to live with I'm afraid. Put the comment on the previous line and that problem will disappear. Good luck.

[Wazza0161]

Thanks NeoPa,

Tips are welcome for both website and coding. I thought of trying to have an open-ended loop (say do while something obvious where the loop specifies the link, not an If Statement) and then let the user dictate when to exit (exiting the loop has been proven to work) and thus not letting the code get confused.

[NeoPa]

Hi Wazza.

Don't misunderstand what I'm saying. It's perfectly possible to use a multi-line If statement. You simply can't add a comment on the same line if that's what you want. That would turn it into a single-line If statement - which would be no use to you or anyone. You simply have to realise that and move the comment from that line to another one so it's treated as you mean it - as a multi-line statement.

Does that make it clearer?

[NeoPa]

Oh, just as another little pointer for another part of your code - not really relevant to the question but worth pointing out anyway - line #23, where you use MsgBox(), should not be found within the Edit / Update section of your code. The user may not respond immediately to the prompt and then you get left with a lock on the table that is never released. Even a delay releasing it can cause serious problems so I suggest you move that after the rs.Update.

[NeoPa]

Hi Wazza.

Please disregard my earlier post about the multi-line If statements. It turns out my memory was flawed and that isn't a thing at all after all :-(

So, I guess we'd better look at what actually is going wrong then. Perhaps you could share some details of what you see as a problem with line #19 of your posted code and we could go from there?

[Wazza0161]

I have solved the problem by using a nested loop. Works brilliantly. Thanks for all your advice but I was able nut it out myself. I have tidied the code up a bit, used a query instead of a table, removed all the MsgBoxes relating to step confirmations, removed all comments. I am buzzing with this now. here is the sample from Immediate window, where I linked 3 valves with the same model. Hopefully I have added the coding correctly this time.
Valve No = 315, Index No = 576
Valve No = 317, Index No = 576
Valve No = 324, Index No = 576

Public Sub ValveLink_Click()

    Dim db As Database
    Dim rs As Recordset

    Dim IndexFocus  As Long
    Dim Valve_No As Long
    Dim Answer As String
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("21A-ValveIndexUpdate") 'Fields: ValveNo and Index + others
    
    IndexFocus = Me.[Valve Index]
    Valve_No = InputBox("Please enter the valve number you want to link to this model", "Valve Link", "Enter Valve")
    
    rs.MoveFirst
    Do While (Not rs.EOF)
        
        Do While (Not rs.EOF)
            If rs!ValveNo = Valve_No Then
                rs.Edit
                rs!Index = IndexFocus
                rs.Update
                Debug.Print "Valve No = " & rs!ValveNo & ", Index No = " & rs!Index
            End If
            rs.MoveNext
        Loop
        
        Answer = MsgBox("Do you have another valve to link?", vbQuestion + vbYesNo + vbDefaultButton2, "Another Valve to Link")
        If Answer = vbNo Then
           Exit Do
        Else
            Valve_No = InputBox("Please enter the valve number you want to link to this model", "Valve Link", "Enter Valve")
        End If
        rs.MoveFirst
    Loop
    rs.Close
    Set rs = Nothing
    Set db = Nothing
       
End Sub

[NeoPa]

Hi Wazza.

Nicely done. Including the [/CODE] on the last line instead of the next is something very few spot ;-)

Also, your code has a very logical look to it, and that's always a good sign.

As there's little else I can do for you I'll just post a version that's hopefully slightly improved and leave you to spot the differences & decide if there's anything there you want to take for yourself.

Public Sub ValveLink_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Dim IndexFocus As Long, Valve_No As Long, Answer As Long
    Dim strMsg As String

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("21A-ValveIndexUpdate") 'Fields: ValveNo and Index + others

    IndexFocus = Me.[Valve Index]

    Do Until rs.EOF
        Call rs.MoveFirst
        strMsg = "Please enter the valve number you want to link to this model"
        Valve_No = InputBox(strMsg, "Valve Link", "Enter Valve")

        Do Until rs.EOF
            If rs!ValveNo = Valve_No Then
                Call rs.Edit
                rs!Index = IndexFocus
                Call rs.Update
                strMsg = Replace("Valve No = %VN, Index No = %IN." _
                               , "%VN", rs!ValveNo)
                strMsg = Replace(strMsg, "%IN", rs!Index)
                Debug.Print strMsg
            End If
            Call rs.MoveNext
        Loop

        Answer = MsgBox(Prompt:="Do you have another valve to link?" _
                      , Buttons:=vbQuestion Or vbYesNo Or vbDefaultButton2 _
                      , Title:="Another Valve to Link")
        If Answer = vbNo Then Exit Do
    Loop
    Call rs.Close
    Set rs = Nothing
    Set db = Nothing

End Sub

At some other time, and in another thread, we could maybe talk about applying the updates more simply using SQL code. Much less to worry about that way. As & when you're up for it though.