none
How to allow a combo box to be left blank or skipped on a form RRS feed

  • Question

  • I am relatively new to Access and any SQL and VBA I have picked up is self-taught, so I would really appreciate answers that are not too heavily laden with technical terms...that said, I am having an issue with allowing comboboxes to be left blank on a form if the user chooses not to input data. Also, I am using Access 2016.

    Initially the problem I ran into was that if a combobox was entirely skipped and left blank, or if the user selects the combobox and then tries to move on without making a selection from the list, they got the error "You tried to assign the Null value to a variable that is not a Variant data type," and were unable to move on to any other fields on the form or to save the record being entered.

    I found this MSDN article (sorry, I guess I can't post direct links::  https://msdn.microsoft.com/en-us/library/office/ff823177.aspx ) that details a possible solution, but I can't seem to get it to work. I made an unbound combobox, set the Row Source to:

    SELECT EmailID, PersonalEmail FROM EmailT  UNION SELECT "<N/A>","<N/A>" FROM EmailT
    ORDER BY PersonalEmail;

    where PersonalEmail is a field of type short text and the EmailID is an autonumber. I also followed the article's steps for formatting the combobox (column width, etc.) and set it to Limit to List = Yes.

    Here is my exact code in the After Update and the On Current events:

    Private Sub Combo62_AfterUpdate()
    If Combo62 = "<N/A>" Then
        EmailID = Null
    Else
        EmailID = Combo62
    End If
    End Sub
    
    Private Sub Form_Current()
    If IsNull(EmailID) Then
        Combo62 = "<N/A>"
    Else
        Combo62 = EmailID
    End If
    End Sub

    < N/A> now shows up on my list, but if it is selected I get the error: "The value you entered isn't valid for this field. For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits."

    Access's debugger highlights the line:

        EmailID = Null

    but I am not sure what steps I should take now to try and fix this.  On a suggestion from someone else, I tried setting the value to -1, and then tried 0 instead, and got the same "value isn't valid" error message.  They both still didn't work when I tried changing a line in the OnCurrent event to:

    If (EmailID = -1) Then

    or

    If (EmailID = 0) Then

    I am completely open to other methods of allowing the combobox to be left blank if someone knows of a better way to do this also. For all I know, I am missing something really obvious! Thanks in advance for any insight!

    Saturday, April 30, 2016 5:59 PM

All replies

  • It seems you are trying to assign the value of the combo box to a variable named "EmailID".  How is that variable defined? If it is defined as String, as in the statement:

        Dim EmailID As String

    then it can't contain Null. If you want it to allow Null, then you have to defined it as Variant:

        Dim EmailID As Variant


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Saturday, April 30, 2016 6:16 PM
  • Handling user input is very challenging because users come up with ideas no programmer can imagine :)

    Error handling might be useful to you. Instead of

    Private Sub Form_Current()
    If IsNull(EmailID) Then
       
    Combo62 = "<N/A>"
    Else
       
    Combo62 = EmailID
    End If

    you can use

    Private Sub Form_Current()

    On Error Goto HandleUserInputError
    Combo62 = EmailID
    Exit sub

    HandleUserInputError:
    MsgBox "You need to enter a valid ID"
    End Sub

    ... or something like that ...


    Best regards, George


    Sunday, May 1, 2016 2:01 PM
  • Hi AliG359,

    First I want to confirm with you that what is the data type of variable Emailid.

    so I assume here that you have entered <N/A>. so I use string datatype. but when I select <N/A> from the list.

    I get the Error. "Invalid use of Null"

    but you are getting an Error like

    "The value you entered isn't valid for this field. For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits."

    If you use Variant as datatype it will enter the null without Error.

    you have mentioned that you have used AutoNumber datatype for Emailid in table.

    so it contains numbers.

    can you exactly tell us what you want to do here?

    In the subject of this thread you have some problem if combobox skipped or blanked on form.

    at that time you can use the default value of the combobox. if user doesn't select any value or it get blanked at that time default value will take place.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, May 2, 2016 6:59 AM
    Moderator
  • In the origin table, EmailID is an autonumber set to long integer.  I tried your suggestion of defining it as a variant like this:

    Dim EmailID As Variant
    
    
    Private Sub Combo62_AfterUpdate()
    If Combo62 = "<N/A>" Then
            EmailID = Null
        Else
            EmailID = Combo62
        End If
    End Sub
    
    
    
    Private Sub Form_Current()
    If IsNull(EmailID) Then
        Combo62 = "<N/A>"
    Else
        Combo62 = EmailID
    End If
    End Sub

    Is that what you meant? With that code in place, I now get an error message on opening the form that says "The expression On Current you entered as the event property setting produced the following error: Member already exists in an object module from which this object module derives."


    Monday, May 2, 2016 11:00 PM
  • Hi Deepak,

    EmailID is an autonumber (long integer) in the table EmailT, where it is the primary key.  I tried Dirk's suggestion of using 

    Dim EmailID As Variant

    at the beginning of my original code to define EmailID as a Variant, but still get an error message (albeit a different one).  To use the default value setting like you suggest, should I put the default value as Null?

    I just want to make sure that if a user wants to leave this combobox blank on the form that they are able to do so and save the record.  Right now that error message pops up until one of the list items is selected, and if you try to close the form without selecting anything from the list a warning pops up and the record is not saved.

    Monday, May 2, 2016 11:10 PM
  • In the origin table, EmailID is an autonumber set to long integer.  I tried your suggestion of defining it as a variant like this:

    Dim EmailID As Variant
    
    
    Private Sub Combo62_AfterUpdate()
    If Combo62 = "<N/A>" Then
            EmailID = Null
        Else
            EmailID = Combo62
        End If
    End Sub
    
    
    
    Private Sub Form_Current()
    If IsNull(EmailID) Then
        Combo62 = "<N/A>"
    Else
        Combo62 = EmailID
    End If
    End Sub

    Is that what you meant? With that code in place, I now get an error message on opening the form that says "The expression On Current you entered as the event property setting produced the following error: Member already exists in an object module from which this object module derives."

    Sorry, the situation is different from what I'd guessed.  But what you say doesn't make sense to me.  You can't assign a value to an autonumber field, and it's very unlikely that you would want to.  I think we'd better step back and discuss the table structure that is involved here.

    Your form is bound to a table; what is the name of that table, and what is its purpose?  As I understand, EmailID is a field in that table, correct?  Is EmailID the primary key of that table, or is it just one of the secondary fields in the table?

    Is their another table, maybe table "EmailT" as used in your SQL example, in which EmailID is the primary key?  Is the combo box on the form used to select an EmailID from the table and assign it to the EmailID field in the table to which the form is bound?

    I think I know what's wrong and it's a simple fix, but I was wrong before, so please answer my questions above to help me verify my understanding.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, May 3, 2016 1:28 AM
  • Hi AliG359,

    Are you trying to insert or edit a record in table EmailT? because you have mentioned that record is not saved.

    I am agree with Dirk Goldgar MVP's suggestion here why you want to assign a value to Auto Number Field?

    I think here is something misunderstanding between us.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, May 3, 2016 2:50 AM
    Moderator
  • Hi AliG359,

    is your issue solved ? or you still facing a problem with that?

    if your issue is solved please can you mark the suggestion that help you to solve your issue.

    if none of suggestion help you to solve your issue and you have find the solution by yourself then would you like to share the solution with our community so that if some other community members have same issue like you then they can get solution by your post.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 12, 2016 4:12 AM
    Moderator