Table field not being updated when using OpenRecordset

[jsccorps]

I 'm running the event from a form with User and Sales inputs. I am
attempting to get the Sales from one table (SumSales) and store it in another
table (Test_App). However,the sales field is not being updated. Code is
below (bear with me - for i am a novice):

Dim rs As Recordset
Dim tmpSales As Long

Set rs = CurrentDb.OpenRecordset("Select * From [Test_SumSales] Where
[User]=" & [User], dbOpenDynaset)
With rs
tmpSales = ![SumSales] ' SumSales (265) comes from Test_SumSales
table
End With

Set rs = CurrentDb.OpenRecordset("Select * From [Test_App] Where [User]="
& [User], dbOpenDynaset)
With rs
MsgBox (tmpSales) ' value is good (= 265)
Sales = tmpSales ' set Sales (in Test_App) = SumSales value (from
the Test_SumSales table)"
MsgBox (Sales) ' value is good (= 265)
' HOWEVER, Sales is not being updated (to
265)in the Test_App table??
End With

Any ideas or more elegant solutions?

 

[Dirk Goldgar]

I 'm running the event from a form with User and Sales inputs. I am
attempting to get the Sales from one table (SumSales) and store it in
another table (Test_App). However,the sales field is not being
updated. Code is below (bear with me - for i am a novice):

Dim rs As Recordset
Dim tmpSales As Long

Set rs = CurrentDb.OpenRecordset("Select * From [Test_SumSales]
Where [User]=" & [User], dbOpenDynaset)
With rs
tmpSales = ![SumSales] ' SumSales (265) comes from
Test_SumSales table
End With

Set rs = CurrentDb.OpenRecordset("Select * From [Test_App] Where
[User]=" & [User], dbOpenDynaset)
With rs
MsgBox (tmpSales) ' value is good (= 265)
Sales = tmpSales ' set Sales (in Test_App) = SumSales value
(from the Test_SumSales table)"
MsgBox (Sales) ' value is good (= 265)
' HOWEVER, Sales is not being
updated (to 265)in the Test_App table??
End With

Any ideas or more elegant solutions?

I'd guess that to make this work using the recordset approach you've
started with, you'd need to change the last part of your code as
follows:

Set rs = CurrentDb.OpenRecordset( _
"Select * From [Test_App] Where [User]=" & _
& [User], _
dbOpenDynaset)

With rs
.Edit
!Sales = tmpSales
.Update
End With

However, if there is just one record for [User] in each of these tables,
which your code seems to imply, then you could do the update more
efficiently by executing an update query. I think it would be something
like this:

Dim strSQL As String

strSQL = _
"UPDATE Test_App INNER JOIN Test_SumSales " & _
"ON Test_App.[User] = Test_SumSales.[User] " & _
"WHERE Test_SumSales.[User] = " & Me![User] & _
" SET Test_App.Sales = Test_SumSales.SumSales"

CurrentDb.Execute strSQL, dbFailOnError

I haven't verified that syntax or tested it, though, so be warned.

 

[Graham R Seach]

You just need to swap the positions of the SET and WHERE clauses to make it
work.

strSQL = _
"UPDATE Test_App INNER JOIN Test_SumSales " & _
"ON Test_App.[User] = Test_SumSales.[User] " & _
"SET Test_App.Sales = Test_SumSales.SumSales " & _
"WHERE Test_SumSales.[User] = " & Me![User]

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

I 'm running the event from a form with User and Sales inputs. I am
attempting to get the Sales from one table (SumSales) and store it in
another table (Test_App). However,the sales field is not being
updated. Code is below (bear with me - for i am a novice):

Dim rs As Recordset
Dim tmpSales As Long

Set rs = CurrentDb.OpenRecordset("Select * From [Test_SumSales]
Where [User]=" & [User], dbOpenDynaset)
With rs
tmpSales = ![SumSales] ' SumSales (265) comes from
Test_SumSales table
End With

Set rs = CurrentDb.OpenRecordset("Select * From [Test_App] Where
[User]=" & [User], dbOpenDynaset)
With rs
MsgBox (tmpSales) ' value is good (= 265)
Sales = tmpSales ' set Sales (in Test_App) = SumSales value
(from the Test_SumSales table)"
MsgBox (Sales) ' value is good (= 265)
' HOWEVER, Sales is not being
updated (to 265)in the Test_App table??
End With

Any ideas or more elegant solutions?

I'd guess that to make this work using the recordset approach you've
started with, you'd need to change the last part of your code as
follows:

Set rs = CurrentDb.OpenRecordset( _
"Select * From [Test_App] Where [User]=" & _
& [User], _
dbOpenDynaset)

With rs
.Edit
!Sales = tmpSales
.Update
End With

However, if there is just one record for [User] in each of these tables,
which your code seems to imply, then you could do the update more
efficiently by executing an update query. I think it would be something
like this:

Dim strSQL As String

strSQL = _
"UPDATE Test_App INNER JOIN Test_SumSales " & _
"ON Test_App.[User] = Test_SumSales.[User] " & _
"WHERE Test_SumSales.[User] = " & Me![User] & _
" SET Test_App.Sales = Test_SumSales.SumSales"

CurrentDb.Execute strSQL, dbFailOnError

I haven't verified that syntax or tested it, though, so be warned.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

You just need to swap the positions of the SET and WHERE clauses to
make it work.

strSQL = _
"UPDATE Test_App INNER JOIN Test_SumSales " & _
"ON Test_App.[User] = Test_SumSales.[User] " & _
"SET Test_App.Sales = Test_SumSales.SumSales " & _
"WHERE Test_SumSales.[User] = " & Me![User]

Thanks, Graham. I had a nagging feeling there was something wrong
there, but the pumpkin pie wasn't letting me see it.

 

[Graham R Seach]

No worries; you provided a pretty good solution for something that was
off-the-cuff.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

You just need to swap the positions of the SET and WHERE clauses to
make it work.

strSQL = _
"UPDATE Test_App INNER JOIN Test_SumSales " & _
"ON Test_App.[User] = Test_SumSales.[User] " & _
"SET Test_App.Sales = Test_SumSales.SumSales " & _
"WHERE Test_SumSales.[User] = " & Me![User]

Thanks, Graham. I had a nagging feeling there was something wrong
there, but the pumpkin pie wasn't letting me see it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)