That shouldn't be a problem, and it still wouldn't require a loop.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.
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
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:Code:doevents
I will give this a try. Thank youUsing Update query without using loop:
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 " 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
This line is getting syntax errorI will give this a try. Thank you
I apologize for being so inept with SQL code writing.Remove the final ) or add a ( before Forms
Hi. Glad to hear you're making good progress without using a loop. Cheers!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
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?& "" & "'))"
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.I thought I remed out the concatenating empty string.