Populate Blank Fields with "0" based on checkmark.

[DM - NPS]

I have a form with 20 fields. The user enters data into some but not all of
the fields depending on where they collected data. Is it possible to create
a checkbox where, after the user enters their data, they click the checkbox,
and any of the 20 fields that are blank get populated with a 0?

Thanks for the help.

 

[Dorian]

Is there any reason you want them to be zero?
If they are numeric fields, you can set the default to zero in table design
view.
There is no need to use a checkbox, you can arrange it so any empty fields
are converted to zero when you move to another record or exit the form.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[DM - NPS]

It is a quality check so an individual has a chance to review the fields they
entered prior to having all the fields populated.

 

[Al Campagna]

DM,

It is a quality check so an individual has a chance to review the fields
they
entered prior to having all the fields populated.

I don't see how a "quality check" has any bearing on the
solution offered by Dorian. And, that that very basic solution negates the
need for a check box.

If a user has failed to enter a value into a control, how does clicking
a
checkbox, and setting null values to zero, improve or enhance the quality
of that process?

If you make the Default Value of each field 0 (for numeric fields),
if the user does not enter anything into those particular fields, their
value will,
by default, be 0.
As you move to another record, those values are written to the table for
that record.
If you close the form, those values are written to the table for that
record.
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[DM - NPS]

I understand what you are both saying but it still does not get at what I
need so maybe I am not explaining it correctly. If I have the fields
autopopulate with 0 then it makes it very difficult for the user to look at
the form/table to see where they have entered data and where they have not
entered data. By clicking on a check box, it tells the project manager they
have reviewed the data and agree that it is complete. Instead of having them
review the data and then enter all the 0's I would wanted them to just be
able to click the checkbox and then have it autopopulate.

this is important becasue I will need to do the same task for another
database in the upcoming days where the user enters data into over 200 field.

Is there a way to set the default to 0 but not actually have the 0 show up
on the form until the user checks the box?

If you know of a way to do this tasks I would really appreciate the help
since my deadline is fast approaching.

 

[John W. Vinson]

I understand what you are both saying but it still does not get at what I
need so maybe I am not explaining it correctly. If I have the fields
autopopulate with 0 then it makes it very difficult for the user to look at
the form/table to see where they have entered data and where they have not
entered data. By clicking on a check box, it tells the project manager they
have reviewed the data and agree that it is complete. Instead of having them
review the data and then enter all the 0's I would wanted them to just be
able to click the checkbox and then have it autopopulate.

You could use the AfterUpdate event of the checkbox with some VBA code to loop
through all the controls on the form. It would simplify matters if you set the
Tag property of each control which should be autopopulated to 1 so the code
can tell what to populate and what to skip. Example code might be

Private Sub chkReviewed_AfterUpdate()
Dim ctl As Control
If Me!chkReviewed = True Then ' only populate if the box was checked
For Each ctl In Me.Controls ' loop through all controls on form
If ctl.Tag = 1 Then ' only populate the desired controls
If IsNull(ctl.Value) Then
ctl = 0
End If
End If
Next ctl
End If
End Sub

this is important becasue I will need to do the same task for another
database in the upcoming days where the user enters data into over 200 field.

you *do* know that a) there is a hard limit of 255 fields in any Access table
and b) that a table with 200 number fields is *CERTAINLY* incorrectly
designed? You should REALLY stop and check your table normalization before you
plunge into this misguided form design!!!

Is there a way to set the default to 0 but not actually have the 0 show up
on the form until the user checks the box?

No.