Updating a value using SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I go about changing the value of a field on a form based on a
selection in a combo box and some sql stuff.

The following query will produce the correct value, I did that as a stand
alone query, but I don't know how to make the text box that value. The
following is in the after update of a combo box. There are values hard coded
in here that will be variables in the final, but work for now. I am trying
to put the sum into text12, but am missing something. The text16.value
statement is simply another test, trying to come up with something.

dim ssql as string

ssql = "SELECT Sum([tblEVENT_LOG].[intRISK_SCORE]) AS SumOfintRISK_SCORE " _
& "FROM tblEVENT_LOG " _
& "WHERE ((([tblEVENT_LOG]![txtIMPACT_YEAR] &
[tblEVENT_LOG]![txtIMPACT_QTR])<'20051') And
(([tblEVENT_LOG].[txtAUDIT_ENTITY])='1'))"

'Text12.Value = ssql
Text16.Value = sumofintrisk_score
 
Actually, as I look at this more, and take more stabs in the dark, would it
be easier to use a DSUM function?
 
Hi Biggles,

The DLookup() built in function returns a scalar value. This function is
what you need.

syntax:

Text12.value = DLookup("Sum(intRISK_SCORE)", "tblEVENT_LOG", "txtIMPACT_QTR
< '20051' And txtAUDIT_ENTITY = '1'")

You will have to correct the where criteria in the syntax above as I was a
bit confused with your the where criteria in the SQL statement you include in
your post.

I hope this helps. Cheers.
MMS
 
Here's one way: make a standalone query out of your SQL statement (let's call
it Query1), then do this in VBA code to set:

Private Sub ComboBox1_AfterUpdate
Text12 = dlookup("[SumOfintRISK_SCORE]","[Query1]")
End Sub

Better yet, use a domain aggregate statement in VBA:

Private Sub ComboBox1_AfterUpdate

Text12 = DSum("[intRISK_SCORE]","[tblEVENT_LOG]", "([txtIMPACT_YEAR]*10 +
txImpact_Quarter]) < 20051 And [txtAUDIT_ENTITY])='1'")

I cheated a little with the Year/Quarter to get it into the single value
that works in your statement. I am assuming that these are actually numeric
values; if not, you will have to do a little fancier footwork to concatenate
them.

End Sub
 
You only left out the part that retrieves the data :)
Below is addition to your code that will get it for you. I did notice one
thing that may be a problem. You include [tblEVENT_LOG]![txtIMPACT_YEAR] in
your WHERE clause, but you don't compare it to anything.

Dim ssql as string
Dim rst as Recordset

ssql = "SELECT Sum([tblEVENT_LOG].[intRISK_SCORE]) AS SumOfintRISK_SCORE " _
& "FROM tblEVENT_LOG " _
& "WHERE ((([tblEVENT_LOG]![txtIMPACT_YEAR] &
[tblEVENT_LOG]![txtIMPACT_QTR])<'20051') And
(([tblEVENT_LOG].[txtAUDIT_ENTITY])='1'))"

Set rst = Currentdb.Openrecordset(ssql)
If rst.RecordCount = 0 Then
Me.Text12 = ""
Else
Me.Text12 = rst![SumOfintRISK_SCORE]
End If
 
Thank you to each for those answers, I think the DSUM might work in this
case, but I am sure the others will be more useful in other situations.

Thanks.
--
Ficticiously Yours, Biggles


Klatuu said:
You only left out the part that retrieves the data :)
Below is addition to your code that will get it for you. I did notice one
thing that may be a problem. You include [tblEVENT_LOG]![txtIMPACT_YEAR] in
your WHERE clause, but you don't compare it to anything.

Dim ssql as string
Dim rst as Recordset

ssql = "SELECT Sum([tblEVENT_LOG].[intRISK_SCORE]) AS SumOfintRISK_SCORE " _
& "FROM tblEVENT_LOG " _
& "WHERE ((([tblEVENT_LOG]![txtIMPACT_YEAR] &
[tblEVENT_LOG]![txtIMPACT_QTR])<'20051') And
(([tblEVENT_LOG].[txtAUDIT_ENTITY])='1'))"

Set rst = Currentdb.Openrecordset(ssql)
If rst.RecordCount = 0 Then
Me.Text12 = ""
Else
Me.Text12 = rst![SumOfintRISK_SCORE]
End If



Biggles said:
How do I go about changing the value of a field on a form based on a
selection in a combo box and some sql stuff.

The following query will produce the correct value, I did that as a stand
alone query, but I don't know how to make the text box that value. The
following is in the after update of a combo box. There are values hard coded
in here that will be variables in the final, but work for now. I am trying
to put the sum into text12, but am missing something. The text16.value
statement is simply another test, trying to come up with something.

dim ssql as string

ssql = "SELECT Sum([tblEVENT_LOG].[intRISK_SCORE]) AS SumOfintRISK_SCORE " _
& "FROM tblEVENT_LOG " _
& "WHERE ((([tblEVENT_LOG]![txtIMPACT_YEAR] &
[tblEVENT_LOG]![txtIMPACT_QTR])<'20051') And
(([tblEVENT_LOG].[txtAUDIT_ENTITY])='1'))"

'Text12.Value = ssql
Text16.Value = sumofintrisk_score
Click to expand...
Click to expand...
 
Brian

If I want to replace 20051 with a variable, i.e.

mthqtr = me.txtimpact_year & me.txtimpact_qtr

and then

do I need to separate the string like :

"(([tblEVENT_LOG]![txtIMPACT_YEAR] & [tblEVENT_LOG]![txtIMPACT_QTR]) <= "
mthqtr & ") And ...."
--
Ficticiously Yours, Biggles


Brian said:
Here's one way: make a standalone query out of your SQL statement (let's call
it Query1), then do this in VBA code to set:

Private Sub ComboBox1_AfterUpdate
Text12 = dlookup("[SumOfintRISK_SCORE]","[Query1]")
End Sub

Better yet, use a domain aggregate statement in VBA:

Private Sub ComboBox1_AfterUpdate

Text12 = DSum("[intRISK_SCORE]","[tblEVENT_LOG]", "([txtIMPACT_YEAR]*10 +
txImpact_Quarter]) < 20051 And [txtAUDIT_ENTITY])='1'")

I cheated a little with the Year/Quarter to get it into the single value
that works in your statement. I am assuming that these are actually numeric
values; if not, you will have to do a little fancier footwork to concatenate
them.

End Sub

Biggles said:
How do I go about changing the value of a field on a form based on a
selection in a combo box and some sql stuff.

The following query will produce the correct value, I did that as a stand
alone query, but I don't know how to make the text box that value. The
following is in the after update of a combo box. There are values hard coded
in here that will be variables in the final, but work for now. I am trying
to put the sum into text12, but am missing something. The text16.value
statement is simply another test, trying to come up with something.

dim ssql as string

ssql = "SELECT Sum([tblEVENT_LOG].[intRISK_SCORE]) AS SumOfintRISK_SCORE " _
& "FROM tblEVENT_LOG " _
& "WHERE ((([tblEVENT_LOG]![txtIMPACT_YEAR] &
[tblEVENT_LOG]![txtIMPACT_QTR])<'20051') And
(([tblEVENT_LOG].[txtAUDIT_ENTITY])='1'))"

'Text12.Value = ssql
Text16.Value = sumofintrisk_score
Click to expand...
Click to expand...
 
I have tried to do this, but keep getting invalid use of null error - any
ideas?

Dim mthqtr As String

mthqtr = Me.txtIMPACT_YEAR & Me.txtIMPACT_QTR
Text16 = mthqtr
Text12 = DSum(intRISK_SCORE, tblEVENT_LOG,
"(([tblEVENT_LOG]![txtIMPACT_YEAR] & [tblEVENT_LOG]![txtIMPACT_QTR]) <= " &
mthqtr & ") And ([tblEVENT_LOG].[txtAUDIT_ENTITY]) = " & Combo0 & ")")

--
Ficticiously Yours, Biggles


Biggles said:
Brian

If I want to replace 20051 with a variable, i.e.

mthqtr = me.txtimpact_year & me.txtimpact_qtr

and then

do I need to separate the string like :

"(([tblEVENT_LOG]![txtIMPACT_YEAR] & [tblEVENT_LOG]![txtIMPACT_QTR]) <= "
mthqtr & ") And ...."
--
Ficticiously Yours, Biggles


Brian said:
Here's one way: make a standalone query out of your SQL statement (let's call
it Query1), then do this in VBA code to set:

Private Sub ComboBox1_AfterUpdate
Text12 = dlookup("[SumOfintRISK_SCORE]","[Query1]")
End Sub

Better yet, use a domain aggregate statement in VBA:

Private Sub ComboBox1_AfterUpdate

Text12 = DSum("[intRISK_SCORE]","[tblEVENT_LOG]", "([txtIMPACT_YEAR]*10 +
txImpact_Quarter]) < 20051 And [txtAUDIT_ENTITY])='1'")

I cheated a little with the Year/Quarter to get it into the single value
that works in your statement. I am assuming that these are actually numeric
values; if not, you will have to do a little fancier footwork to concatenate
them.

End Sub

Biggles said:
How do I go about changing the value of a field on a form based on a
selection in a combo box and some sql stuff.

The following query will produce the correct value, I did that as a stand
alone query, but I don't know how to make the text box that value. The
following is in the after update of a combo box. There are values hard coded
in here that will be variables in the final, but work for now. I am trying
to put the sum into text12, but am missing something. The text16.value
statement is simply another test, trying to come up with something.

dim ssql as string

ssql = "SELECT Sum([tblEVENT_LOG].[intRISK_SCORE]) AS SumOfintRISK_SCORE " _
& "FROM tblEVENT_LOG " _
& "WHERE ((([tblEVENT_LOG]![txtIMPACT_YEAR] &
[tblEVENT_LOG]![txtIMPACT_QTR])<'20051') And
(([tblEVENT_LOG].[txtAUDIT_ENTITY])='1'))"

'Text12.Value = ssql
Text16.Value = sumofintrisk_score
Click to expand...
Click to expand...
Click to expand...
 
Back
Top