Table driven Update Script

[Tupacmoche]

I have a form that collects three (3) user selected values that are inserted into a table. I want to run the script below so that it uses the values entered into the table to 'drive' the update process of another table. Here is the code:

Dim K_id As String
Dim HowMany As Integer
Dim Code As String

K_id = myOthTbl.Caller
HM = myOthTbl.HowMany
Code = myOthTbl.Code

strSQL = "Update mytbl as y Set y.Call_Assign_to =" K_id & _
"Where Id_Num in (Select Top" &" HM " & " t.Id_Num" & _
"From Tkn as t " & _
"inner join mytbl as y on y.Id_Num = t.Id_Num " & _
"where y.Call_Assign_to IS NULL" & _
"and x.poky = Code" & _
"order by xyz DESC"

DoCmd.RunSQL strSQL

The variable K_id become a users id, the HM would become an int number for the select Top part of the script and finally the Code variable would become the code selected.

As you can see there are three (3) variables that hold table values and are used in the script. Ideally, I would like to loop through the table that may have twenty (20) or more rows to update all the records in a different table.:banghead:

 

[pbaldy]

You can't refer to a table's values that way. Since you want to loop multiple records, open a recordset on the table and loop it. Here's my template code for a loop:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT * FROM TableName"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF
    'refer to table fields with:  rs!FieldName
    rs.MoveNext
  Loop

  rs.Close
  set rs = nothing
  set db = nothing

 

[Tupacmoche]

Clarification on the implementation:

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()

strSQL = "SELECT * FROM tblAssignments"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Do While Not rs.EOF

'refer to table fields with: rs!FieldName

strSQL = "Update tmp_xid Set Call_Assign_to = '" & CboCaller & _
"Where Id_Number in (Select Top" & '" CboHowMany "' & t.Id_Number" & _
"From Thankathon as t" & _
"inner join tmp_xid as x on t.Id_Number = x.Id_Number" & _
"Where x.Call_Assign_to IS NULL" & _
"and x.sala = 1" & _
"Order by LTC DESC"

rs.MoveNext
Loop
rs.Close
set rs = nothing
set db = nothing

I have inserted my code but am not sure how to fully implement it. Some guidance please!

 

[Tupacmoche]

I tried this but it did not work.

Private Sub btnSubmit_Click()
Dim StrSQL As String
Dim db As dao.Database
Dim rs As dao.Recordset
Dim fld As Field
Set db = CurrentDb()
StrSQL = "Select * from tblAssignments"
Set rs = db.OpenRecordset(StrSQL, dbOpenDynaset)
If rs.RecordCount = 0 Then
MsgBox "no records"
Exit Sub
End If
Do Until rs.EOF
For Each fld In rs.Fields

"Update tmp_xid Set Call_Assign_to = " & rs!Caller & _
"Where Id_Number in (Select Top " & rs!HowMany &" "& t.Id_Number" & _
"From Thankathon as t " & _
"inner join tmp_xid as x on t.Id_Number = x.Id_Number " & _
"Where x.Call_Assign_to IS NULL " & _
"and x.sala = rs!GrpCode " & _
"Order by LTC DESC"

Next
rs.MoveNext
Loop
MsgBox ("Your selection has been processed!")
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

All the code works except for the update part of the script. What I did to test is I put a message box to display the values of the table and it looped through them without an issue. I used this code:

MsgBox ("The first row is " & rs!Caller & " " & rs!HowMany & " " & rs!GrpCode)

But, when I put the update script it turns red and can't run . Any suggestions?

 

[pbaldy]

You have to execute the SQL:

strSQL = "UPDATE..."
db.Execute strSQL, dbFailOnError

this may help with the SQL:

http://www.baldyweb.com/ImmediateWindow.htm

 

[pbaldy]

Oh, and all recordset references need to be concatenated:

"and x.sala = rs!GrpCode " & _

would be

"and x.sala = " & rs!GrpCode & _

presuming it's numeric.

 

[Tupacmoche]

Super, I got it to work with your help. But one issue, I can't track down in the code. The first field in my update script rs!Caller is causing a parameter box to open up. I understand that this happens when a variable can't be resolved. But, I can't see what is wrong. Can anyone see it? I checked the name in the table and it is spelled correctly.

Private Sub btnSubmit_Click()
Dim StrSQL1 As String
Dim strSQL2 As String
Dim db As dao.Database
Dim rs As dao.Recordset
Dim fld As Field
Set db = CurrentDb()
StrSQL1 = "Select * from tblAssignments"
Set rs = db.OpenRecordset(StrSQL1, dbOpenDynaset)
If rs.RecordCount = 0 Then
MsgBox ("Sorry, you have no records.")
Exit Sub
End If
Do While Not rs.EOF

strSQL2 = "Update tmp_xid Set Call_Assign_to = " & rs!Caller & " " & _
"Where Id_Number in (Select Top " & rs!HowMany & " " & "t.Id_Number" & " " & _
"From Thankathon as t " & _
"inner join tmp_xid as x on t.Id_Number = x.Id_Number " & _
"Where x.Call_Assign_to IS NULL " & _
"and x.sala = " & " " & rs!GrpCode & " " & _
"Order by LTC DESC)"

DoCmd.RunSQL strSQL2
rs.MoveNext
Loop
MsgBox ("Your selection has been processed!")
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

 

[pbaldy]

If it's text it needs delimiters:
strSQL2 = "Update tmp_xid Set Call_Assign_to = '" & rs!Caller & "' " & _

 

[Tupacmoche]

Follow-up question regarding run time error 3024. I have been using local access copies of the tables in the queries but, have now changed the name to dbo version which is on the SQL server and got the message above. That is Error 3024. Is it true that since jet 4 queries with a join are not updatable?

 

[pbaldy]

Some are, some aren't:

http://allenbrowne.com/ser-61.html

What is the error description?