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.
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.
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
?
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
?
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
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?
<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
Private Sub Member_BeforeUpdate(Cancel
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
ASKER
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?
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.
ASKER
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
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
ASKER
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)
me.Member.undo '<--- add this
This just rolls back any changes to the field (this post) or to the record (previous post)
ASKER
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.
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.
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.
ASKER
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Excellent response, bitsqueezer!
Thanks, mbizup...:-)
ASKER
With a little trial and error I got bitsqueezer's solution working. Thanks!
Private Sub Member_BeforeUpdate(Cancel
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