Error: Assign the null value to a variable

Hello everyone -

I have a form (frmReceiptEntry) which contains a subform (sfrmReceiptDetails).

The form/subform design works fine as it is. I was approached by a user to see if I could stop an error message she receives when she accidently starts to enter a line she doesn't need.

The error message appears when the user is in the subform entering the detail lines of the receipt (i.e. entering the item code, lot #, container ID and quantity. Item code is a primary key field in the table (tblItems). When you start entering an item code, the database assigns the record a line number which is used on invoices.

Here is where the problem occurs: She is entering a receipt and accidently starts entering an item code after the last item was entered. The next line number is assigned automatically. She deletes the item code that she entered in error but when she tries to move off the record or close the form, an error message pop up that says "You tried to assign the Null value to a variable that is not a Variant data type". I'm assuming that is becasue it is looking for a match in tblItems and can't find one but I don't know that for sure.

Is there a way to avoid this error message if a new record is accidently started? The database was created in Access 2003 and the sql for the query that feeds the subform is below.

SELECT tblReceiptDetails.ReceiptNumber, tblReceipts.ReceiptDate, tblReceiptDetails.LineNumber, tblReceiptDetails.ItemCode, tblItems.Description, tblItems.UOM, tblItems.Customer, tblItems.QtyPerPallet, tblItems.UOMWeight, tblReceiptDetails.LotNumber, tblReceiptDetails.ContainerID, tblReceiptDetails.QtyReceived, tblReceiptDetails.PutawayLocation, tblReceiptDetails.OrderQtyTaken, tblReceiptDetails.PrintOnPickSheet, [QtyReceived]/[QtyPerPallet] AS PalletQty1, IIf([PalletQty1]-Int([PalletQty1])0,Int([PalletQty1])+1,Int([PalletQty1])) AS PalletQty, IIf([UOM]="PL",[UOMWeight]*[PalletQty],[UOMWeight]*[QtyReceived]) AS LineWeight, tblReceiptDetails.QtyAvailable, tblReceiptDetails.AnniversaryInvoiceDate
FROM tblReceipts INNER JOIN (tblReceiptDetails INNER JOIN tblItems ON tblReceiptDetails.ItemCode = tblItems.ItemCode) ON tblReceipts.ReceiptNumber = tblReceiptDetails.ReceiptNumber;


Thank you in advance.


Post your answer or comment

comments powered by Disqus
I am geting the error "You tried to assign the Null value to a variable that is not a Variant data type" after deleting the data from a field. I kinda get why I am getting the error, but I need to find a way to avoid the user getting it. Also, no code behind it is trying to assign any value to any variable. I'll explain further how I am getting it:

I have an orders form, which the structure is extremely similar to the orders table from the Northwind sample database. The form is based on a query of my Customers table and Orders table. When the user enters the customers account number in the account number field in the orders table, it "auto populates" the rest of the customer data from the customers table. If the user, for whatever reason, deletes the account number, and then tries to switch fields, they get the error indicated above. I can understand that the reason I am getting this is because that is the field that is "linking" the customers and orders table, and an empty value would essentially break that link and "confuse" the database.

So my question is, how can I supress that warning? The reason why a user may delete the contents of that filed, generating the error, would be only when entering new records/orders. The user often may start a new order form, and enter the account number to only realize that the customer is not yet set up in the database (account numbers are generated from another software system and then set up in this database only when need be). If that is the case the user may sometimes delete the entered account number before clicking my "cancel" button, which would throw that error.

What I would ultimatelly like the behavior to be is that if the user "clears" the contents of that field, it undo's any data entry since the form was opened. Obviously I have tried to use undo commands in the after update event of the field, but this error is triggered before the after update event is triggered.

Any suggestions on how to aviod this error?

Quote: You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162) I've exhausted all the resources that Google has given me on this error, and still can't find a fix... at least one that I am capable of performing.

I am entering data into a subform embedded in the main form. There is one field called "Fine" that when I press any key while the cursor is in that field's text box I get the above error. After pressing 'ok' I am able to enter a value in that field which will appear in 'tblFines', but will not appear as a part of the rest of the record nor in the actual form if you navigate away from and back to the record.
My master/child links seem correct, all of the form properties I can think of are correct. Each form is built off of it's own table there are no query recordsources.

I will attach a dumbed down version of the database that I received help with yesterday from another forum member. He can't replicate the error, so I would like someone to try and add a new record being sure to enter something in the "Fine" text box to see if it happens to you as well.

Thanks.

hi.
I've got a form with a subform. The subform contains company information. The NotInList event is set to open up the Company form in order for you input the company details. It then requeries the combo and finds the information you just entered, and applies it to the fields.
However, when I enter a name, then delete it, it comes up to say
'You tried to assign the Null value to a variable that isn't a variant type'.
I have tried to put if null etc in the NotInList section but it doesn't help.

Any ideas?
Thanks.
HW

You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162)

You tried to assign a Null value to a variable that is not a Variant data type. Use the Dim statement to declare the variable as a Variant, and then try the operation again.



Here's the situation as best I can describe it:

I am currently using Access 2003

Form - frmProperties
subform - frmPropertiesMortgageCompanies
control on subform - txtMortgageCompany

Private Sub txtMortgageCompany_AfterUpdate()txtPropertieID = Forms![frmProperties]![txtPropertieID]
Forms![frmProperties]![txtPMCID] = txtPMCID
txtMCAddress1 = txtMortgageCompany.Column(2)
txtMCAddress2 = txtMortgageCompany.Column(3)
txtMCCity = txtMortgageCompany.Column(4)
txTMCState = txtMortgageCompany.Column(5)
txtMCZip = txtMortgageCompany.Column(6)
End Sub

The following References are used:
Visual Basic for Applications
Microsoft Access 11.0 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
OLE Automation

Options - in order to find out what was happening, I set the Error Trapping to "Break on All Errors", but it does not break when this error comes up.


This error occurs every time that I enter any information in the combo box the first time. Once you click OK (only OK and Help on error message box and Help gives the message that is at the top), the information is accepted and it moves on to the "Not in List" code.

Private Sub txtMortgageCompany_NotInList(NewData As String, Response As Integer)
Dim strMessage As String
Dim db As Database
Dim rs As Recordset

strMessage = "Are you sure you want to add '" & NewData & _
"' to the list of Mortgage Companies?"

If Confirm(strMessage) Then DoCmd.OpenForm "frmMortgageCompanies", , , , acFormAdd, acDialog
Response = acDataErrAdded
Else If MsgBox(NewData & " is not a Mortgage Company in the list. " & vbCrLf & _
"Please pick a Mortgage Company from the list.", vbOKOnly) = vbOK Then Me!txtMortgageCompany.Undo
Response = acDataErrDisplay
End If
End If
End Sub


I have tried to give you every thing that I can, but I just don't know what else to do. There are not variables that need to be set and I can't get this error to show me where the problem is.

Any help or answers that you can give me will be greatly appreciated. Thanking you in advance.
BJ

as assigning a null value to a string and number variables is not possible, what is better ?
declare these variables as Variant type, or checking for a null value before trying to assign the value to he variable.

checking for a null value before trying to assign the value to he variable require more code to be writen and proccessed, and taking this approach is sure more vanurable for errors.
I'm sure there are good reasons not declaring all variables as Variant.


Hi.
I get the 'Cannot assign a null value to a variable' error when I enter a new name in my 'company' (company name) combo. I have some info on creating a null value in a combo, but I don't think it worked for me.

What I need to do is allow users to enter an unknown company in the combo, which triggers the NotInList event (which I've done - this is set to open up the Company Form). This works as long as the user leaves their 'unknown' company in the field. If they delete it (and render it 'Null') I just get an error (at the top) and it won't open up the Company form etc.

Anyone know how to get around this please?

Thanks,
HW

I have read several posts on this forum about this problem, but none of the solutions presented so far help me.

I have a combo box w/ three columns in it (2 of which are invisible). When a user enters data in the combo box, the other two columns are used to update info in other fields. The combo box represents the company name a person works for. If the person quits working there, but wants to stay on our mailing list as an individual, they would no longer have data in the company field, so it would need to be deleted. Of course when it is deleted, we get this error:

You have tried to assign the null value to a variable that is not a variant data type.

I'm assuming because there is no event to address what to do with the other two columns of information?

So I figured, if i have a "blank" record in the combo box recordsource, users can select the blank record, but I have to figure out a way to idiot-proof it also. Basically, I figured, if I use something like the following code, I could set the combo box to the blank record value behind the scenes when necessary.
Note: "0" is the Id of the "blank" dummy record in my combo box

If Me.OrgName Is Null Then
Me.OrgName.Column(0) = "0"
Me.OrgName.Column(1) = ""
Me.OrgName.Column(2) = ""

Any ideas on how to make this work?

On a form I have two combo box's
The contents of the second combo box are filtered by the item selected in the first combo box.

If the value of the first combo box changes I want to delete the value in the second combo box

I have tried:
Forms![frmCustomer]![frmCustomerQuotation]![frmCustomerQuotationProduct].Form!cboProductId = Null

But get a run time error -2147352567 ~ You tried to assign the NULL value to a variable that is not a Variant data type. The data type is Numeric, Long

How do I do this?

Thanks in advance

I have a form with a number of subforms but instead of using tabs, I inserted an unbound subform where the sourceobject changes depending on the command button clicked.

The data displays correctly, however, every time I go to add a new record, I get the error:

"You tried to assign the null value to a variable that is not a variant data type"

It then allows me to data enter, but If I go back a record, the new one disappears, and then reappears if I close and reopen the form.

Any ideas?

Hi!

I'm trying to handle an error when the user deletes the value selected in combo box which eventually leads to the "You tried to assign the Null value to a variable that is not a Variant data type" error message. I haven't even thought about it before receiving user feedback on this thing.

I have a combo box that is limited to list and I can handle errors when the user writes something unexpected in the combo box, using the "On Not In List" event, but apparently this event is not triggered in case of deletion (= Null value).

I've tried to put extra code under different events to prevent the Null value error message, with little success. Any suggestions?

Cheers,
Ryko

After almost 9 months the above error has started popping up on my users PC's.

Any Ideas? Has Microsoft published a patch that broke existing code?

Access 2007

I have recently moved a DB over to SQL Server - still using Access as front-end (linked tables). 2 fields having trouble with in SQL Server are both defined to not accept null values and have default values defined as such: ie ('No Donor'). If I remove a value from one of these fields via my form, instead of putting the default value into the field upon saving the record, I get an error message: " You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162)". This error msg occurs BEFORE going to any event, although it appears to originate from within Access itself. I tried the following:
1. changing from double quotes to single quotes in the forms default value
2. Made sure form will assign default values via fetch default values property
3 Verifying that default values were assigned in SQL Server
4. Changing the default value in the link (there is NO default value in design mode on the Access side for the link and it won't allow me to add it. This is where I am guessing the problem lies - I am presuming that when I establish a link, it should pass this info to Access, so it appears this is not happening.
5. Re-linked (although did not re-create the DSN)

I really need some help with this!!! Have been trying to figure out during last few days!!

I'm having one of those days when I can't get my head around a problem.

I have an Access bound form, SQL back end. I'm trying to set a bound date text field to null (from its current date value), but it keeps yelling at me that "you tried to assign the null value to a variable that is not a variant data type". I've tried setting it to null and "".
When I set the field to vbnull, it sets it to 31/12/1899. Not good.

The answer is probably staring me in the face, but can someone please advise.

After reading multiple threads here I have built a Form for the user to select and/or enter their parameters that they want used within a Report.

The Form includes 3 fields One a combobox to select the Employee and then 2 text boxes to select BeginDate and EndDate

My select query works well to show ALL employees if that Employee box is left blank when I first open the form. The Report includes all employees.

It also works well if I select an Employee preview and then change to a different selected employee and rerun the Report.

************************************
HOWEVER
If in that combobox the first time I run it I select an employee and preview the report and THEN try to re-preview the report for all employees by blanking out the field by highlighting whatever is in that field and then hitting the delete key and try to get a report of all employees I get the error:
"You tried to assign the Null value to a Variable that is not a Variant data Type"
Why would it be different the first time I open the form and leave that field blank and preview vs. re-previewing with a blank field?

Any way to fix this?

I have a combo box that I'm trying to trap an error in. If the user selects an item from the box and then deletes it (possible!) and then tries to move off the field I get error message No 3162 "You are trying to assign the null value to a variable that is not a varient data type". All I want to do is replace that message with something more user friendly like DON'T DO THAT. Can't seem to suppress the message. Any ideas?

[This message has been edited by D B Lawson (edited 01-21-2001).]

I am trying to duplicate a form and subform. Each record in the form is identiefied by a BookingID which is an autonumber. When I run the following duplicate command, a message appears saying 'You tried to assign the Null value to a variable that is not a Variant data type.' It is a Runtime error 3162. This form is a booking form and the subform contains food items for each booking.


Private Sub DuplicateARecord_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!BookingID = Me.BookingID
!SerialNo = Me.SerialNo
!NoOfBookingPerRequistionNo2 = Me.NoOfBookingPerRequistionNo2
!NameOfHost = Me.NameOfHost
!TrustOrNonTrust = Me.TrustOrNonTrust
!Department = Me.Department
!ContactTelephone = Me.ContactTelephone
!ContactMobile = Me.ContactMobile
!ContactFax = Me.ContactFax
!DateOfRequest = Me.DateOfRequest
!DateOfFunction = Me.DateOfFunction
!TimeRequiredStart = Me.TimeRequiredStart
!TimeRequiredEnd = Me.TimeRequiredEnd
!Venue = Me.Venue
!ReasonForBooking = Me.ReasonForBooking
!NoOfGuests = Me.NoOfGuests
!StandingOrder = Me.StandingOrder
!Comments = Me.Comments
!BookingReceivedBy = Me.BookingReceivedBy
.Update

'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = !BookingID

'Duplicate the related records: append query.
If Me.[MenuSelection].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [MenuSelected] ( BookingID, ItemID, Itemname, NumberofitemID, [Trust:Itemcost], SubTrustcost, [NonTrust:Itemcost], SubNonTrustcost, Itemcomment, PriceComment ) " & _
"SELECT " & lngID & " As NewID, ItemID, Itemname, NumberofitemID, [Trust:Itemcost], SubTrustcost, [NonTrust:Itemcost], SubNonTrustcost, Itemcomment, PriceComment ) " & _
"FROM [MenuSelected] WHERE BookingID = " & Me.BookingID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler

End Sub

I am no programmer as I found the coding on internet and substituted the necessary fields. Is there a way round this autonumber issue?

Thanks.

Paul

I've added the following which seems to work albeit with a couple unwanted results:
Quote:
STATEMENT = "INSERT INTO Tble_Registration (ClassID, StaffID, Registered, Attended, Lateness, NotShowCall, RegistrationNotes) VALUES ('" & Me.Tble_Registration_ClassID & "', '" & Me.Tble_Registration_StaffID & "', '" & Me.Registered.Value & "', '" & Me.Attended.Value & "', '" & Me.Lateness.Value & "', '" & Me.NotShowCall.Value & "', '" & Me.RegistrationNotes.Value & "')"
DoCmd.RunSQL STATEMENT

Me.Tble_Registration_StaffID.SetFocus
Me.Tble_Registration_StaffID.Text = ""
MsgBox "Registration saved successfully"
The first unexpected result is that it gives me a pop-up message when it executes DoCmd.RunSQL STATEMENT saying "You are about to append 1 row(s). Are you sure you want to append the selected rows?" (Yes or No). I suppose this isn't a terrible thing, but I didn't really want it since I have a "success" message box.

The second issue is when it executes the clearing of Tble_Registration_StaffID (a drop down list). I get the following error message: "You tried to assign the Null value to a variable that is not a Variant data type." It's weird because it does, in fact, clear the value in the drop down list.

Somehow a form has suddenly stopped working. I get the following error:
Run-time error '-2147352567 (80020009)':
You tried to assign the Null value to a variable that is not a Variant data type.

Not a clue what I could have done / changed to cause this to happen but the debugger highlights line 2 (InventoryItem = Null) as being incorrect (see below). Any ideas on how to fix this?

Private Sub Category_AfterUpdate()
InventoryItem = Null
InventoryItem.Requery
End Sub
Private Sub Form_Current()
InventoryItem.Requery
End Sub
Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

DoCmd.Close
Exit_Command17_Click:
Exit Sub
Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

I have a number of mutiusers that access my database. I was attempting to split the database by using the tool wizard, but every attempt I have made returns an error. I truely don't know what this error message is referring to. The message reads as follows: "You tried to assign the Null value to a variable that is not a Variant data type." If you could give me any hints on why I am receiving this error and what I might need to do to resolve this problem.
Thank you

I have a "contact" database that needs to be updated on a regular basis with information that I download in .csv format and save into Excel format. I'm trying to expedite the transfer of data from the spreadsheet to my database so I don't have to enter each contact individually (which is how I've been doing it).

The basic table structure is as follows:

ID - Autonum
Last - Text
First - Text
Address - Text

etc.

I import the spreadsheet into Access into my "Append Table" which has the same structure except that the ID field is assigned as a number (long integer).

I developed an Append Query to append the Append Table to the Contact Listing Table but when I try to execute the query I get the following error: "You tried to assign the null value to a variable that is not a Variant data type."

Is this because of the AutoNumbered field? How can I fix it so that it will append the records and assign the next "autonum" for each new record.

This is probably really basic and I do apologize if it is but my brain doesn't seem to be engaging today . . . Any and all help will be appreciated!

Tina

Hi all,
I have been trying to use the standard template Contact Management available here.

It does most of what I want it to do, but it seems to have a fatal flaw and no matter what I have tried I cannot solve it.

Go to the Calls Tab. Press the tab key to cycle through the records. When you get to the New Record, press tab again. The cursor moves to the Notes subform. Try to enter something. You receive the error:


You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162)



How can this be stopped?

Thanks
Andrew

Hello,

I currently have a combo list on a purchase order form that has the following event procedure(s) in it so as to open the inventory window as needed to add/edit inventory.

Not in List:
Private Sub Combo55_NotInList(NewData As String, Response As Integer)
MsgBox "ERROR: ITEM not in list. Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub

On Double-Click:
Private Sub Combo55_DblClick(Cancel As Integer)
On Error GoTo Err_Combo55_DblClick
Dim lngCombo55 As Long

If IsNull(Me![Combo55]) Then
Me![Combo55].Text = ""
Else
lngCombo55 = Me![Combo55]
Me![Combo55] = Null
End If
DoCmd.OpenForm "INVENTORYITEMSform", , , , , acDialog, "GotoNew"
Me![Combo55].Requery
If lngCombo55 0 Then Me![Combo55] = lngCombo55

Exit_Combo55_DblClick:
Exit Sub

Err_Combo55_DblClick:
MsgBox Err.Description
Resume Exit_Combo55_DblClick

This works GREAT on other forms I have however, on this particular form it gives me the following error:


ERROR:
You have tried to assign the null value to a variable that is not a variable data type.

I have started from scratch several times....can anyone help? Is there anyway to add a button to the form to open the inventory form and then requery the combo box when closing the inventory form?

Thanks,
Dianne

Hi guys.

first post (please be gentle ) but have a little problem with some combo boxes that I can't find an answer for.

I have set up some dependant combo boxes, Combo1(Areas) and Combo2(a list of properties), so that Combo 2 only shows the desired fields based on entry in Combo1, and working fine.

I needed to be able to change Combo1 so set up an after update procedure to clear Combo2 to Null and to do a requery.

All was fine until I have tried to enter another text box ("info1") that has a link to Combo2. I have another table that shows some different information about each property and wanted to show this on the form but whenever I try to include it(or even just include the required field within the query that the form is based upon) and change Combo1 I get a Null error ("tried to assign the Null value to a variable that is not a variant data type"). I am guessing it is struggling to return something for Info1 because the procedure has set Combo2 to null

Any ideas would be very very welcome as really hit a dead end as far as my very limited knowledge goes!

Cheers

Hello All,

I have a pretty simple database i'm creating to track "responses" from differen divisions. Tables right now:

tblPerson
> pkPersonID
> FirstName
> LastName

tblDivision
> pkDivID
> txtDivName

tblPersonDivison
> pkPersonDivision
> fkDivId
> fkPersonID

tblResponseReceived
> pkResponseID
> fkPersonDivision
> dteResponse (date response received)
> typeResponse (type of response)

For any division, there can be multiple people. Also, for any person, he can be responsible for many divisions. I believe, therefore, that my table structure is fine.

I now created a form to track these responses (should occur monthly). What i need is when i select a combo box with the name of the person, it will only populate the relative divisions that the person is responsible for.

Right now, my combo box for the person is row source is:

SELECT pkPersonID, [txtFirstName] & " " & [txtLastName] FROM tblPerson ORDER BY [txtFirstName];

After Update:
Private Sub cboxPerson_AfterUpdate()
Me.cboxDivision = Null
Me.cboxDivision.Requery
End Sub


For the Division is:

SELECT pkDivisionID, txtDivisionName FROM tblDivision WHERE ((tblPersonDivision.fkPersonID)=Forms!frmResponseT rackingGood!cboxPerson);

I'm missing something here - because i keep getting an error ("You tried to assign the Null value to a variable that is not a Variant data type").

Any advice?
Andrew.


Not finding an answer? Try a Google search.