Link to home
Start Free TrialLog in
Avatar of itsupport1199
itsupport1199

asked on

Canceling Event When Field Is Null

Hi Experts Exchange,

I have a combo box on an Access form that looks up and stores an ID field but displays some concatenated text.  The problem I'm having is that if the person entering the data doesn't find the item on the list they want to enter, they can't leave the form.  It gives an error that they tried to assign a null value to a variable that is not a variant data type.  I have the attached code in the before update event.  

The second part of the code checks to see if that member has already attended that event and is working properly.

Thanks.
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Try this:

Private Sub Member_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Member) Then
            Cancel = true
Else
    If Me.NewRecord Then
        If DCount("MemberID", "dbo_MemberActivities", "MemberID = " & Me.MemberID & " and ActivityID = " & Me.ActivityID) > 0 Then
            MsgBox "This member is already attending this event", vbOKOnly, "WHOA!"
            Cancel = true
        End If
    End If
End If
End Sub
I think your error is coming from elsewhere, though.

What code do you have in other events such as the form's close event that might be doing something along the lines of

Something = Me.Member

?
Avatar of itsupport1199
itsupport1199

ASKER

I can't find anything like that.  I've converted the table to local tables, populated them with test data, and gotten rid of any extraneois tables and forms and attached it here.  The offending form is MemberActivties.  
ActivityTrackulatorDev.accdb
"Unrecognized database format." - I think your uploaded file might be corrupt.

<The problem I'm having is that if the person entering the data doesn't find the item on the list they want to enter, they can't leave the form. >

Something that is not clear to me is why you are disallowing nulls in the before update event if you want the user to be able to leave the form.

What exaclty do you want to have happen if the user does not select anything from the list?

From your description, it sounds like you should simply"do nothing" if no selection is made:

Private Sub Member_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me.Member) Then
    If Me.NewRecord Then
        If DCount("MemberID", "dbo_MemberActivities", "MemberID = " & Me.MemberID & " and ActivityID = " & Me.ActivityID) > 0 Then
            MsgBox "This member is already attending this event", vbOKOnly, "WHOA!"
            Cancel = true
        End If
    End If
End If
End Sub
Thank you for your help.  No luck on the code, though.

If the user doesn't select anything from the list, I want them to be able to leave the form.  Right now, the way it works is that the user begins typing the name into the combo box.  If the there is no match, the user needs to be able to get out of the field and leave the form.  But when she backspaces and deletes the value in the combo box she gets the error about trying to insert a null value.  

Does that answer your question?  

As far as the attachment not opening... It opens on my end.  It's an Access 2010 application.  I know I've had issues opening 2010 apps in 2007.  Could that be the issue?
Possibly -  Both of the machines I've tried it on are A2K7.  I'll give it a shot in Access 2010 later on.
Thank you.
In the meantime try adding an "Undo" to your code:

If IsNull(Me.Member) Then
            me.undo  '<--- add this
            Cancel = true
Else
    If Me.NewRecord Then
        If DCount("MemberID", "dbo_MemberActivities", "MemberID = " & Me.MemberID & " and ActivityID = " & Me.ActivityID) > 0 Then
            MsgBox "This member is already attending this event", vbOKOnly, "WHOA!"
            Cancel = true
        End If
    End If
End If
End Sub
No luck.  Same error.  Thank you again for all your help.  
Or even

            me.Member.undo  '<--- add this

This just rolls back any changes to the field (this post) or to the record (previous post)
I'm afraid I got the same error again.  
Okay -

I am able to open your sample on a 2010 machine.

As I initially suspected it is not your before update event that is causing this problem.  You can remove the code entirely, and the same problem persists.

I'm not sure yet but I believe it may be related to your query and table structure.  I'll look into it a bit more... but if you are pressed for time, hit the "request Attention" button and ask the Mods to try to get others involved.
Here are some problems -

The table your problematic form is based on has a composite primary key, including MemberID.  MemberID with this setup afaik, can never be null, and you may run into other issues using the combo box in your popup form.  I recommend revising your table structure so that there is a single autonumbering Primary Key field, with MemberID and ActivityID used as foriegn keys in this table.  You can use code to validate things like one MemberID per Activity (as you are already doing).

Also, your Member/Activity form's recordsource query can and should be greatly simplified.  The form is a great example of where a mainform/subform setup should be used.  See the following for a tutorial:

http://office.microsoft.com/en-us/access-help/create-a-form-that-contains-a-subform-a-one-to-many-form-HA010098674.aspx

These may be applicable elsewhere in your database too.

I believe setting things up like this will help make things more stable.

<assign a null value to a variable that is not a variant data type.>

That said, this error is unusual.  It looks like it is coming from a Macro or table validation rule.  If it were a VBA issue, there would be a 'debug" button to take you into the code - but that button is not present here.  I did look around for validation rules, found none and ran out of time.  

Do you have any of the new Access 2010 Data Macros running when fields are updated in the underlying table?  I am new to Access 2010 myself and am not sure where to look for that, but it seems reasonable to consider that as a possible source of trouble.
I don't have any macros running on the tables.  I actually don't want the memberid to be allowed to be null.  That's what I was hoping to do with the code.  Is that if that value is null, I want to cancel the update, so that the database doesn't try to update the table.

Thank you for your help.  
What you consider null is possibly empty. The following version handles for both null and empty:

If Nz(Me.Member,"")="" Then
            me.undo  '<--- add this
            Cancel = true
Else
    If Me.NewRecord Then
        If DCount("MemberID", "dbo_MemberActivities", "MemberID = " & Me.MemberID & " and ActivityID = " & Me.ActivityID) > 0 Then
            MsgBox "This member is already attending this event", vbOKOnly, "WHOA!"
            Cancel = true
        End If
    End If
End If
End Sub
ASKER CERTIFIED SOLUTION
Avatar of Bitsqueezer
Bitsqueezer
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Excellent response,  bitsqueezer!
Thanks, mbizup...:-)
With a little trial and error I got bitsqueezer's solution working.  Thanks!