Open Subform to specific record

[tboggs25]

I would like to open up a subform to a specific record based upon the record
that is currently open in the main form.

Both tables have a field called Firm ID (both are integers)

Under Arguments I have:
Law Firms (Lawyer-411), Form, , "[Firm ID]=" & [Firm ID], , Normal

Obviously under Action I have:
OpenForm

The button opens up the correct form, but not to the correct record. In
fact, it opens to the exact same record every time and I don't know why.
Does anyone have any troubleshooting ideas or know of a fix? Thanks for the
help!

- Travis

 

[Jeff Boyce]

In a typical main form/subform design, the subform is "live". That is, you
tell Access what field is the "parent" in the main form and the "child" in
the subform, and Access does all the work to get the subform record
displayed for the record showing in the main form.

That said, and I may be taking your description too literally, it sounds
like you only have a single record that is related to and would need to show
up in the subform.

Since it all starts with the data, it would probably help others here if you
could describe the underlying data and the relationship between what you are
showing in the main form and in the subform.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[tboggs25]

Absolutely.

The Main Form is essentially a V-Card. It displays common things like first
name, last name, address, phone number, email, some notes, etc etc. The
important information on this form is the Firm ID number.

The Firm ID number represents which Law Firm the contact works at. So in
the Contact's table Jon Smith may have a Firm ID of 150. Then in the Law
Firm table the Firm ID of 150 will have all of the information about that law
firm, such as, firm name, address, phone, website, size, etc etc.

The Firm ID is a integer in both tables. Almost all other fields in each
table are set to text (restricted to a size that I deemed large enough).

So really, the crux of what I am trying to do is this:

I would like to open up the Contacts Form and press a button that knows to
find the current Firm ID number in the Law Firm form.

Hope all of this helps. Thanks!

- Travis

In a typical main form/subform design, the subform is "live". That is, you
tell Access what field is the "parent" in the main form and the "child" in
the subform, and Access does all the work to get the subform record
displayed for the record showing in the main form.

That said, and I may be taking your description too literally, it sounds
like you only have a single record that is related to and would need to show
up in the subform.

Since it all starts with the data, it would probably help others here if you
could describe the underlying data and the relationship between what you are
showing in the main form and in the subform.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would like to open up a subform to a specific record based upon the
record
that is currently open in the main form.

Both tables have a field called Firm ID (both are integers)

Under Arguments I have:
Law Firms (Lawyer-411), Form, , "[Firm ID]=" & [Firm ID], , Normal

Obviously under Action I have:
OpenForm

The button opens up the correct form, but not to the correct record. In
fact, it opens to the exact same record every time and I don't know why.
Does anyone have any troubleshooting ideas or know of a fix? Thanks for
the
help!

- Travis

 

[Jeff Boyce]

Have you opened the main form in design view, highlighted the subform and
check on its properties? That's where you'll tell Access how the two are
related.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Absolutely.

The Main Form is essentially a V-Card. It displays common things like
first
name, last name, address, phone number, email, some notes, etc etc. The
important information on this form is the Firm ID number.

The Firm ID number represents which Law Firm the contact works at. So in
the Contact's table Jon Smith may have a Firm ID of 150. Then in the Law
Firm table the Firm ID of 150 will have all of the information about that
law
firm, such as, firm name, address, phone, website, size, etc etc.

The Firm ID is a integer in both tables. Almost all other fields in each
table are set to text (restricted to a size that I deemed large enough).

So really, the crux of what I am trying to do is this:

I would like to open up the Contacts Form and press a button that knows to
find the current Firm ID number in the Law Firm form.

Hope all of this helps. Thanks!

- Travis

In a typical main form/subform design, the subform is "live". That is,
you
tell Access what field is the "parent" in the main form and the "child"
in
the subform, and Access does all the work to get the subform record
displayed for the record showing in the main form.

That said, and I may be taking your description too literally, it sounds
like you only have a single record that is related to and would need to
show
up in the subform.

Since it all starts with the data, it would probably help others here if
you
could describe the underlying data and the relationship between what you
are
showing in the main form and in the subform.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would like to open up a subform to a specific record based upon the
record
that is currently open in the main form.

Both tables have a field called Firm ID (both are integers)

Under Arguments I have:
Law Firms (Lawyer-411), Form, , "[Firm ID]=" & [Firm ID], , Normal

Obviously under Action I have:
OpenForm

The button opens up the correct form, but not to the correct record.
In
fact, it opens to the exact same record every time and I don't know
why.
Does anyone have any troubleshooting ideas or know of a fix? Thanks
for
the
help!

- Travis

 

[tboggs25]

Not sure what exactly I am to be looking for in the Properties of the main
form.

I am probably missing the entire boat on this one.

Have you opened the main form in design view, highlighted the subform and
check on its properties? That's where you'll tell Access how the two are
related.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Absolutely.

The Main Form is essentially a V-Card. It displays common things like
first
name, last name, address, phone number, email, some notes, etc etc. The
important information on this form is the Firm ID number.

The Firm ID number represents which Law Firm the contact works at. So in
the Contact's table Jon Smith may have a Firm ID of 150. Then in the Law
Firm table the Firm ID of 150 will have all of the information about that
law
firm, such as, firm name, address, phone, website, size, etc etc.

The Firm ID is a integer in both tables. Almost all other fields in each
table are set to text (restricted to a size that I deemed large enough).

So really, the crux of what I am trying to do is this:

I would like to open up the Contacts Form and press a button that knows to
find the current Firm ID number in the Law Firm form.

Hope all of this helps. Thanks!

- Travis

In a typical main form/subform design, the subform is "live". That is,
you
tell Access what field is the "parent" in the main form and the "child"
in
the subform, and Access does all the work to get the subform record
displayed for the record showing in the main form.

That said, and I may be taking your description too literally, it sounds
like you only have a single record that is related to and would need to
show
up in the subform.

Since it all starts with the data, it would probably help others here if
you
could describe the underlying data and the relationship between what you
are
showing in the main form and in the subform.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would like to open up a subform to a specific record based upon the
record
that is currently open in the main form.

Both tables have a field called Firm ID (both are integers)

Under Arguments I have:
Law Firms (Lawyer-411), Form, , "[Firm ID]=" & [Firm ID], , Normal

Obviously under Action I have:
OpenForm

The button opens up the correct form, but not to the correct record.
In
fact, it opens to the exact same record every time and I don't know
why.
Does anyone have any troubleshooting ideas or know of a fix? Thanks
for
the
help!

- Travis

 

[tboggs25]

FYI, I found a solution that will work just fine for me. I was making things
much harder than they needed to be.

I am still curious why my earlier method still did not work. Thank you for
any information and help on that.

- Travis

Not sure what exactly I am to be looking for in the Properties of the main
form.

I am probably missing the entire boat on this one.

Have you opened the main form in design view, highlighted the subform and
check on its properties? That's where you'll tell Access how the two are
related.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Absolutely.

The Main Form is essentially a V-Card. It displays common things like
first
name, last name, address, phone number, email, some notes, etc etc. The
important information on this form is the Firm ID number.

The Firm ID number represents which Law Firm the contact works at. So in
the Contact's table Jon Smith may have a Firm ID of 150. Then in the Law
Firm table the Firm ID of 150 will have all of the information about that
law
firm, such as, firm name, address, phone, website, size, etc etc.

The Firm ID is a integer in both tables. Almost all other fields in each
table are set to text (restricted to a size that I deemed large enough).

So really, the crux of what I am trying to do is this:

I would like to open up the Contacts Form and press a button that knows to
find the current Firm ID number in the Law Firm form.

Hope all of this helps. Thanks!

- Travis

:

In a typical main form/subform design, the subform is "live". That is,
you
tell Access what field is the "parent" in the main form and the "child"
in
the subform, and Access does all the work to get the subform record
displayed for the record showing in the main form.

That said, and I may be taking your description too literally, it sounds
like you only have a single record that is related to and would need to
show
up in the subform.

Since it all starts with the data, it would probably help others here if
you
could describe the underlying data and the relationship between what you
are
showing in the main form and in the subform.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would like to open up a subform to a specific record based upon the
record
that is currently open in the main form.

Both tables have a field called Firm ID (both are integers)

Under Arguments I have:
Law Firms (Lawyer-411), Form, , "[Firm ID]=" & [Firm ID], , Normal

Obviously under Action I have:
OpenForm

The button opens up the correct form, but not to the correct record.
In
fact, it opens to the exact same record every time and I don't know
why.
Does anyone have any troubleshooting ideas or know of a fix? Thanks
for
the
help!

- Travis

 

[Jeff Boyce]

Travis

I've not used your approach, so I'm not sure what wasn't working about it.
I've been ... efficient (i.e., lazy) ... and let Access do the heavy lifting
<G>

Regards

Jeff Boyce
Microsoft Office/Access MVP

FYI, I found a solution that will work just fine for me. I was making
things
much harder than they needed to be.

I am still curious why my earlier method still did not work. Thank you
for
any information and help on that.

- Travis

Not sure what exactly I am to be looking for in the Properties of the
main
form.

I am probably missing the entire boat on this one.

Have you opened the main form in design view, highlighted the subform
and
check on its properties? That's where you'll tell Access how the two
are
related.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Absolutely.

The Main Form is essentially a V-Card. It displays common things
like
first
name, last name, address, phone number, email, some notes, etc etc.
The
important information on this form is the Firm ID number.

The Firm ID number represents which Law Firm the contact works at.
So in
the Contact's table Jon Smith may have a Firm ID of 150. Then in the
Law
Firm table the Firm ID of 150 will have all of the information about
that
law
firm, such as, firm name, address, phone, website, size, etc etc.

The Firm ID is a integer in both tables. Almost all other fields in
each
table are set to text (restricted to a size that I deemed large
enough).

So really, the crux of what I am trying to do is this:

I would like to open up the Contacts Form and press a button that
knows to
find the current Firm ID number in the Law Firm form.

Hope all of this helps. Thanks!

- Travis

:

In a typical main form/subform design, the subform is "live". That
is,
you
tell Access what field is the "parent" in the main form and the
"child"
in
the subform, and Access does all the work to get the subform record
displayed for the record showing in the main form.

That said, and I may be taking your description too literally, it
sounds
like you only have a single record that is related to and would need
to
show
up in the subform.

Since it all starts with the data, it would probably help others
here if
you
could describe the underlying data and the relationship between what
you
are
showing in the main form and in the subform.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would like to open up a subform to a specific record based upon
the
record
that is currently open in the main form.

Both tables have a field called Firm ID (both are integers)

Under Arguments I have:
Law Firms (Lawyer-411), Form, , "[Firm ID]=" & [Firm ID], , Normal

Obviously under Action I have:
OpenForm

The button opens up the correct form, but not to the correct
record.
In
fact, it opens to the exact same record every time and I don't
know
why.
Does anyone have any troubleshooting ideas or know of a fix?
Thanks
for
the
help!

- Travis