Some newbie help with forms

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Hi i was hoping someone could help me with a problem im having in access.

I have a many to many relationship between two tables tblPerson and
tblDoctor. These are related in a third table called tblLine which has the
primary key from tblPerson and the primary key from tblDocotr in them. These
two keys in tblLine are the primary key of that table.

Im trying, without any success, to set up a form where i can enter
information for a person, then either select an existing Doctor, and a new
one, or add several new ones.

The way ive been trying to do this is to have a form for entering data about
Person, a seperate form for entering data about Doctor, then adding the
Doctor form into the Person form as a subform. This i know is a huge
mistake, but i dont really know any other way to do it.


Please help!

Tom
 
A primary key is, by definition, unique, so it can only be
on the "one" half of a one-to-many relationship. If
tblLine is the linking (or junction) table, PKs from the
other two table are foreign keys (FK) in the junction
table. tblLine can have its own PK, but that's another
issue.
Your subform should probably be based on tblLine (the
junction table). Something you could do, which I have
done recently with help from this group, is to set up a
form (use AutoForm to get started) based on tblPerson and
a form based on tblDoctor. Enter all of the doctor and
patient information you have, each on their own forms,
then use a query based on tblDoctor as the row source for
a combo box (or list box) in the subform (based on
tblLine). Set the Combo box's Not in List event to
something like this (careful of the line breaks - the
second line of code starts with the word "Response"):
MsgBox "This doctor is not listed. Click the Add Doctor
button."
Response = acDataErrContinue

Now that you have promised an Add Doctor button, you had
best create one. If your combo box is cboDoctor, the
command button's On Click event would be something like:
'Reset the combo box to empty
Me.cboDoctor = Null
DoCmd.OpenForm "frmDoctor", , , , , acDialog
'Adds the new doctor to the combo box list
Me.cboDoctor.Requery

This code also depends on there being a command button on
the Doctor form to close it, or at least to hide it and
save the record. The Doctor form should have an On Open
(or maybe On Current) event something like:
Me.FilterOn = False
Me.Recordset.AddNew
Me.NameofControl = Null

That last line clears any named text box or other control.
Now when the doctor is not listed, the combo box directs
the user to click a button to open frmDoctor. If the user
already knows the doctor is not listed, they can click the
command button at any time.
 
Hi i was hoping someone could help me with a problem im having in access.

I have a many to many relationship between two tables tblPerson and
tblDoctor. These are related in a third table called tblLine which has the
primary key from tblPerson and the primary key from tblDocotr in them. These
two keys in tblLine are the primary key of that table.

Im trying, without any success, to set up a form where i can enter
information for a person, then either select an existing Doctor, and a new
one, or add several new ones.

The way ive been trying to do this is to have a form for entering data about
Person, a seperate form for entering data about Doctor, then adding the
Doctor form into the Person form as a subform. This i know is a huge
mistake, but i dont really know any other way to do it.
Click to expand...

Take a look at the Orders form in the Northwind sample database - it's
solving the exact same problem in a different context.

Typically you would have two forms: one based on tblPerson with a
Subform based on tblLine; and a second form based on tblDoctors. There
would be a Combo Box on the subform based on tblDoctors; in that
combo's NotInList event you could pop up the Doctors form to add a new
doctor. Search for "Not In List" at http://www.mvps.org/access for
sample code.
 
Hi John - this looked like it would be useful for me too
(post above) but I didn't get any results when I searched
http://www.mvps.org/access/ for 'not in list'. Tried it
bot with and without quotes, which may be dumb - but no
luck! - Thanks
Take a look at the Orders form in the Northwind sample database - it's
solving the exact same problem in a different context.

Typically you would have two forms: one based on tblPerson with a
Subform based on tblLine; and a second form based on tblDoctors. There
would be a Combo Box on the subform based on tblDoctors; in that
combo's NotInList event you could pop up the Doctors form to add a new
doctor. Search for "Not In List" at
Click to expand...
http://www.mvps.org/access for
 
Thanks a bunch - I'm headed there next. If you have a
chance to look over my form/table confusion post and rbw's
replies, I'd appreciate it. I'm still pecking away at it.
I will need to be able to add new Contact and ContactVeh
records in the course of entering car show registrations
so I'm sure NoInList (NotInList?) will help!

Regards, Lisa
 
Back
Top