Solved Use Textbox value in a Loop (1 Viewer)

[arnelgp]

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

 

[mreniff]

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.

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.

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

 

[MajP]

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

 

[mreniff]

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.

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

 

[theDBguy]

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

Hi. You can also try this function. For example:

fExecuteQuery strSQL, dbFailOnError

Code Snippets

 

[mreniff]

Hi. You can also try this function. For example:

fExecuteQuery strSQL, dbFailOnError

Code Snippets

I am sorry but I do not understand where to put that code or how to use it.

 

[theDBguy]

I am sorry but I do not understand where to put that code or how to use it.

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?

 

[Micron]

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.

 

[mreniff]

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?

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

 

[theDBguy]

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

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...

 

[mreniff]

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...

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

thank you for reviewing this for me.

 

[Micron]

After 3 pages of posts, time to upload a file, I think. If it was requested/uploaded I missed it.

 

[mreniff]

Give me a few minutes to make a stripped down version

 

[mreniff]

After 3 pages of posts, time to upload a file, I think. If it was requested/uploaded I missed it.

Give me a few minutes to make a stripped down version

 

[theDBguy]

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.

Ah, sorry, I didn't notice your code earlier. So, replace all this:

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

with simply this (for now):

fExecuteQuery strSQL, dbFailOnError

Please note there is no leading dot before fExecuteQuery.

 

[mreniff]

After 3 pages of posts, time to upload a file, I think. If it was requested/uploaded I missed it.

File is too large to upload. Can you access it from here? https://uoregon-my.sharepoint.com/:.../2020 Introducktion MASTER.zip?csf=1&e=9beepn

 

[arnelgp]

can you just make a snapshot of Students table structure.

 

[Micron]

File is too large to upload.

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.

 

[mreniff]

Ah, sorry, I didn't notice your code earlier. So, replace all this:

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

with simply this (for now):

fExecuteQuery strSQL, dbFailOnError

Please note there is no leading dot before fExecuteQuery.

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.

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.

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

 

[mreniff]

can you just make a snapshot of Students table structure.

let me know if this will not work for you.