Runtime Error 2465

[iamrdbrown]

I am trying to use a query (q_OpSupSecurity) with a 'IsNull' to limit input
to a textbox. The query is based on a table where Operators =1 and
Supervisors = 2. The query is acting as a filter to remove values that
shouldn't be allowed into this textbox.

I get a "Microsoft Office Access can't find the field '|' referred to in
your expression" error when I try to run the code. How else can I accomplish
what I am trying to do with this code?

Private Sub txtUserID1_AfterUpdate()
If IsNull([q_OpSupSecurity]) Then
MsgBox "Invalid Operator ID. Please try it again.", vbExclamation,
"Operator ID Entry"""
txtUserID1 = 0
txtUserID1.SetFocus
End If
End Sub

Thanks in advance,
R Brown

 

[John Spencer]

You cannot use a query that way.

Perhaps what you want is to use the DCount function to see if the id already
exists in a table or a query.

IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1) = 0 THEN
MsgBox "Invalid Operator ID. Please try it again.", _
vbExclamation, "Operator ID Entry"
txtUserID1 = 0
txtUserID1.SetFocus
End If

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[iamrdbrown]

Actually, I am trying to confirm that the UserID is in the table with the
correct clearance (<3) before allowing the data being input into the textbox
to be saved. I don't want just ID number to work - just the ones with the
appropriate clearance.

TIA,
R Brown

You cannot use a query that way.

Perhaps what you want is to use the DCount function to see if the id already
exists in a table or a query.

IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1) = 0 THEN
MsgBox "Invalid Operator ID. Please try it again.", _
vbExclamation, "Operator ID Entry"
txtUserID1 = 0
txtUserID1.SetFocus
End If

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I am trying to use a query (q_OpSupSecurity) with a 'IsNull' to limit input
to a textbox. The query is based on a table where Operators =1 and
Supervisors = 2. The query is acting as a filter to remove values that
shouldn't be allowed into this textbox.

I get a "Microsoft Office Access can't find the field '|' referred to in
your expression" error when I try to run the code. How else can I accomplish
what I am trying to do with this code?

Private Sub txtUserID1_AfterUpdate()
If IsNull([q_OpSupSecurity]) Then
MsgBox "Invalid Operator ID. Please try it again.", vbExclamation,
"Operator ID Entry"""
txtUserID1 = 0
txtUserID1.SetFocus
End If
End Sub

Thanks in advance,
R Brown

 

[John Spencer]

So you can expand the criteria to something like the following assuming
that you have a field named Clearance and it is a number field.

IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1 & " AND Clearance
<3") = 0 THEN

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Actually, I am trying to confirm that the UserID is in the table with the
correct clearance (<3) before allowing the data being input into the textbox
to be saved. I don't want just ID number to work - just the ones with the
appropriate clearance.

TIA,
R Brown

You cannot use a query that way.

Perhaps what you want is to use the DCount function to see if the id already
exists in a table or a query.

IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1) = 0 THEN
MsgBox "Invalid Operator ID. Please try it again.", _
vbExclamation, "Operator ID Entry"
txtUserID1 = 0
txtUserID1.SetFocus
End If

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I am trying to use a query (q_OpSupSecurity) with a 'IsNull' to limit input
to a textbox. The query is based on a table where Operators =1 and
Supervisors = 2. The query is acting as a filter to remove values that
shouldn't be allowed into this textbox.

I get a "Microsoft Office Access can't find the field '|' referred to in
your expression" error when I try to run the code. How else can I accomplish
what I am trying to do with this code?

Private Sub txtUserID1_AfterUpdate()
If IsNull([q_OpSupSecurity]) Then
MsgBox "Invalid Operator ID. Please try it again.", vbExclamation,
"Operator ID Entry"""
txtUserID1 = 0
txtUserID1.SetFocus
End If
End Sub

Thanks in advance,
R Brown

 

[iamrdbrown]

John,
I apologize for being dense, but when I plug this in I get a 'Expected: list
separator or )' error. The code I plugged in is as follows:
IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1 & " AND
"SecLevelOp<3") = 0 THEN

I am still very new to VB/VBA and have fits trying to debug... I have
learned a LOT from you guys here, but still have a ways to go before I even
consider myself 'literate' with this programming thing.

R Brown

So you can expand the criteria to something like the following assuming
that you have a field named Clearance and it is a number field.

IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1 & " AND Clearance
<3") = 0 THEN

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Actually, I am trying to confirm that the UserID is in the table with the
correct clearance (<3) before allowing the data being input into the textbox
to be saved. I don't want just ID number to work - just the ones with the
appropriate clearance.

TIA,
R Brown

You cannot use a query that way.

Perhaps what you want is to use the DCount function to see if the id already
exists in a table or a query.

IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1) = 0 THEN
MsgBox "Invalid Operator ID. Please try it again.", _
vbExclamation, "Operator ID Entry"
txtUserID1 = 0
txtUserID1.SetFocus
End If

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

iamrdbrown wrote:
I am trying to use a query (q_OpSupSecurity) with a 'IsNull' to limit input
to a textbox. The query is based on a table where Operators =1 and
Supervisors = 2. The query is acting as a filter to remove values that
shouldn't be allowed into this textbox.

I get a "Microsoft Office Access can't find the field '|' referred to in
your expression" error when I try to run the code. How else can I accomplish
what I am trying to do with this code?

Private Sub txtUserID1_AfterUpdate()
If IsNull([q_OpSupSecurity]) Then
MsgBox "Invalid Operator ID. Please try it again.", vbExclamation,
"Operator ID Entry"""
txtUserID1 = 0
txtUserID1.SetFocus
End If
End Sub

Thanks in advance,
R Brown

 

[Douglas J. Steele]

What are your Regional Settings? Might you have them set such that you need
to use a semicolon rather than a comma as a list separator?

See whether this works:

IF Dcount("*";"q_OpSupSecurity";"UserID=" & txtUserId1 & _
" AND "SecLevelOp<3") = 0 THEN


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

John,
I apologize for being dense, but when I plug this in I get a 'Expected:
list
separator or )' error. The code I plugged in is as follows:
IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1 & " AND
"SecLevelOp<3") = 0 THEN

I am still very new to VB/VBA and have fits trying to debug... I have
learned a LOT from you guys here, but still have a ways to go before I
even
consider myself 'literate' with this programming thing.

R Brown

So you can expand the criteria to something like the following assuming
that you have a field named Clearance and it is a number field.

IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1 & " AND Clearance
<3") = 0 THEN

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Actually, I am trying to confirm that the UserID is in the table with
the
correct clearance (<3) before allowing the data being input into the
textbox
to be saved. I don't want just ID number to work - just the ones with
the
appropriate clearance.

TIA,
R Brown

:

You cannot use a query that way.

Perhaps what you want is to use the DCount function to see if the id
already
exists in a table or a query.

IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1) = 0 THEN
MsgBox "Invalid Operator ID. Please try it again.", _
vbExclamation, "Operator ID Entry"
txtUserID1 = 0
txtUserID1.SetFocus
End If

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

iamrdbrown wrote:
I am trying to use a query (q_OpSupSecurity) with a 'IsNull' to limit
input
to a textbox. The query is based on a table where Operators =1 and
Supervisors = 2. The query is acting as a filter to remove values
that
shouldn't be allowed into this textbox.

I get a "Microsoft Office Access can't find the field '|' referred to
in
your expression" error when I try to run the code. How else can I
accomplish
what I am trying to do with this code?

Private Sub txtUserID1_AfterUpdate()
If IsNull([q_OpSupSecurity]) Then
MsgBox "Invalid Operator ID. Please try it again.",
vbExclamation,
"Operator ID Entry"""
txtUserID1 = 0
txtUserID1.SetFocus
End If
End Sub

Thanks in advance,
R Brown

 

[iamrdbrown]

The expression(s) looks for the comma, not a semi-colon. It changed what
gets high-lighted as the error point when I changed the comma to a
semi-colon. With the original code, I get the error at the end like there is
something missing - a list separator or such.

My boss keeps talking about sending me to school for VB/VBA... I sure do
wish he would... (smile). It would save you guys a bunch of headaches.

R Brown

What are your Regional Settings? Might you have them set such that you need
to use a semicolon rather than a comma as a list separator?

See whether this works:

IF Dcount("*";"q_OpSupSecurity";"UserID=" & txtUserId1 & _
" AND "SecLevelOp<3") = 0 THEN


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

John,
I apologize for being dense, but when I plug this in I get a 'Expected:
list
separator or )' error. The code I plugged in is as follows:
IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1 & " AND
"SecLevelOp<3") = 0 THEN

I am still very new to VB/VBA and have fits trying to debug... I have
learned a LOT from you guys here, but still have a ways to go before I
even
consider myself 'literate' with this programming thing.

R Brown

So you can expand the criteria to something like the following assuming
that you have a field named Clearance and it is a number field.

IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1 & " AND Clearance
<3") = 0 THEN

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


iamrdbrown wrote:
Actually, I am trying to confirm that the UserID is in the table with
the
correct clearance (<3) before allowing the data being input into the
textbox
to be saved. I don't want just ID number to work - just the ones with
the
appropriate clearance.

TIA,
R Brown

:

You cannot use a query that way.

Perhaps what you want is to use the DCount function to see if the id
already
exists in a table or a query.

IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1) = 0 THEN
MsgBox "Invalid Operator ID. Please try it again.", _
vbExclamation, "Operator ID Entry"
txtUserID1 = 0
txtUserID1.SetFocus
End If

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

iamrdbrown wrote:
I am trying to use a query (q_OpSupSecurity) with a 'IsNull' to limit
input
to a textbox. The query is based on a table where Operators =1 and
Supervisors = 2. The query is acting as a filter to remove values
that
shouldn't be allowed into this textbox.

I get a "Microsoft Office Access can't find the field '|' referred to
in
your expression" error when I try to run the code. How else can I
accomplish
what I am trying to do with this code?

Private Sub txtUserID1_AfterUpdate()
If IsNull([q_OpSupSecurity]) Then
MsgBox "Invalid Operator ID. Please try it again.",
vbExclamation,
"Operator ID Entry"""
txtUserID1 = 0
txtUserID1.SetFocus
End If
End Sub

Thanks in advance,
R Brown

 

[Douglas J. Steele]

Looking closer, you've got an extra double quote in there after the AND
(which I happened to propagate). Try:

IF Dcount("*", "q_OpSupSecurity", "UserID=" & txtUserId1 & _
" AND SecLevelOp<3") = 0 THEN


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The expression(s) looks for the comma, not a semi-colon. It changed what
gets high-lighted as the error point when I changed the comma to a
semi-colon. With the original code, I get the error at the end like there
is
something missing - a list separator or such.

My boss keeps talking about sending me to school for VB/VBA... I sure do
wish he would... (smile). It would save you guys a bunch of headaches.

R Brown

What are your Regional Settings? Might you have them set such that you
need
to use a semicolon rather than a comma as a list separator?

See whether this works:

IF Dcount("*";"q_OpSupSecurity";"UserID=" & txtUserId1 & _
" AND "SecLevelOp<3") = 0 THEN


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

John,
I apologize for being dense, but when I plug this in I get a 'Expected:
list
separator or )' error. The code I plugged in is as follows:
IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1 & " AND
"SecLevelOp<3") = 0 THEN

I am still very new to VB/VBA and have fits trying to debug... I have
learned a LOT from you guys here, but still have a ways to go before I
even
consider myself 'literate' with this programming thing.

R Brown

:

So you can expand the criteria to something like the following
assuming
that you have a field named Clearance and it is a number field.

IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1 & " AND
Clearance
<3") = 0 THEN

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


iamrdbrown wrote:
Actually, I am trying to confirm that the UserID is in the table
with
the
correct clearance (<3) before allowing the data being input into the
textbox
to be saved. I don't want just ID number to work - just the ones
with
the
appropriate clearance.

TIA,
R Brown

:

You cannot use a query that way.

Perhaps what you want is to use the DCount function to see if the
id
already
exists in a table or a query.

IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1) = 0 THEN
MsgBox "Invalid Operator ID. Please try it again.", _
vbExclamation, "Operator ID Entry"
txtUserID1 = 0
txtUserID1.SetFocus
End If

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

iamrdbrown wrote:
I am trying to use a query (q_OpSupSecurity) with a 'IsNull' to
limit
input
to a textbox. The query is based on a table where Operators =1
and
Supervisors = 2. The query is acting as a filter to remove values
that
shouldn't be allowed into this textbox.

I get a "Microsoft Office Access can't find the field '|' referred
to
in
your expression" error when I try to run the code. How else can I
accomplish
what I am trying to do with this code?

Private Sub txtUserID1_AfterUpdate()
If IsNull([q_OpSupSecurity]) Then
MsgBox "Invalid Operator ID. Please try it again.",
vbExclamation,
"Operator ID Entry"""
txtUserID1 = 0
txtUserID1.SetFocus
End If
End Sub

Thanks in advance,
R Brown

 

[Brendan Reynolds]

It looks to me like the quotes between AND and SecLevelOp might be the
cause of the problem.

 

[iamrdbrown]

This did fix the problem with that. Now I have another problem that I think
is linked to the Dcount... If I put in a value that returns a 0 from the
Dcount, I get the following error:


"you cannot add or change a record because a related record is required in
table..."

Here is the code for one section (I have 3) that is giving this problem.

If DCount("*", "Users", "UserID=" & txtOperator & _
" AND SecLevel<3") = 0 Then
MsgBox "Invalid Operator ID. Please try it again.", vbExclamation,
"Operator ID Entry"""
txtOperator = ""
txtOperator.SetFocus
Else:
txtDate1.Visible = True
txtDate1 = Date
txtSupervisor.Visible = True
txtSupervisor.SetFocus
End If

I suspect it is tied to the table relationships, but I can't figure out why...

R Brown

Looking closer, you've got an extra double quote in there after the AND
(which I happened to propagate). Try:

IF Dcount("*", "q_OpSupSecurity", "UserID=" & txtUserId1 & _
" AND SecLevelOp<3") = 0 THEN


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The expression(s) looks for the comma, not a semi-colon. It changed what
gets high-lighted as the error point when I changed the comma to a
semi-colon. With the original code, I get the error at the end like there
is
something missing - a list separator or such.

My boss keeps talking about sending me to school for VB/VBA... I sure do
wish he would... (smile). It would save you guys a bunch of headaches.

R Brown

What are your Regional Settings? Might you have them set such that you
need
to use a semicolon rather than a comma as a list separator?

See whether this works:

IF Dcount("*";"q_OpSupSecurity";"UserID=" & txtUserId1 & _
" AND "SecLevelOp<3") = 0 THEN


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


John,
I apologize for being dense, but when I plug this in I get a 'Expected:
list
separator or )' error. The code I plugged in is as follows:
IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1 & " AND
"SecLevelOp<3") = 0 THEN

I am still very new to VB/VBA and have fits trying to debug... I have
learned a LOT from you guys here, but still have a ways to go before I
even
consider myself 'literate' with this programming thing.

R Brown

:

So you can expand the criteria to something like the following
assuming
that you have a field named Clearance and it is a number field.

IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1 & " AND
Clearance
<3") = 0 THEN

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


iamrdbrown wrote:
Actually, I am trying to confirm that the UserID is in the table
with
the
correct clearance (<3) before allowing the data being input into the
textbox
to be saved. I don't want just ID number to work - just the ones
with
the
appropriate clearance.

TIA,
R Brown

:

You cannot use a query that way.

Perhaps what you want is to use the DCount function to see if the
id
already
exists in a table or a query.

IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1) = 0 THEN
MsgBox "Invalid Operator ID. Please try it again.", _
vbExclamation, "Operator ID Entry"
txtUserID1 = 0
txtUserID1.SetFocus
End If

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

iamrdbrown wrote:
I am trying to use a query (q_OpSupSecurity) with a 'IsNull' to
limit
input
to a textbox. The query is based on a table where Operators =1
and
Supervisors = 2. The query is acting as a filter to remove values
that
shouldn't be allowed into this textbox.

I get a "Microsoft Office Access can't find the field '|' referred
to
in
your expression" error when I try to run the code. How else can I
accomplish
what I am trying to do with this code?

Private Sub txtUserID1_AfterUpdate()
If IsNull([q_OpSupSecurity]) Then
MsgBox "Invalid Operator ID. Please try it again.",
vbExclamation,
"Operator ID Entry"""
txtUserID1 = 0
txtUserID1.SetFocus
End If
End Sub

Thanks in advance,
R Brown

 

[iamrdbrown]

This code worked for the most part. I kept getting an error about 'can't add
or modify a record because a related record is needed in table ...' I think
I figured it out... at least it's working now. I ended up having to move
some code (what would have been the Else portion of the IF...Then) into other
control events to get everything to dovetail. I really appreciate the help
you guys gave me on this. I am sure I will be back with more questions as I
move forward with this database...

R Brown

Looking closer, you've got an extra double quote in there after the AND
(which I happened to propagate). Try:

IF Dcount("*", "q_OpSupSecurity", "UserID=" & txtUserId1 & _
" AND SecLevelOp<3") = 0 THEN


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The expression(s) looks for the comma, not a semi-colon. It changed what
gets high-lighted as the error point when I changed the comma to a
semi-colon. With the original code, I get the error at the end like there
is
something missing - a list separator or such.

My boss keeps talking about sending me to school for VB/VBA... I sure do
wish he would... (smile). It would save you guys a bunch of headaches.

R Brown

What are your Regional Settings? Might you have them set such that you
need
to use a semicolon rather than a comma as a list separator?

See whether this works:

IF Dcount("*";"q_OpSupSecurity";"UserID=" & txtUserId1 & _
" AND "SecLevelOp<3") = 0 THEN


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


John,
I apologize for being dense, but when I plug this in I get a 'Expected:
list
separator or )' error. The code I plugged in is as follows:
IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1 & " AND
"SecLevelOp<3") = 0 THEN

I am still very new to VB/VBA and have fits trying to debug... I have
learned a LOT from you guys here, but still have a ways to go before I
even
consider myself 'literate' with this programming thing.

R Brown

:

So you can expand the criteria to something like the following
assuming
that you have a field named Clearance and it is a number field.

IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1 & " AND
Clearance
<3") = 0 THEN

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


iamrdbrown wrote:
Actually, I am trying to confirm that the UserID is in the table
with
the
correct clearance (<3) before allowing the data being input into the
textbox
to be saved. I don't want just ID number to work - just the ones
with
the
appropriate clearance.

TIA,
R Brown

:

You cannot use a query that way.

Perhaps what you want is to use the DCount function to see if the
id
already
exists in a table or a query.

IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1) = 0 THEN
MsgBox "Invalid Operator ID. Please try it again.", _
vbExclamation, "Operator ID Entry"
txtUserID1 = 0
txtUserID1.SetFocus
End If

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

iamrdbrown wrote:
I am trying to use a query (q_OpSupSecurity) with a 'IsNull' to
limit
input
to a textbox. The query is based on a table where Operators =1
and
Supervisors = 2. The query is acting as a filter to remove values
that
shouldn't be allowed into this textbox.

I get a "Microsoft Office Access can't find the field '|' referred
to
in
your expression" error when I try to run the code. How else can I
accomplish
what I am trying to do with this code?

Private Sub txtUserID1_AfterUpdate()
If IsNull([q_OpSupSecurity]) Then
MsgBox "Invalid Operator ID. Please try it again.",
vbExclamation,
"Operator ID Entry"""
txtUserID1 = 0
txtUserID1.SetFocus
End If
End Sub

Thanks in advance,
R Brown

 

[Douglas J. Steele]

You're setting txtOperator to a zero-length string (""). Presumably you've
got a relationship between whatever table you're updating and some other
table on the Operator, so that a ZLS isn't valid for that field.

Your original post shows that you're doing the validation in the AfterUpdate
event. It strikes me that you should be doing it in the BeforeUpdate event,
so that you don't save the record until the data's been fixed. Note that you
might need to use the form's BeforeUpdate event, rather than the control's
BeforeUpdate event.