Solved Use Textbox value in a Loop (1 Viewer)

[mreniff]

the number of records selected will always vary. I need the means to allow for user input before the records are selected.

 

[theDBguy]

the number of records selected will always vary. I need the means to allow for user input before the records are selected.

That shouldn't be a problem, and it still wouldn't require a loop.

 

[arnelgp]

you can rename the subform in Design view (of the main form and not of the subform itself).
click on the Border of the subform so as a yellow rectangle surrounds the subform.
on it's Property Sheet>Other->Name, type something shorter, ie sfrm1.
use sfrm1 in your code.

correction on Post#5, should be ColumnHidden property and not ColumnVisible.

 

[isladogs]

That shouldn't be a problem, and it still wouldn't require a loop.

I agree and made the same point in posts #6 & #10.

 

[arnelgp]

Using Update query without using loop:

Private Sub cmdUpdate_Click()
Dim strSQL As String
strSQL = "UPDATE Students SET Students.UpdateWorkshop = 'Yes' "
strSQL = strSQL & "WHERE Students.[SID] IN "
strSQL = strSQL & "(SELECT TOP " & Clng(Me.TextBox1) & " [SID] FROM Students As S "
strSQL = strSQL & "WHERE (Nz(S.UpdateWorkshop, 'No')='No') AND (S.Major In ('BADM', 'PBA')) "
strSQL = strSQL & "AND (S.Session='" & [Forms]![WorkshopsSession]![SessionCombo] & "" & "'"));"

With Currentdb
    .Execute strSQL, dbFailonError
    Msgbox "Actual records updated: " & .RecordsAffected
End With
' reset textbox to blank
Me.Textbox1 = Null

End Sub

 

[The_Doc_Man]

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

Adam, without knowing whether the database is specifically using a pass-through query to a non-Access backend, this statement is not technically correct. Please do not disseminate incorrect answers like this.

ALL native Access queries (i.e. Access back-end) are synchronous because Access itself is single-threaded. Both JET and ACE engines (the two possible native Access engines) run queries single-threaded. The only thing they do asynchronously is cleanup after a query is done. (We learned that from an article published by Microsoft in one of their corporate-sponsored forums.) VBA code, which is interpreted, will NOT outrun VBA code for which the SQL processor is compiled. And in any case, for native Access, if you do a RunSQL OR if you do a CurrentDB.Execute, your process pauses for the end of the query.

If and ONLY if there is an intelligent back end like SQL Server or some other active SQL engine, AND you are using pass-through queries, THEN you can get asynchronous behavior. Therefore, without determining the environment, you cannot say what you said.

 

[mreniff]

Using Update query without using loop:

Private Sub cmdUpdate_Click()
Dim strSQL As String
strSQL = "UPDATE Students SET Students.UpdateWorkshop = 'Yes' "
strSQL = strSQL & "WHERE Students.[SID] IN "
strSQL = strSQL & "(SELECT TOP " & Clng(Me.TextBox1) & " [SID] FROM Students As S "
strSQL = strSQL & "WHERE (Nz(S.UpdateWorkshop, 'No')='No') AND (S.Major In ('BADM', 'PBA')) "
strSQL = strSQL & "AND (S.Session='" & [Forms]![WorkshopsSession]![SessionCombo] & "" & "'"));"

With Currentdb
    .Execute strSQL, dbFailonError
    Msgbox "Actual records updated: " & .RecordsAffected
End With
' reset textbox to blank
Me.Textbox1 = Null

End Sub

I will give this a try. Thank you

 

[mreniff]

I will give this a try. Thank you

This line is getting syntax error
strSQL = strSQL & "AND (S.Session='" & [Forms]![WorkshopsSession]![SessionCombo] & "" & "'"));"
I think there is parenthesis missing.

 

[isladogs]

Remove the final ) or add a ( before Forms

 

[mreniff]

Remove the final ) or add a ( before Forms

I apologize for being so inept with SQL code writing.

As soon as I pasted the code in the I got a compile error. I tried placing or removing the applicable parenthesis and I. still got the same error message.

The update query SQL,

UPDATE Students SET Students.UpdateWorkshop = Yes
WHERE (((Students.UpdateWorkshop)=No) AND ((Students.Major) In (Select [Majorcode] from [List of BADM Majorcodes])) AND ((Students.Workshop) Is Null) AND ((Students.Session)=[Forms]![WorkshopsSession]![SessionCombo]));

There is no reference to Students.SID. I assume SID is the record ID from the Students table.

 

[Micron]

EDITED:
"AND...![SessionCombo] & "" & "'))" Pretty sure you don't need the ending semicolon in vba generated sql but no matter.

I think that should fix that line, I think.

PS code is cooler than pictures - easier to copy/paste than it is to retype what you have.

 

[arnelgp]

replace the last line with:

    strSQL = strSQL & "AND (S.Session='" & [Forms]![WorkshopsSession]![SessionCombo] & "'));"

 

[mreniff]

Replacling the last line with strSQL = strSQL & "AND (S.Session='" & [Forms]![WorkshopsSession]![SessionCombo] & "'));" fixed compile error but now I am getting a "run time error 3061, too few parameters. Expected 2". What would be the 2nd parameter?

Private Sub cmdUpdate_Click()
Dim strSQL As String
strSQL = "UPDATE Students SET Students.UpdateWorkshop = 'Yes' "
strSQL = strSQL & "WHERE Students.[SID] IN "
strSQL = strSQL & "(SELECT TOP " & CLng(Me.Textbox1) & " [SID] FROM Students As S " ' select # or records based textbox1 value
strSQL = strSQL & "WHERE (Nz(S.UpdateWorkshop, 'No')='No') AND (S.Major In ('BADM', 'PBA')) " ' UpdateWorkshop must No or Null, Major IN BADM or PBA
'strSQL = strSQL & "AND (S.Session='" & ([Forms]![WorkshopsSession]![SessionCombo] & "" & "'");
strSQL = strSQL & "AND (S.Session='" & [Forms]![WorkshopsSession]![SessionCombo] & "'));"

With CurrentDb
.Execute strSQL, dbFailonError
MsgBox "Actual records updated: " & .RecordsAffected
End With
' reset textbox to blank
Me.Textbox1 = Null

End Sub

 

[arnelgp]

comment this out:

'.Execute strSQL, dbFailonError
'MsgBox "Actual records updated: " & .RecordsAffected

replace it with:

msgbox strSQL

is the SQL string well formed?

 

[mreniff]

You are amazing. I hope you understand how much I appreciate your help.

It is getting very close. I am getting encouraging results now except that the checkboxes are being selected. Maybe YES needs to be TRUE. e.g
"UPDATE Students SET Students.UpdateWorkshop = 'True' "

Latest code:
Private Sub cmdUpdate_Click()
Dim strSQL As String
strSQL = "UPDATE Students SET Students.UpdateWorkshop = 'Yes' "
strSQL = strSQL & "WHERE Students.[SID] IN "
strSQL = strSQL & "(SELECT TOP " & CLng(Me.Textbox1) & " [SID] FROM Students As S " ' select # or records based textbox1 value
strSQL = strSQL & "WHERE (Nz(S.UpdateWorkshop, 'No')='No') AND (S.Major In ('BADM', 'PBA')) " ' UpdateWorkshop must No or Null, Major IN BADM or PBA
'strSQL = strSQL & "AND (S.Session='" & ([Forms]![WorkshopsSession]![SessionCombo] & "" & "'");
strSQL = strSQL & "AND (S.Session='" & [Forms]![WorkshopsSession]![SessionCombo] & "'));"

With CurrentDb
'.Execute strSQL, dbFailonError
'MsgBox "Actual records updated: " & .RecordsAffected
MsgBox strSQL
End With
' reset textbox to blank
Me.Textbox1 = Null

End Sub

 

[theDBguy]

You are amazing. I hope you understand how much I appreciate your help.

It is getting very close. I am getting encouraging results now except that the checkboxes are being selected. Maybe YES needs to be TRUE. e.g
"UPDATE Students SET Students.UpdateWorkshop = 'True' "

View attachment 80204

Latest code:
Private Sub cmdUpdate_Click()
Dim strSQL As String
strSQL = "UPDATE Students SET Students.UpdateWorkshop = 'Yes' "
strSQL = strSQL & "WHERE Students.[SID] IN "
strSQL = strSQL & "(SELECT TOP " & CLng(Me.Textbox1) & " [SID] FROM Students As S " ' select # or records based textbox1 value
strSQL = strSQL & "WHERE (Nz(S.UpdateWorkshop, 'No')='No') AND (S.Major In ('BADM', 'PBA')) " ' UpdateWorkshop must No or Null, Major IN BADM or PBA
'strSQL = strSQL & "AND (S.Session='" & ([Forms]![WorkshopsSession]![SessionCombo] & "" & "'");
strSQL = strSQL & "AND (S.Session='" & [Forms]![WorkshopsSession]![SessionCombo] & "'));"

With CurrentDb
'.Execute strSQL, dbFailonError
'MsgBox "Actual records updated: " & .RecordsAffected
MsgBox strSQL
End With
' reset textbox to blank
Me.Textbox1 = Null

End Sub

Hi. Glad to hear you're making good progress without using a loop. Cheers!

 

[Micron]

& "" & "'))"

So why were you concatenating an empty string? Only difference is that I left it in. You never needed it, or will it affect your results if you don't have it?

 

[mreniff]

I meant that the checkboxes ARE NOT be selected or be set to True or to Yes. I am going to comment out the Me.Textbox1 = null

 

[mreniff]

I thought I remed out the concatenating empty string.

'strSQL = strSQL & "AND (S.Session='" & ([Forms]![WorkshopsSession]![SessionCombo] & "" & "'");

also I am such an unskilled newbie at this and I barely understand what I am reading.

 

[Micron]

I thought I remed out the concatenating empty string.

Then I don't think the line would have been red. I almost thought that maybe you formatted your post that way then remembered it's a pic.
BTW, maybe you're not using the Nz function correctly because you are not providing a value if Null. When Nz is used in a query, if you don't provide a value if Null, you will insert a zls (zero length string) where Null IF the field will accept it. If not, I presume you'd either partially update and then generate an error when it hits the first record in that circumstance, but more likely the query would simply not run at all. Since this is vba constructed sql, your code would fail and you have no error handler in it.