By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,727 Members | 1,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,727 IT Pros & Developers. It's quick & easy.

PASSING KEYS

P: n/a
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
===
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
am*******@verizon.net (amywolfie) wrote in
news:33**************************@posting.google.c om:
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.


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!OtherForm!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
Nov 13 '05 #2

P: n/a
On Tue, 06 Jul 2004 21:00:04 GMT, "David W. Fenton" <dX********@bway.net.invalid> wrote:
am*******@verizon.net (amywolfie) wrote in
news:33**************************@posting.google. 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).


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
Nov 13 '05 #3

P: n/a
Wayne Gillespie <be*****@NObestfitsoftwareSPAM.com.au> wrote in
news:ju********************************@4ax.com:
On Tue, 06 Jul 2004 21:00:04 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
am*******@verizon.net (amywolfie) wrote in
news:33**************************@posting.google .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).


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?


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
Nov 13 '05 #4

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

amy
===
"David W. Fenton" <dX********@bway.net.invalid> wrote in message news:<Xn**********************************@24.168. 128.74>...
am*******@verizon.net (amywolfie) wrote in
news:33**************************@posting.google.c om:
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.


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!OtherForm!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).

Nov 13 '05 #5

P: n/a
am*******@verizon.net (amywolfie) wrote in
news:33**************************@posting.google.c om:
Thanks a lot, David! Do you also do FileMaker? I seem to recall
seeing your name on the FMP boards.


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
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.