Open Form Code Error

[Access::Student]

Hey, I've just got a simple button to move to another form, but it's giving
me an error. The code is:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Authorities"
stLinkCriteria = "kp_authorities_id = " & "'" & [kp_authorities_id] & "'"

DoCmd.OpenForm stDocName
Forms(stDocName).Recordset.FindFirst stLinkCriteria


It will bring me to the correct form, but only to the first record, and
gives me the error:
"Datatype mismatch in criteria expression"
But I don't see why my criteria is wrong (the two 'kp_authorities_id' are
the same field).
Am I doing something wrong here? I'm just trying to go between two forms
based on the same table without loosing focus on the current record. Thanks
for any help.

 

[Dirk Goldgar]

Hey, I've just got a simple button to move to another form, but it's
giving
me an error. The code is:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Authorities"
stLinkCriteria = "kp_authorities_id = " & "'" & [kp_authorities_id] &
"'"

DoCmd.OpenForm stDocName
Forms(stDocName).Recordset.FindFirst stLinkCriteria


It will bring me to the correct form, but only to the first record, and
gives me the error:
"Datatype mismatch in criteria expression"
But I don't see why my criteria is wrong (the two 'kp_authorities_id' are
the same field).
Am I doing something wrong here? I'm just trying to go between two forms
based on the same table without loosing focus on the current record.
Thanks
for any help.

The criterion you are building assumes that kp_authorities_id is a text
field. Is it? If it's a numeric field, then you do in fact have a type
mismatch in your criteria expression, since you are comparing the value of a
numeric field with a quoted text value. In that case, use this:

stLinkCriteria = "kp_authorities_id = " & [kp_authorities_id]

 

[Access::Student]

I could have sworn I tried that...

Thanks a lot for your help, it is a numeric field. I didn't realize the ' '
made it be seen as text.

Hey, I've just got a simple button to move to another form, but it's
giving
me an error. The code is:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Authorities"
stLinkCriteria = "kp_authorities_id = " & "'" & [kp_authorities_id] &
"'"

DoCmd.OpenForm stDocName
Forms(stDocName).Recordset.FindFirst stLinkCriteria


It will bring me to the correct form, but only to the first record, and
gives me the error:
"Datatype mismatch in criteria expression"
But I don't see why my criteria is wrong (the two 'kp_authorities_id' are
the same field).
Am I doing something wrong here? I'm just trying to go between two forms
based on the same table without loosing focus on the current record.
Thanks
for any help.

The criterion you are building assumes that kp_authorities_id is a text
field. Is it? If it's a numeric field, then you do in fact have a type
mismatch in your criteria expression, since you are comparing the value of a
numeric field with a quoted text value. In that case, use this:

stLinkCriteria = "kp_authorities_id = " & [kp_authorities_id]


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Also, why not simply filter the second form:
[...]
DoCmd.OpenForm stDocName, Wherecondition:=stLinkCriteria

I can't speak for the OP, but it's not uncommon to want to open a form
prepositioned to a particular record, but allowing the user to navigate to
other records.

 

[Dirk Goldgar]

Perhaps I'm reading too much into it, but I interpreted their penultimate
sentence as meaning otherwise.

IIRC, I previously answered a question from the same OP about how to open
the second form to display the record, while keeping all other records
available. That's why I didn't suggest filtering, but you wouldn't have
known that, of course.

Whatever the case, they still need to cater
for the second form being opened while the first is at an empty new
record,
either by calling the Nz function to avoid an error, by handling the
error,
by disabling the ability to open the second from when navigating to a new
record in the first form, or by disallowing the insertion of a new record
via
the first form; and the first form's current record should be explicitly
saved before opening it in the second form, both when editing an existing
record or inserting a new one.

Unless these bases are covered Murphy's Law will inevitably be invoked at
some stage!

I agree completely.

 

[Access::Student]

Hey, thanks for the extra input. The form the button is on actually has no
open fields so the user can't make any edits on it, so I don't think what you
said applies. And I did want to send the user to another page without having
the filter on.