Commonly asked question.........

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Have 2 table fields [Part #:] and [Operation #:] that a user will input from
a form "Input Form" (have 2 text boxes). That all works fine & dandy. Now I
want to put code in "Input Form" so when a report is requested by hitting a
print button and there are no matches for part # & operation # a message box
informs the user. Trying to fit it in under the btn_print sub using such
attempts as:
if [queries]![main table query]![part #:] = "" then
msgbox "Sorry",
Keeps telling me it can't find field ' ' with some symbol in the ''. What
the......any ideas, are : and # things I shouldn't use in field names?
Thanks
 
First, it's not good practice to use spaces, #, !, ', @, ?, etc. characters
in table names, field names, and control names. At a minimum, you must
always surround the name with [ ] characters, and these characters can cause
unintended results in queries with regard to which records might be found
and returned.

Second, there is no queries collection, so the code that you are trying to
use will not be successful. Instead, use the DLookup function to "look up" a
value from a query or use the DCount function to see if there is any record
at all with that value:

If DLookup("Part #", "main table query") = "" Then
MsgBox "Sorry"
(etc.)


or (if Part # is a numeric field)

If DCount("*", "main table query", _
"[Part #]=" & Me.NameOfControlOnFormWithThePart#.Value) _
= 0 Then
MsgBox "Sorry"
(etc.)


or (if Part # is a text field)

If DCount("*", "main table query", _
"[Part #]='" & Me.NameOfControlOnFormWithThePart#.Value _
& "'")= 0 Then
MsgBox "Sorry"
(etc.)
 
What does Dlookup return, T/F , Null. Threw in DLookup inside my Print sub
to check if an 8 digit user inputted number (queried) was in the table to
print, if it's not I want to inform the user, I've got this:
Private Sub btnPrint_Click()

If DLookup("[part no]", "main table query") = Null Then
MsgBox "No Process Sheet Written", vbRetryCancel
Makes me think I've got to run my query first, or do I throw in a new
variable, assign the dlookup result and play with that?
MRL again

Ken Snell said:
First, it's not good practice to use spaces, #, !, ', @, ?, etc. characters
in table names, field names, and control names. At a minimum, you must
always surround the name with [ ] characters, and these characters can cause
unintended results in queries with regard to which records might be found
and returned.

Second, there is no queries collection, so the code that you are trying to
use will not be successful. Instead, use the DLookup function to "look up" a
value from a query or use the DCount function to see if there is any record
at all with that value:

If DLookup("Part #", "main table query") = "" Then
MsgBox "Sorry"
(etc.)


or (if Part # is a numeric field)

If DCount("*", "main table query", _
"[Part #]=" & Me.NameOfControlOnFormWithThePart#.Value) _
= 0 Then
MsgBox "Sorry"
(etc.)


or (if Part # is a text field)

If DCount("*", "main table query", _
"[Part #]='" & Me.NameOfControlOnFormWithThePart#.Value _
& "'")= 0 Then
MsgBox "Sorry"
(etc.)

--

Ken Snell
<MS ACCESS MVP>


MRL said:
Have 2 table fields [Part #:] and [Operation #:] that a user will input from
a form "Input Form" (have 2 text boxes). That all works fine & dandy. Now I
want to put code in "Input Form" so when a report is requested by hitting a
print button and there are no matches for part # & operation # a message box
informs the user. Trying to fit it in under the btn_print sub using such
attempts as:
if [queries]![main table query]![part #:] = "" then
msgbox "Sorry",
Keeps telling me it can't find field ' ' with some symbol in the ''. What
the......any ideas, are : and # things I shouldn't use in field names?
Thanks
Click to expand...
Click to expand...
 
DLookup returns a value if one exists, and Null otherwise. What type of
value it returns depends on the field being queried.

You cannot use = to check whether Null is returned. By definition, Null does
not equal Null.

Try

If IsNull(DLookup("[part no]", "main table query")) Then
MsgBox "No Process Sheet Written", vbRetryCancel

However, if you're trying to determine whether a specific value exists, you
need to provide a third parameter to DLookup (or DCount, for that matter),
specifying the value for which you're looking.

If [part no] is numeric, you'd need:

If IsNull(DLookup("[part no]", "main table query", "[part no] = " &
InputNumber)) Then

If it's text, you'd need:

If IsNull(DLookup("[part no]", "main table query", "[part no] = " &
Chr$(34) & InputNumber & Chr$(34))) Then

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


MRL said:
What does Dlookup return, T/F , Null. Threw in DLookup inside my Print sub
to check if an 8 digit user inputted number (queried) was in the table to
print, if it's not I want to inform the user, I've got this:
Private Sub btnPrint_Click()

If DLookup("[part no]", "main table query") = Null Then
MsgBox "No Process Sheet Written", vbRetryCancel
Makes me think I've got to run my query first, or do I throw in a new
variable, assign the dlookup result and play with that?
MRL again

Ken Snell said:
First, it's not good practice to use spaces, #, !, ', @, ?, etc. characters
in table names, field names, and control names. At a minimum, you must
always surround the name with [ ] characters, and these characters can cause
unintended results in queries with regard to which records might be found
and returned.

Second, there is no queries collection, so the code that you are trying to
use will not be successful. Instead, use the DLookup function to "look up" a
value from a query or use the DCount function to see if there is any record
at all with that value:

If DLookup("Part #", "main table query") = "" Then
MsgBox "Sorry"
(etc.)


or (if Part # is a numeric field)

If DCount("*", "main table query", _
"[Part #]=" & Me.NameOfControlOnFormWithThePart#.Value) _
= 0 Then
MsgBox "Sorry"
(etc.)


or (if Part # is a text field)

If DCount("*", "main table query", _
"[Part #]='" & Me.NameOfControlOnFormWithThePart#.Value _
& "'")= 0 Then
MsgBox "Sorry"
(etc.)

--

Ken Snell
<MS ACCESS MVP>


MRL said:
Have 2 table fields [Part #:] and [Operation #:] that a user will
Click to expand...
input
from
a form "Input Form" (have 2 text boxes). That all works fine & dandy. Now I
want to put code in "Input Form" so when a report is requested by
Click to expand...
hitting
a
print button and there are no matches for part # & operation # a
Click to expand...
message
box
informs the user. Trying to fit it in under the btn_print sub using such
attempts as:
if [queries]![main table query]![part #:] = "" then
msgbox "Sorry",
Keeps telling me it can't find field ' ' with some symbol in the ''. What
the......any ideas, are : and # things I shouldn't use in field names?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Ups, yes. DLookup will return Null (not "") if there is no value. So wrap
the DLookup with IsNull function:

If IsNull(DLookup("Part #", "main table query")) = True Then
MsgBox "Sorry"
(etc.)

--

Ken Snell
<MS ACCESS MVP>

MRL said:
What does Dlookup return, T/F , Null. Threw in DLookup inside my Print sub
to check if an 8 digit user inputted number (queried) was in the table to
print, if it's not I want to inform the user, I've got this:
Private Sub btnPrint_Click()

If DLookup("[part no]", "main table query") = Null Then
MsgBox "No Process Sheet Written", vbRetryCancel
Makes me think I've got to run my query first, or do I throw in a new
variable, assign the dlookup result and play with that?
MRL again

Ken Snell said:
First, it's not good practice to use spaces, #, !, ', @, ?, etc. characters
in table names, field names, and control names. At a minimum, you must
always surround the name with [ ] characters, and these characters can cause
unintended results in queries with regard to which records might be found
and returned.

Second, there is no queries collection, so the code that you are trying to
use will not be successful. Instead, use the DLookup function to "look up" a
value from a query or use the DCount function to see if there is any record
at all with that value:

If DLookup("Part #", "main table query") = "" Then
MsgBox "Sorry"
(etc.)


or (if Part # is a numeric field)

If DCount("*", "main table query", _
"[Part #]=" & Me.NameOfControlOnFormWithThePart#.Value) _
= 0 Then
MsgBox "Sorry"
(etc.)


or (if Part # is a text field)

If DCount("*", "main table query", _
"[Part #]='" & Me.NameOfControlOnFormWithThePart#.Value _
& "'")= 0 Then
MsgBox "Sorry"
(etc.)

--

Ken Snell
<MS ACCESS MVP>


MRL said:
Have 2 table fields [Part #:] and [Operation #:] that a user will
Click to expand...
input
from
a form "Input Form" (have 2 text boxes). That all works fine & dandy. Now I
want to put code in "Input Form" so when a report is requested by
Click to expand...
hitting
a
print button and there are no matches for part # & operation # a
Click to expand...
message
box
informs the user. Trying to fit it in under the btn_print sub using such
attempts as:
if [queries]![main table query]![part #:] = "" then
msgbox "Sorry",
Keeps telling me it can't find field ' ' with some symbol in the ''. What
the......any ideas, are : and # things I shouldn't use in field names?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
And as Doug has correctly noted,

If IsNull(DLookup("Part #", "main table query", "[Part #]='" & _
Me.NameOfControlOnFormWithThePart#.Value _
& "'")) = True Then
MsgBox "Sorry"
(etc.)

or (above for text, below for numeric)

If IsNull(DLookup("Part #", "main table query", "[Part #]=" & _
Me.NameOfControlOnFormWithThePart#.Value)) _
= True Then
MsgBox "Sorry"
(etc.)

My brain has really left town today..... :-(


--

Ken Snell
<MS ACCESS MVP>

_
"[Part #]='" & Me.NameOfControlOnFormWithThePart#.Value _
& "'")= 0 Then
MsgBox "Sorry"
(etc.)
Click to expand...
Click to expand...


Ken Snell said:
Ups, yes. DLookup will return Null (not "") if there is no value. So wrap
the DLookup with IsNull function:

If IsNull(DLookup("Part #", "main table query")) = True Then
MsgBox "Sorry"
(etc.)

--

Ken Snell
<MS ACCESS MVP>

MRL said:
What does Dlookup return, T/F , Null. Threw in DLookup inside my Print sub
to check if an 8 digit user inputted number (queried) was in the table to
print, if it's not I want to inform the user, I've got this:
Private Sub btnPrint_Click()

If DLookup("[part no]", "main table query") = Null Then
MsgBox "No Process Sheet Written", vbRetryCancel
Makes me think I've got to run my query first, or do I throw in a new
variable, assign the dlookup result and play with that?
MRL again

Ken Snell said:
First, it's not good practice to use spaces, #, !, ', @, ?, etc. characters
in table names, field names, and control names. At a minimum, you must
always surround the name with [ ] characters, and these characters can cause
unintended results in queries with regard to which records might be found
and returned.

Second, there is no queries collection, so the code that you are
Click to expand...
Click to expand...
trying
to
use will not be successful. Instead, use the DLookup function to "look up" a
value from a query or use the DCount function to see if there is any record
at all with that value:

If DLookup("Part #", "main table query") = "" Then
MsgBox "Sorry"
(etc.)


or (if Part # is a numeric field)

If DCount("*", "main table query", _
"[Part #]=" & Me.NameOfControlOnFormWithThePart#.Value) _
= 0 Then
MsgBox "Sorry"
(etc.)


or (if Part # is a text field)

If DCount("*", "main table query", _
"[Part #]='" & Me.NameOfControlOnFormWithThePart#.Value _
& "'")= 0 Then
MsgBox "Sorry"
(etc.)

--

Ken Snell
<MS ACCESS MVP>


Have 2 table fields [Part #:] and [Operation #:] that a user will input
from
a form "Input Form" (have 2 text boxes). That all works fine & dandy.
Now I
want to put code in "Input Form" so when a report is requested by hitting
a
print button and there are no matches for part # & operation # a message
box
informs the user. Trying to fit it in under the btn_print sub using such
attempts as:
if [queries]![main table query]![part #:] = "" then
msgbox "Sorry",
Keeps telling me it can't find field ' ' with some symbol in the ''. What
the......any ideas, are : and # things I shouldn't use in field names?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
I Got It!!!!!!!!!! No, we got it! Thanks

Douglas J. Steele said:
DLookup returns a value if one exists, and Null otherwise. What type of
value it returns depends on the field being queried.

You cannot use = to check whether Null is returned. By definition, Null does
not equal Null.

Try

If IsNull(DLookup("[part no]", "main table query")) Then
MsgBox "No Process Sheet Written", vbRetryCancel

However, if you're trying to determine whether a specific value exists, you
need to provide a third parameter to DLookup (or DCount, for that matter),
specifying the value for which you're looking.

If [part no] is numeric, you'd need:

If IsNull(DLookup("[part no]", "main table query", "[part no] = " &
InputNumber)) Then

If it's text, you'd need:

If IsNull(DLookup("[part no]", "main table query", "[part no] = " &
Chr$(34) & InputNumber & Chr$(34))) Then

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


MRL said:
What does Dlookup return, T/F , Null. Threw in DLookup inside my Print sub
to check if an 8 digit user inputted number (queried) was in the table to
print, if it's not I want to inform the user, I've got this:
Private Sub btnPrint_Click()

If DLookup("[part no]", "main table query") = Null Then
MsgBox "No Process Sheet Written", vbRetryCancel
Makes me think I've got to run my query first, or do I throw in a new
variable, assign the dlookup result and play with that?
MRL again

Ken Snell said:
First, it's not good practice to use spaces, #, !, ', @, ?, etc. characters
in table names, field names, and control names. At a minimum, you must
always surround the name with [ ] characters, and these characters can cause
unintended results in queries with regard to which records might be found
and returned.

Second, there is no queries collection, so the code that you are trying to
use will not be successful. Instead, use the DLookup function to "look up" a
value from a query or use the DCount function to see if there is any record
at all with that value:

If DLookup("Part #", "main table query") = "" Then
MsgBox "Sorry"
(etc.)


or (if Part # is a numeric field)

If DCount("*", "main table query", _
"[Part #]=" & Me.NameOfControlOnFormWithThePart#.Value) _
= 0 Then
MsgBox "Sorry"
(etc.)


or (if Part # is a text field)

If DCount("*", "main table query", _
"[Part #]='" & Me.NameOfControlOnFormWithThePart#.Value _
& "'")= 0 Then
MsgBox "Sorry"
(etc.)

--

Ken Snell
<MS ACCESS MVP>


Have 2 table fields [Part #:] and [Operation #:] that a user will input
from
a form "Input Form" (have 2 text boxes). That all works fine & dandy.
Now I
want to put code in "Input Form" so when a report is requested by hitting
a
print button and there are no matches for part # & operation # a message
box
informs the user. Trying to fit it in under the btn_print sub using such
attempts as:
if [queries]![main table query]![part #:] = "" then
msgbox "Sorry",
Keeps telling me it can't find field ' ' with some symbol in the ''. What
the......any ideas, are : and # things I shouldn't use in field names?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
We got it my friend, thanks!!!!!!!!!!!!!!

Ken Snell said:
Ups, yes. DLookup will return Null (not "") if there is no value. So wrap
the DLookup with IsNull function:

If IsNull(DLookup("Part #", "main table query")) = True Then
MsgBox "Sorry"
(etc.)

--

Ken Snell
<MS ACCESS MVP>

MRL said:
What does Dlookup return, T/F , Null. Threw in DLookup inside my Print sub
to check if an 8 digit user inputted number (queried) was in the table to
print, if it's not I want to inform the user, I've got this:
Private Sub btnPrint_Click()

If DLookup("[part no]", "main table query") = Null Then
MsgBox "No Process Sheet Written", vbRetryCancel
Makes me think I've got to run my query first, or do I throw in a new
variable, assign the dlookup result and play with that?
MRL again

Ken Snell said:
First, it's not good practice to use spaces, #, !, ', @, ?, etc. characters
in table names, field names, and control names. At a minimum, you must
always surround the name with [ ] characters, and these characters can cause
unintended results in queries with regard to which records might be found
and returned.

Second, there is no queries collection, so the code that you are trying to
use will not be successful. Instead, use the DLookup function to "look up" a
value from a query or use the DCount function to see if there is any record
at all with that value:

If DLookup("Part #", "main table query") = "" Then
MsgBox "Sorry"
(etc.)


or (if Part # is a numeric field)

If DCount("*", "main table query", _
"[Part #]=" & Me.NameOfControlOnFormWithThePart#.Value) _
= 0 Then
MsgBox "Sorry"
(etc.)


or (if Part # is a text field)

If DCount("*", "main table query", _
"[Part #]='" & Me.NameOfControlOnFormWithThePart#.Value _
& "'")= 0 Then
MsgBox "Sorry"
(etc.)

--

Ken Snell
<MS ACCESS MVP>


Have 2 table fields [Part #:] and [Operation #:] that a user will input
from
a form "Input Form" (have 2 text boxes). That all works fine & dandy.
Now I
want to put code in "Input Form" so when a report is requested by hitting
a
print button and there are no matches for part # & operation # a message
box
informs the user. Trying to fit it in under the btn_print sub using such
attempts as:
if [queries]![main table query]![part #:] = "" then
msgbox "Sorry",
Keeps telling me it can't find field ' ' with some symbol in the ''. What
the......any ideas, are : and # things I shouldn't use in field names?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

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

Updating tables when you input new data 0
Assign a Table field a value from a form 1
Launching a Form from Within a Form to Create a New Record 1
Run Form thru Query back to Form 2
Problem with Combo box on form 1
Help buidling data input form, please? 6
combining two or more table fields into a single table field 1
Filter dropdown list based on previous field selected? 1

Back
Top