Error: Assign the null value to a variable

[ErinL]

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.

 

[boblarson]

Just tell her to hit CTRL + Z which will undo the record.

Any Autonumber will already have been used so that won't be reset but it will undo the record and she won't get the error message.

 

[ErinL]

Cool! I didn't know about that little trick. I tried it myself and it worked perfectly.

Thank you Bob for the quick response.

 

[pr2-eugin]

Hello Bob, not trying to be rude, but I just have a qucik question on your answer to ErinL. You mentioned..

"Any Autonumber will already have been used so that won't be reset....."

I have in my code a button called 'Exit without saving', which on click property I have used the code,

DoCmd.RunCommand acCmdUndo
DoCmd.Close acForm, "Customers"

..

Only when the user moves/force close the record the AutoNumber is commited, am I correct? So will this code not revert all changes? If not is there any way I can do it (I mean DROP ALL CHANGES, since I opened the record)

Thanks again.

 

[boblarson]

Hello Bob, not trying to be rude, but I just have a qucik question on your answer to ErinL. You mentioned..


I have in my code a button called 'Exit without saving', which on click property I have used the code,

DoCmd.RunCommand acCmdUndo
DoCmd.Close acForm, "Customers"

..

Only when the user moves/force close the record the AutoNumber is commited, am I correct? So will this code not revert all changes? If not is there any way I can do it (I mean DROP ALL CHANGES, since I opened the record)

Thanks again.

Nope, when an autonumber is on the table, when you start a record the autonumber is assigned. If you cancel that record the autonumber that was assigned is permanently GONE, never to be used again. That is the way it works and there is no way to keep that from happening (short of using unbound forms and then committing the record somehow manually) so many people will use the DMAX + 1 method and NOT use an autonumber but you need to assign the value at the last second before the record is committed so as to avoid possible conflicts.

 

[pr2-eugin]

Thanks Bob, that actually is a good insight. I have in my table around 7000 records with the ID as autonumber(Primary key), so changing their type and using the formula; will it be a problem? I once accidentally used up two ID.. and I had to manually add another field making it autonumber to make up the consistency.. any thoughts on that??

 

[boblarson]

Thanks Bob, that actually is a good insight. I have in my table around 7000 records with the ID as autonumber(Primary key), so changing their type and using the formula; will it be a problem? I once accidentally used up two ID.. and I had to manually add another field making it autonumber to make up the consistency.. any thoughts on that??

Yes, if it matters what the number is - you are using the autonumber incorrectly. It is good for providing a UNIQUE number but does not always provide a positive number, nor does it guarantee consecutive. If you can't have gaps, then don't use it.

In fact, I just approved a sample from RainLover who posted a DMAX + 1 Sample database in the Samples area.
http://www.access-programmers.co.uk/forums/showthread.php?t=221597