Form Wizard Question

[Guest]

I have 3 tables
Table 1 PK = OwnerID
Table 2 PK = AnimalID FK to Table 1 = OwnerID-AnimalTable
Table 3 PK = FeeID FK to Table 2 = AnimalID-FeeTable

I chose all the fields from these 3 tables in the first screen. On the next
screen I select Table 1 as the one to view data by. If I select the "form
with subforms" option, everything is fine. However, when I chose the "linked
forms" option, the OwnerID from Table 1 doesn't appear in Table 2 which
causes an error when Access tries to write the record in Table 2 when I move
to Table 3. I thought if you used the wizard, it was suppose to handle all
the parent/child relationships. Any ideas as to why this works with the
subforms and not with the linked forms, or is there something I'm suppose to
do to make this work? Just when you think you're on the right path, you find
yourself totally lost.

Help please,
RandyM

 

[tina]

in the "linked" table, you can set the default value of the control bound to
the foreign key field, to the value of the primary key in the main form. the
simplest way would be to set the control's Default property in the linked
form's Load event procedure, as

Me!ForeignKeyControlName.DefaultValue =
Forms!MainFormName!PrimaryKeyFieldName

the above goes all on one line in the VBA window.

btw, your table relationships aren't quite clear to me. is OwnerID a foreign
key field in AnimalTable? and is AnimalID a foreign key in FeeTable?

hth

 

[Guest]

Thanks Tina for the answer. I was out of the office all weekend, thank god,
and have just logged on. I'll see if that works. The FK stands for 'foreign
key' and the OwnerID-AnimalTable is the foreign key for the Animal Table to
link to the Owner Table and the AnimalID-FeeTable is the foreign key to link
the Fee Table to the Animal Table.

Thanks again,
Randy M

 

[tina]

you're welcome.
btw, it's not a good idea to use hyphens or other special characters
(excepting only the underscore) in table names. see
http://home.att.net/~california.db/tips.html#aTip5 for more information.

hth

 

[Guest]

Tina,
That seems to work. At least the right ID number from the Owners Form is
now appearing in the Animals Sub-form. But, I'm very curious as to why the
wizard didn't make this happen. It seems to link it right when I use the 'use
subforms' option in the Form Wizard, but not if I use the use "linked forms'
option. Do you have any idea why? It makes it kinda 'scary' when you really
aren't sure what you're doing and you can't depend on Access to do it the
right way for you. I wonder what might 'bite me' down the road that looks
right, but really isn't. Oh, well.

Thanks again for your help,
Randy M

 

[tina]

well, form and report wizards aren't perfect, and the more complex the task,
the more un-perfect they can be. just keep in mind that when you use a
wizard, you need to test the result thoroughly - just as you would have to
test your own design and code - and tweak it as needed. rather than looking
on this as a drawback, consider it an opportunity: if you think of the
wizard as simply a labor-saving device, that quickly throws together objects
so you don't have to do the "scut work" manually, then your expectations
will be pretty much met; and you will have more time to concentrate on
testing and trouble-shooting the design - which is actually a great way to
learn how things work in Access. happy developing!

 

[Guest]

Thanks tina. I do appreciate your time. And, I'm finding out just what you
are saying. I have learned a great deal about Access by doing it, it not
working, asking questions, trying the answers, etc.etc. It helps. Sometimes
it's just a bit frustrating when I'm pretty sure I know what the problem is,
I just don't know how to 'fix' it in Access. But, I'll get there eventually
with help from you and all the other knowledgeable, patient, and nice people
who are willing to give of their time to help 'newbies' like me. Thanks so
much.

RandyM

 

[tina]

you're very welcome

Thanks tina. I do appreciate your time. And, I'm finding out just what you
are saying. I have learned a great deal about Access by doing it, it not
working, asking questions, trying the answers, etc.etc. It helps. Sometimes
it's just a bit frustrating when I'm pretty sure I know what the problem is,
I just don't know how to 'fix' it in Access. But, I'll get there eventually
with help from you and all the other knowledgeable, patient, and nice people
who are willing to give of their time to help 'newbies' like me. Thanks so
much.

RandyM