Solved Use Textbox value in a Loop (1 Viewer)

[mreniff]

I want to nest an update query inside a Do Loop. I am not sure which loop is the best for this.

The user will enter a number in a textbox and then an update will run until the value in the textbook is reached. This Query is to run to select records by checking a box (title is update) in a subform. I would rather have a module that I can use on multiple forms and subforms and not have to create code for each form.


I am picturing some code like this

Private Sub Text27_Afterupdate()
i = Textbox value
Do Until i>[Textbook value] 'Condition is False.Hence loop will be executed

UPDATE Students SET Students.UpdateWorkshop = Yes
WHERE (((Students.UpdateWorkshop)=No) AND ((Students.Major)="BADM" Or (Students.Major)="PBA") AND ((Students.Workshop) Is Null) AND ((Students.Session)=[Forms]![WorkshopsSession]![SessionCombo]));
i = i + 1
Loop
End Sub

I am a newbie to Do Until loops

 

[theDBguy]

Hi. What is the purpose of the loop? Will you be running the same update query using the same values in each iteration of the loop?

 

[CJ_London]

a loop is a loop is a loop. you can use

do until true
loop

while true
wend

for i=1 to 5
next i

not sure if it is a typo but if textbox and textbook are supposed to be the same then your code will only go through one iteration

 

[vba_php]

you need to watch out for running extremely speedy code with query operations. Access isn't THAT good. you might be headed for corruption issues if you attempt to run too many loops. the code doesn't slow down, but queries take a while to process. UPDATE queries might be an exception to this rule, though. you might do yourself a favor by putting the following code in after your UPDATE sql statement:

doevents

 

[arnelgp]

you may try adding a Command button to do the actual Update (caption like Update).
add code the the Click Event:

' cmdUpdate is the name of the command button
Private Sub cmdUpdate_Click()
    Dim bolFindNext As Boolean
    ' TextBox1 is the name of the textbox with number of Loop
    If IsNull(Me.TextBox1) = False Then
        ' TheSubForm is the name of the subform
        ' you will need to add the fields UpdateWorkshop, Major, Workshop and Session to
        ' the this subform. If you dont want to show these fields
        ' add code to the subforms Load event making their ColumnVisible property to False.
        With Me.TheSubForm.Form.RecordsetClone
            If Not (.BOF And .EOF) Then
                .MoveFirst
                While Me.TextBox1 > 0
                    If bolFindNext Then
                        .FindNext "Nz(UpdateWorkshop, 'No')='No' And [Major] IN ('BADM', 'PBA') And [Workshop] Is Null AND [Session]='" & [Forms]![WorkshopsSession]![SessionCombo] & "'"
                    Else
                        .FindFirst "Nz(UpdateWorkshop, 'No')='No' And [Major] IN ('BADM', 'PBA') And [Workshop] Is Null AND [Session]='" & [Forms]![WorkshopsSession]![SessionCombo] & "'"
                        bolFindNext = True
                    End If
                    If Not .NoMatch Then
                        .Edit
                        !UpdateWorkshop = "Yes"
                        .Update
                    End If
                    Me.TextBox1 = Me.TextBox1 - 1
                Wend
            End If
        End With
    End If
End Sub

 

[isladogs]

Perhaps I'm missing something but I cannot see why a loop is needed at all.
Unlike using recordsets where only one record is updated each time, when the update query runs it updates all the records specified by the WHERE conditions.
Running the same code repeatedly does exactly the same update each time and is therefore superfluous

 

[mreniff]

Hi. What is the purpose of the loop? Will you be running the same update query using the same values in each iteration of the loop?

If the value 10 in the textbox then the update query run 10 times and check 10 boxes

 

[MajP]

You probably do not need this complicated loop. If for some reason you want to only update the top X records that meet this criteria contained in the subform then simply do an update query on the top X using the same criteria of the subform. That should be like 2-3 lines of code.

 

[theDBguy]

If the value 10 in the textbox then the update query run 10 times and check 10 boxes

Okay, but which 10 checkboxes would those be? How do you know which one to update? My question was whether the update query will use the same or different values every time it goes through the loop. Which one?

 

[isladogs]

If the value 10 in the textbox then the update query run 10 times and check 10 boxes

That confirms my suspicion. You don't need a loop.

 

[MajP]

I am assuming that means ten records in a continuous form and not ten different field boxes.

 

[mreniff]

you may try adding a Command button to do the actual Update (caption like Update).
add code the the Click Event:

' cmdUpdate is the name of the command button
Private Sub cmdUpdate_Click()
    Dim bolFindNext As Boolean
    ' TextBox1 is the name of the textbox with number of Loop
    If IsNull(Me.TextBox1) = False Then
        ' TheSubForm is the name of the subform which is BADM PBA Student - Group advising.Form".RecordsetClone
        ' you will need to add the fields UpdateWorkshop, Major, Workshop and Session to
        ' the this subform. If you dont want to show these fields
        ' add code to the subforms Load event making their ColumnVisible property to False.
        **With Me. BADM PBA Student - Group advising.Form.Form.RecordsetClone
            If Not (.BOF And .EOF) Then
                .MoveFirst
                While Me.TextBox1 > 0
                    If bolFindNext Then
                        .FindNext "Nz(UpdateWorkshop, 'No')='No' And [Major] IN ('BADM', 'PBA') And [Workshop] Is Null AND [Session]='" & [Forms]![WorkshopsSession]![SessionCombo] & "'"
                    Else
                        .FindFirst "Nz(UpdateWorkshop, 'No')='No' And [Major] IN ('BADM', 'PBA') And [Workshop] Is Null AND [Session]='" & [Forms]![WorkshopsSession]![SessionCombo] & "'"
                        bolFindNext = True
                    End If
                    If Not .NoMatch Then
                        .Edit
                        !UpdateWorkshop = "Yes"
                        .Update
                    End If
                    Me.TextBox1 = Me.TextBox1 - 1
                Wend
            End If
        End With
    End If
End Sub

this is the code I am trying to run
Private Sub cmdUpdate_Click()
' cmdUpdate is the name of the command button
'Private Sub cmdUpdate_Click()
Dim bolFindNext As Boolean
' TextBox1 is the name of the textbox with number of Loop
If IsNull(Me.Textbox1) = False Then
' TheSubForm is the name of the subform
' you will need to add the fields UpdateWorkshop, Major, Workshop and Session to
' the this subform. If you dont want to show these fields
' add code to the subforms Load event making their ColumnVisible property to False.
With Me.BADM PBA Student - Group advising.Form.RecordsetClone
If Not (.BOF And .EOF) Then
.MoveFirst
While Me.Textbox1 > 0
If bolFindNext Then
.FindNext "Nz(UpdateGrpAdvisingApt, 'No')='No' And [Major] IN ('BADM', 'PBA') And [GroupAdvisingSessionID] Is Null AND [Session]='" & [Forms]![SelectSessionsAdvisingGroup]![SessionCombo] & "'"
Else
.FindFirst "Nz(UpdateGrpAdvisingApt, 'No')='No' And [Major] IN ('BADM', 'PBA') And [GroupAdvisingSessionID] Is Null AND [Session]='" & [Forms]![SelectSessionsAdvisingGroup]![SessionCombo] & "'"
bolFindNext = True
End If
If Not .NoMatch Then
.Edit
!GroupAdvisingSessionID = "Yes"
.Update
End If
Me.Textbox1 = Me.Textbox1 - 1
Wend
End If
End With
End If
End Sub

**I believe I am getting a syntax error on With Me.BADM PBA Student - Group advising.Form.RecordsetClone because of the spaces in the object's name. Is there a way to force ACCESS VBA to accept the spaces or do I have to rename the subform.?

 

[MajP]

Couple of ways
me.controls("BADM PBA Student - Group ")
me![BADM PBA Student - Group]

 

[mreniff]

Couple of ways
me.controls("Me.BADM PBA Student - Group ")
me![Me.BADM PBA Student - Group]

I was close. I tried the form name in brackets and quotes but did not include the ME part.

This Me.Controls("Me.BADM PBA Student - Group advising").Form.RecordsetClone get this error messge

Okay, but which 10 checkboxes would those be? How do you know which one to update? My question was whether the update query will use the same or different values every time it goes through the loop. Which one?

It will use the same values to loop from one record to the next until ten records has a checkbox selected.

 

[mreniff]

Couple of ways
me.controls("Me.BADM PBA Student - Group ")
me![Me.BADM PBA Student - Group]

I was close. I tried the form name in brackets and quotes but did not include the ME part.

this error is the results of both of the following code View attachment 80156
With Me![Me.BADM PBA Student - Group advising].Form.RecordsetClone
With Me.Controls("Me.BADM PBA Student - Group advising").Form.RecordsetClone

The subform BADM PBA Student - Group advising with the list with checkboxes is a subform of BADM PBA GROUP ADVISING SCHEDULER

The textbox1 and the cmdUpdate reside on the BADM PBA GROUP ADVISING SCHEDULER

 

[MajP]

I apologize I was cutting and pasting, and did not see what I typed. The me should not be in the inside. Sorry for that..
Should read
me.controls("BADM PBA Student - Group ")
me![BADM PBA Student - Group]

 

[mreniff]

I am sorry but neither of the code statements work. They get the same error message as before.

 

[theDBguy]

It will use the same values to loop from one record to the next until ten records has a checkbox selected.

Okay, thanks for trying to clarify the requirement. But since I can't see what you're looking at, I guess I still don't understand what you're trying to do. For example, I still don't understand the need for a loop. Why can't a single UPDATE query do the job? In any case, I think you are already working on a solution towards your goal, so I'll just let you continue with it. Good luck!

 

[mreniff]

Okay, thanks for trying to clarify the requirement. But since I can't see what you're looking at, I guess I still don't understand what you're trying to do. For example, I still don't understand the need for a loop. Why can't a single UPDATE query do the job? In any case, I think you are already working on a solution towards your goal, so I'll just let you continue with it. Good luck!

An update query has to be intelligent enough to know how many records to select. I have update queries that work perfectly well for lists that do not exceed a preset limit. As you know, my first thought was to simply "nest" an update query in a loop. The loop stops when it has reached the value in the textbox.

The DB is 67MBs. I doubt I can upload it.

I appreciate your help. You have provided me with very helpful skills that I did not have before.

Again, thank you.

 

[theDBguy]

An update query has to be intelligent enough to know how many records to select. I have update queries that work perfectly well for lists that do not exceed a preset limit. As you know, my first thought was to simply "nest" an update query in a loop. The loop stops when it has reached the value in the textbox.

The DB is 67MBs. I doubt I can upload it.

I appreciate your help. You have provided me with very helpful skills that I did not have before.

Again, thank you.

Hi. An UPDATE query can be as smart as you make it. Are you saying, for example, to only update 10 random records or only the first 10 records? If so, that should be possible, I believe.