Error 2465

[PHisaw]

Will someone please tell me how to correct this line of code? I've changed
it so many different ways - with and w/out quotes, brackets, parenthesis -
not isnull, is not null, etc. and I get either an error 2465 "can't find
field "|" refered to" or error 13 "type mismatch".

DoCmd.OpenForm "fTTLogOff", acNormal, , "[StopTime]Is Null" And "Not
IsNull[Tech]"

The first part of the code thru "[StopTime]Is Null" was supplied by Allen
Browne and of course works perfectly but I wanted to tack on where Tech is
not null to keep the form from opening with a blank record.

Thanks for any help.
Pam

 

[Allen Browne]

If no records match the criteria, then the form *will* open to a new record.
You're trying to prevent that?

You could set the form's AllowAdditions property to No. That will stop it
opening to a new record. Instead, the entire detail section of the form will
go completely blank, as explained here:
http://allenbrowne.com/casu-20.html

 

[J_Goddard via AccessMonster.com]

Hi -

Try this:

DoCmd.OpenForm "fTTLogOff", acNormal, , "[StopTime] Is Null And Not IsNull(
[Tech])"

Space after [Stoptime]
IsNull is a function, so [tech] need to be in brackets (...)
The whole clause is in a single set of double quotes

John

Will someone please tell me how to correct this line of code? I've changed
it so many different ways - with and w/out quotes, brackets, parenthesis -
not isnull, is not null, etc. and I get either an error 2465 "can't find
field "|" refered to" or error 13 "type mismatch".

DoCmd.OpenForm "fTTLogOff", acNormal, , "[StopTime]Is Null" And "Not
IsNull[Tech]"

The first part of the code thru "[StopTime]Is Null" was supplied by Allen
Browne and of course works perfectly but I wanted to tack on where Tech is
not null to keep the form from opening with a blank record.

Thanks for any help.
Pam

 

[John W. Vinson]

Will someone please tell me how to correct this line of code? I've changed
it so many different ways - with and w/out quotes, brackets, parenthesis -
not isnull, is not null, etc. and I get either an error 2465 "can't find
field "|" refered to" or error 13 "type mismatch".

DoCmd.OpenForm "fTTLogOff", acNormal, , "[StopTime]Is Null" And "Not
IsNull[Tech]"

The first part of the code thru "[StopTime]Is Null" was supplied by Allen
Browne and of course works perfectly but I wanted to tack on where Tech is
not null to keep the form from opening with a blank record.

The And needs to be *inside* the quoted string, not outside; and you need
either parentheses for the IsNull function or (probably better) use the SQL
predicate Is Null (two words not one). Try

DoCmd.OpenForm "fTTLogOff", acNormal, , "[StopTime] Is Null And [Tech] Is Not
Null"


John W. Vinson [MVP]

 

[PHisaw]

Allen,
Thank you for replying. Yes, I want to prevent a new record from opening.
I've set this form up so that it will open for records with one field that is
blank for users to view and/or fill in, if necessary. I didn't want them to
see a new blank record because it would not apply here. It was doing as you
described in the link you provided - the detail section going blank. I tried
the work arounds suggested, but will leave as is, for now.
Thanks again,
Pam

If no records match the criteria, then the form *will* open to a new record.
You're trying to prevent that?

You could set the form's AllowAdditions property to No. That will stop it
opening to a new record. Instead, the entire detail section of the form will
go completely blank, as explained here:
http://allenbrowne.com/casu-20.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Will someone please tell me how to correct this line of code? I've
changed
it so many different ways - with and w/out quotes, brackets, parenthesis -
not isnull, is not null, etc. and I get either an error 2465 "can't find
field "|" refered to" or error 13 "type mismatch".

DoCmd.OpenForm "fTTLogOff", acNormal, , "[StopTime]Is Null" And "Not
IsNull[Tech]"

The first part of the code thru "[StopTime]Is Null" was supplied by Allen
Browne and of course works perfectly but I wanted to tack on where Tech is
not null to keep the form from opening with a blank record.

Thanks for any help.
Pam

 

[PHisaw]

Thanks to you both for replying. It worked without giving the errors I had
been getting.
Now I seem to have run into another problem. The code won't open the form
it is supposed to. I'm hoping you won't mind taking a look to see what I'm
doing wrong.

Form loading is a hidden form that opens, when switchboard, opens to trigger
events such as a reminder form when LogOff (date/time field) has not been
entered from the day before. The hidden form opens and shows a record with
the LogOff field blank, but the reminder fails to open.

Private Sub Form_Load()
On Error GoTo Form_Load_Error

If Len([LogOff] & "") Then

DoCmd.OpenForm "fReminder"

End If

Form_Load_Exit:
Exit Sub
Form_Load_Error:

If Err <> 2501 Then
MsgBox Err.Number & "" & Err.Description
End If

GoTo Form_Load_Exit

End Sub

Thanks for your help,
Pam

Will someone please tell me how to correct this line of code? I've changed
it so many different ways - with and w/out quotes, brackets, parenthesis -
not isnull, is not null, etc. and I get either an error 2465 "can't find
field "|" refered to" or error 13 "type mismatch".

DoCmd.OpenForm "fTTLogOff", acNormal, , "[StopTime]Is Null" And "Not
IsNull[Tech]"

The first part of the code thru "[StopTime]Is Null" was supplied by Allen
Browne and of course works perfectly but I wanted to tack on where Tech is
not null to keep the form from opening with a blank record.

The And needs to be *inside* the quoted string, not outside; and you need
either parentheses for the IsNull function or (probably better) use the SQL
predicate Is Null (two words not one). Try

DoCmd.OpenForm "fTTLogOff", acNormal, , "[StopTime] Is Null And [Tech] Is Not
Null"


John W. Vinson [MVP]

 

[PHisaw]

Nevermind, I figured it out. thanks for all the help.
Pam

Thanks to you both for replying. It worked without giving the errors I had
been getting.
Now I seem to have run into another problem. The code won't open the form
it is supposed to. I'm hoping you won't mind taking a look to see what I'm
doing wrong.

Form loading is a hidden form that opens, when switchboard, opens to trigger
events such as a reminder form when LogOff (date/time field) has not been
entered from the day before. The hidden form opens and shows a record with
the LogOff field blank, but the reminder fails to open.

Private Sub Form_Load()
On Error GoTo Form_Load_Error

If Len([LogOff] & "") Then

DoCmd.OpenForm "fReminder"

End If

Form_Load_Exit:
Exit Sub
Form_Load_Error:

If Err <> 2501 Then
MsgBox Err.Number & "" & Err.Description
End If

GoTo Form_Load_Exit

End Sub

Thanks for your help,
Pam

Will someone please tell me how to correct this line of code? I've changed
it so many different ways - with and w/out quotes, brackets, parenthesis -
not isnull, is not null, etc. and I get either an error 2465 "can't find
field "|" refered to" or error 13 "type mismatch".

DoCmd.OpenForm "fTTLogOff", acNormal, , "[StopTime]Is Null" And "Not
IsNull[Tech]"

The first part of the code thru "[StopTime]Is Null" was supplied by Allen
Browne and of course works perfectly but I wanted to tack on where Tech is
not null to keep the form from opening with a blank record.

The And needs to be *inside* the quoted string, not outside; and you need
either parentheses for the IsNull function or (probably better) use the SQL
predicate Is Null (two words not one). Try

DoCmd.OpenForm "fTTLogOff", acNormal, , "[StopTime] Is Null And [Tech] Is Not
Null"


John W. Vinson [MVP]