Check Box Default Value

[Rebeca]

I would like to make the default value of a check box 0 If another field in
the same table is Null and -1 if that field is Not Null. How can I do this?

 

[Jeff Boyce]

Rebeca

It sounds like you have a field (your Yes/No-"checkbox" field) whose
(default) value depends on "another field in the same table".

If you already have that other field, why do you need the Yes/No field?
(note -- it is considered poor relational database design to have the value
of one field in a table dependent on another field's value in that same
table.)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BruceM]

The default value applies when a record is new, or if the control is
unbound. I expect you may mean that as you navigate throught the records
you want the check box to be clear if a field is null, and checked
otherwise. In that case you could have something like this in the form's
Current event:

Me.YourCheckBox = Not IsNull(Me.SomeField)

If you really mean default value, how is "another field" assigned its value
when you go to a new record?

 

[Rebeca]

My check box tells me whether or not the record is "closed". It's closed once
we've delivered the merchandise, so when delivery date is filled in, I would
like Access to automatically "close" the record. Is there something that
would work better?

 

[BruceM]

Jeff's point is that the presence of a delivery date means the record is
closed. It is not more closed if you add another field. If you wish to use
an unbound check box for user convenience you could use the code I
suggested.

 

[Rebeca]

Bruce,
I guess I was wrong to say "default", but you're right, as I navigate
through the records, I would like the value of the check box to change
accordingly. Now to the next step, I'm very new at this so sorry if I ask
silly questions, but where is the form's current event? If I go to properties
and click Event, there is no 'current'. Also, can you tell me what 'Me.'
signifies in your expression?

 

[BruceM]

Open the database. The database window is the thing that shows up with
Tables, Queries, etc. on the left side, and the actual objects on the right.
If you click Forms you will see a list of the forms in your database. Open
your form in design view, then click View >> Properties. This opens what is
known as the form's Property Sheet. The Property sheet for a control such
as a text box may be opened similarly, except you first need to click the
control to select it. For now, just look at the form's Property Sheet.
Click the Events tab. Click Current, and click the three dots on the right.
Click Code Builder, OK. The VBA editor should open, with something like
this:
Private Sub Form_Current()

End Sub

The cursor should be blinking between those two lines. Enter the code
there.

If I once knew what exactly the Me stands for, I have forgotten. When you
type Me and a dot you are then able to enter one of the form's properties.
Fields and controls are considered properties of the form. Me.CheckBoxName
refers to the text box. SomeField in my suggested code would be your
DeliveryDate field. Not IsNull(Me.DeliveryDate) evaluates to True if
DeliveryDate is not null (i.e. contains a value). In that case
Me.YourCheckBox = True; otherwise it is False.

Again, no need to store Closed. The presence of a delivery date already
indicates the record is closed.

 

[Rebeca]

Thank you. Based on what you and Jeff said, I guess I should just create a
query that shows me all closed records based on the contents of the delivery
date field, rather than having this check box.

 

[BruceM]

Or create a query ordered by that field so you can see open, then closed
orders, or however you choose to view the records. Have a check box if it
makes it easier to see the information at a glance, but there is no need to
store the information. You can see closed records by using a crtieria in
the query:
DeliveredDate Is Not Null
If you had a yes/no field, the criteria for that field:
=True
would have produced the exact same recordset at the first criteria on the
DeliveredDate field; therefore one of the fields is unnecessary. Remember,
display and storage are two different things. Another example is that you
can store a name as FirstName and LastName, and display it as:
FullName: [FirstName] & " " & [LastName]
without ever storing the full name.
The main point is not to store the same information in two or more places.