Error: You tried to assign a null value...

[Kathy R.]

Hi Folks!
I'm having trouble trying to figure out the error "You tried to assign a
null value to a variable that is not a variant type."

I have a NotInList event in a combobox (LastName_cbo) of a subform
(sfrAttendee) on a form (frmAttendance). A message box pops open when a
new name is entered into the combobox. Clicking "yes" opens a new form;
clicking "no" generates the error. What I want to happen when I click
"no" is for the field to be returned to its "clean" state with nothing
in it, so that my user can start fresh.

If I understand this correctly, I am getting the error because
LastName_cbo isn't of the variant type, but (if I'm on the right track)
I can't figure out the proper syntax to make it so.

I am using Access 2007 and am slightly above a beginner with VBA. I
modified the code below from a sample that I found at the Blueclaw
website. Could you kind folks take a look at it and point me in the
right direction?

And on a side note, as you can see, I commented out the line
"gbl_exit_name = False" Can someone explain to me what it is for? I
couldn't find any reference to gbl_exit_name either elsewhere in his
code or in Access help.

Thanks in advance,
Kathy R.


Private Sub LastName_cbo_NotInList(newdata As String, Response As Integer)
'base code from www.blueclaw-db.com/access_notinlist_advanced_example.htm

On Error GoTo LastName_cbo_err

Response = acDataErrContinue
Dim ans As Variant

'gbl_exit_name = False ==> I don't know what this original line is for.
'I commented it out and it still seems to work fine.

ans = MsgBox("Do you want to add this PERSON?", _
vbYesNo, "Add New Individual")

If ans = vbNo Then
Forms!frmAttendance!sfrAttendee.Form.LastName_cbo = Null
DoCmd.GoToControl "LastName_cbo"
GoTo exit_sub

End If

' add ATTENDEE to NewFamily form
DoCmd.OpenForm ("frmNewFamily")
DoCmd.GoToRecord , , acNewRec
Form_frmNewFamily.FamLastName = newdata

'Me.LastName_cbo = Null ==> this line was in the original code from
Blue Claw, NotInList sample
'it gave me the error "You tried to assign a null value to a value that
is not a Variant type"
'I replaced it with the line "DoCmd.GoToRecord,,acNewRec" above

DoCmd.GoToControl "Street"

LastName_cbo_err:
Dim Msg As String
Msg = "Error Number " & Err.Number & ": " & Err.Description
MsgBox Msg


exit_sub:

End Sub

 

[Dorian]

Try:
Forms!frmAttendance!sfrAttendee.Form.LastName_cbo.Value = Null
or
Forms!frmAttendance!sfrAttendee.Form.LastName_cbo = vbNullString

The line you commented out is setting a global variable, do a 'Find in
Project' for it, may be there is a comment explaining its purpose.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Kathy R.]

Thank you Dorian.

I finally got it working just before you replied. I'm not sure exactly
what I did, but comparing my working code with what I posted, the only
thing that appears different is that I removed the line "MsgBox Msg"
from the LastName_cbo_err:

I've tested the If ans = vbNo Then with both
Forms!frmAttendance!sfrAttendee.Form.LastName_cbo = Null
and
Me!sfrAttendee.Form.LastName_cbo = Null

Either appears to work as intended.

Unfortunately, I cannot look for the global variable elsewhere since the
website where I found the code originally only showed me that snippet of
code. It's nice to know I'm not cutting something crucial out without
knowing though. Thank you.

Kathy R.