is there a way

[Lhuffst]

I have a bound form that has 20 fields. The first field is a date which I set to today so the customer doesn't have to enter it. (they can only enter data for current date).

During testing, I've noticed that I have many records that have the date filled in but not the remaining fields. Is there a way to make sure that if only the date is filled in, do not save the record or since it's bound, delete the record before going to the next one? I don't want to make all fields required because there are alot of fields.
Thanks
lhuffst

 

[dhookom]

How do you set the first field value? I would set the default value to Date() and lock the field. I assume your current method my be responsible for adding blank records.

Duane
Hook'D on Access
MS Access MVP

 

[Darrylles]

Lhuffst,

DHOOKUM is of course (as always) correct.
Set the date field <default> to Now() or Date() - that sorts IT out (without VBA code).

However, this will still happen if you haven't set any other fields to mandatory on the table.
If you haven't set any field to 'required' - Access will of course save the record (say: if the user clicked a field and 'dirtied' it (e.g. entered a space)).
Check all fields of the 'blank' records - one of each will have some entry (a space?).

I like to think that 75% of all software development logic with databases can (and should) be covered by the table designs.

So, put as much 'control' of data in one, single place (the table definitions) as possible - that means that you then don't have to 'control' data entry (many times) with code.
All you have to do is capture error codes automatically provided by your table definitions.

So, choose the fields in your table that MUST be entered for the whole record to be valid - ensure that you set them to 'REQUIRED' in the table.
Now, if they aren't ALL filled - there will be no INSERT of the record (it will throw an error and refuse to insert it).
If they ARE filled, only then will the default date field be added by Access.

DHOOKUM: Am I right, your view is always appreciated?

ATB,

Darrylle ;-)









Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[dhookom]

Darrylles,
Good recommendations on required fields and 100% agreement on getting the table structures correct.

Duane
Hook'D on Access
MS Access MVP

 

[missinglinq]

If your requirement is truly

"...to make sure that if only the date is filled in, do not save the record..."

and you don't want to have to check specific Controls for data, why not only populate the date field if the Form is Dirtied, i.e. a Record is entered?

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If Me.NewRecord Then
  Me.YourDateFieldName = Date
 End If
End Sub

If no data is entered into any other Control, then the Form_BeforeUpdate event will not fire, the date won't be assigned, and no Record will be created.

Of course, this would still be defeated if the user enter some data, then deleted all of it, but a little bit of user education is could alleviate this possibility! Years ago, before desktop publishing came into being, and before most people had any idea of what the term 'font' meant, a local printer gave away posters with pithy sayings on it, each in a different font, to guide their customers in selecting a font, and the saying I always remember is

"The problem with making anything foolproof is that fools are so damn ingenious!"

Linq ;0)>

Hope this helps!

There's always more than one way to skin a cat!

All posts/responses based on Access 2003/2007

 

[Lhuffst]

Thanks everyone. I'll first try the table method

 

[Lhuffst]

Everything works great except for the blank records issue.
I took Darrylles advice and made a field required on the first subform. If there was only one form, I think this would be great but currently it is blocking me from going to another subform. Not all subforms have to be filled out every day.

so my dilemma is how to get rid of the blank records or prevent them on a continual basis. I defaulted the data entry date to date() because will be entering multiple items per subreport on a daily basis and this keeps the date clean which is what reports are based off of.

Any ideas/suggestions are most welcomed

 

[Darrylles]

Lh,

Blank records: are they historical? Because, they should not be being added now, there should be no new ones.
If historical, then can't you simply delete them? They are 'pointless' anyway. (Please explain in more detail).

As for the 'subform' issue; this depends on your master / subform design.

I generally view subforms as sub-entities; they are related to a another recordset in a 1 - to many way.
E.g. Master record = Person
Sub record = Vehicles owned (0, 1 or many).

You have said that not all subforms need to be populated, well, my past advice should also solve this issue:
[ul]
[li]If 'Person' is blank - vehicles can never be added (it would be pointless - they'd be unattached to a 'master' record).
[/li]
[li]If 'required' field on the 'Vehicles' subform is populated - it will save the 'Person' - with the 'Vehicle' sub record.
[/li]
[li]If 'required' field is not populated (and no other fields dirty) - it will save the 'Person', but save no sub record.
[/li]
[li]If 'required' field is not populated (and other fields ARE dirty) - it will throw an error for you to trap. (And you can't leave it until it is populated or the record removed).
[/li]
[/ul]

The possibilities are endless; I think that you may be talking about 'dirtying' a sub-form, then attempting to leave it, and being trapped because the required field is empty (and there is no intention to fill it); if so, here's a discussion that may help...

http://www.accessforums.net/forms/resetting-form-having-required-fields-12724.html

Again, can you explain with a little more detail?

The key to all of this, is clearly understanding the <Event> (trigger) structures / paths (through the form / subform / sub-subform etc) when they fire, why and in what order)).
I'm no expert at this, but improving it will make your life much easier.

ATB,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.