Preventing a Null error

[tylerg11]

Due to database design, I am not allowing Null values in my tables. On my main form, the default value for the text boxes is " ". However, if the user removes the zero-length string, they will get a Null error. I've tried many different things to capture the Null entry and convert it using the Nz() function, but I can't get rid of it!

[help_me_with_access]

did you try checking it in visual basic before the rec is entered? try "isnull(control)"

[tylerg11]

I tried the following code:

Private Sub txtTitle_LostFocus()
    If IsNull(txtTitle) Then
        txtTitle.Text = ""
    End If
End Sub

It still returned the "cannot insert Null value into Variant data type" error.

[help_me_with_access]

well you can't change a value on one of the control's events sir. the control can't change when it's got focus.

how about changing it at the time the record is entered? e.g. - BEFORE the record is entered? what do you have setup as an entry control? button?

[tylerg11]

I have a seperate form for entering new records, and I can catch any nulls when they hit the "Submit" button. But I am talking about editing existing records. If the field currently contains data, but the users wants to remove it. A zero-string value should be entered if they leave the field blank.

[help_me_with_access]

oh i see....so this is happening on a form for editing purposes, right?

well I'm not sure but I know there are options. like maybe a validation rule like this?

=iif(me!control = null, "", me!control)

or maybe a query that runs after the form is closed? like something to update all nulls in the table to ""? I think the default is null if someone just delets out data in a field? isn't it?

maybe you could try using the nz() function in the actual validation rule? try all of that stuff, something is bound to work. obviously sql would work, but if you don't have form locks for multiple users set up that's not a good idea as you're asking for corruption.

[tylerg11]

Ok. I'll probably keep messing with it. Thanks.

[Rod]

Please excuse me butting in.

tylerg11: What are you doing testing the Text property? You need to test the Value (default) property and I suggest it is best to do this is in the BeforeUpdate event because it occurs before anything is changed in the underlying recordset or database and because you can if you wish cancel the event. Hence:

Private Sub txtTitle_BeforeUpdate(Cancel as Integer)
    If IsNull(txtTitle) Then
        txtTitle= ""
    End If
End Sub

or pedantically

Private Sub txtTitle_BeforeUpdate(Cancel as Integer)
    If IsNull(txtTitle.Value) Then  txtTitle.Value = ""
End Sub

[tylerg11]

Still not working, sir. My DB is linked to SQL tables. Could this also be part of the issue. The error says, "You tried to assign the Null value to a variable that is not a Variant data type." Is there a different way this needs to be addressed?

[John_G]

Hi -

When do you get the error? As soon as you leave the text field, or as soon as you leave the record? There is a big difference. You will only get the error when you leave the record, and MS Access tries to update the record in the database. Try using the AfterUpdate event of the Text Box:

[txtTitle] = nz(txtTitle,"")

Rod: You have the right idea, but trying to change the contents of a control in the Before_Update event of that same control will give you an error. All you can do in the Before Update is check the value, and cancel the update if the value isn't valid.

John

[tylerg11]

It is immediately after I leave the field, not the record.

If I clear the contents of the field, and hit the Tab key, I get the error.

[tylerg11]

I tried what you suggested, but I still get the error.

[John_G]

You asked earlier if using a SQL Server database might be the issue - well, maybe. What is the data type of the field in the SQL database that is causing the problem?

In MS Asscess 2003 help, I found this:

"...that is connected to a Microsoft SQL Server database, you can enter a zero-length string in a field whose data type is varchar or nvarchar."

I'm not all that familiar with SQL Server databases yet - but you might want to look into that aspect.

John

[tylerg11]

Thanks for the help. Yes, the field is nvarchar. Not sure why I'm having so much trouble with it. If I manually enter a zero-length string and leave the field it works perfectly. Just trying to do it with code and it is just not working..

[John_G]

I may have found it. Put the [txtTitle] = nz(txtTitle,"") in the ON Exit event of the text box.

It seems to work with an MS Access table - here's hoping it good for SQL as well.

John