Run-time error 94, invalid use of null

[TerraEarth]

I'm having a strange problem with one of my form buttons. Essentially the button is a save button that saves the record after validating that the record is accurate and complete.



This line is what is giving me the issue:

If CStr(Me!Child86.Form!FYSummary.Value) <> "" Then ' Triggers Error 94
'Do stuff
End If

Child86 is the name of a subform on my form.
FYSummary is the name of a field (integer) on the subform that I want to check (it shouldn't be empty!)
However, even when the field on the subform is filled out, Me!Child86.Form!FYSummary.Value returns Null which triggers the run-time error 94.
How can this be? How can FYSummary be a null value when it is filled out with numbers? For example:

FYSummary:
12
13
14
15

This line of code was working just fine a few days ago, I am not sure why Access is acting up now when nothing has been changed to the FYSummary field itself. Any ideas?

[June7]

Conversion functions error on Null. Are you sure every record has data? If yes, that is odd.

Use Nz() or IsNull() functions instead. Or:

If Me.Child86!FYSummary & "" = "" Then

Suggest naming control different from field:

If Me.Child86.Form.tbxFYSummary & "" = "" Then

Notice the shorter referencing and use of dot.

Suggest you give subform container more meaningful name.

[ranman256]

OR
If isNull(Me!Child86.Form!FYSummary.Value) then

[TerraEarth]

Right, however, Me!Child86.Form!FYSummary.Value returns NULL even before it is passed to the conversion function.

In other words, despite the field having several inputted values in the subform, it is still seen as being empty by VBA, any idea what is causing this?

If CStr(Me!Child86.Form!FYSummary.Value) <> "" Then
        If (Me!Child86.Form!FYSummary.Value < -1) Or (Me!Child86.Form!FYSummary.Value > 100) Then
            MsgBox "Please make sure to enter a year between 1 and 99 (corresponding to any year between 2001 and 2099)"
            Exit Sub
        End If
'SQL queries to update my tables with form entry
End If

[June7]

Are you validating data just entered before it is committed to table? What event is this code in?

[TerraEarth]

Are you validating data just entered before it is committed to table?

The form/subform is based off two tables, tTempDataEntry (form) and tTempFiscalYears (subform)
They are connected/linked by a ID key.
The validation does not check the tables (tTempDataEntry/tTempFiscalYears), rather it checks the values in the forms itself.
i.e.

Me!Child86.Form!FYSummary.Value (for the subform) 'Check if this value is empty or null, if so then don't save
Me!POCName (for the main form) 'Check if this value is empty or null, if so then don't save
Me!POCEmail (for the main form) 'Check if this value is empty or null, if so then don't save
Me![InsertMainFormFieldNameHere] 'Check if this value is empty or null, if so then don't save

etc...

What event is this code in?

This code is contained in the on-click event of my 'Save' button. It first checks that every entry in the form has an input, and if these inputs are in the correct format. It then runs a series of SQL queries to update my database tables with the form tables and then it wipes the form tables clean.

So this error occurs very early in my code, in the initial checking phase. The snippet is in the format shown above in the previous posts. A few days ago if my subform had the following entries:

FYSummary:
12
13
14
15

Me!Child86.Form!FYSummary.Value would return an integer 12 and my check ran fine just then.

[June7]

Think I edited my comments in post 2 after you read it. Review again.

[TerraEarth]

Thanks for the suggestions, I'll try to tidy up my code with those implementations.

I was able to figure out why my code was having the error. I had hidden the columns of the table so for some reason those columns were not updating as normal. My table looks like this:


Auto | ContractID | FYSummary | Calc
1 | abcd | 17 | abcd17

Auto is a autokey and also the primary key
ContractID is the foreign key that links to the main form
FYSummary represents a year (17=2017)
Calc is a unique index calculated field. It automatically concatenates contractID and FYSummary I hid the columns using this code:

Private Sub Form_Load()
    Forms!fRecordEntry!Child86.Form!Auto.ColumnHidden = -1
    Forms!fRecordEntry!Child86.Form!Calc.ColumnHidden = -1
    Forms!fRecordEntry!Child86.Form!ContractID.ColumnHidden = -1
    CurrentDb.Execute "DELETE * FROM tTempDataEntry;", dbFailOnError 'tTempDataEntry is table sourcing the main form
    CurrentDb.Execute "DELETE * FROM tTempFiscalYears;", dbFailOnError ' tTempFiscalYears is the table sourcing the subform
    Me.Requery
End Sub

When I changed the ColumnHidden property back to false ( = 0) my code worked and when I queried the value of FYSummary field in my form it returned the year (17). Is there a reason why hiding the column makes their values null?

When I enter in a ContractID in the mainform this automatically sets the ContractID of the subform, then all I really need to do is enter in a year for the entry to be considered complete. As soon as a contractID is entered a autokey should be generated into AUTO and the calc field is set to automatically concatenate the ContractID and Year. However, when I queried all four of these fields, the only non-null value returned was from ContractID; Auto, FYSummary and Calc were all null.

EDIT:

Turns out, if you have columns hidden, they don't update properly, but using a me.requery fills out all the fields properly as it ought to be. How bizarre.