Insert unknown into multiple fields on save

[Kayleigh]

Hi,
I have a data entry form with multiple fields. None are required however when the form is saved I would like the blank fields to be filled with 'unknown'. Is there any way to do this without specifying each field?
Would appreciate if there was a function since this will be applied to several forms.
Thank you

 

[Isaac]

You can try looping through the controls. If the type of the control is textbox or combobox, then set your tb or cbo variable to the control, then fill it if it's blank.

 

[The_Doc_Man]

The other viewpoint is that you do nothing with the record but put a special format on the text box in the form.

Format a text field - Microsoft Support

Custom formats for text fields in Access can be useful when you want the data displayed in a specific way.

support.microsoft.com

You could put @; "undefined" as the .Format property of text boxes for which you wish to do this. The @-sign says "If it isn't blank, display what is there." The second argument (right side of semicolon) is what you display for a null or zero-length string.

 

[Kayleigh]

I'll try that @Isaac . The point of setting to 'unknown' is not for the form view but rather later when querying what data is required, the unknown fields will be flagged up.

 

[The_Doc_Man]

Krayna, with due respect, you are making extra work for yourself. The formatting trick I referenced will SHOW you "undefined" on forms (or reports, for that matter), and if you are querying fields, it is equally easy to look for "" (a zero-length string) as it is to look for "undefined" in a search through a specific field.

 

[Kayleigh]

But this sounds like you are restricting any entry from the user?
I adjusted this loop...

 

[The_Doc_Man]

No, I'm not restricting anything at all. The format property only controls what the form will show for that control when the value is non-blank or blank. This has nothing to do with what it looks like if you edit / fill the record's appropriate field to something that isn't blank. If you put something there, the "@" format kicks in and shows you what was there. I'm just saying if you put the format as I showed you, then you don't have to update the record if nobody puts anything there AND if you don't put anything there, you can search for nothing being there as easily as if you put the word "unknown" there. But that leads to this question... can your user ever legitimately put UNKNOWN" in that slot?

 

[jdraw]

Untested, but you might be able to make "Unknown" as the default value. I still like Doc's approach better.

 

[Uncle Gizmo]

I first saw the approach that Doc mentions in a post by MarkK, about 2 or 3 years ago. I did a blog & YouTube about it here:- (See Video 1)

Display a Message for the User - Nifty Access

Display a Message for the User There are many ingenious ways to offer help to your MS Access Application user. In a help table in a Text Box. There is a YouTube video by way of explanation here, and a downloadable example database. Display Message inside TextBox Display a Message for the User...

www.niftyaccess.com

There is a link to Mark's original post with a sample database.

 

[Pat Hartman]

it is equally easy to look for "" (a zero-length string) as it is to look for "undefined" in a search

I hate to disagree with Doc but I would set the default for non-required fields to null. I would set the AllowZeroLength property of text fields to NO. So your query would check for null. That is the "undefined" value. Remember "" is a Zero Length STRING and that means that it is NOT valid for numeric fields or dates so if you have dates and numeric fields, you have no alternative but to use null as their default unless you want to make up some ridiculous values such as 9999999999 or 12/31/2999. Using Null lets you be consistent. You will never care what the data type of a particular field is. You always check for null.

You can still use the format trick to show "unknown" and I don't think that will conflict with numbers or dates but check first.

 

[The_Doc_Man]

@Pat Hartman - I've known you through this forum too long to be bothered by us having different viewpoints on something. And if you read carefully, I didn't say to not put a null for a default value. I was thinking more specifically that if the field in the form is null (for whatever reason) then don't automatically change it to the string "unknown" - but if the default value was what made the field null on a new record, fine. I was specifically against making the field contain "UNKNOWN" in the underlying record through a "BeforeUpdate" event routine.

Your point about "unknown" being unsuitable for dates and numeric fields is, of course, totally on point. Which is why I made my suggestion, because there is a format equivalent for non-string fields. If the field is null by the time the form shows it, my thought is to do nothing else to it. If it was null because of a default value on a new record, that occurs before the form ever sees the record. We aren't so far apart. Just looking at different stages of the same process.