Newbie having trouble with VBA Code in Access 
Author Message
 Newbie having trouble with VBA Code in Access

Hi, I am an Intern with NO VB training at all, and I need
to use some to protect 2 fields on an Access form.  I am
using the following code in an event procedue on the After
Update event:

Private Sub Discontinued_By_AfterUpdate()

Dim lintCurrentUser As String
    lintCurrentUser = CurrentUser
Dim lintOrigValue   As String
    lintOrigValue = Forms![Forms]![Discontinued
By].OldValue

        If lintCurrentUser = "cp107" Or lintCurrentUser
= "cp081" Or lintCurrentUser = "cp438" Then
            'procedure works
        Else
            MsgBox ("You are not allowed to update the
Discont'd By Field")
            Forms![Forms]![Discontinued By] = lintOrigValue
        End If
End Sub

The code is working perfectly, except I get an error if a
user w/o access tries to change the protected field that
is balnk.  When they move to a different field, they get
a "Run Time Error 94: Invalid use of null.  My best guess
is that there is something wrong with using the *.OldValue
function on a field what was orignally blank.  Any one out
there have any suggestions for a lost intern?

Thanks in Advance,

Adam



Mon, 10 Jan 2005 03:18:03 GMT  
 Newbie having trouble with VBA Code in Access


Quote:

> The code is working perfectly, except I get an error if a
> user w/o access tries to change the protected field that
> is balnk.  When they move to a different field, they get
> a "Run Time Error 94: Invalid use of null.  My best guess
> is that there is something wrong with using the *.OldValue
> function on a field what was orignally blank.  Any one out
> there have any suggestions for a lost intern?

    Dim lintOrigValue As String
    lintOrigValue = _
        Forms![Forms]![Discontinued By].OldValue

Yes quite right: an empty Access textbox returns Null rather than "" or 0.
You will need the NZ function to return a valid Long Int:

  lintOrigValue = NZ(me![discontinue by].oldvalue, 0)

The other thing to do is to change the type of OrigValue to Variant, to
match what it is being set to.

    Dim varOrigValue as Variant
    varOrigValue = me![discontinue by].oldvalue

should be fine.

Tim F



Mon, 10 Jan 2005 03:35:31 GMT  
 Newbie having trouble with VBA Code in Access
Tim-

Thank you so much!  It works perfectly!

Adam

Quote:
>-----Original Message-----


>> The code is working perfectly, except I get an error if
a
>> user w/o access tries to change the protected field
that
>> is balnk.  When they move to a different field, they
get
>> a "Run Time Error 94: Invalid use of null.  My best
guess
>> is that there is something wrong with using the
*.OldValue
>> function on a field what was orignally blank.  Any one
out
>> there have any suggestions for a lost intern?

>    Dim lintOrigValue As String
>    lintOrigValue = _
>        Forms![Forms]![Discontinued By].OldValue

>Yes quite right: an empty Access textbox returns Null

rather than "" or 0.

- Show quoted text -

Quote:
>You will need the NZ function to return a valid Long Int:

>  lintOrigValue = NZ(me![discontinue by].oldvalue, 0)

>The other thing to do is to change the type of OrigValue
to Variant, to
>match what it is being set to.

>    Dim varOrigValue as Variant
>    varOrigValue = me![discontinue by].oldvalue

>should be fine.

>Tim F

>.



Mon, 10 Jan 2005 04:04:04 GMT  
 Newbie having trouble with VBA Code in Access
I think that the problem stems from the lintOrigValue trying to write a null
value to overwrite the value that the user changed in the form.  Try testing
for a null value before storing the field to lintOrigValue and if it is null
assign the value of " ".  (Unless you are going to be testing for a null
value in this field in another part of the database.)


Quote:
> Hi, I am an Intern with NO VB training at all, and I need
> to use some to protect 2 fields on an Access form.  I am
> using the following code in an event procedue on the After
> Update event:

> Private Sub Discontinued_By_AfterUpdate()

> Dim lintCurrentUser As String
>     lintCurrentUser = CurrentUser
> Dim lintOrigValue   As String
>     lintOrigValue = Forms![Forms]![Discontinued
> By].OldValue

>         If lintCurrentUser = "cp107" Or lintCurrentUser
> = "cp081" Or lintCurrentUser = "cp438" Then
>             'procedure works
>         Else
>             MsgBox ("You are not allowed to update the
> Discont'd By Field")
>             Forms![Forms]![Discontinued By] = lintOrigValue
>         End If
> End Sub

> The code is working perfectly, except I get an error if a
> user w/o access tries to change the protected field that
> is balnk.  When they move to a different field, they get
> a "Run Time Error 94: Invalid use of null.  My best guess
> is that there is something wrong with using the *.OldValue
> function on a field what was orignally blank.  Any one out
> there have any suggestions for a lost intern?

> Thanks in Advance,

> Adam



Mon, 10 Jan 2005 05:24:09 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Newbie having trouble with Object Error

2. A Newbie having trouble passing variables from one sup-procedure to another

3. Newbie Having trouble with this User Info script, Please help

4. Having Trouble w/Data Validation Code (Acc97)

5. Having trouble with source code

6. Hi, I am having trouble with bar code font not working

7. Having trouble converting SQL Server encrypted field in VB code

8. Having trouble with Access 97 and the sort order

9. Having trouble accessing single field key with two concatenated fields

10. trouble code VBA

11. Trouble faxing report in access VBA on window XP and 2003

12. Access 2000 VBA code not working Access 2002

 

 
Powered by phpBB® Forum Software