470,593 Members | 2,471 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,593 developers. It's quick & easy.

Having trouble updating a recordset

3 2Bits
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).
Expand|Select|Wrap|Line Numbers
  1. Public Sub ValveLink_Click()
  2.  
  3.     Dim db As Database
  4.     Dim rs As Recordset
  5.  
  6.     Dim IndexFocus  As Long
  7.     Dim Valve_No As Long
  8.     Dim i As Integer
  9.     Dim Answer As String
  10.  
  11.     Set db = CurrentDb
  12.     Set rs = db.OpenRecordset("NameplateIndexUpdate") 'Fields: ValveNo and Index + others
  13.  
  14.     i = 0
  15.     IndexFocus = Me.[Valve Index]             'transferred from a form, proven to be ok.
  16.     Valve_No = InputBox("Please enter the valve number you want to link to this model", "Valve Link", "Enter Valve")
  17.  
  18.     Do While (Not rs.EOF) And (i < 15)
  19.         If rs!ValveNo = Valve_No Then                               '***** only works when I change it to =i *****
  20.             MsgBox ("This works, valve No is " & Valve_No)          'works ok when tested
  21.             rs.Edit
  22.             rs!Index = IndexFocus                                   'works ok when tested
  23.             MsgBox ("This works as well, Index No is " & rs![Index])  'works ok when tested
  24.             rs.Update
  25.             Debug.Print rs!Index                                    'works ok when tested
  26.         End If
  27.         Answer = MsgBox("Do you have another valve to link?", vbQuestion + vbYesNo + vbDefaultButton2, "Another Valve to Link") 'Works OK
  28.         If Answer = vbNo Then                                       'works ok
  29.             Exit Do                                                 'works ok when tested, drops out of loop
  30.         Else
  31.             Valve_No = InputBox("Please enter the valve number you want to link to this model", "Valve Link", "Enter Valve") 'works ok
  32.         End If
  33.         rs.MoveNext
  34.         i = i + 1
  35.         Loop
  36.     rs.Close
  37.     Set rs = Nothing
  38.     db.Close
  39.  
  40. End Sub
Mar 15 '22 #1
7 5095
NeoPa
32,298 Expert Mod 16PB
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.
Mar 15 '22 #2
Wazza0161
3 2Bits
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.
Mar 15 '22 #3
NeoPa
32,298 Expert Mod 16PB
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?
Mar 16 '22 #4
NeoPa
32,298 Expert Mod 16PB
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.
Mar 16 '22 #5
NeoPa
32,298 Expert Mod 16PB
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?
Mar 16 '22 #6
Wazza0161
3 2Bits
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

Expand|Select|Wrap|Line Numbers
  1. Public Sub ValveLink_Click()
  2.  
  3.     Dim db As Database
  4.     Dim rs As Recordset
  5.  
  6.     Dim IndexFocus  As Long
  7.     Dim Valve_No As Long
  8.     Dim Answer As String
  9.  
  10.     Set db = CurrentDb
  11.     Set rs = db.OpenRecordset("21A-ValveIndexUpdate") 'Fields: ValveNo and Index + others
  12.  
  13.     IndexFocus = Me.[Valve Index]
  14.     Valve_No = InputBox("Please enter the valve number you want to link to this model", "Valve Link", "Enter Valve")
  15.  
  16.     rs.MoveFirst
  17.     Do While (Not rs.EOF)
  18.  
  19.         Do While (Not rs.EOF)
  20.             If rs!ValveNo = Valve_No Then
  21.                 rs.Edit
  22.                 rs!Index = IndexFocus
  23.                 rs.Update
  24.                 Debug.Print "Valve No = " & rs!ValveNo & ", Index No = " & rs!Index
  25.             End If
  26.             rs.MoveNext
  27.         Loop
  28.  
  29.         Answer = MsgBox("Do you have another valve to link?", vbQuestion + vbYesNo + vbDefaultButton2, "Another Valve to Link")
  30.         If Answer = vbNo Then
  31.            Exit Do
  32.         Else
  33.             Valve_No = InputBox("Please enter the valve number you want to link to this model", "Valve Link", "Enter Valve")
  34.         End If
  35.         rs.MoveFirst
  36.     Loop
  37.     rs.Close
  38.     Set rs = Nothing
  39.     Set db = Nothing
  40.  
  41. End Sub
Mar 17 '22 #7
NeoPa
32,298 Expert Mod 16PB
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.
Expand|Select|Wrap|Line Numbers
  1. Public Sub ValveLink_Click()
  2.  
  3.     Dim db As DAO.Database
  4.     Dim rs As DAO.Recordset
  5.  
  6.     Dim IndexFocus As Long, Valve_No As Long, Answer As Long
  7.     Dim strMsg As String
  8.  
  9.     Set db = CurrentDb()
  10.     Set rs = db.OpenRecordset("21A-ValveIndexUpdate") 'Fields: ValveNo and Index + others
  11.  
  12.     IndexFocus = Me.[Valve Index]
  13.  
  14.     Do Until rs.EOF
  15.         Call rs.MoveFirst
  16.         strMsg = "Please enter the valve number you want to link to this model"
  17.         Valve_No = InputBox(strMsg, "Valve Link", "Enter Valve")
  18.  
  19.         Do Until rs.EOF
  20.             If rs!ValveNo = Valve_No Then
  21.                 Call rs.Edit
  22.                 rs!Index = IndexFocus
  23.                 Call rs.Update
  24.                 strMsg = Replace("Valve No = %VN, Index No = %IN." _
  25.                                , "%VN", rs!ValveNo)
  26.                 strMsg = Replace(strMsg, "%IN", rs!Index)
  27.                 Debug.Print strMsg
  28.             End If
  29.             Call rs.MoveNext
  30.         Loop
  31.  
  32.         Answer = MsgBox(Prompt:="Do you have another valve to link?" _
  33.                       , Buttons:=vbQuestion Or vbYesNo Or vbDefaultButton2 _
  34.                       , Title:="Another Valve to Link")
  35.         If Answer = vbNo Then Exit Do
  36.     Loop
  37.     Call rs.Close
  38.     Set rs = Nothing
  39.     Set db = Nothing
  40.  
  41. 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.
Mar 17 '22 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by ed | last post: by
2 posts views Thread by Stu | last post: by
1 post views Thread by omar.norton | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.