Is it possible that the Available field could be an empty string instead of
null?
Here is what can happen. A field will start out null (i.e. has never had a
value). When you insert a value and then delete it, its value is no longer
Null, but "" (an empty string). Both appear blank in the table, but "" and
Null are recognized differently by Access.
To correctly set Available contents back to null, you must programmatically
set Available = Null instead of setting it to "", just deleting it from a
bound text box, or manually clearing the value in the table.
Thankfully, there is a way to prevent this behavior. Check the table to see
if the Available field has Allow Zero length set to No. If so, it will ensure
that if it is empty it is Null and not "", regardless of how you empty it.
Here is a good test to see if this is happening:
Open the query builder
Add the 1 Vehicle Info table
Add the Vehicle# & Available fields to the query grid
Type Null as the criteria for the Available field.
Run the query.
You will see all the vehicles where Available is null. Now look in the table
directly to see if Available is blank for all (and only) the same ones. If
not, the above scenario is probably the answer. If not, repost, and we will
dig a little deeper.
Sonya said:
We are getting so close. I got the message box to come up by using the below
code, but each time I made a car unavailable the message box came up saying I
only had 1 vehicle left (eventhough there were 3 or 4 cars left). I am using
null for available because there are certain vehicles that are n/a. So I
have null, unavailable or n/a in the available field. I really appreciate
your help.
If DCount("[Vehicle#]", "[1 Vehicle Info]", "IsNull([Available])") Then
MsgBox "There is only one available vehicle left.", vbExclamation, "One
vehicle left"
End If
:
Hmmm....it works for me. I was assuming that the Available field was a Yes/No
field, thus the Available = True. If it is a text field where you enter
"Unavailable" when it is no longer available, then you might want this
instead:
MsgBox DCount("[Vehicle#]","[1 Vehicle Info]","IsNull([Available])") 'if
you want to find all where Available is blank
Or
MsgBox DCount("[Vehicle#]","[1 Vehicle Info]","[Available] = 'Available'")
' if you want to find all where Available says "Available"
Or
MsgBox DCount("[Vehicle#]","[1 Vehicle Info]","[Available] <>
'Unavailable'") 'if you want to find all that do not say "Unavailable"
In my opinion, it would be best to change the Available field to a Yes/No
field, default to True (Available) and set it to False (i.e. not Available)
when it is made unavailable.
If the message box gives you 0, then it is finding no records where
Available = True.
:
I did the below code in the Form (after update). Changing Vehicle # (field)
and
1 Vehicle Info (table). Available is the name of my field. I made all the
vehicles unavailabe except one and the message box never came up. What am I
doing wrong?
If DCount("[Vehicle#]","[1 Vehicle Info]","[Available] = True") = 1 Then
MsgBox "There is only one available vehicle left.", vbExclamation, "One
vehicle left"
End If
:
In Form_AfterUpdate, add this (adjusting for your table/field names):
If DCount("[VehicleID]","[Vehicles]","[Available] = True") = 1 Then
MsgBox "There is only one available vehicle left.", vbExclamation, "One
vehicle left"
End If
This runs after the current record is saved and will issue the message when
only one available vehicle is left.
Yes, you can set up Access to e-mail automatically. The code would go right
after the MsgBox line above. Search the Access Programming forum for
e-mailing from Access, since that has been addressed there many times.
:
I have a database w/ vehicles and thier info listed. In my query, I have a
field that will be blank if the vehicle is available and when it is issued
the field reads unavailable. The criteria is set to show only the records
that are blank in the available/unavailble fields. I would like a warning
box to automatically display when only one vehicle is left to use. And can I
set up Access to e-mail someone telling them that only one vehicle is
currently available for use? Thank you in advance for your help.
