Null value to a variable that is not variant

[Lhoj]

Hello everyone

This one has me completely lost. I have a Form used to modify the data of a table (namely t1). Due to a redesign on the DB, some of those fields have been moved to another table (let's call it t2), so now the original form (with its data origin being t1) has a subform with t2 as data origin, so that the related record from t2 can also be edited. This subform allows edition as well as inserts. Both tables are linked through a FK in t1 linked to the PK in t2. The PK in t2 is an auto-numerated PK.

Main form has a combobox linked to the FK in t1 that lists all the desired records from t2 (mainly any record in t2 that is not already linked to a record in t1 plus the one related to the current t1 record (if any)). The user can choose any record from the combobox to assign a different t2 record to the current t1 record, which in turn updates the subform info.

If nothing is selected (that is, if the FK on t1 is null or gets set to null) a button becomes enabled to create a new t2 record, in case it doesn't exist and the user wants to create it without leaving the main form, that does the following:

        Me.cmbbox.Enabled = False
        Me.Subfrm.Enabled = True
        Me.Subfrm.Visible = True
        Me.Subfrm.SetFocus
        DoCmd.GoToRecord , , acNewRec
        Me.btn.Caption = "Volver a selección"

Basically, it disables the combobox to prevent changing the t1 FK during creation of the new t2 record, enables and shows the subform for table2 (since it's hidden if the combobox has no value) and goes to new record on its recordset. Once it's done, clicking the same button returns to the previous state, and hides the subfrm again, but requeries the combobox so the user can select the newly t2 created record to link it to the current t1 record:

        Me.Cmbox.Requery
        Me.Cmbox.Enabled = True
        Me.Subfrm.Enabled = False
        Me.Subfrm.Visible = False
        Me.btn.Caption = "Nueva línea voz"

Now, all of this works well, but the moment I try to type anything on any field of the subform for a new record I get the error message: "You tried to assign the NULL value to a variable that is not a Variant data type." If I skip it and input the required data (some of the fields are unique and required) the record gets added to t2 and appears on the combobox to be assigned to t1. That is, everything works but I can't get rid of the error message nor do I know where it's coming from. Subform for t2 doesn't have any code or events, main form doesn't do anything else with subform aside from enabling/disabling and hiding/showing it. The error message pops up with whatever field I try to alter first on the new record form, be it a text field, numeric field, checkbox....

Subform is on single form view, just in case. No field on t2 has a null value as default, so I'm not sure where this null association is being made.

Any tip on how to tackle it (because right now I'm out of ideas even on how to troubleshoot it) would be appreciated.

[Bob Fitz]

Can you post a copy of the db

[Lhoj]

Hi, Bob. Sorry for the delay, had to clean most of the DB to reduce the size and remove sensitive data and most of the code from the form to avoid error at form load. Here you go, just open "Editar_oficina_Movil" form and forgive the ugly appearance, not only did I remove many fields but also those are modal forms with access UI hidden and some functions to center the form and adjust the borders depending on screen resolution, they get messy when opened as a normal form :P

[señor_sriracha]

Hi, Bob. Sorry for the delay, had to clean most of the DB to reduce the size and remove sensitive data and most of the code from the form to avoid error at form load. Here you go, just open "Editar_oficina_Movil" form and forgive the ugly appearance, not only did I remove many fields but also those are modal forms with access UI hidden and some functions to center the form and adjust the borders depending on screen resolution, they get messy when opened as a normal form :P

Seems like your Combo Box Row Source has a circular reference to itself. It's hard to be sure since I can't run it in my language.

SELECT Lineas_Voz.Id, Lineas_Voz.Num_Linea, Lineas_Voz.ICC_SIM FROM Lineas_Voz WHERE (((Lineas_Voz.Id) Not In (select lin_voz from oficina_movil where lin_voz is not null))) OR (((Lineas_Voz.Id)=[Formularios]![Editar_Oficina_Movil]![Linea_voz]) AND (([lineas_voz].[fec_baja]) Is Null)) ORDER BY Lineas_Voz.Num_Linea;

[Lhoj]

Seems like your Combo Box Row Source has a circular reference to itself. It's hard to be sure since I can't run it in my language.

Hadn't thought of that, I guess changing Formularios to Forms would be enough for it to run in English, but I'm not sure if some other punctuation differs.

Regarding the circular reference, I see your point but I changed it to reference the table instead of the actual field in the form and nothing changed. In fact removing the whole condition (which prevents the currently selected lineas_voz record from appearing in the combobox since it's actually assigned to a record in oficina_movil table) doesn't fix the null error either.

Furthermore, I made the combobox unbound and removed its data source. Since its value was still null the button still activated, but trying to alter any field in the new record subform still throws the error, so it seems the combobox is not at fault here. Once you close the error message you can keep filling fields without it ever appearing again for that record and said new record gets inserted normally.

Btw, the amount of parentheses Access automatically throws at my where clauses on SQL queries seems overkill and make it harder to read, at least for me. Is there any way to prevent it?

[Minty]

Btw, the amount of parentheses Access automatically throws at my where clauses on SQL queries seems overkill and make it harder to read, at least for me. Is there any way to prevent it?

Not that I know of, you can try only ever opening the query in SQL view, and see if your SQL statement "sticks"
It is very annoying, and most of the time unnecessary.

[señor_sriracha]

The main form is linked to the sub form using the fields [lin_voz] (main form field) and [Id] (sub form field). This is often referred to as a Master/Detail form. When the [lin_voz] field is blank it contains a NULL, and when you try to add a value in the sub form it tries to set the [Id] field value to the value of the linked [lin_voz] field. Since the [Id] field is a Long Integer data type, it cannot accept a NULL value and returns an error.

[Lhoj]

The main form is linked to the sub form using the fields [lin_voz] (main form field) and [Id] (sub form field). This is often referred to as a Master/Detail form. When the [lin_voz] field is blank it contains a NULL, and when you try to add a value in the sub form it tries to set the [Id] field value to the value of the linked [lin_voz] field. Since the [Id] field is a Long Integer data type, it cannot accept a NULL value and returns an error.

Bullseye! Not the behaviour I expected for the new record 'mode' of the subform, but now it's pretty easy to fix. I just added the following code before moving the recordset to the new record (back to the general naming to match the OP code):

Me.Subfrm.linkchildfields=""
Me.Subfrm.linkmasterfields=""

And then re-establish the link to the corresponding fields when returning to the previous state. Probably removing and re-establishing one of the 'ends' of the link would be enough but better safe than sorry. Now it's working flawlessly. Thank you so much sriracha!