Check for Null in Table

A

accesshacker

Hello,
I have a form with a subform that is in continous view that allows the user
to add/change records in a table. Before moving the records from one table to
another, I want to check for Null in specific fields that are tied to 3
fields displayed on the Main form. No luck so far! Thanks in advance!
 
A

Arvin Meyer [MVP]

On a button click event, or even the form's BeforeUpdate event use the
expression:

If Len(Me.ControlName & vbNullString) = 0 Then
MsgBox "No Nulls Allowed"
Me.ControlName.SetFocus
End If

add a similar expression for each control that you want to check. If you use
the Before Update event, you'll also need to cancel it or it will just go on
after the message box.
 
A

accesshacker

Hi Arvin,

Thanks for the response, though unfortunately, the code isn't recognizing a
Null value. I am checking for Null in a subform that is a continous form. I
changed the code as follows:

If Len(Me.OENTRY2_SUBA![Descrip 1] & vbNullString) = 0 Then
MsgBox "No Nulls Allowed"
Me.OENTRY2_SUBA![Descrip 1].SetFocus
End If

I purposely left a record in the 'Descrip 1' field in the table that is the
source for the subform. Any other suggestions? Thanks.
 
D

Douglas J. Steele

Where did yout put that code? It looks as though you're trying to do it on
the parent form, not the subform. It needs to go in the form being used as a
subform.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


accesshacker said:
Hi Arvin,

Thanks for the response, though unfortunately, the code isn't recognizing
a
Null value. I am checking for Null in a subform that is a continous form.
I
changed the code as follows:

If Len(Me.OENTRY2_SUBA![Descrip 1] & vbNullString) = 0 Then
MsgBox "No Nulls Allowed"
Me.OENTRY2_SUBA![Descrip 1].SetFocus
End If

I purposely left a record in the 'Descrip 1' field in the table that is
the
source for the subform. Any other suggestions? Thanks.
Arvin Meyer said:
On a button click event, or even the form's BeforeUpdate event use the
expression:

If Len(Me.ControlName & vbNullString) = 0 Then
MsgBox "No Nulls Allowed"
Me.ControlName.SetFocus
End If

add a similar expression for each control that you want to check. If you
use
the Before Update event, you'll also need to cancel it or it will just go
on
after the message box.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Click to expand...
Click to expand...
 
A

accesshacker

I have the following code in a button on the parent form.
If Len(Forms!POC_IP_A![OENTRY2_SUBA]![Descrip1] & vbNullString) = 0 Then
MsgBox "No Nulls Allowed"

ElseIf MsgBox("This will mark this order as 'Complete'. Additional Items can
not be added at this time. Do you wish to perfrom this action?", vbYesNo,
"Warning") = vbNo Then
Cancel = True
ElseIf vbYes Then
DoCmd.SetWarnings False
x = XQuery("QryU:COMP_ORDER_POC_IP")
x = XQuery("QryD:TO_CO_POC_IP")
DoCmd.SetWarnings True
DoCmd.Close acForm, "POC_IP_A", acSaveNo
Forms!IP_SEARCH.Requery
End If

If I click on the control that is null when displayed, the code works. This
is a continous form, so the control I am checking for NULL may not have the
focus.

Thanks
Douglas J. Steele said:
Where did yout put that code? It looks as though you're trying to do it on
the parent form, not the subform. It needs to go in the form being used as a
subform.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


accesshacker said:
Hi Arvin,

Thanks for the response, though unfortunately, the code isn't recognizing
a
Null value. I am checking for Null in a subform that is a continous form.
I
changed the code as follows:

If Len(Me.OENTRY2_SUBA![Descrip 1] & vbNullString) = 0 Then
MsgBox "No Nulls Allowed"
Me.OENTRY2_SUBA![Descrip 1].SetFocus
End If

I purposely left a record in the 'Descrip 1' field in the table that is
the
source for the subform. Any other suggestions? Thanks.
Arvin Meyer said:
On a button click event, or even the form's BeforeUpdate event use the
expression:

If Len(Me.ControlName & vbNullString) = 0 Then
MsgBox "No Nulls Allowed"
Me.ControlName.SetFocus
End If

add a similar expression for each control that you want to check. If you
use
the Before Update event, you'll also need to cancel it or it will just go
on
after the message box.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Hello,
I have a form with a subform that is in continous view that allows the
user
to add/change records in a table. Before moving the records from one
table
to
another, I want to check for Null in specific fields that are tied to 3
fields displayed on the Main form. No luck so far! Thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Any code in the parent form is only going to work with the current row in
any subform (unless you put in looping code into the parent form). It looks
as though you're using queries to do the copies. Why not create a query that
checks where there are any "bad" rows in the originating data?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


accesshacker said:
I have the following code in a button on the parent form.
If Len(Forms!POC_IP_A![OENTRY2_SUBA]![Descrip1] & vbNullString) = 0 Then
MsgBox "No Nulls Allowed"

ElseIf MsgBox("This will mark this order as 'Complete'. Additional Items
can
not be added at this time. Do you wish to perfrom this action?", vbYesNo,
"Warning") = vbNo Then
Cancel = True
ElseIf vbYes Then
DoCmd.SetWarnings False
x = XQuery("QryU:COMP_ORDER_POC_IP")
x = XQuery("QryD:TO_CO_POC_IP")
DoCmd.SetWarnings True
DoCmd.Close acForm, "POC_IP_A", acSaveNo
Forms!IP_SEARCH.Requery
End If

If I click on the control that is null when displayed, the code works.
This
is a continous form, so the control I am checking for NULL may not have
the
focus.

Thanks
Douglas J. Steele said:
Where did yout put that code? It looks as though you're trying to do it
on
the parent form, not the subform. It needs to go in the form being used
as a
subform.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


accesshacker said:
Hi Arvin,

Thanks for the response, though unfortunately, the code isn't
recognizing
a
Null value. I am checking for Null in a subform that is a continous
form.
I
changed the code as follows:

If Len(Me.OENTRY2_SUBA![Descrip 1] & vbNullString) = 0 Then
MsgBox "No Nulls Allowed"
Me.OENTRY2_SUBA![Descrip 1].SetFocus
End If

I purposely left a record in the 'Descrip 1' field in the table that is
the
source for the subform. Any other suggestions? Thanks.
:

On a button click event, or even the form's BeforeUpdate event use the
expression:

If Len(Me.ControlName & vbNullString) = 0 Then
MsgBox "No Nulls Allowed"
Me.ControlName.SetFocus
End If

add a similar expression for each control that you want to check. If
you
use
the Before Update event, you'll also need to cancel it or it will just
go
on
after the message box.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


message
Hello,
I have a form with a subform that is in continous view that allows
the
user
to add/change records in a table. Before moving the records from one
table
to
another, I want to check for Null in specific fields that are tied
to 3
fields displayed on the Main form. No luck so far! Thanks in
advance!
Click to expand...
Click to expand...
Click to expand...
 
A

accesshacker

Thanks Doug, thought that could be the issue and was hoping there was a way
to move through the subform. Haven't used looping code, so not sure how to
make it look. On the other suggestion, I can create the query, how do I check
for values from the code I wrote? Thanks again.

Douglas J. Steele said:
Any code in the parent form is only going to work with the current row in
any subform (unless you put in looping code into the parent form). It looks
as though you're using queries to do the copies. Why not create a query that
checks where there are any "bad" rows in the originating data?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


accesshacker said:
I have the following code in a button on the parent form.
If Len(Forms!POC_IP_A![OENTRY2_SUBA]![Descrip1] & vbNullString) = 0 Then
MsgBox "No Nulls Allowed"

ElseIf MsgBox("This will mark this order as 'Complete'. Additional Items
can
not be added at this time. Do you wish to perfrom this action?", vbYesNo,
"Warning") = vbNo Then
Cancel = True
ElseIf vbYes Then
DoCmd.SetWarnings False
x = XQuery("QryU:COMP_ORDER_POC_IP")
x = XQuery("QryD:TO_CO_POC_IP")
DoCmd.SetWarnings True
DoCmd.Close acForm, "POC_IP_A", acSaveNo
Forms!IP_SEARCH.Requery
End If

If I click on the control that is null when displayed, the code works.
This
is a continous form, so the control I am checking for NULL may not have
the
focus.

Thanks
Douglas J. Steele said:
Where did yout put that code? It looks as though you're trying to do it
on
the parent form, not the subform. It needs to go in the form being used
as a
subform.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Arvin,

Thanks for the response, though unfortunately, the code isn't
recognizing
a
Null value. I am checking for Null in a subform that is a continous
form.
I
changed the code as follows:

If Len(Me.OENTRY2_SUBA![Descrip 1] & vbNullString) = 0 Then
MsgBox "No Nulls Allowed"
Me.OENTRY2_SUBA![Descrip 1].SetFocus
End If

I purposely left a record in the 'Descrip 1' field in the table that is
the
source for the subform. Any other suggestions? Thanks.
:

On a button click event, or even the form's BeforeUpdate event use the
expression:

If Len(Me.ControlName & vbNullString) = 0 Then
MsgBox "No Nulls Allowed"
Me.ControlName.SetFocus
End If

add a similar expression for each control that you want to check. If
you
use
the Before Update event, you'll also need to cancel it or it will just
go
on
after the message box.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


message
Hello,
I have a form with a subform that is in continous view that allows
the
user
to add/change records in a table. Before moving the records from one
table
to
another, I want to check for Null in specific fields that are tied
to 3
fields displayed on the Main form. No luck so far! Thanks in
advance!
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Another thought just occurred to me. You can use DCount to see whether there
are any rows that have Null:

If DCount("*", "NameOfTable", "Descrip1 IS NULL") > 0 Then
MsgBox "One or more rows have Null in them. That's not allowed!"
Else
....

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


accesshacker said:
Thanks Doug, thought that could be the issue and was hoping there was a
way
to move through the subform. Haven't used looping code, so not sure how to
make it look. On the other suggestion, I can create the query, how do I
check
for values from the code I wrote? Thanks again.

Douglas J. Steele said:
Any code in the parent form is only going to work with the current row in
any subform (unless you put in looping code into the parent form). It
looks
as though you're using queries to do the copies. Why not create a query
that
checks where there are any "bad" rows in the originating data?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


accesshacker said:
I have the following code in a button on the parent form.
If Len(Forms!POC_IP_A![OENTRY2_SUBA]![Descrip1] & vbNullString) = 0
Then
MsgBox "No Nulls Allowed"

ElseIf MsgBox("This will mark this order as 'Complete'. Additional
Items
can
not be added at this time. Do you wish to perfrom this action?",
vbYesNo,
"Warning") = vbNo Then
Cancel = True
ElseIf vbYes Then
DoCmd.SetWarnings False
x = XQuery("QryU:COMP_ORDER_POC_IP")
x = XQuery("QryD:TO_CO_POC_IP")
DoCmd.SetWarnings True
DoCmd.Close acForm, "POC_IP_A", acSaveNo
Forms!IP_SEARCH.Requery
End If

If I click on the control that is null when displayed, the code works.
This
is a continous form, so the control I am checking for NULL may not have
the
focus.

Thanks
:

Where did yout put that code? It looks as though you're trying to do
it
on
the parent form, not the subform. It needs to go in the form being
used
as a
subform.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Hi Arvin,

Thanks for the response, though unfortunately, the code isn't
recognizing
a
Null value. I am checking for Null in a subform that is a continous
form.
I
changed the code as follows:

If Len(Me.OENTRY2_SUBA![Descrip 1] & vbNullString) = 0 Then
MsgBox "No Nulls Allowed"
Me.OENTRY2_SUBA![Descrip 1].SetFocus
End If

I purposely left a record in the 'Descrip 1' field in the table that
is
the
source for the subform. Any other suggestions? Thanks.
:

On a button click event, or even the form's BeforeUpdate event use
the
expression:

If Len(Me.ControlName & vbNullString) = 0 Then
MsgBox "No Nulls Allowed"
Me.ControlName.SetFocus
End If

add a similar expression for each control that you want to check.
If
you
use
the Before Update event, you'll also need to cancel it or it will
just
go
on
after the message box.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


message
Hello,
I have a form with a subform that is in continous view that
allows
the
user
to add/change records in a table. Before moving the records from
one
table
to
another, I want to check for Null in specific fields that are
tied
to 3
fields displayed on the Main form. No luck so far! Thanks in
advance!
Click to expand...
Click to expand...
Click to expand...
 
A

accesshacker

Doug,

Fantastict, the code works. I took it one step further and incorporated [art
of your previous answer by creating a query based on the criteria from the
form so that I am only checking those items associated with the TempID.
Thanks for the code, it is greatly appreciated!!

Douglas J. Steele said:
Another thought just occurred to me. You can use DCount to see whether there
are any rows that have Null:

If DCount("*", "NameOfTable", "Descrip1 IS NULL") > 0 Then
MsgBox "One or more rows have Null in them. That's not allowed!"
Else
....

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


accesshacker said:
Thanks Doug, thought that could be the issue and was hoping there was a
way
to move through the subform. Haven't used looping code, so not sure how to
make it look. On the other suggestion, I can create the query, how do I
check
for values from the code I wrote? Thanks again.

Douglas J. Steele said:
Any code in the parent form is only going to work with the current row in
any subform (unless you put in looping code into the parent form). It
looks
as though you're using queries to do the copies. Why not create a query
that
checks where there are any "bad" rows in the originating data?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have the following code in a button on the parent form.
If Len(Forms!POC_IP_A![OENTRY2_SUBA]![Descrip1] & vbNullString) = 0
Then
MsgBox "No Nulls Allowed"

ElseIf MsgBox("This will mark this order as 'Complete'. Additional
Items
can
not be added at this time. Do you wish to perfrom this action?",
vbYesNo,
"Warning") = vbNo Then
Cancel = True
ElseIf vbYes Then
DoCmd.SetWarnings False
x = XQuery("QryU:COMP_ORDER_POC_IP")
x = XQuery("QryD:TO_CO_POC_IP")
DoCmd.SetWarnings True
DoCmd.Close acForm, "POC_IP_A", acSaveNo
Forms!IP_SEARCH.Requery
End If

If I click on the control that is null when displayed, the code works.
This
is a continous form, so the control I am checking for NULL may not have
the
focus.

Thanks
:

Where did yout put that code? It looks as though you're trying to do
it
on
the parent form, not the subform. It needs to go in the form being
used
as a
subform.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Hi Arvin,

Thanks for the response, though unfortunately, the code isn't
recognizing
a
Null value. I am checking for Null in a subform that is a continous
form.
I
changed the code as follows:

If Len(Me.OENTRY2_SUBA![Descrip 1] & vbNullString) = 0 Then
MsgBox "No Nulls Allowed"
Me.OENTRY2_SUBA![Descrip 1].SetFocus
End If

I purposely left a record in the 'Descrip 1' field in the table that
is
the
source for the subform. Any other suggestions? Thanks.
:

On a button click event, or even the form's BeforeUpdate event use
the
expression:

If Len(Me.ControlName & vbNullString) = 0 Then
MsgBox "No Nulls Allowed"
Me.ControlName.SetFocus
End If

add a similar expression for each control that you want to check.
If
you
use
the Before Update event, you'll also need to cancel it or it will
just
go
on
after the message box.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


message
Hello,
I have a form with a subform that is in continous view that
allows
the
user
to add/change records in a table. Before moving the records from
one
table
to
another, I want to check for Null in specific fields that are
tied
to 3
fields displayed on the Main form. No luck so far! Thanks in
advance!
Click to expand...
Click to expand...
Click to expand...
 
A

accesshacker

That should be 'part' not '[art]'.

Douglas J. Steele said:
Another thought just occurred to me. You can use DCount to see whether there
are any rows that have Null:

If DCount("*", "NameOfTable", "Descrip1 IS NULL") > 0 Then
MsgBox "One or more rows have Null in them. That's not allowed!"
Else
....

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


accesshacker said:
Thanks Doug, thought that could be the issue and was hoping there was a
way
to move through the subform. Haven't used looping code, so not sure how to
make it look. On the other suggestion, I can create the query, how do I
check
for values from the code I wrote? Thanks again.

Douglas J. Steele said:
Any code in the parent form is only going to work with the current row in
any subform (unless you put in looping code into the parent form). It
looks
as though you're using queries to do the copies. Why not create a query
that
checks where there are any "bad" rows in the originating data?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have the following code in a button on the parent form.
If Len(Forms!POC_IP_A![OENTRY2_SUBA]![Descrip1] & vbNullString) = 0
Then
MsgBox "No Nulls Allowed"

ElseIf MsgBox("This will mark this order as 'Complete'. Additional
Items
can
not be added at this time. Do you wish to perfrom this action?",
vbYesNo,
"Warning") = vbNo Then
Cancel = True
ElseIf vbYes Then
DoCmd.SetWarnings False
x = XQuery("QryU:COMP_ORDER_POC_IP")
x = XQuery("QryD:TO_CO_POC_IP")
DoCmd.SetWarnings True
DoCmd.Close acForm, "POC_IP_A", acSaveNo
Forms!IP_SEARCH.Requery
End If

If I click on the control that is null when displayed, the code works.
This
is a continous form, so the control I am checking for NULL may not have
the
focus.

Thanks
:

Where did yout put that code? It looks as though you're trying to do
it
on
the parent form, not the subform. It needs to go in the form being
used
as a
subform.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Hi Arvin,

Thanks for the response, though unfortunately, the code isn't
recognizing
a
Null value. I am checking for Null in a subform that is a continous
form.
I
changed the code as follows:

If Len(Me.OENTRY2_SUBA![Descrip 1] & vbNullString) = 0 Then
MsgBox "No Nulls Allowed"
Me.OENTRY2_SUBA![Descrip 1].SetFocus
End If

I purposely left a record in the 'Descrip 1' field in the table that
is
the
source for the subform. Any other suggestions? Thanks.
:

On a button click event, or even the form's BeforeUpdate event use
the
expression:

If Len(Me.ControlName & vbNullString) = 0 Then
MsgBox "No Nulls Allowed"
Me.ControlName.SetFocus
End If

add a similar expression for each control that you want to check.
If
you
use
the Before Update event, you'll also need to cancel it or it will
just
go
on
after the message box.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


message
Hello,
I have a form with a subform that is in continous view that
allows
the
user
to add/change records in a table. Before moving the records from
one
table
to
another, I want to check for Null in specific fields that are
tied
to 3
fields displayed on the Main form. No luck so far! Thanks in
advance!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Append only Null records in table 2
Subform with no records 8
Form filter combo box choices are Is Null/Is Not Null 3
Separate Form 0
Need all records on continuous form to meet a criteria 2
Show null values in Query 0
form null value fields 1
Filter a list containing null values 3

Top