PASSING KEYS

[amywolfie]

In FileMaker, it's very simple to pass a PK in one table to a FK in
another by using Global fields and setting them to the PK.

Is there an equivalent way to pass keys in Access?

I don't want to use a sub-form to create a new record -- I would like
to create the new record on a separate form where a PK key is passed
to a FK.

Thanks!
amy
===

[David W. Fenton]

Re: PASSING KEYS

amywolfie@veriz on.net (amywolfie) wrote in
news:33651c5d.0 407060837.668d4 aca@posting.goo gle.com:
[color=blue]
> In FileMaker, it's very simple to pass a PK in one table to a FK
> in another by using Global fields and setting them to the PK.
>
> Is there an equivalent way to pass keys in Access?
>
> I don't want to use a sub-form to create a new record -- I would
> like to create the new record on a separate form where a PK key is
> passed to a FK.[/color]

If the other form is open and has the FK field in its recordsource,
you don't have to pass it -- all the fields and controls in the
other form are availabe to you. Say your field in the other form was
called FK, in your main form (where the PK is), you'd simply do this
in code:

Forms!OtherForm !FK = Me!PK

That's assuming that you've navigated in the other form to a new
record, since you don't want to overwrite the existing FK value of a
different record.

If you know that the main form will always be open when you create a
new record on the main form, you could have a control on the other
form (visible or not) that has a default value of Forms!MainForm! PK.

The key point here is this:

As long as a form is open, all the data in it is available from any
other form.

This is accomplished because each form has collections and the
default collection for every form is a combination of the Fields in
the form's recordsource and the controls on the form.
Forms!OtherForm !FK is a shortcut for Forms.Fields!Ot herForm!FK, or
if you have a textbox on OtherForm that displays the FK,
Forms.Controls! OtherForm!txtFK (it's always a good idea to name the
controls something different from the underlying field name because
otherwise there's a built-in ambiguity as to which one you're
updating -- and it can make a difference).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Wayne Gillespie]

Re: PASSING KEYS

On Tue, 06 Jul 2004 21:00:04 GMT, "David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote:
[color=blue]
>amywolfie@veri zon.net (amywolfie) wrote in
>news:33651c5d. 0407060837.668d 4aca@posting.go ogle.com:
>
> (it's always a good idea to name the
>controls something different from the underlying field name because
>otherwise there's a built-in ambiguity as to which one you're
>updating -- and it can make a difference).[/color]

Assuming the control is bound to a single field in the recordsource, ie not a calculated field, I have yet to see a
situation where having the control / field names the same makes the slightest difference at all.
Do you have an example?


Wayne Gillespie
Gosford NSW Australia

[David W. Fenton]

Re: PASSING KEYS

Wayne Gillespie <bestfit@NObest fitsoftwareSPAM .com.au> wrote in
news:jueme0l9es 14l6mgbhfk3esnh kqhmf676e@4ax.c om:
[color=blue]
> On Tue, 06 Jul 2004 21:00:04 GMT, "David W. Fenton"
> <dXXXfenton@bwa y.net.invalid> wrote:
>[color=green]
>>amywolfie@ver izon.net (amywolfie) wrote in
>>news:33651c5d .0407060837.668 d4aca@posting.g oogle.com:
>>
>> (it's always a good idea to name the
>>controls something different from the underlying field name
>>because otherwise there's a built-in ambiguity as to which one
>>you're updating -- and it can make a difference).[/color]
>
> Assuming the control is bound to a single field in the
> recordsource, ie not a calculated field, I have yet to see a
> situation where having the control / field names the same makes
> the slightest difference at all. Do you have an example?[/color]

Well, my principle is this:

I disambiguate the name of any controls from the underlying field
they are bound to if the control (or field) is referred to in code
or in any expression in any part of another control.

The reason is not to avoid the #NAME problem, but simply to make it
clear whether you're working with a control or a field. I don't want
to leave it up to VBA to figure out that when I write FK.SetFocus
that I mean the control not the field (which would give an error).
I can't say for certain, but I think that sometimes VBA is *not*
able to tell which I mean, and gives an error.

I see it as good coding practice.

And given the problems A2K has with references to fields vs.
controls that are from child subforms, I think it's pretty important
to work that way, to be certain that you're always referring
unambiguously to one or the other.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[amywolfie]

Re: PASSING KEYS

Thanks a lot, David! Do you also do FileMaker? I seem to recall
seeing your name on the FMP boards.

amy
===


"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message news:<Xns951EAE 4AC76F8dfentonb waynetinvali@24 .168.128.74>...[color=blue]
> amywolfie@veriz on.net (amywolfie) wrote in
> news:33651c5d.0 407060837.668d4 aca@posting.goo gle.com:
>[color=green]
> > In FileMaker, it's very simple to pass a PK in one table to a FK
> > in another by using Global fields and setting them to the PK.
> >
> > Is there an equivalent way to pass keys in Access?
> >
> > I don't want to use a sub-form to create a new record -- I would
> > like to create the new record on a separate form where a PK key is
> > passed to a FK.[/color]
>
> If the other form is open and has the FK field in its recordsource,
> you don't have to pass it -- all the fields and controls in the
> other form are availabe to you. Say your field in the other form was
> called FK, in your main form (where the PK is), you'd simply do this
> in code:
>
> Forms!OtherForm !FK = Me!PK
>
> That's assuming that you've navigated in the other form to a new
> record, since you don't want to overwrite the existing FK value of a
> different record.
>
> If you know that the main form will always be open when you create a
> new record on the main form, you could have a control on the other
> form (visible or not) that has a default value of Forms!MainForm! PK.
>
> The key point here is this:
>
> As long as a form is open, all the data in it is available from any
> other form.
>
> This is accomplished because each form has collections and the
> default collection for every form is a combination of the Fields in
> the form's recordsource and the controls on the form.
> Forms!OtherForm !FK is a shortcut for Forms.Fields!Ot herForm!FK, or
> if you have a textbox on OtherForm that displays the FK,
> Forms.Controls! OtherForm!txtFK (it's always a good idea to name the
> controls something different from the underlying field name because
> otherwise there's a built-in ambiguity as to which one you're
> updating -- and it can make a difference).[/color]

[David W. Fenton]

Re: PASSING KEYS

amywolfie@veriz on.net (amywolfie) wrote in
news:33651c5d.0 407070838.7e74e de0@posting.goo gle.com:
[color=blue]
> Thanks a lot, David! Do you also do FileMaker? I seem to recall
> seeing your name on the FMP boards.[/color]

Heh. No, I don't use FM, but I was recently involved in a very long
discussion about the relative merits of FM and Access in which I was
accused of all sorts of things.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc