Solved Use Textbox value in a Loop

what are the DataType of these fields from your table Students:

1.UpdateWorkshop field
2. Session field

if UpdateWorkshop field is Yes/No field try substituting 0 for 'No', and -1 for 'Yes'.
if Session is Numeric datatype then replace:

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

with:

strSQL = strSQL & "AND (S.Session=" & Val([Forms]![WorkshopsSession]![SessionCombo] & "") & "));"
 
Micron said:
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.
Click to expand...

Hello again

Is it possible that the updating of the Actual records is 0 (zero) as the SQL statement is not executing because I have remed out the .execute strSQL?
.Execute strSQL, dbFailonError produces a Microsoft VB run time error 3061. Too few parameters. Expected 2.

I am such a novice I do not have a clue as to what the second parameter should be.

1585338182219.png

Here is the code that is producing that error

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] & "'));"

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

End Sub
 
Can you put
debug.print strSql

before the currentdb.execute? Then post back here the sql string so we can see the issue. Also If you alias a Table in a subquery I do not know if you need to lead all fields with the alias. S.Sid
 
I must be putting Debug.Printer strSQL in the wrong spot as I am getting the same message I get when the Execute is remed out.
1585339895417.png


Private Sub cmdUpdate_Click()
Dim strSQL As String
'strSQL = "UPDATE Students SET Students.UpdateWorkshop = 'Yes' "
strSQL = "UPDATE Students SET Students.UpdateWorkshop = 'Yes' "
'strSQL = "UPDATE Students SET Students.UpdateWorkshop = 'True' "
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] & "'));"

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

End Sub
 
mreniff said:
Hello again

Is it possible that the updating of the Actual records is 0 (zero) as the SQL statement is not executing because I have remed out the .execute strSQL?
.Execute strSQL, dbFailonError produces a Microsoft VB run time error 3061. Too few parameters. Expected 2.

I am such a novice I do not have a clue as to what the second parameter should be.

View attachment 80220
Here is the code that is producing that error

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] & "'));"

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

End Sub
Click to expand...
Hi. You can also try this function. For example:
Code: 
fExecuteQuery strSQL, dbFailOnError

 
mreniff said:
I am sorry but I do not understand where to put that code or how to use it.
Click to expand...
Hi. How to use it, I just showed you above with an example. As for where to put it, copy and paste the function from the website into a Standard Module and leave the name of the module as "Module1." Does that help?
 
the too few parameters usually happens because a) reference is mis-spelled, or b) in the case of passing vba constructed sql to JET/ACE the references are not resolved. One complicated way is to declare the parameters and assign values as part of your code. Less complicated is to define the parameters in the query itself, but here you don't have one. Another method that I've found works is to Dim variables for your form fields, assign values (by referring to the form controls) then concatenate the variables into your sql and not the form references.
 
theDBguy said:
Hi. How to use it, I just showed you above with an example. As for where to put it, copy and paste the function from the website into a Standard Module and leave the name of the module as "Module1." Does that help?
Click to expand...
Thank you. I am grateful for your help, but this is just too far passed my skills level. I will continue with the original code
 
mreniff said:
Thank you. I am grateful for your help, but this is just too far passed my skills level. I will continue with the original code
Click to expand...
Okay, just one last try and then that's it. Can you try the following steps?
  1. Got to the link/website and copy the entire code
  2. Open your Access database file and hit Alt+F11
  3. Create a new blank module
  4. Paste the copied code in the new module
  5. Save the module and accept the default name (e.g. Module1)
  6. In your current code, replace the line: CurrentDb.Execute with fExecuteQuery (as shown earlier)

    Let us know how it goes...
 
theDBguy said:
Okay, just one last try and then that's it. Can you try the following steps?
  1. Got to the link/website and copy the entire code
  2. Open your Access database file and hit Alt+F11
  3. Create a new blank module
  4. Paste the copied code in the new module
  5. Save the module and accept the default name (e.g. Module1)
  6. In your current code, replace the line: CurrentDb.Execute with fExecuteQuery (as shown earlier)

    Let us know how it goes...
Click to expand...
Thank you for being so patient with me. I followed your instructions. I pasted the code from http://www.accessmvp.com/thedbguy/codes.php?title=execqry and saved it as Module1.

My code is a follows:

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] & "'));"

With CurrentDb
.fExecuteQuery strSQL, dbFailOnError
MsgBox "Actual records updated: " & .RecordsAffected

End With
' reset textbox to blank 'remed out
Me.Textbox1 = Null

End Sub

the resulting error message is

1585348798190.png


thank you for reviewing this for me.
 
After 3 pages of posts, time to upload a file, I think. If it was requested/uploaded I missed it.
 
mreniff said:
Thank you for being so patient with me. I followed your instructions. I pasted the code from http://www.accessmvp.com/thedbguy/codes.php?title=execqry and saved it as Module1.

My code is a follows:

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] & "'));"

With CurrentDb
.fExecuteQuery strSQL, dbFailOnError
MsgBox "Actual records updated: " & .RecordsAffected

End With
' reset textbox to blank 'remed out
Me.Textbox1 = Null

End Sub

the resulting error message is

View attachment 80242

thank you for reviewing this for me.
Click to expand...
Ah, sorry, I didn't notice your code earlier. So, replace all this:
Code: 
With CurrentDb
    .fExecuteQuery strSQL, dbFailOnError 
   MsgBox "Actual records updated: " & .RecordsAffected
   
End With
with simply this (for now):
Code: 
fExecuteQuery strSQL, dbFailOnError
Please note there is no leading dot before fExecuteQuery.
 
can you just make a snapshot of Students table structure.
 
mreniff said:
File is too large to upload.
Click to expand...
Did you compact it first then zip it? If still too large, remove unnecessary forms/reports/ etc - especially tables with attachment type fields if you have any and are not required for this. The forum is the preferred place to download from. Your link requires an M$ login.
 
theDBguy said:
Ah, sorry, I didn't notice your code earlier. So, replace all this:
Code: 
With CurrentDb
    .fExecuteQuery strSQL, dbFailOnError
   MsgBox "Actual records updated: " & .RecordsAffected
 
End With
with simply this (for now):
Code: 
fExecuteQuery strSQL, dbFailOnError
Please note there is no leading dot before fExecuteQuery.
Click to expand...
Micron said:
Did you compact it first then zip it? If still too large, remove unnecessary forms/reports/ etc - especially tables with attachment type fields if you have any and are not required for this. The forum is the preferred place to download from. Your link requires an M$ login.
Click to expand...
Micron said:
Did you compact it first then zip it? If still too large, remove unnecessary forms/reports/ etc - especially tables with attachment type fields if you have any and are not required for this. The forum is the preferred place to download from. Your link requires an M$ login.
Click to expand...
I will have to remove objects next as I already did compact it. I will read the instructions for prepping files and uploading to this forum
 

Attachments

  • 1585353302908.png
    1585353302908.png
    12.4 KB · Views: 135
You must log in or register to reply here.

Similar threads

D
Solved Looping on a form for several textboxes to be enabled
Replies
5
Views
272
June7
June7
B
form/subform problem
Replies
12
Views
315
bigmac
B
T
Refresh Form value based on update Query
Replies
5
Views
253
Pat Hartman
P
D
Solved Referencing a field within a RecordSet...
2
Replies
23
Views
1,084
cheekybuddha
cheekybuddha
L
Forms Substitution Within A Loop
Replies
11
Views
412
lhooker
L

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom