Finding a record with a macro based on two parameters

[Mark]

I'm trying to write a macro that will allow me to find a specific record in
"Form A" by matching two fields in that record to two fields in "Form B".
(This by using the [forms]![formname]![fieldname] expression as the
argument.) "Field A" is an interger, and "Field B" is a string. The macro
functions off a button-click, so pulls the information from the current
record, if I'm correct in my thinking.

I've got it to work nicely enough finding just the interger, but run into
problems with either finding the one record where both match, or for that
matter, just the string (when the string consists of letters and
numbers--when the string is just a number, it works fine too).

I've tried OpenForm, and GoToRecord, etc. and am finally admitting to myself
I'm stumped. I may just be fixated in my thinking and missing something
simple, too. Any suggestions? Thanks in advance!

 

[KARL DEWEY]

Why not create a query and have macro call the query?

 

[Mark]

I need it to open the actual form and correct record, or I would.

Also, I can't get a query to return properly when the string contains a
number and a letter. (when using [forms]![formname]![fieldname] as the
query argument)

If I could do that with a query, then get the query to do an OpenForm on
open or close or something, that would work too. But the main issue is that
I need to end up at the correct record on my form, in case the record needs
editing.

Why not create a query and have macro call the query?

I'm trying to write a macro that will allow me to find a specific record in
"Form A" by matching two fields in that record to two fields in "Form B".
(This by using the [forms]![formname]![fieldname] expression as the
argument.) "Field A" is an interger, and "Field B" is a string. The macro
functions off a button-click, so pulls the information from the current
record, if I'm correct in my thinking.

I've got it to work nicely enough finding just the interger, but run into
problems with either finding the one record where both match, or for that
matter, just the string (when the string consists of letters and
numbers--when the string is just a number, it works fine too).

I've tried OpenForm, and GoToRecord, etc. and am finally admitting to myself
I'm stumped. I may just be fixated in my thinking and missing something
simple, too. Any suggestions? Thanks in advance!

 

[tina]

so you have FormB open, and have navigated to a specific record. you want to
click on a button, which runs a macro that opens FormA to a specific record
based on certain field values in the current record in FormB. correct?

try the following macro settings, as

Condition: <leave it blank>
Action: OpenForm
Form Name: FormA
Where Condition: [FieldA]=[Forms]![FormB]![FieldA] And
[FieldB]=[Forms]![FormB]![FieldB]

FieldA and FieldB need to be in FormA's underlying table or query, and you
need to make sure you're using the correct names. the fields referred to in
FormB need to be in FormB's underlying table/query, and again you need to
use the correct names. other than that, in a macro you don't need to use
quotes for field references - in the test i did using the Where Condition
posted above, both values A and B were Text, and i made no changes to the
expression other than changing the names of the fields to match your
scenario. just make sure that the data type on both sides of each equation
match: Number = Number And Text = Text.

hth

I need it to open the actual form and correct record, or I would.

Also, I can't get a query to return properly when the string contains a
number and a letter. (when using [forms]![formname]![fieldname] as the
query argument)

If I could do that with a query, then get the query to do an OpenForm on
open or close or something, that would work too. But the main issue is that
I need to end up at the correct record on my form, in case the record needs
editing.

Why not create a query and have macro call the query?

I'm trying to write a macro that will allow me to find a specific record in
"Form A" by matching two fields in that record to two fields in "Form B".
(This by using the [forms]![formname]![fieldname] expression as the
argument.) "Field A" is an interger, and "Field B" is a string. The macro
functions off a button-click, so pulls the information from the current
record, if I'm correct in my thinking.

I've got it to work nicely enough finding just the interger, but run into
problems with either finding the one record where both match, or for that
matter, just the string (when the string consists of letters and
numbers--when the string is just a number, it works fine too).

I've tried OpenForm, and GoToRecord, etc. and am finally admitting to myself
I'm stumped. I may just be fixated in my thinking and missing something
simple, too. Any suggestions? Thanks in advance!

 

[Brandie]

hi I'm having the same problem...i tried this but it isn't working....or at
least i don't think it is. If i have the fields showing in the second form
should they automatically show, afterall it is supposed to be the same record.

so you have FormB open, and have navigated to a specific record. you want to
click on a button, which runs a macro that opens FormA to a specific record
based on certain field values in the current record in FormB. correct?

try the following macro settings, as

Condition: <leave it blank>
Action: OpenForm
Form Name: FormA
Where Condition: [FieldA]=[Forms]![FormB]![FieldA] And
[FieldB]=[Forms]![FormB]![FieldB]

FieldA and FieldB need to be in FormA's underlying table or query, and you
need to make sure you're using the correct names. the fields referred to in
FormB need to be in FormB's underlying table/query, and again you need to
use the correct names. other than that, in a macro you don't need to use
quotes for field references - in the test i did using the Where Condition
posted above, both values A and B were Text, and i made no changes to the
expression other than changing the names of the fields to match your
scenario. just make sure that the data type on both sides of each equation
match: Number = Number And Text = Text.

hth

I need it to open the actual form and correct record, or I would.

Also, I can't get a query to return properly when the string contains a
number and a letter. (when using [forms]![formname]![fieldname] as the
query argument)

If I could do that with a query, then get the query to do an OpenForm on
open or close or something, that would work too. But the main issue is that
I need to end up at the correct record on my form, in case the record needs
editing.

Why not create a query and have macro call the query?

:

I'm trying to write a macro that will allow me to find a specific record in
"Form A" by matching two fields in that record to two fields in "Form B".
(This by using the [forms]![formname]![fieldname] expression as the
argument.) "Field A" is an interger, and "Field B" is a string. The macro
functions off a button-click, so pulls the information from the current
record, if I'm correct in my thinking.

I've got it to work nicely enough finding just the interger, but run into
problems with either finding the one record where both match, or for that
matter, just the string (when the string consists of letters and
numbers--when the string is just a number, it works fine too).

I've tried OpenForm, and GoToRecord, etc. and am finally admitting to myself
I'm stumped. I may just be fixated in my thinking and missing something
simple, too. Any suggestions? Thanks in advance!

 

[tina]

are you entering a *new* record in a form, and then trying to use the macro
to open another form showing the same record? if so, you have to save the
record in the first form *before* trying to view it in the second form.
beyond that, i don't know what else to offer - didn't really understand your
statement

If i have the fields showing in the second form
should they automatically show, afterall it is supposed to be the same

record.

hth

hi I'm having the same problem...i tried this but it isn't working....or at
least i don't think it is. If i have the fields showing in the second form
should they automatically show, afterall it is supposed to be the same record.

so you have FormB open, and have navigated to a specific record. you want to
click on a button, which runs a macro that opens FormA to a specific record
based on certain field values in the current record in FormB. correct?

try the following macro settings, as

Condition: <leave it blank>
Action: OpenForm
Form Name: FormA
Where Condition: [FieldA]=[Forms]![FormB]![FieldA] And
[FieldB]=[Forms]![FormB]![FieldB]

FieldA and FieldB need to be in FormA's underlying table or query, and you
need to make sure you're using the correct names. the fields referred to in
FormB need to be in FormB's underlying table/query, and again you need to
use the correct names. other than that, in a macro you don't need to use
quotes for field references - in the test i did using the Where Condition
posted above, both values A and B were Text, and i made no changes to the
expression other than changing the names of the fields to match your
scenario. just make sure that the data type on both sides of each equation
match: Number = Number And Text = Text.

hth

I need it to open the actual form and correct record, or I would.

Also, I can't get a query to return properly when the string contains a
number and a letter. (when using [forms]![formname]![fieldname] as the
query argument)

If I could do that with a query, then get the query to do an OpenForm on
open or close or something, that would work too. But the main issue

is
that

I need to end up at the correct record on my form, in case the record needs
editing.

:

Why not create a query and have macro call the query?

:

I'm trying to write a macro that will allow me to find a specific record in
"Form A" by matching two fields in that record to two fields in

"Form
B".

(This by using the [forms]![formname]![fieldname] expression as the
argument.) "Field A" is an interger, and "Field B" is a string.

The
macro

functions off a button-click, so pulls the information from the current
record, if I'm correct in my thinking.

I've got it to work nicely enough finding just the interger, but

run
into

problems with either finding the one record where both match, or

for
that

matter, just the string (when the string consists of letters and
numbers--when the string is just a number, it works fine too).

I've tried OpenForm, and GoToRecord, etc. and am finally admitting

to
myself

I'm stumped. I may just be fixated in my thinking and missing something
simple, too. Any suggestions? Thanks in advance!