By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,124 Members | 1,528 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,124 IT Pros & Developers. It's quick & easy.

access2002: udpate query

P: n/a
hi - i'm trying to populate the field rateintegrated with an update sql
statement. it is a calculated value from two other fields, feeloc +
feeremarketing. everytime i type in a value for feeloc, the sum would
change in rateintegrated on the form. it does populate in the field
within the table but with the same exact value for all records. what am
i missing? any suggestions?

please see my code below and thank you in advance....

Private Sub RateIntegrated_AfterUpdate()
'2006-09-07 : Insert the numerical value from RateIntegrated field into
tblIssue.RateIntegrated
'the sum of FeeLOC and FeeRemarketing will be the value for the
RateIntegrated field.

Dim a As Integer
Dim b As Integer
Dim mysql As String

'This adds the two values together. We use nZ because that defaults
nulls to a 0 preventing errors.
a = Nz([FeeLOC], 0) + Nz([FeeRemarketing], 0)

b = RateIntegrated.Value 'the field to be populated into tblIssue

''insert the RateIntegrated value into the tblIssue
mysql = "UPDATE tblIssues SET RateIntegrated = '" & b & " ' ;"

DoCmd.RunSQL (mysql)

end sub

Sep 15 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
mc******@gmail.com wrote:
hi - i'm trying to populate the field rateintegrated with an update sql
statement. it is a calculated value from two other fields, feeloc +
feeremarketing. everytime i type in a value for feeloc, the sum would
change in rateintegrated on the form. it does populate in the field
within the table but with the same exact value for all records. what am
i missing? any suggestions?

please see my code below and thank you in advance....

Private Sub RateIntegrated_AfterUpdate()
'2006-09-07 : Insert the numerical value from RateIntegrated field into
tblIssue.RateIntegrated
'the sum of FeeLOC and FeeRemarketing will be the value for the
RateIntegrated field.

Dim a As Integer
Dim b As Integer
Dim mysql As String

'This adds the two values together. We use nZ because that defaults
nulls to a 0 preventing errors.
a = Nz([FeeLOC], 0) + Nz([FeeRemarketing], 0)

b = RateIntegrated.Value 'the field to be populated into tblIssue

''insert the RateIntegrated value into the tblIssue
mysql = "UPDATE tblIssues SET RateIntegrated = '" & b & " ' ;"

DoCmd.RunSQL (mysql)

end sub
Question...is RateIntegrated a number? If so, why do you surround B
with single quotes?

I would think it would update every record. You don't filter it with a
Where statement...ex:
"Where ID = " & Me.ID
Since there is no filter, I'd expect it to update all records.
Sep 15 '06 #2

P: n/a

salad wrote:
mc******@gmail.com wrote:
hi - i'm trying to populate the field rateintegrated with an update sql
statement. it is a calculated value from two other fields, feeloc +
feeremarketing. everytime i type in a value for feeloc, the sum would
change in rateintegrated on the form. it does populate in the field
within the table but with the same exact value for all records. what am
i missing? any suggestions?

please see my code below and thank you in advance....

Private Sub RateIntegrated_AfterUpdate()
'2006-09-07 : Insert the numerical value from RateIntegrated field into
tblIssue.RateIntegrated
'the sum of FeeLOC and FeeRemarketing will be the value for the
RateIntegrated field.

Dim a As Integer
Dim b As Integer
Dim mysql As String

'This adds the two values together. We use nZ because that defaults
nulls to a 0 preventing errors.
a = Nz([FeeLOC], 0) + Nz([FeeRemarketing], 0)

b = RateIntegrated.Value 'the field to be populated into tblIssue

''insert the RateIntegrated value into the tblIssue
mysql = "UPDATE tblIssues SET RateIntegrated = '" & b & " ' ;"

DoCmd.RunSQL (mysql)

end sub
Question...is RateIntegrated a number? If so, why do you surround B
with single quotes?

I would think it would update every record. You don't filter it with a
Where statement...ex:
"Where ID = " & Me.ID
Since there is no filter, I'd expect it to update all records.
Thank you for your reply. Teh RateIntegrated is a number (double).
Thank you again and I'll try to use the WHERE clause....
Cheers!

Sep 18 '06 #3

P: n/a

mc******@gmail.com wrote:
salad wrote:
mc******@gmail.com wrote:
hi - i'm trying to populate the field rateintegrated with an update sql
statement. it is a calculated value from two other fields, feeloc +
feeremarketing. everytime i type in a value for feeloc, the sum would
change in rateintegrated on the form. it does populate in the field
within the table but with the same exact value for all records. what am
i missing? any suggestions?
>
please see my code below and thank you in advance....
>
Private Sub RateIntegrated_AfterUpdate()
'2006-09-07 : Insert the numerical value from RateIntegrated field into
tblIssue.RateIntegrated
'the sum of FeeLOC and FeeRemarketing will be the value for the
RateIntegrated field.
>
Dim a As Integer
Dim b As Integer
Dim mysql As String
>
'This adds the two values together. We use nZ because that defaults
nulls to a 0 preventing errors.
a = Nz([FeeLOC], 0) + Nz([FeeRemarketing], 0)
>
b = RateIntegrated.Value 'the field to be populated into tblIssue
>
''insert the RateIntegrated value into the tblIssue
mysql = "UPDATE tblIssues SET RateIntegrated = '" & b & " ' ;"
>
DoCmd.RunSQL (mysql)
>
end sub
>
Question...is RateIntegrated a number? If so, why do you surround B
with single quotes?

I would think it would update every record. You don't filter it with a
Where statement...ex:
"Where ID = " & Me.ID
Since there is no filter, I'd expect it to update all records.

Thank you for your reply. Teh RateIntegrated is a number (double).
Thank you again and I'll try to use the WHERE clause....
Cheers!
*******
hi- i tried to work with the code but it still doe snot update the
tblIssues.... Here's the code again.. thanks :)

Private Sub RateIntegrated_GotFocus()
'Insert the numerical value from RateIntegrated field into
tblIssue.RateIntegrated
'the sum of FeeLOC and FeeRemarketing will be the value for the
RateIntegrated field.

Dim a As Integer
Dim b As Integer
Dim mysql As String

'This adds the two values together. We use nZ because that defaults
nulls to a 0 preventing errors.
a = Nz([FeeLOC], 0) + Nz([FeeRemarketing], 0)

b = RateIntegrated.Value 'the field to be populated into tblIssue

''insert the RateIntegrated value into the tblIssue
mysql = "UPDATE tblIssues SET RateIntegrated = '" & b & " ' WHERE
UniqueKey = " & Me.UniqueKey & ";"

DoCmd.RunSQL (mysql)
End Sub

Sep 18 '06 #4

P: n/a
Hi - I modified the code and changed the datatype of "b" to long. then
i took out the quotes around b as you questioned. it worked :) now i
have to worry about why i receive an error message in regards to the
"overwrite" issue. this tblissues is linked to another table. fun
fun... thanks again :)

salad wrote:
mc******@gmail.com wrote:
hi - i'm trying to populate the field rateintegrated with an update sql
statement. it is a calculated value from two other fields, feeloc +
feeremarketing. everytime i type in a value for feeloc, the sum would
change in rateintegrated on the form. it does populate in the field
within the table but with the same exact value for all records. what am
i missing? any suggestions?

please see my code below and thank you in advance....

Private Sub RateIntegrated_AfterUpdate()
'2006-09-07 : Insert the numerical value from RateIntegrated field into
tblIssue.RateIntegrated
'the sum of FeeLOC and FeeRemarketing will be the value for the
RateIntegrated field.

Dim a As Integer
Dim b As Integer
Dim mysql As String

'This adds the two values together. We use nZ because that defaults
nulls to a 0 preventing errors.
a = Nz([FeeLOC], 0) + Nz([FeeRemarketing], 0)

b = RateIntegrated.Value 'the field to be populated into tblIssue

''insert the RateIntegrated value into the tblIssue
mysql = "UPDATE tblIssues SET RateIntegrated = '" & b & " ' ;"

DoCmd.RunSQL (mysql)

end sub
Question...is RateIntegrated a number? If so, why do you surround B
with single quotes?

I would think it would update every record. You don't filter it with a
Where statement...ex:
"Where ID = " & Me.ID
Since there is no filter, I'd expect it to update all records.
Sep 19 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.