Need help with DSum computation

[Guest]

Hi,
I have a data entry form where among other fields, a field 'amount' needs
to be validated before the entry. Several different amounts (i.e. data entry) are made for a particular accountid. The sum of these amounts should be less than that of the total award amount i.e. I need to make sure for an amount entered for a particular accountid in the data entry screen, the sum of all the amounts for that particular accountid (including the present one)should be less than the reward
amount for that case. It is noted here that the accountid in the data entry screen gets chosen form a list in a combobox namely cboSelectBlockGrant.



I am trying to achieve this in the following code with BeforeUpdate event.



If (Me.Amount + DSum("[Amount]", "tblAccounts", "[AccounttID] = Me.cboSelectBlockGrant ")) > Me.AwardAmount Then
MsgBox "You cannot have the sum of accounts greater than the award amount"

However, this seems not to be working. Any help is appreciated.

 

[Graham Mandeno]

Hi Jack

The problem is in the selection criteria argument:
"[AccounttID] = Me.cboSelectBlockGrant "

First, I assume AccounttID with a double-t is a type.

More importantly, Me.cboSelectBlockGrant has no meaning outside the scope of
the form, and this expressioin is being interpreted in the scope of the Jet
SQL parser. Instead, you need to substitute the *value* from the combobox
into the expression:
"[AccountID] = " & Me.cboSelectBlockGrant

If AccountID is a text field, then you must also include quotes around the
value:
"[AccountID] = '" & Me.cboSelectBlockGrant & "'"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,
I have a data entry form where among other fields, a field 'amount' needs
to be validated before the entry. Several different amounts (i.e. data

entry) are made for a particular accountid. The sum of these amounts should
be less than that of the total award amount i.e. I need to make sure for an
amount entered for a particular accountid in the data entry screen, the sum
of all the amounts for that particular accountid (including the present
one)should be less than the reward

amount for that case. It is noted here that the accountid in the data

entry screen gets chosen form a list in a combobox namely
cboSelectBlockGrant.

I am trying to achieve this in the following code with BeforeUpdate event.



If (Me.Amount + DSum("[Amount]", "tblAccounts", "[AccounttID] =

Me.cboSelectBlockGrant ")) > Me.AwardAmount Then

 

[Guest]

Hi Graham,
Thanks for the kind answer. I have another application where similar type of scenario exists. I am trying to validate the amount entered in the data entry screen
and have changed the code in accordance with your advise. However, when I am trying to save an amount, it does not validate it against the DSum + current amount
entered. Do you have any further advise on this? I have the code as below.
Thanks in advance.

If (Me.Amount + DSum("[Amount]", "tblGMISDrawdown", "[BlockGrantIntID] = " & Me.cboSelectBlockGrant.Column(0))) > Me.AwardAmount_mod Then
MsgBox "You cannot have the drawdown amount greater than the award amount"
End If

Hi Jack

The problem is in the selection criteria argument:
"[AccounttID] = Me.cboSelectBlockGrant "

First, I assume AccounttID with a double-t is a type.

More importantly, Me.cboSelectBlockGrant has no meaning outside the scope of
the form, and this expressioin is being interpreted in the scope of the Jet
SQL parser. Instead, you need to substitute the *value* from the combobox
into the expression:
"[AccountID] = " & Me.cboSelectBlockGrant

If AccountID is a text field, then you must also include quotes around the
value:
"[AccountID] = '" & Me.cboSelectBlockGrant & "'"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,
I have a data entry form where among other fields, a field 'amount' needs
to be validated before the entry. Several different amounts (i.e. data

entry) are made for a particular accountid. The sum of these amounts should
be less than that of the total award amount i.e. I need to make sure for an
amount entered for a particular accountid in the data entry screen, the sum
of all the amounts for that particular accountid (including the present
one)should be less than the reward

amount for that case. It is noted here that the accountid in the data

entry screen gets chosen form a list in a combobox namely
cboSelectBlockGrant.

I am trying to achieve this in the following code with BeforeUpdate event.



If (Me.Amount + DSum("[Amount]", "tblAccounts", "[AccounttID] =

Me.cboSelectBlockGrant ")) > Me.AwardAmount Then

MsgBox "You cannot have the sum of accounts greater than the award amount"

However, this seems not to be working. Any help is appreciated.

 

[Graham Mandeno]

Hi Jack

It's difficult to guess what the problem is, without knowing more about your
database structure and without some further information about the problem
than "it does not validate...".

I suggest you set a breakpoint in your code at the If... line, and use the
debug window to examine the key players to find out who is the culprit.

Especially, examine the result of the DSum:
?DSum("[Amount]", "tblGMISDrawdown", "[BlockGrantIntID] = " &
Me.cboSelectBlockGrant.Column(0))

You might be surprised how quickly the answer becomes obvious
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,
Thanks for the kind answer. I have another application where similar type

of scenario exists. I am trying to validate the amount entered in the data
entry screen

and have changed the code in accordance with your advise. However, when I

am trying to save an amount, it does not validate it against the DSum +
current amount

entered. Do you have any further advise on this? I have the code as below.
Thanks in advance.

If (Me.Amount + DSum("[Amount]", "tblGMISDrawdown", "[BlockGrantIntID] = "

& Me.cboSelectBlockGrant.Column(0))) > Me.AwardAmount_mod Then

MsgBox "You cannot have the drawdown amount greater than the award amount"
End If

Hi Jack

The problem is in the selection criteria argument:
"[AccounttID] = Me.cboSelectBlockGrant "

First, I assume AccounttID with a double-t is a type.

More importantly, Me.cboSelectBlockGrant has no meaning outside the scope of
the form, and this expressioin is being interpreted in the scope of the Jet
SQL parser. Instead, you need to substitute the *value* from the combobox
into the expression:
"[AccountID] = " & Me.cboSelectBlockGrant

If AccountID is a text field, then you must also include quotes around the
value:
"[AccountID] = '" & Me.cboSelectBlockGrant & "'"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,
I have a data entry form where among other fields, a field 'amount' needs
to be validated before the entry. Several different amounts (i.e. data

entry) are made for a particular accountid. The sum of these amounts should
be less than that of the total award amount i.e. I need to make sure for an
amount entered for a particular accountid in the data entry screen, the sum
of all the amounts for that particular accountid (including the present
one)should be less than the reward

amount for that case. It is noted here that the accountid in the data

entry screen gets chosen form a list in a combobox namely
cboSelectBlockGrant.

I am trying to achieve this in the following code with BeforeUpdate event.



If (Me.Amount + DSum("[Amount]", "tblAccounts", "[AccounttID] =

Me.cboSelectBlockGrant ")) > Me.AwardAmount Then

MsgBox "You cannot have the sum of accounts greater than the

award
amount"

However, this seems not to be working. Any help is appreciated.

 

[Guest]

Thanks Graham, I appreciate your feedback

Hi Jack

It's difficult to guess what the problem is, without knowing more about your
database structure and without some further information about the problem
than "it does not validate...".

I suggest you set a breakpoint in your code at the If... line, and use the
debug window to examine the key players to find out who is the culprit.

Especially, examine the result of the DSum:
?DSum("[Amount]", "tblGMISDrawdown", "[BlockGrantIntID] = " &
Me.cboSelectBlockGrant.Column(0))

You might be surprised how quickly the answer becomes obvious
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,
Thanks for the kind answer. I have another application where similar type

of scenario exists. I am trying to validate the amount entered in the data
entry screen

and have changed the code in accordance with your advise. However, when I

am trying to save an amount, it does not validate it against the DSum +
current amount

entered. Do you have any further advise on this? I have the code as below.
Thanks in advance.

If (Me.Amount + DSum("[Amount]", "tblGMISDrawdown", "[BlockGrantIntID] = "

& Me.cboSelectBlockGrant.Column(0))) > Me.AwardAmount_mod Then

MsgBox "You cannot have the drawdown amount greater than the award amount"
End If

Hi Jack

The problem is in the selection criteria argument:
"[AccounttID] = Me.cboSelectBlockGrant "

First, I assume AccounttID with a double-t is a type.

More importantly, Me.cboSelectBlockGrant has no meaning outside the scope of
the form, and this expressioin is being interpreted in the scope of the Jet
SQL parser. Instead, you need to substitute the *value* from the combobox
into the expression:
"[AccountID] = " & Me.cboSelectBlockGrant

If AccountID is a text field, then you must also include quotes around the
value:
"[AccountID] = '" & Me.cboSelectBlockGrant & "'"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,
I have a data entry form where among other fields, a field 'amount' needs
to be validated before the entry. Several different amounts (i.e. data
entry) are made for a particular accountid. The sum of these amounts should
be less than that of the total award amount i.e. I need to make sure for an
amount entered for a particular accountid in the data entry screen, the sum
of all the amounts for that particular accountid (including the present
one)should be less than the reward
amount for that case. It is noted here that the accountid in the data
entry screen gets chosen form a list in a combobox namely
cboSelectBlockGrant.



I am trying to achieve this in the following code with BeforeUpdate event.



If (Me.Amount + DSum("[Amount]", "tblAccounts", "[AccounttID] =
Me.cboSelectBlockGrant ")) > Me.AwardAmount Then
MsgBox "You cannot have the sum of accounts greater than the award
amount"

However, this seems not to be working. Any help is appreciated.