?
Solved

Canceling Event When Field Is Null

Posted on 2011-10-20
22
Medium Priority
?
405 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:itsupport1199
  • 10
  • 7
  • 2
  • +1
20 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37000449
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
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37000524
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

?
0
 

Author Comment

by:itsupport1199
ID: 37001387
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Expert Comment

by:mbizup
ID: 37001530
"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?

0
 
LVL 61

Expert Comment

by:mbizup
ID: 37001545
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
0
 

Author Comment

by:itsupport1199
ID: 37002302
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?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37002581
Possibly -  Both of the machines I've tried it on are A2K7.  I'll give it a shot in Access 2010 later on.
0
 

Author Comment

by:itsupport1199
ID: 37002589
Thank you.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37002599
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
0
 

Author Comment

by:itsupport1199
ID: 37002611
No luck.  Same error.  Thank you again for all your help.  
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37002612
Or even

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

This just rolls back any changes to the field (this post) or to the record (previous post)
0
 

Author Comment

by:itsupport1199
ID: 37002621
I'm afraid I got the same error again.  
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37003182
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.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37005802
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.
0
 

Author Comment

by:itsupport1199
ID: 37005843
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.  
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 37013739
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
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 1000 total points
ID: 37013765
Hi,

you cannot use a bound field which doesn't allow NULL values as search field AND field to enter data. As with your "ABC.." combobox it needs to be an unbound field which forwards the change of the ID to the real field - you already have a MemberID field as hidden field which you can use for that.

The error message comes from Access itself and not from code or data macro and has nothing to do with Access 2010, it would also appear in older versions. MemberID is part of the primary key and this means that it must have a value and so you can't leave the field with an empty value.

The attached file shows how this could work, but it doesn't requery the rest of the form to show the member data. It would be better to separate this into a subform with an own query because this data is only to show, the purpose of the form is to add/edit the member, not the member data.

I found this approach very confusing for the user. You cannot see which fields are for searching and which for entering/editing data. I would use a subform in the main activity form which shows a list of members and then maybe some buttons to add, change or delete a member. Also the "ABC"-dropdown can make the search longer because you need to go through the letters to find the searched person if you don't now the spelling of the name. The easiest thing for the user would be to have a complete sorted list on one side and the list of members on other side and maybe some buttons between to move entries from one side to the other. That's simple and everybody understand that.

Further recommendations (to only mention some):

 - don't use macros. Simply forget them if you do not really want to try to create a web application with A2010 or data macros (it seems that you use a SQL Server as backend so you don't ever need data macros, SQL Server can use triggers).
 - use "Option Explicit" in each and every module to be forced to declare any variable before the first use.
 - compile your code.
 - use correct indents to have a better overview.
 - use concrete names for any object, don't use table/field names as names for controls.
 - try to keep any object separated from each other. For example: One form should never know anything about if another form is opened and doesn't need to know any control name to look up values from there. Use public properties instead and arguments (in the simplest case the "OpenArgs" parameter of DoCmd.OpenForm) and let the form itself decide where to save the value or get it from.
 - never use "*" in any query, except "COUNT(*)" etc. Make sure that you only query exactly the fields you need for the purpose you want to achieve. It makes it easier for you to exactly see where the data comes from and for the database to not load data which is not needed. SQL Server for example automatically changes such queries to a field list in the query editor.

Cheers,

Christian

ActivityTrackulatorDev.zip
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37013912
Excellent response,  bitsqueezer!
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 37013933
Thanks, mbizup...:-)
0
 

Author Comment

by:itsupport1199
ID: 37017564
With a little trial and error I got bitsqueezer's solution working.  Thanks!
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post, we will learn to set up the Group Naming policy and will see how it is going to impact the Display Name and the Email addresses of the Group.
If Skype for Business came with your office 2016 or office 365 installation, you may find that it's almost impossible to either disable or remove it. The application will often launch with each start of Windows, even when explicitly configured not t…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question