How would I set up code to check all null fields?

[Dave R.]

Hi. I am working on a form which I am trying to setup some code on. The idea
is to check the fields for being complete and to write any incomplete field
names (e.g. var1, var2) to an unbounded text box (qcbox).

I know that one way to do it is:

If Isnull(Var1) then
QCBox = "Var1"
End if

If Isnull (Var2) then
QCBox = QCBox & " Var2"

.... and so on.

There must be a quicker way, but I am not sure how. Is it possible to put a
list of all variable names (there are around 30) into the code, then use a
"For" statement to run the above code that loops through and checks all of
them?

By the way I know I can use validation for this, but the form should not
actually REQUIRE anything; I just want this feature so that I am aware of
what is blank, and so forth.

Thanks for any help.

 

[Douglas J Steele]

It's not clear to me exactly what var1 and var2. Are they controls on the
form, or are they fields in the underlying recordset? Are you attempting to
validate before saving, or are you attempting to validate upon reading the
row?

 

[Dave R.]

Var1 etc. were just examples of variable names. My intention is that this
would be attached to a "clickable" button. When clicked, the unbound text
box would be populated with field names that happen to be null. For now, I
have used Excel to create the code (by putting field names in one column,
and in another column the code with & A1 to insert the field names), so that
I wouldn't have to do as much manual typing. It is seeming to work BUT..

At this point I am having a related problem that I have no clue about. This
program was seemingly not recognizing certain fields (they would not show up
in the list being written to the unbounded text box).

So I created another unbounded text box for testing (called testbox), and
even when I am entering data into that field on the form, I cannot get the
code to write to my test box. For example I update the field "Nephrop8b"
with a value (0 or 1 or 2). When I run the test code which contains the
line Testbox = Nephro8b (inbetween sub, & end sub.) , nothing is written to
the test box. It does this (fails to write) only on fields that didn't show
up in the first box (the one that was supposed to work on all the fields).
But it does work for those that did show up in the first box (i.e., it will
write the value of that field to this test box). I cannot see any difference
between the ones that work and those that don't, and I have verified that I
am referring to the correct field name.

Is there anything else I can look for?

 

[Dave R.]

That was a typo of mine in this message (nephrop8b), the actual code is
accurate though.

 

[Dave R.]

I learnt a valuable lesson I think. I cannot refer to the actual variable in
the table when writing code in a form. I have to refer to the name of the
form element e.g. listbox115. That seems to have fixed it.

 

[Douglas J. Steele]

Sorry, but that doesn't answer my question!

Is var1 supposed to represent the name of a control on your form?

Do you want to loop through all of the controls on your form, checking each
textbox?

Dim ctlCurr As Control

For Each ctlCurr In Me.Controls
If TypeOf ctlCurr Is TextBox
QCBox = QCBox & ctlCurr.Name & ", "
End If
Next ctlCurr

If Len(QCBox) > 0 Then
' Remove the superfluous ", " from the end
QCBox = Left$(QCBox, Len(QCBox) - 2)
Me.UnboundTextBox = QBBox
End If

As to your other problem, I don't know why Access insists on naming the
control the same as the field to which it's bound because that causes
problems. I always go back and rename all of my controls so that, for
instance, the CustonerName field in the underlying recordset would be bound
to a control named txtCustomerName.