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

[maw230]

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.

 

[missinglinq]

Please give your Access version when attaching a file. People not running Access 2007/2010 ( and there are quite a few of us) are wasting time downloading a file they cannot open, and many things are different in these versions, as well.

Thanks

Linq ;0)>

 

[maw230]

Oops, my apologies.

The file is in Access 2007.

 

[maw230]

If anyone running Access 2007 can open this up real quick and try to do what I described above I would be very grateful.
Shouldn't take but a couple of clicks, and I will know if the error is localized to my machine. Thanks.

 

[maw230]

Well, it has been over a week and I still haven't resolved this problem. Is there anyone who can open this database for me and try to replicate the error?

 

[maw230]

Well, it has been over 2 weeks and I still haven't resolved this problem. Is there anyone who can open this database for me and try to replicate the error?

 

[gemma-the-husky]

not seen this post before

I can't look at the dbs, but are you trying to assign the value of the text box to some variable in code

a field/control on a form can hold null, but a variable cannot, Therefore if you are trying to assign the null to a variable, that will fail.

also, maybe there is a corruption of some sort. I would delete the control, and recreate it. See if that helps

 

[vbaInet]

It might be worth noting that assigning Null to a variable of type Variant will succeed, so if you have a variable of type String or Number it will fail like Dave mentioned.

So my gut feeling would be you're assigning Null to a String or Number type variable. Maybe indirectly, i.e. via a control's value like:

Dim myVar as String

myVar = txtBox1.Value

If txtbox1's value is Null then it will fire an error.

Save your database as an older version of Access for others to see.

 

[maw230]

Attached is a copy in .mdb format.

Fine is a Currency value located in tblFines. Im still new to Access, so I'm not sure if i indirectly declared Fine as a variable..

 

[vbaInet]

I took out the Format you set on the Fine field in tblFines and it seems to be happy. But keep the format on the control.

 

[maw230]

Are you using "frmNoQueries"?

I removed the Currency format as well, but am still receiving the error. Do I need to rebuild the form?

 

[vbaInet]

Yes. But obviously I can't add records because it's meant to be linked to an external source.

Do I need to rebuild the form?

You may need to do that.

 

[maw230]

Rebuilding had no effect.

vbaInet,
I see what you mean being as you aren't connected. Are you still able to use the "add record" button even if you arent truly adding a new record...?
If so, you can just go straight to the Fine field and try to enter a value and see if you get the same error. Otherwise I can remove the ODBC connection and make a quick store table..

I am what you would call completely clueless at this point and have spent hours modifying form properties and making sure that each form is its own table, and then changing everything back again all to fix this tiny little error. It's becoming a huge waste of time.

 

[vbaInet]

I was doing this at work earlier on and without adding a record, whenever I found a blank Fine field and type it a letter it threw the error. After removing the Format (as I explained) it seemed to work.

So yes, create a quick table (with some dummy data) and resend.

 

[maw230]

Yes, after I removed the format I was able to edit an existing record's Fine and or add one to an existing record, but was not able to add a fine to a newly created record. Probably should have mentioned it. I attached a revised database that should allow you to add records.

Thanks.

 

[vbaInet]

I looked at your relationships and I remember seeing your db. I also remember mentioning that you needed to change the layout of your subforms.

Basically, because of your relationships you will get an error for new records because tblFines is at the One side of the One to Many relationship, however, you have linked your tblFines subform from Many to One.

Suggestions:

Rethink your form layout and table design. Maybe use tabs and put the tblFines form on a different tab.

To add a new Fine, you need a different form for that.

To amend a file linked to Pass/Fail, you need a combo box with the list of Fines that you can choose from.

 

[maw230]

I changed my subform layouts previously so each table had its own form. I received help from this forum on my table structure and getting everything normalized, so I thought I was ok there.

I am drawing a blank when it comes to my relationships.
There cannot be a Check_ID without a Fine. There can be a Fine without a Check_ID as the CheckNum may need to be added at a later time. I set the default value of Fine to 0 thinking that might make a difference, but it still seems to think I am assigning a Null value to a 'Variant' data type. I filled in all of the Null fines in tblFines to '0' as well.

My limited knowledge of form and subform interaction is why I don't understand this:

tblFines is at the One side of the One to Many relationship, however, you have linked your tblFines subform from Many to One.

I tried separating the Fines subform from everything else but still got the error. So, how can I make the Fines subform a One to Many link?

 

[vbaInet]

Basically, you cannot add a new fine in that subform. A new record within the tblFines subform is supposed to be adding to the tblFines table, no relation to your main form.

So, you need to make it a combo box with the list of fines that can be selected. Also disable the navigation buttons.

You select from the list, not add to the list. Adding to the list of fines requires a separate form altogether.

 

[maw230]

Thanks for clarifying. So, you are saying the above based on how it's set up currently, right?

To me it sounds like I may need to re-structure my tables, because I need the user to be able to add a new fine each time he adds a new record. Or, would I have to have the user enter the fine on a separate form and then use a combo box on the main form to retrieve the new value from tblFines?

I have to make sure that there is a Fine associated with each record, and would like to have it all available within one form.

Also disable the navigation buttons.

How will the user navigate through the records if he needs to edit them?

 

[vbaInet]

You need to restructure your tables. Rethink your design. Remember I mentioned months ago and it's come back to hunt you.

What is tblChecks? Shouldn't tblChecks be related to Pass/Fail?