Form subform field link

[Tom]

I have a form with an imbedded subform. I have a record number field linked
from the main form to the subform. The subform contains an input mask to
correct the record ID from a DMR prefix to a CAR prefix and it uses the same
unique recoerd number AA00000, like this DMRAA00000 in the from field and
CARAA00000 in the subform field. I want to open the subform with a command
button on the main form because not all records will contain a CAR. I tried
using the wizard and creating the form subform with a button, but when I open
the for any other way that having it embedded it will not pick up the record
ID I entered in the DMR Number field. It is there immediately on the embedded
version, I just don't want it embedded. Help!

 

[kingston via AccessMonster.com]

The linked field between a main form and a subform must contain the same data.
So DMRAA00000 will never be linked to CARAA00000. If however, the ID is
simply AA00000 in both datasets, the relationship will work. It's not clear
how you implement an "input mask to correct the record ID..." I suggest that
you leave the ID alone in both datasets (hide the master/child ID fields
perhaps), and add textboxes based on the ID (e.g. ="DMR" & Me.IDField).
Unless the user should be able to change the ID, this will work. hth

 

[Tom]

I created some custom input masks that place the DMR & CAR portions of the
field respectively. It works. I then created a form based on all of the
tables (all fields that could require inputs). If I open the form as one huge
form then it does link the fields. I want the CAR portion of the form (approx
10 fileds) to work from a command button (not all DMR's will have a
corresponding CAR). When I configure the CAR form to open, I lose the link
and the CAR field is blank? I have tried using the expression builder to
point the control source to the appropriate field, but it does not save my
selection on exit (this was tried when the form for a CAR was created as a
subform, so that it used the command button to open). If I reopen the
propeties box it is back to the original field setting? The form wizard
doesn't allow me to link fields in subforms like the help shows me to. If you
need more info let me know.

 

[kingston via AccessMonster.com]

Here's how to make a simple subform work. Let me know how your situation is
different.

1) Start with two tables.
2) Each table contains the same field with the same data.
3) Create a form (use the wizard) for each table.
4) In design mode of the main form, drag and drop the other form into the
details section and specify the field (from step 2) that Access should use to
link the two forms.

That's it. I'm not sure what you're trying to achieve with the command
button. You said that not all DMRs have a CAR and that's fine. However, do
all CARs correspond to a DMR? If not, your main/sub form will be of limited
use and you'll have to come up with a separate way to deal with CARs that
don't have a parent DMR. What do you mean by "configure the CAR form to open?
" The CAR subform is embedded into the the main form and should always be
present even if there is no CAR data for the DMR.

I created some custom input masks that place the DMR & CAR portions of the
field respectively. It works. I then created a form based on all of the
tables (all fields that could require inputs). If I open the form as one huge
form then it does link the fields. I want the CAR portion of the form (approx
10 fileds) to work from a command button (not all DMR's will have a
corresponding CAR). When I configure the CAR form to open, I lose the link
and the CAR field is blank? I have tried using the expression builder to
point the control source to the appropriate field, but it does not save my
selection on exit (this was tried when the form for a CAR was created as a
subform, so that it used the command button to open). If I reopen the
propeties box it is back to the original field setting? The form wizard
doesn't allow me to link fields in subforms like the help shows me to. If you
need more info let me know.

The linked field between a main form and a subform must contain the same data.
So DMRAA00000 will never be linked to CARAA00000. If however, the ID is

[quoted text clipped - 14 lines]

 

[Tom]

The command button was simply used to launch the subform when necessary. The
actual data that is stored in the CAR and DMR field is in fact AA00000, by
applying an input mask to the field it displays as CARAA00000 or DMRAA00000.
The problem that I have is in the linking. There are 2 fields in two tables
with the same data AA00000. If I make the form as one big form with all
fields from all tables they are linked ok. If I make two seperate forms and
attempt to link them as form subform they fields are no longer linked... It
seems simple to me, but it does not work?

Here's how to make a simple subform work. Let me know how your situation is
different.

1) Start with two tables.
2) Each table contains the same field with the same data.
3) Create a form (use the wizard) for each table.
4) In design mode of the main form, drag and drop the other form into the
details section and specify the field (from step 2) that Access should use to
link the two forms.

That's it. I'm not sure what you're trying to achieve with the command
button. You said that not all DMRs have a CAR and that's fine. However, do
all CARs correspond to a DMR? If not, your main/sub form will be of limited
use and you'll have to come up with a separate way to deal with CARs that
don't have a parent DMR. What do you mean by "configure the CAR form to open?
" The CAR subform is embedded into the the main form and should always be
present even if there is no CAR data for the DMR.

I created some custom input masks that place the DMR & CAR portions of the
field respectively. It works. I then created a form based on all of the
tables (all fields that could require inputs). If I open the form as one huge
form then it does link the fields. I want the CAR portion of the form (approx
10 fileds) to work from a command button (not all DMR's will have a
corresponding CAR). When I configure the CAR form to open, I lose the link
and the CAR field is blank? I have tried using the expression builder to
point the control source to the appropriate field, but it does not save my
selection on exit (this was tried when the form for a CAR was created as a
subform, so that it used the command button to open). If I reopen the
propeties box it is back to the original field setting? The form wizard
doesn't allow me to link fields in subforms like the help shows me to. If you
need more info let me know.

The linked field between a main form and a subform must contain the same data.
So DMRAA00000 will never be linked to CARAA00000. If however, the ID is

[quoted text clipped - 14 lines]

ID I entered in the DMR Number field. It is there immediately on the embedded
version, I just don't want it embedded. Help!

 

[kingston via AccessMonster.com]

How are you creating one big form with all fields from all tables?
When you make two separate forms and link the fields, do you actually embed
the subform into the main form?
Have you gotten subforms to work with other tables and fields in the same
database?

The command button was simply used to launch the subform when necessary. The
actual data that is stored in the CAR and DMR field is in fact AA00000, by
applying an input mask to the field it displays as CARAA00000 or DMRAA00000.
The problem that I have is in the linking. There are 2 fields in two tables
with the same data AA00000. If I make the form as one big form with all
fields from all tables they are linked ok. If I make two seperate forms and
attempt to link them as form subform they fields are no longer linked... It
seems simple to me, but it does not work?

Here's how to make a simple subform work. Let me know how your situation is
different.

[quoted text clipped - 33 lines]

 

[Tom]

Can I email you my table layouts, so you can see what I'm trying to do?

How are you creating one big form with all fields from all tables?
When you make two separate forms and link the fields, do you actually embed
the subform into the main form?
Have you gotten subforms to work with other tables and fields in the same
database?

The command button was simply used to launch the subform when necessary. The
actual data that is stored in the CAR and DMR field is in fact AA00000, by
applying an input mask to the field it displays as CARAA00000 or DMRAA00000.
The problem that I have is in the linking. There are 2 fields in two tables
with the same data AA00000. If I make the form as one big form with all
fields from all tables they are linked ok. If I make two seperate forms and
attempt to link them as form subform they fields are no longer linked... It
seems simple to me, but it does not work?

Here's how to make a simple subform work. Let me know how your situation is
different.

[quoted text clipped - 33 lines]

ID I entered in the DMR Number field. It is there immediately on the embedded
version, I just don't want it embedded. Help!

 

[kingston via AccessMonster.com]

kkylam<at>excite<dot>com
I won't be able to get to this until tomorrow though.

Can I email you my table layouts, so you can see what I'm trying to do?

How are you creating one big form with all fields from all tables?
When you make two separate forms and link the fields, do you actually embed

[quoted text clipped - 16 lines]