Run Time Error (1 Viewer)

Mark1982

Registered User.
Local time
Today, 12:11
Joined
Jul 12, 2018
Messages
37
Dear All,

i am Getting Error while to apply the VBA.
here is my VBA Code:

Option Compare Database
Private Sub Form_AfterUpdate()
Call Form_Current
End Sub

' make this Public so we can call in subform
Public Sub Form_Current()
Me.TotalInv = Nz(DSum("[InvAmount]", "[PR 33101]", "[PR Number]=" & Me![PR Number]), 0)
Me.Remaining = Nz(Me![PR Amount], 0) - Nz(Me.TotalInv, 0)
End Sub


and Here Is the Error:
The Expression you entered as a Query Parameter produced this error: '[InvAmount]'


How can i Solve this?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:11
Joined
Aug 30, 2003
Messages
36,118
Have you confirmed the spelling of the field name? I assume the DSum() is the line throwing the error?
 

Mark1982

Registered User.
Local time
Today, 12:11
Joined
Jul 12, 2018
Messages
37
Yes You are Right i got error line of DSum()
but i check my spelling its fine.
still i got error.
 

moke123

AWF VIP
Local time
Today, 05:11
Joined
Jan 11, 2013
Messages
3,852
You should have Option Explicit declared under Option Compare Database.

Rather than make the form current event public I would add a Public sub and you could call it in the subform using Parent.YoursubName.

is [InvAmount] a field and spelled correctly in [PR 33101]?
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:11
Joined
Aug 30, 2003
Messages
36,118
Can you attach the db here?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:11
Joined
Aug 30, 2003
Messages
36,118
The spelling of the field in PR 33101 is "Invoice Amount"

Using spaces or symbols in your object names will cause you more grief than they're worth in the long run.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:11
Joined
Aug 30, 2003
Messages
36,118
Just add the delimiters like the string example for form controls.
 

moke123

AWF VIP
Local time
Today, 05:11
Joined
Jan 11, 2013
Messages
3,852
in addition to Paul's advice regarding spaces in names, you really should set the default in your VBE to require variable declaration. See this link on how and why...http://www.fmsinc.com/microsoftaccess/modules/options/index.html

You could also consolidate your tables as they all appear to contain the same fields. In effect you are storing data in a table name. Just add a field to indicate the dept. number.

You are also storing data in field names. (Annual Budget 2018, Annual Budget 2019). You should have a field for the year. What happens when you want to add Annual Budget 2020? You'll have to re-do all your tables, forms,reports, queries, etc.

same goes for all your forms and reports. You can use one form and filter those with a where clause.
 
Last edited:

Users who are viewing this thread

Top Bottom