searchforrecord macro pulling wrong record

[Hatchet]

I have two different forms (I'll call form A and form B) that share the same
subform (I'll call form C) which in turn contains a query. Form C does not
have a direct relationship with either Form A or B - there are no child
fields or master fields linked between the forms. On this query (in form C),
I have an "On Click" macro set for the ID field. This macro essentially
searches for the record on form A or B that I just clicked on in form C,
thereby bringing up the information associated with that particular record.
Form A and Form B are never open at the same time (two different audiences
each uses one of them), so I have the macro set with conditions to determine
which one to search for the record.

My problem is that the SearchForRecord macro works perfectly for Form A, but
starts pulling the wrong record up on Form B after the third time using the
macro. Does anyone have any ideas?

Here is the code for the macro I have. I've listed it out the condition,
action, and arguments as they appear on the macro per row.

Row 1:
Condition:
Action: SetTempVar
Arguments: ClickedID, [ID]
Row 2:
Condition: [screen].[activeform].[Name] = "Form A"
Action: SearchForRecord
Arguments: Form, Form A, First, ="[ID] =" & [TempVars]![ClickedID]
Row 3:
Condition: [screen].[activeform].[Name] = "Form B"
Action: SearchForRecord
Arguments: Form, Form B, First, ="[ID] =" & [TempVars]![ClickedID]
Row 4:
Condition:
Action: RemoveTempVar
Arguments: ClickedID

Thanks in advanced for your help.

 

[Steve Schapel]

Hatchet,

I have stared at it, gone away, and stared at it some more. I can see
nothing wrong with your macro.

When you get the wrong result, is the focus moving to *a* record on Form B,
but just the wrong one? If so, is there a pattern to this that you can
notice?

Also, is the Form C bound? If so, is the Form C record saved at the time
that you run the macro? If not, you might try putting a
RunCommand/SaveRecord at the beginning of the macro.

--
Steve Schapel, Microsoft Access MVP

I have two different forms (I'll call form A and form B) that share the
same
subform (I'll call form C) which in turn contains a query. Form C does
not
have a direct relationship with either Form A or B - there are no child
fields or master fields linked between the forms. On this query (in form
C),
I have an "On Click" macro set for the ID field. This macro essentially
searches for the record on form A or B that I just clicked on in form C,
thereby bringing up the information associated with that particular
record.
Form A and Form B are never open at the same time (two different audiences
each uses one of them), so I have the macro set with conditions to
determine
which one to search for the record.

My problem is that the SearchForRecord macro works perfectly for Form A,
but
starts pulling the wrong record up on Form B after the third time using
the
macro. Does anyone have any ideas?

Here is the code for the macro I have. I've listed it out the condition,
action, and arguments as they appear on the macro per row.

Row 1:
Condition:
Action: SetTempVar
Arguments: ClickedID, [ID]
Row 2:
Condition: [screen].[activeform].[Name] = "Form A"
Action: SearchForRecord
Arguments: Form, Form A, First, ="[ID] =" & [TempVars]![ClickedID]
Row 3:
Condition: [screen].[activeform].[Name] = "Form B"
Action: SearchForRecord
Arguments: Form, Form B, First, ="[ID] =" & [TempVars]![ClickedID]
Row 4:
Condition:
Action: RemoveTempVar
Arguments: ClickedID

Thanks in advanced for your help.

__________ Information from ESET Smart Security, version of virus signature database 4146 (20090611) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Hatchet]

Hopefully I've understood what you are asking, but if you need more details
(or if I seem completely off base) please let me know.

Form B is pulling up a record, but it keeps pulling the wrong one. I tried
to look for a pattern to the problem but it seems entirely random. There is
no connection between the different records (some are at the beginning of the
table, other times it pulls it up a record that is midway through the table,
no similarity between ID numbers, etc).

Form C is not bound. In that sense it isn't like a normal subform which
pulls a subset of information. The intent was that Form C would appear as a
subform, but would not be limited to the same record that Form A or B had
open.

Let me know if any of that makes sense or if I can provide other important
details. I've been banging my head against a wall for over a week now trying
to figure out what's wrong. I'm wondering if I should create a new form
called Form D as a copy of Form C and use that on Form B, although that is
not ideal.

Thanks for your help and your advice Steve.

Hatchet,

I have stared at it, gone away, and stared at it some more. I can see
nothing wrong with your macro.

When you get the wrong result, is the focus moving to *a* record on Form B,
but just the wrong one? If so, is there a pattern to this that you can
notice?

Also, is the Form C bound? If so, is the Form C record saved at the time
that you run the macro? If not, you might try putting a
RunCommand/SaveRecord at the beginning of the macro.

--
Steve Schapel, Microsoft Access MVP

I have two different forms (I'll call form A and form B) that share the
same
subform (I'll call form C) which in turn contains a query. Form C does
not
have a direct relationship with either Form A or B - there are no child
fields or master fields linked between the forms. On this query (in form
C),
I have an "On Click" macro set for the ID field. This macro essentially
searches for the record on form A or B that I just clicked on in form C,
thereby bringing up the information associated with that particular
record.
Form A and Form B are never open at the same time (two different audiences
each uses one of them), so I have the macro set with conditions to
determine
which one to search for the record.

My problem is that the SearchForRecord macro works perfectly for Form A,
but
starts pulling the wrong record up on Form B after the third time using
the
macro. Does anyone have any ideas?

Here is the code for the macro I have. I've listed it out the condition,
action, and arguments as they appear on the macro per row.

Row 1:
Condition:
Action: SetTempVar
Arguments: ClickedID, [ID]
Row 2:
Condition: [screen].[activeform].[Name] = "Form A"
Action: SearchForRecord
Arguments: Form, Form A, First, ="[ID] =" & [TempVars]![ClickedID]
Row 3:
Condition: [screen].[activeform].[Name] = "Form B"
Action: SearchForRecord
Arguments: Form, Form B, First, ="[ID] =" & [TempVars]![ClickedID]
Row 4:
Condition:
Action: RemoveTempVar
Arguments: ClickedID

Thanks in advanced for your help.

__________ Information from ESET Smart Security, version of virus signature database 4146 (20090611) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Steve Schapel]

Hatchet,

Sorry for the delayed response.

So, where does the ID value on the unbound subform come from? Is that
manually entered by the user? Immediately before this macro is run?

--
Steve Schapel, Microsoft Access MVP

Hopefully I've understood what you are asking, but if you need more
details
(or if I seem completely off base) please let me know.

Form B is pulling up a record, but it keeps pulling the wrong one. I
tried
to look for a pattern to the problem but it seems entirely random. There
is
no connection between the different records (some are at the beginning of
the
table, other times it pulls it up a record that is midway through the
table,
no similarity between ID numbers, etc).

Form C is not bound. In that sense it isn't like a normal subform which
pulls a subset of information. The intent was that Form C would appear as
a
subform, but would not be limited to the same record that Form A or B had
open.

Let me know if any of that makes sense or if I can provide other important
details. I've been banging my head against a wall for over a week now
trying
to figure out what's wrong. I'm wondering if I should create a new form
called Form D as a copy of Form C and use that on Form B, although that is
not ideal.

Thanks for your help and your advice Steve.

__________ Information from ESET Smart Security, version of virus signature database 4152 (20090612) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Hatchet]

I have the short answer and the long answer. The short answer is that Form C
does not really have an ID value. Rather, it is a way for user-friendly
filtering can be done on the main table of the database. This allows the
user to enter information in to find a particular record and then use the
ClickID macro (the one that I originally posted and is not working properly)
to pull the clicked record into Form A or Form B.

Now for the long answer. Don't feel like you have to read all of this, but
this gives the exact process that Form C follows.

Form C is really used to search for a name so that they can be pulled into
Form A or Form B (allowing for someone to then work with that particular
record). I have several unbound text fields that are labeled for first name,
last name, city, state, etc. When the user enters text into one of those
fields, an embedded macro for the "After Update" property of the text field
then sets a temporary variable for whatever field the user typed information
into. I've copied one of the "After Update" macros below to give a better
idea. This particular macro is used on the First Name text field.

Row 1:
Condition: [screen].[activeform].[Name] = "Form A"
Action: SetTempVar
Arguments: SelectFirst, [Forms]![Form A]![Form C]![First]
Row 2:
Condition: [screen].[activeform].[Name] = "Form B"
Action: SetTempVar
Arguments: SelectFirst, [Forms]![Form B]![Form C]![First]

The other text fields are similar except that the temporary variable is
changed - so the "Last Name" text field sets a temporary variable called
SelectLast, etc. There is also a "Before Update" embedded macro that removes
each temporary variable.

Once the user has completed filling in the information, he/she clicks on a
command button labeled "Search". This button then requeries the query within
Form C with the temporary variables listed being set in the appropriate
field. So the SelectFirst variable is in the criteria for the column for
first names in the query.

I designed this form so that the user could easily filter through the
20,000+ records that this database must handle. I have other
queries/subforms built that uses queries that I have already designed (for
instance, one query that selects individuals that have been entered recently,
the ClickedID macro is set to work with these too, but they have only been
designed for Form A not Form B). However, I needed a query that a user could
define to find a particular record should they need it.

I really hope that makes sense. Please let me know if you need further
clarification.

 

[Steve Schapel]

Hatchet,

I think someone would need to see the full details of the macros and also
the queries, or else to see the actual database itself, to get to the bottom
of this.

However, I suspect it has something to do with an incorrect processing
related to the assigning and/or clearing of the TempVars, or the way these
are handled in the query.

--
Steve Schapel, Microsoft Access MVP

I have the short answer and the long answer. The short answer is that
Form C
does not really have an ID value. Rather, it is a way for user-friendly
filtering can be done on the main table of the database. This allows the
user to enter information in to find a particular record and then use the
ClickID macro (the one that I originally posted and is not working
properly)
to pull the clicked record into Form A or Form B.

Now for the long answer. Don't feel like you have to read all of this,
but
this gives the exact process that Form C follows.

Form C is really used to search for a name so that they can be pulled into
Form A or Form B (allowing for someone to then work with that particular
record). I have several unbound text fields that are labeled for first
name,
last name, city, state, etc. When the user enters text into one of those
fields, an embedded macro for the "After Update" property of the text
field
then sets a temporary variable for whatever field the user typed
information
into. I've copied one of the "After Update" macros below to give a better
idea. This particular macro is used on the First Name text field.

Row 1:
Condition: [screen].[activeform].[Name] = "Form A"
Action: SetTempVar
Arguments: SelectFirst, [Forms]![Form A]![Form C]![First]
Row 2:
Condition: [screen].[activeform].[Name] = "Form B"
Action: SetTempVar
Arguments: SelectFirst, [Forms]![Form B]![Form C]![First]

The other text fields are similar except that the temporary variable is
changed - so the "Last Name" text field sets a temporary variable called
SelectLast, etc. There is also a "Before Update" embedded macro that
removes
each temporary variable.

Once the user has completed filling in the information, he/she clicks on
a
command button labeled "Search". This button then requeries the query
within
Form C with the temporary variables listed being set in the appropriate
field. So the SelectFirst variable is in the criteria for the column for
first names in the query.

I designed this form so that the user could easily filter through the
20,000+ records that this database must handle. I have other
queries/subforms built that uses queries that I have already designed (for
instance, one query that selects individuals that have been entered
recently,
the ClickedID macro is set to work with these too, but they have only been
designed for Form A not Form B). However, I needed a query that a user
could
define to find a particular record should they need it.

__________ Information from ESET Smart Security, version of virus signature database 4174 (20090620) __________

The message was checked by ESET Smart Security.

http://www.eset.com