Changing a value of field using a loop

I have some code that opens a form and moves to a specific field on a subform. I want my code to search the listed records on that field, change the value if they are a specific value and loop until the last record. Any record that is changed also needs to have a date entered (in a already assigned variable)
Here is some code to clarify:
If strchoice = vbYes Then
        DoCmd.OpenForm "RankHis"
        DoCmd.GoToControl "Rank History"
        DoCmd.RunCommand acCmdRecordsGoToFirst
        DoCmd.GoToControl "Rank Status"
        Do Until IsNull([Rank Status])
 -->Here's where I need help
             If [Rank Status] = "A" Then
             [Rank Status] = "U"
             [Date Removed] = datevar
             DoCmd.Run Command acCmdRecordsGoToNext
             Loop      
             Else
                Exit Sub
                DoCmd.Close
            End If
 
As you can see I would like the value of A to be changed to U and loop until all A's are changed

Thanks in advance
kskAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrianWrenCommented:
If strchoice = vbYes Then
        DoCmd.OpenForm "RankHis"
        DoCmd.GoToControl "Rank History"
        DoCmd.RunCommand acCmdRecordsGoToFirst
        DoCmd.GoToControl "Rank Status"
        Do Until Forms!RankHis.NewRecord = False
           If [Rank Status] = "A" Then
              [Rank Status] = "U"
              [Date Removed] = datevar
              docmd.RunCommand acCmdSaveRecord
           End If
           DoCmd.Run Command acCmdRecordsGoToNext
        Loop      

Does that do it?

Brian
0
BrianWrenCommented:
Dim frm As Form

If strchoice = vbYes Then
   DoCmd.OpenForm "RankHis"
   Set frm = Forms("RankHis")
   frm.SetFocus
   Do Until frm.NewRecord = False
      If frm![Rank Status] = "A" Then
         frm![Rank Status] = "U"
         frm![Date Removed] = datevar
         docmd.RunCommand acCmdSaveRecord
      End If
      DoCmd.RunCommand acCmdRecordsGoToNext
   Loop
   Set frm = Nothing
End If

This is probably better...
0
ZWhizCommented:
Instead of running a painfully slow loop through the recordset, try this instead:

Dim strSQL As String
strSQL = "UPDATE tblRankHis SET [Rank Status] = 'A', "
strSQL = strSQL & "[Date Removed] = Date() "
strSQL = strSQL & "WHERE [Rank Status] = 'U' "

CurrentDB().Execute strSQL

***
You will have to substitute your actual table name where I put "tblRankHis"
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ZWhizCommented:
Sorry; I crossed up the 'A' and the 'U' but you can still see what I mean.
0
kskAuthor Commented:
All of the possible solutions get the focus to the Rank Status field but they don't execute any changes. Rank History is a subform (Rank Status is one of its fields) on this form - would that effect why its not working? Also the Rank Status field is a combo box with only two choices (U and A) - Could this have any effect on the code?
0
tomk120999Commented:
ksk, this is just a couple of thoughts, but have you used 'Me.Refresh' or 'Me.Requery' on the form?  If not, you may need to do that after the changes have taken place.  Verify this by looking at the underlying table after the code runs to see if the changes are actually taking place.  Another thing to check into is if the combobox is on a subform, you might need to use syntax like Forms!FormName.Form!ComboBoxName.

I tend to favor the update query method although I can't think of a particular reason.

good luck as usual...
0
kskAuthor Commented:
I decided to test this code in the subform - separate from the form in which it is embedded. This code works:
DoCmd.RunCommand acCmdRecordsGoToFirst
DoCmd.GoToControl "Rank Status"
Do Until IsNull([Rank Status])                        
If [Rank Status] = "A" Then
[Rank Status] = "U"
[Date Removed] = datevar
DoCmd.Run Command acCmd SaveRecord
End If
DoCmd.Run Command acCmdRecordsGoToNext
Loop

I pasted it into the other code for the big form and it doesn't seem to work. Maybe some of the preceding code is causing a problem. Here is some of the code before the
Dim strresponse As String
Dim strchoice As String
On Error GoTo caseerr
If [Status] = "C" Then
strresponse = InputBox("You are closing this case." & Chr(13) & "Please enter a closed date." & Chr(13) & "FORMAT 12/1/1999", "Closed Date", Date)
[Date Closed].Value = strresponse
Else
 Exit Sub
 End If
strchoice = MsgBox("Do you want to change the ranking for this case?", vbYesNo, "Case Closed")
        If strchoice = vbYes Then
            DoCmd.OpenForm "RankHis"
            DoCmd.GoToControl "Rank History"
            DoCmd.RunCommand acCmdRecordsGoToFirst
                      ETC.
Would the fact that the form RankHis and subform Rank History are opened with filtered data affect the code working?
0
BrianWrenCommented:
All of these commands

Do Until IsNull([Rank Status])                        
If [Rank Status] = "A" Then
[Rank Status] = "U"
[Date Removed] = datevar

refer to controls on the form which has this code.  With no qualifier, it is essentially as if you have said

Do Until IsNull(Me![Rank Status])                        
If Me![Rank Status] = "A" Then
Me![Rank Status] = "U"
Me![Date Removed] = datevar

Instead, refer explicitly to the subform's controls

Do Until IsNull(Me![Rank History].Form![Rank Status])                        
If Me![Rank History].Form![Rank Status] = "A" Then
Me![Rank History].Form![Rank Status] = "U"
Me![Rank History].Form![Date Removed] = datevar

Brian
0
tomk120999Commented:
In the first code segment you used GoToControl "Rank Status", in the bottom segment it says, "Rank History".  Isn't that the subform?  If it is, you may not necessarily be on the control you expect.  Also, let me suggest a way to avoid possible confusion:  substitute Me!ControlName.SetFocus for DoCmd.GoToControl, that explicitly sets the active control to where you want it. (also see my comment above).  For subforms, you can use Me!SubFormName.SetFocus and then Me!ControlName.SetFocus.  'Me' always refers to the active form and is faster, and the bang(!) refers to the default collection, in this case controls, and is faster.  Take another look at Brian's earlier post for the bang operator.

If that isn't the case, then let me ask some basic questions:
1. Are you matching/linking the current record on the "big form" to RankHis when you open it?  If so how?  Also see 4 below.

2.  Is RankHis a subform of the big form or separate?  IOW, why does it need a .OpenForm?

3.  Specifically, what is the record source for the big form and RankHis and Rank History (the SubForm?)?  I'm asking for the specific table name(s) or to see the SQL.

4.  You said something about RankHis using filtered data.  How, exactly, does it get filtered?  SQL in the record source using criteria from the big form, or...?  It would help to see it.

5.  And I guess this should be first, just what actions, in words, do you want to achieve from the code in that whole loop, starting from the big form and how is the data constructed, many records referring to one case?  I gather that you want to user to change a value, and then have all records change, but I am really asking for the workflow so to speak.

I know this is a lot of work on your part, but the guys on this board can sort it out.  We appreciate your patience.

good luck as usual...
0
tomk120999Commented:
My, my, Brian, we were both working on it at the same time and I didn't check for another comment before posting.  Sorry. :)
0
BrianWrenCommented:
's awright!
0
kskAuthor Commented:
In response to tomk, here's how this all works:
First - the code is located in the on change event, status field, in Form A:
In Form A, click on status field combo box, select C and msgbox appears closing case and prompting for date. Date is assigned to date closed field.
msgbox appears asking to open rankhis form. Rankhis Form and Form A have a one-to-many relationship with a join type of 3. Form A's primary key is field called control no. When the Rankhis Form is opened it opens with the control no from Form A. Rankhis has a subform called Rank History. The child and master fields are also the control no. After Rankhis is opened the focus moves to the subform and then the field (Rank Status).
      In my last posting the code I showed gets the focus on the correct field (Rank Status), but it will not initiate the loop. When I attached the code (that's shown in my last posting) to the got focus event in the Rank History (subform) and moved the cursor, the loop inititate fine.
     Other than the relationships above, there is no SQL attached to the forms.
0
tomk120999Commented:
ksk, I suspect that with the loop in the main form, the DoCmd structure is referring to the main form record source instead of the Rank History (filtered) records.  Since you say that the code works when in the subform, I would try to put the code in the subform or subform control event (OnOpen, or OnGotFocus).  However, if you use the RankHis form and the Rank History subform for other purposes, you may have to provide some decision logic in those forms to make sure records aren't changed when you don't want them to be.  If the form is there solely to change the records on request, then it should work fine.

Alternatively, you could declare a recordset within the main form loop, set it to the filtered records and do the loop from there.  Since you already have the forms constructed, I think that the simplest way is to try the change loop in the Rank Hisory subform to see if that gets you there.  Let us know.  If that doesn't get it, then I will test it and try to post some code to accomplish the goal.  I have to say that Brian and ZWhiz have given some excellant stuff.

good luck as usual...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrianWrenCommented:
ZWhiz's idea of using a query is simple and elegant.  Queries were made just for this sort of thing.  They're fast, accurate, and you don't have to think as hard as you do when you are writing code to do the same thing.

When it is time to update the subform, I would do this.

Turn off the screen
Set the Sub Form's RecordSource to ""
Run the SQL ZWhiz suggested, with appropriate constraints in the WHERE clause.
Set the Sub Form's RecordSource back to what it was.
Turn the screen back on.

  Dim s as string
  Dim HoldRS As String
  Dim d As Database
  Set d = CurrentDB

  Application.Echo False
  HoldRS = Me!<SubControlName>.Form.RecordSource
  Me!<SubControlName>.Form.RecordSource = ""

  s = "UPDATE tblRankHis SET [Rank Status] = 'A', " & _
      "[Date Removed] = Date() " & _
       "WHERE [Rank Status] = 'U' " 

  d.Execute s

  Me!<SubControlName>.Form.RecordSource = HoldRS

  Application.Echo True

Brian
 
0
tomk120999Commented:
Brian, as I said before, I favor the update Query.  It does what's required in a 'gulp'.  But there's always more ways to skin a cat, which is woderful if you aren't a cat!

ksk, what do you think so far?  If you like ZWhiz's method, test it and see what it does for you.  May make it easier.

Good luck as usual...
0
tomk120999Commented:
I favor the update query because it does what you want in one gulp.  ZWhiz's got a handle on that.

ksk, what do you think so far?
0
kskAuthor Commented:
Adjusted points to 60
0
kskAuthor Commented:
I think I am going to go the route of update Query. I'll probably attach the SQL to the code. Thanks to everyone for their help. You certainly have given me enough code to think about! Thanks!
     KSK
0
tomk120999Commented:
KSK, thanks for the points.  If you decide to use another method and it does good things for you, consider posting a question specifically for the guy who proposed it to give him the nod for his contribution.  We're always glad we could help.

good luck as usual...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.