Null error on first record 1

[pd06498]

I have a database that, when it contains data, works fine. However, when the main form is first loaded to enter the very first record, it produces an error "Run Time Error 94 - Invalid Use of Null"

I have a check box in the form that when checked means that record is completed and fields in that record are locked. This operates from the forms 'Current' event.

How can I overcome this problem. I have posted some of the offending code. Is someone able to assist?

Current in Form
Private Sub Form_Current()
If Destroyed Then
DateDestroyed.Visible = True
Else
DateDestroyed.Visible = False
End If
If Me.Destroyed = True Then
Me.AllowEdits = False
Me.AllowDeletions = False
Else
Me.AllowEdits = True
Me.AllowDeletions = True
End If

End Sub

and

After Update in Check Box
Private Sub Destroyed_AfterUpdate()
If Destroyed Then
DateDestroyed.Visible = True
Else
DateDestroyed.Visible = False
End If

If Me.Destroyed = True Then
If IsNull(DateDestroyed) Then
MsgBox "Please enter the destruction date"
Me.DateDestroyed.SetFocus
End If
End If
End Sub

Any suggestions.

 

[gusbrunston]

.
Dear pd06498:

I would like to help, and I will take some time this morning to look thorougly through you code.

My first suggestion is that to attract the attention of more people, you might want to surround the code you post here with the TGML tags "code" and "/code". This makes code much easier to read and decipher, especially for those of us over the hill, with failing eyesight. Not complaining, just a suggestion...click on the Process TGML CheckBox at the bottom of the Your Reply window for more information

For example:

Private Sub Form_Current()

If Destroyed Then
   DateDestroyed.Visible = True
Else
   DateDestroyed.Visible = False
End If

If Me.Destroyed = True Then
   Me.AllowEdits = False
   Me.AllowDeletions = False
Else
   Me.AllowEdits = True
   Me.AllowDeletions = True
End If

End Sub

I will get back to you.

Cheers,

Gus

[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7, on the way to 6, and beyond!
Webmaster: www.rentdex.com[/tt]

 

[gusbrunston]

.
.
1.

You may have one or more fields in the underlying data source that is/are designated "Required" in the table. If you can locate that/those fields, you can create a query that will allow you to use the "Null-to-Zero" function to automatically convert nulls into data acceptable to Access.

Here's an example of the syntax if you put it into a field in a query in design view:

   Expr1: nz([AnnualBalance],0)[code][/b]

Here's an example of it's use in VBA:[b][code]

   strRecipientName = Nz(Me.txtEmail, "X")

Although it's called Null-to-Zero, it works with strings too, (and other data types, I suppose) as in the above example.

This might help get you past those people in the box that censor the input of data in first records.

2.

For some time I put up with an error message 94 in a check entry program. At last I corrected it by revising the Tab Order of the form. As I remember it, one field had to have the data from another field for it's calculation... the other field was further down the tab order list than the field that required it's input for its own purposes.

Let us know if one of these suggestions solves your problem.

Cheers,

Gus

[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7, on the way to 6, and beyond!
Webmaster: www.rentdex.com[/tt]

 

[RoyVidar]

Hi!

Think the "offence" here is that since there's no record the checkbox provides a null value, I assume the checkbox is "Destroyed".

One way to test for it, would be using gusbrunston' version with nz, for instance like this:

[tt]dim bolDestroyed as Boolean
bolDestroyed=NZ(Me!Destroyed,0)
if bolDestroyed Then
Me!DateDestroyed.Visible = True
Me.AllowEdits = False
Me.AllowDeletions = False
Else
Me!DateDestroyed.Visible = False
Me.AllowEdits = True
Me.AllowDeletions = True
End If[/tt]

Another suggestion, would be to pehaps use the newrecord property of the form, the test might then look something like this:

[tt]If Me.NewRecord Then[/tt]

This would probably also work. To use it in existing test, put int the 'Not' keyword before me.newrecord, else toggle the true/false lines of code.

- and please when posting, state which line(s) gives the error, it saves a bit of time when trying to assist;-)

HTH Roy-Vidar

 

[pd06498]

Roy

It appears your suggestion worked. Thanks for that.

And I take on board your other suggestions.