forms for : one to one relat tables

  • Thread starter Thread starter Mark r.
  • Start date Start date
M

Mark r.

Despite my research on ACCESS HELP and online MICROSOFT
knowledgebase, I am having a conceptual difficulty
understanding how forms are used to updated 2 tables that
are one-to-one. Maybe part of my answer lies in subforms,
but I don't get that either. I do not want to rewrite my
whole application in a one to many relationship of child
parent tables, so please don't go there. For whatever
reason.

Table1
AUTONUMBER index primary key
fld1 txt
fld255 yes/no

Table2
LONGINTEGER index primary key
fld256 text
fld257 y/n
fld300 text

QUESTION1: My "input" form works fine on table 1.
If I put field300 onto that form, how will the form know
that field300 is in a different table?

QUESTION2: Say my user goes to record (Docmd.gotorecord)
AUTONUMBER (say record 67) The user I inserts data into
field1 and then into field300, how do I set LONGINTEGER
to equal 67, the AUTONUMBER for that record?
 
Mark,

It sounds like your difficulty is more than 'conceptual', it does sound
design oriented, but you don't seem to want 'to go there' to get a proper
design.

Forms are not generally used to update two one-to-one tables simultaneously.
In proper design, one-to-one data items are usually stored in the same
table. Do you have any good reason not to combine these tables into one???
Your problems would then be solved, at least this one. There is a subform
solution, but it is still a work-around for what should be simple if
designed properly.

Gary Miller
 
QUESTION1: My "input" form works fine on table 1.
If I put field300 onto that form, how will the form know
that field300 is in a different table?
Click to expand...

It doesn't. Field300 WOULD BE ON THE SUBFORM, not on the mainform.

You would have the answerer-identification fields, and questions 1-250
(or whenever you run out of fields) in Table1. Form frmMyMainform is
bound to Table1 (and only to Table1).

Form frmMySubform would contain the (Long Integer) Primary Key of
Table2, linked to the Autonumber in Table1, and questions 251-330 (or
however many questions you have). You would have this Subform in a
Subform Control (a special kind of control, perhaps you haven't
actually used it yet) on the mainform. To save screen space, you may
want to put the first 250 checkboxes on one page of a Tab Control, and
the subform on another page.

The Subform Control has a "Master Link Field" and a "Child Link Field"
property. You would set the Master Link Field property to the
fieldname of the Autonumber Primary Key in Table1; the Child Link
Field would contain the name of the Long Integer Primary Key of Table2
(these may, of course, be named the same).

The Subform control facility will ensure that the data visible on the
subform is for the same unique ID as that on the mainform; new records
added on the Subform will "inherit" the value of the Primary Key
currently on the mainform.

QUESTION2: Say my user goes to record (Docmd.gotorecord)
AUTONUMBER (say record 67) The user I inserts data into
field1 and then into field300, how do I set LONGINTEGER
to equal 67, the AUTONUMBER for that record?
Click to expand...


The issue does not arise because the Subform takes care of it
automatically. You do not need to do ANYTHING in code to do this.
 
sigh......siiiiiiiigghhhhhhh
My good reason, since I need one, is that I have no unique
identifier to utilize as my "one" on my one-to-many
relationship, to start off with. Second, I want every
transaction to be uniquely documented in a printable table
for legal and documentation purposes. Thirdly, I therefore
assign a transaction number to each new customer encounter
and therefore any other tables that hold information
concerning this encounter would need to have its primary
indexed field to be that same encounter transaction
number, linked one-to-one. Fourthly, this is a free
country and I shouldn't need a reason.

I don't know whether to be forever indebted to all you
guys that go out of your way (?or are you paid?) to
respond to all these newsgroup questions and offer such
wonderful, live , support.....or whether to scream at how
frustrating it is when you all make me feel like I need
a "good reason" to do and live like I want to do and live.
and just get MY question answered. It might be one thing
if you all permitted telephone calls an dthousands of
words could be transmitted in moments. But we are limited
by these "note pages", and furthermore, you all likewise
limit your answers to brief paragraphs.

I don't want to look a gift horse in the mouth, and I
really would appreciate your help, but before you rewrite
my application, can I just get my question answered?
Later on, or as a side bar, if you want to get into a days
and weeks long back and forth debate whether I really do
infact have a unique identifier to each transaction,
whether I can legally and adequately document my data, and
whether there is actually any significant overhead and
memory sppace savings if I reorganize my data to consider
the 10% of my data which might repeat and be redundant,
then I would be glad to get into it with you, really and
truly. But first MY question please.
 
Wow......awesome reply....really, thank you...thank you.
I understand the way you wrote it. I know you probably
want to investigate whether I truly can't avoid a one-to-
one deal here, and eventually maybe we can discuss it if
you are so inclined, but follow me a little further on
this trail.....I really do appreciate it.

I realize I have to learn subforms. It doesn't sound to
hard. Subform conveniently takes care of my key updating.
It sounds great. I just have to go find out how to create
a subform.......I didn't notice a wizard for it.....the
properties screen has a "pop-up" property but I don't see
subform as a choice there......but I have a book and I
guess I'll figure it out, if I can...I'll give it a try.

But let me ask you this:
On my mainform, I have a command button which

DoCmd.Save 'saves any work done so far
DoCmd.Openform "anotherform2" 'opens a smaller window
with some other fields from
Table1 that "need" (The users
feel they need to be - not "need" to be as in a
programmer's reasons for them to "need" to be)
from a separate screen

DoCmd.Findrecord ID,acentire,false, , true,acAll, False
'that new smaller window
permits other fields from that
same table1 record to be updated

Exit


Question: is there any reason why this "Second" window
(which uses the table1 as a source) couldn't call a
subform? Or am I restricted to calling a subform from the
primary form? I should think it would make no difference.
 
mark said:
sigh......siiiiiiiigghhhhhhh
My good reason, since I need one, is that I have no unique
identifier to utilize as my "one" on my one-to-many
relationship, to start off with. Second, I want every
transaction to be uniquely documented in a printable table
for legal and documentation purposes. Thirdly, I therefore
assign a transaction number to each new customer encounter
and therefore any other tables that hold information
concerning this encounter would need to have its primary
indexed field to be that same encounter transaction
number, linked one-to-one. Fourthly, this is a free
country and I shouldn't need a reason.

I don't know whether to be forever indebted to all you
guys that go out of your way (?or are you paid?) to
respond to all these newsgroup questions and offer such
wonderful, live , support.....or whether to scream at how
frustrating it is when you all make me feel like I need
a "good reason" to do and live like I want to do and live.
and just get MY question answered. It might be one thing
if you all permitted telephone calls an dthousands of
words could be transmitted in moments. But we are limited
by these "note pages", and furthermore, you all likewise
limit your answers to brief paragraphs.

I don't want to look a gift horse in the mouth, and I
really would appreciate your help, but before you rewrite
my application, can I just get my question answered?
Later on, or as a side bar, if you want to get into a days
and weeks long back and forth debate whether I really do
infact have a unique identifier to each transaction,
whether I can legally and adequately document my data, and
whether there is actually any significant overhead and
memory sppace savings if I reorganize my data to consider
the 10% of my data which might repeat and be redundant,
then I would be glad to get into it with you, really and
truly. But first MY question please.
Click to expand...


Hi, Mark.

Firstly this is all volunteer stuff - nobody gets paid by anyone. We
all learn from each other and some are more expert in certain areas
than others.

tjupsn jasbperty kr qopftrhp kleitbxp ... oh er - As to learning to
design things in certain ways, that's nothing new. When we learned to
read and write, we had to learn certain ways of doing that - certain
rules for spelling words, constructing phrases and sentences and how to
express ideas so that others could understand.

Well, Access can't think for itself, so we have to be very particular
how we instruct it, otherwise it will completely misinterpret what we
instruct and will make a pig's ear out of our wanton deviation from its
rules. The clearer we are in writing our instructions according to its
(and general relational database) design rules, the more successful
will be the project.

On your specific point, adding Autonumber primary key and foreign key
Fields to Tables and linking them, together with the use of main and
sub Forms, shouldn't be too big a task.

Yes, you will only get snippets of information from news groups such as
this but mostly they seem to be golden nuggets, targetted directly to
your problem by others who have experienced the same situation and who
often have access to well tried solutions. Stick around and you'll be
surprised at what you will pick up from the professional designers that
will help you in designing good applications.

hth

Hugh
 
Back
Top