VBA to check unfill items ??

M

Martin

I want to write a VBA program to check whether there is any items in a FORM,
which was forgot to fill information in.

The VBA I wrote is as follows:

If [age]="" or [salary]="" or [telephone]="" Then
MsgBox "There is one or several items left, please check and fullfill all
the items"


to my sorrow, ="" is actually not expressing null


How to express this, and check out if there is any unfilled item in a FORM ?
 
D

Dennis

Use this instead (it checks for Null and converts it to anything you want, in
this case an empty string)

If Nz([age],"") ="" or Nz([salary],"") ="" or Nz([telephone],"") ="" Then ....
 
N

Nick Coe \(UK\)

Lookup the IsNull() function in Help, you'll end up with
something like:

If [age]="" Or IsNull([age]) Or [Salary] = "" Or
IsNull([salary]) and so on

The Nz function might be a usefull alternative if the value
being tested is a variant:

If Nz([age])="" Or Nz([salary]) = ""

Remember that "" is a zero length string not a null.

If the values you are testing are actually controls on a
form and the code is in that form's class module then it is
good practice to use FormName.ControlName or Me.ControlName
rather than just [ControlName]. If it is the value from the
underlying table or query then you must be carefull where
you trigger the code. Do it in the wrong place before the
form (edit buffer) is saved to the table and you won't be
testing what's been typed into the form.

--
Nick Coe (UK)
Available - Will work for money :)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.pjandcoe.co.uk/ Online Store

In Martin typed:
 
A

Allen Browne

The last event that runs, just before the record is saved, is the
BeforeUpdate event of the *form* (not control). This is the *only* way to
catch the blanks, regardless of how the record save is triggered.

Use this event to test if the value is null. The example below shows how to
to that.

An even better way is to open the table in design view and set the Required
property to Yes for the fields where a value must be supplied.

Note that Null is different from a zero-length string. More info on handling
nulls:
Nulls: Do I need them?
at:
http://allenbrowne.com/casu-11.html

BTW, storing Age is generally not a good idea. With just a few hundred
people, your data is becoming inaccurate every day. Store the date of birth
if possible, and calculate the age as described here:
http://allenbrowne.com/func-08.html

Example event procedure for the Before Update event of the form:
---------------code starts-----------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.Age) Then
Cancel = True
strMsg = strMsg & "Age is blank." & vbCrLf
End If

If IsNull(Me.Telephone) Then
Cancel = True
strMsg = strMsg & "Telephone is blank." & vbCrLf
End If

'etc for other controls.

If Cancel Then
strMsg = strMsg & vbCrLf & "Correct the entry, or press <Esc> to
undo."
MsgBox strMsg, vbExclamation, "Bad data"
End If
End Sub
---------------code ends-----------------
 
D

DoeG

Sort of relating to this, but how would I be able to detect if a parent
record had any children records? Logically thinking, the syntax would
be something like:

parentrecord = [Parent].[ParentID]

If IsNull([parentrecord].[child]) then
or
If IsNull(Count(*)[parentrecord].[child]) then

I know that these code samples do not work, but I don't know how to
access the child using the parent like that. Just giving you an idea
of what I want here. The more I think about it, the more confused I
become.
 
A

Allen Browne

Use DLookup() on the subform's table to determine if there are no children
for the record in the parent form:

Dim strWhere As String
Dim varResult As Variant
With Me.Parent
If Not .NewRecord Then
strWhere = "SomeField = " & !ID
varResult = DLookup("SubID", "tblSub", strWhere)
IF IsNull(varResult) Then
Debug.Print "No children"
End If
End If
End With
 
D

DoeG

Alright I'm still a bit confused.
In detail, this is what I have. I have a form that displays
information about the parent, and I want a marker of some kind (box
that turns red, text box that is filled) that shows if there are child
records for the current record.
I'll try and break down your code to see if I understand.

I'm not familiar with the "With" function, but I assume that whatever
follows "With" is the object being used to avoid the programmer to have
to type in the same object name over and over.
If the current record is not brand new, then start the code.
strwhere = '"Somefield = " & !ID' is referring to the parent ID of the
parent record.
I don't know what goes in the area of "SubID" and "tblSub" of the
Dlookup function, but I understand the concept. Since I don't use a
subtable in my problem, would I have to make a subtable and turn it
invisible or something to get this to work?
Everything else I understand.
Much Thanks.
 
A

Allen Browne

If you are testing whether the main form's record has any child records,
then presumably there is a table you need to look in for the child records.
"tblSub" represents the name of that table. "SubID" represents the name of
the primary key field of tblSub.

When you DLookup() the primary key of the related table, using the Criteria
that limits the DLookup() to child records, you receive a Null if there is
no match. So if the result is Null, there are no child records.
 
D

DoeG

Ok thanks, I think I understand now. But when I run the code I get an
error that says 'the expression you entered has an invalid reference to
the parent property' and it highlights With Me.Parent as the trouble.
Is .Parent an actual property, or do I need to enter data in there too?
Here is my code:

Dim strWhere As String
Dim varResult As Variant
With Me.Parent
If Not .NewRecord Then
strWhere = "Gage # = " & [Gage Calibrator].[Gage #]
varResult = DLookup("Primary Key", "Failed Calibration
History", strWhere)
If IsNull(varResult) Then
boxflag.BackColor = vbRed
End If
End If
End With

Thanks again.
 
A

Allen Browne

From your original question, I assumed that the code was to run in the
subform's module.

A form opened as a subform has the Parent property.
 
D

DoeG

I have the code running under the main forms Form_Current event, so the
flag changes as the records do. There will be no subforms involved
here.
 
A

Allen Browne

Okay, then use the value of the account number field in the form; you don't
need any reference to the Parent property.
 
D

DoeG

Alright, this is whats going on now, here is my code:

Dim strWhere As String
Dim varResult As Variant
With Me.[Gage #]
If Not Me.NewRecord Then
strWhere = "[Gage #] = " & Forms![Gage Calibrator].[Gage #]
varResult = DLookup("Primary Key", "Failed Calibration
History", strWhere)
If IsNull(varResult) Then
boxflag.BackColor = vbRed
End If
End If
End With
End Sub

I'm not sure if after strWhere I am supposed to have [Gage
Calibrator].[Gage #] or just [Gage #] as I have it now. Gage
Calibrator is the name of the table and Gage # is the name of the
field. Gage Calibrator is also the name of the form in Forms![Gage
Calibrator].[Gage #], with Gage # being the bound text box.
Now the problem is however, I run into errors on the varResult = line.
If I put brackets around Primary Key (I shouldn't have named the
Primary Key field "Primary Key" but I didn't think it really mattered)
then I get this error:

Run time error 2471: The expression you entered as a query parameter
produced this error: The object doesn't contain the Automation object
'(Whatever is in [Gage #]')

If I remove the brackets, then I get the error:

Runtime error 3075: Syntax error (missing operator) in query expression
'Primary Key'.

Sorry if you keep having to dumb it down for me =). I appriciate all
your help.
 
A

Allen Browne

The [Gage #] in square brackets is correct, if this is the name of the field
in the related table. (No table name needed.)

If [Gage #] is a Text type field (not a Number type field), you need extra
quote marks:
strWhere = "[Gage #] = """ & Forms![Gage Calibrator].[Gage #] & """"

You can debug this in the Immediate Window (Ctrl+G) by entering the
DLookup() part there until you get it right.

It may also help to add:
Debug.Print strWhere
so you can see what the string is like when it runs, and see what's wrong.
 
D

DoeG

You are correct in saying that the [Gage #] is the primary key field of
the main table, as well as the field that shares the relationship with
the child table.

I'm not too familiar with using the Immediate Window, but I've used it
before in regular VB6.
 
G

Garret

I tried fooling around with it and entering your examples but none of
it seems to work - I keep getting errors. More help please?
 
A

Allen Browne

Garret, you may need to do some reading and understand the code you are
writing to get it to work. I can't spend further time on this thead to do it
for you.
 
Top