Error Message when closing form: Cannot contain zero length string

[JPR]

Hello,

I am getting the following error message when closing a form: Run Time Error 3315 – Field XXX cannot contain zero length string.

My db is update every month with data that I import from a text file.

Data is imported in a table (table1) and displayed in form1 using a query as records source as to to open the form users select certain criteria.
This data cannot be changed or modified.

This form has also some fields that are duplicated in another table that I call History, which saves data as table1 every month is updates with new records and i need to save the updated information. The old records in Table1 are deleted before I import the new ones.

To save data into table History, I run the following code on the close event:

'Append new data from controls in Table1 to table History.

Dim chgreqrec As Recordset
Set chgreqrec = CurrentDb.OpenRecordset("History")

chgreqrec.AddNew

chgreqrec![NewPhone] = NewPhone
chgreqrec!NewAddress = NewAddress

chgreqrec.Update

Me.NewPhone.Value = Null
Me.NewAddress.Value = Null

DoCmd.Close

The form should be able to close with or without updates to the various fields, meaning even if they are left blank.

Thank you for any help with this matter.

 

[arnelgp]

check the 2 fields you showed in Design view of your History Table.
on their Property Sheet (below sheet) Allow Zero Length should be set to Yes.

 

[JPR]

Thank you. Considering that these field can even be left blank (not update), should I also indicate REQUIRED YES?

 

[arnelgp]

required Yes, means the field Cannot be Blank.

 

[JPR]

Thank you.

 

[Deleted member 147267]

The old records in Table1 are deleted before I import the new ones.

Why? If you changed the process to append rather than over-write, you would not need the other table nor this process. If that would mean you would have duplicate records, you can do a combination of append and update.

Don't confuse an empty string with null. If you use Is Null (or Is Not Null) on a query field with zls values, you will not get records with zls in them.

 

[Isaac]

Also, the way Access treats values when a record is first created vs. later edited, although technically correct I feel, can be confusing.

For example. If you create a table with a column of Short Text, and Allow Zero Length = Yes, and Required=Yes, you still won't be able to create a record without typing anything in that column. Why? Because it starts out as Null, not ZLS. If you type something in a new record and save it, then come back later and delete the text out of that value, (so it looks Blank again), you can then save the record. Why? Because now you have overridden the text value that was there with a ZLS (not Null).

In Access specifically, I usually handle validation like this on the Form level. I just find it easier to have it in one place (Form) rather than two places (Form and Table). Also, if I put the burden of validation on the Table, that just lends itself to obscure and vague error messages sometimes when working with Forms, and then if I want to trap those or elaborate on the error, I need more Form code, which then means I had to code some validation in the Form anyway.

Also, avoiding a lot of table-level validation allows me an in-road when backend updates or inserts are required that may violate constraints as a temporary project, pre-population for a survey or collection project, data remediation, one-offs, etc.

(Just the way I approach it, usually but not always, in Access. In other RDBMS table constraints is a whole science, and meaningfully so).