Update cmd head-scratcher!

[accessguy52]

Can anyone tell me why one of my tables gets it's flds updated but the other one doesn't? This is a real mystery! I can't see why something that worked for one table won't work for the other. Same commands, different table. Here's the code. If you can figure THIS one out, you have my utmost respect! Thanks.
*************************************
Dim rs As Recordset
Dim rst As Recordset
Dim rst3 As Recordset

Dim db As Database

Set db = CurrentDb
Set rst = db.OpenRecordset("tblRisk_Severity_Levels")
Set rs = db.OpenRecordset("tblProject2")
Set rst3 = db.OpenRecordset("tblRiskInput")

Dim strSQL As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String
Dim strSQL6 As String

Dim str1, str2, str3 As String

str1 = Me!Title
str2 = Me!FP_ID
str3 = Me!PM_Name


strSQL = "UPDATE TBLRISK_SEVERITY_LEVELS SET PROJECT_NAME = " & "'" & str1 & "'" & ";"
strSQL2 = "UPDATE TBLRISK_SEVERITY_LEVELS SET PROJECT_NUMBER = " & str2 & ";"
strSQL3 = "UPDATE TBLRISK_SEVERITY_lEVELS SET PROJECT_MGR = " & "'" & str3 & "'" & ";"


'Now put the same info into the Risk Input screen...
strSQL4 = "UPDATE tblRiskInput SET PROJECT_NAME = " & "'" & str1 & "'" & ";"
strSQL5 = "UPDATE tblRiskInput SET FPID = " & str2 & ";"
strSQL6 = "UPDATE tblRiskInput SET PM_NAME = " & "'" & str3 & "'" & ";"

db.Execute strSQL
db.Execute strSQL2
db.Execute strSQL3
db.Execute strSQL4
db.Execute strSQL5
db.Execute strSQL6

rst3.Close
rs.Close
rst.Close
MsgBox "Updating Risk Input", vbOKOnly
MsgBox "Updating Questionnaire", vbOKOnly

accessguy52

 

[vbajock]

Are you saying that none of these work?:

'Now put the same info into the Risk Input screen...
strSQL4 = "UPDATE tblRiskInput SET PROJECT_NAME = " & "'" & str1 & "'" & ";"
strSQL5 = "UPDATE tblRiskInput SET FPID = " & str2 & ";"
strSQL6 = "UPDATE tblRiskInput SET PM_NAME = " & "'" & str3 & "'" & ";"

 

[Golom]

Try this
[blue][tt]
Dim strSQL As String
Dim strSQL2 As String
Dim db As DAO.Database
Dim SeverityRecs As Long
Dim RiskRecs As Long
Dim str1 As String, str2 As Long, str3 As String

Set db = CurrentDb
str1 = Me!Title
str2 = Val(Me!FP_ID)
str3 = Me!PM_Name

strSQL = "UPDATE TBLRISK_SEVERITY_LEVELS " & _
"SET PROJECT_NAME = '" & str1 & "', " & _
" PROJECT_NUMBER = " & str2 & ", "
" PROJECT_MGR = '" & str3 & "';"


'Now put the same info into the Risk Input screen...
strSQL2 = "UPDATE tblRiskInput " & _
"SET PROJECT_NAME = '" & str1 & "', " & _
" FPID = " & str2 & ", " & _
" PM_NAME = '" & str3 && "';"

db.Execute strSQL
SeverityRecs = db.RecordsAffected
db.Execute strSQL2
RiskRecs = db.RecordsAffected

MsgBox "Severity Records Changed: " & SeverityRecs & vbCrLf & _
"Risk Records Changed: " & RiskRecs, vbOKOnly

[/tt][/blue]

You didn't say which table is being updated and which one fails. Why are you opening recordsets? You don't seem to do anything with them.

 

[accessguy52]

Sorry - the Severity table was updated successfully, but the Risk table was not.

Golom, are you telling me that I DON'T need to open the recordsets for updating? If I don't open the recordsets, then how do they get affected? Or do I need to open them at all? I'll try your code up there. Back to you both soon. Thanks.

accessguy52

 

[Golom]

The UPDATE statements are called "Action" queries and they act directly on the tables that they reference. They don't have anything to do with whatever cursor-based views (i.e. recordsets) you may be using. In short ... no you don't need to open recordsets and, in fact, if a recordset has locked a table, that could be a reason why your update fails.

 

[accessguy52]

Golom - I tried your code and the Severity table got updated fine, but the Risk table still did not get updated. You mentioned recordset locking. I purposefully didn't put in commands to open the recordsets, but maybe it's still getting locked. Any way I can test for that? Thanks.

accessguy52

 

[Golom]

There are two ways that the update can "fail". The first is that there is some error (e.g. invalid UPDATE statement, Incorrectly specified field names, data type conflicts, locked tables, etc.). The other is that there are no records that qualify for update OR the records already have the specified values ... in which case an update does happen but nothing appears to change.

To check for the first, try some error trapping.
[blue][tt]
On Error GoTo UpdateError

' Existing Code

Exit Sub

UpdateError:

MsgBox Err.Number & " - " & Err.Description

End Sub
[/tt][/blue]
The message box that I gave you should report the number of records that were changed for each table. What is it telling you?

 

[accessguy52]

The msgbox (which was pretty cool, by the way) said 86 records for Severity, 0 records for Risk.

accessguy52

 

[accessguy52]

Golom - I ran it again and the Update error box read "0-".
What would that indicate? Thanks

accessguy52

 

[Golom]

Did you put in the "Exit Sub" statement? "0" means "No Error".

You have probably checked this but are there any records in "tblRiskInput"? If the update ran successfully (i.e. no errors) and there is nothing to restrict the records being updated (i.e. no WHERE clause), then the only thing left is that there were no records in the table.

Just to be certain, insert this before you run the update on that table.
[blue][tt]
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset ("tblRiskInput")
if rs.EOF then
MsgBox "'tblRiskInput' has NO records"
Else
rs.MoveLast
MsgBox "'tblRiskInput' has " & rs.RecordCount & " records."
End If
Set rs = Nothing
[/tt][/blue]
With that you can determine if there is anything in the table.

 

[accessguy52]

Golom - I put your code in and made sure I had a dummy record which the logic caught, as it should. But, still, no update took place - let me mention that the Severity table has 85 existing records which I update with the 1st update. The Risk table only has (now) 1 record. Would that make a difference? Also, I checked each fld in both tables to make sure that they had the same characteristics to each other.
Any thoughts on this? Thanks.

accessguy52

 

[vbajock]

if your trying to run an Update command on a table with no records, you will get no results, since updates only work on existing records. It sounds like you need to update records in one table, then append the same records in to another table. You need to do an INSERT not an UPDATE.

 

[accessguy52]

Thanks, Golom - I decided to use a query for my form which will give me all my records from the Severity table and keep it cleaner, also I would not have the Append msg showing up every time. So I think I have enough to start on now. Much thanks for all your work!

accessguy52