Need help setting up relationships and query for form source

[Nathan-bfhd]

---sorry about the previous post with no Message Text---

I'm running into a problem when I try to update a form.

Here's the situation: This database tracks medical facilities and one bit
of information it tracks is the Specialty(s) of the Facility (pediatrician,
cardiology, surgery, etc) I have 3 tables that this bit of the form is
working with; tbl_Facility_Info, tbl_Specialties, and a linking table
tbl_Facility_Specialties.

tbl_Facility_Info has a Facility_Id (which is the name of the Facility) and
the other basic info like Address, phone, fax, etc (primary key = Facility_Id
)

tbl_Specialties has a Specialty_Id (auto-number) and Specialty (name of the
specialties) (primary key = Specialty_Id )

tbl_Facility_Specialties has the Facility_Id to link with the first table
and Specialty_Id to link with the second (primary key = combination of the 2
fields and I've also tried it with no primary key) -- SIDE QUESTION -- which
is better for a linking table, no primary key or a combination of the 2
fields, or other???

so far so good, right?

The form I'm using allows for entry of the basic info for the
tbl_Facility_Info and has a button to click that opens another form
(frm_Facility_Specialties - which has 2 controls, a text box for the
Facility_Id and a Combo box for the Specialty) which should be used to select
and edit the specialties of the Facility. THIS IS WHERE I'M RUNNING INTO
PROBLEMS!!!

The frm_Facility_Specialties form opens fine and displays the correct
information (for instance, the form opens with 4 records displayed, all
displaying the Facility I currently have selected in the main form
(frm_Facility_Info), with the 4 different Specialties that that specific
facility has --- I have linking criteria for the open code of the button that
links the Facility_Id of both forms)

The record source of the frm_Facility_Specialties form is a select
statement, of which, I have tried 2 different ways:

Way 1) SELECT tbl_Facility_Specialties.Facility_Id,
tbl_Specialties.Specialty FROM tbl_Specialties INNER JOIN
tbl_Facility_Specialties ON tbl_Specialties.Specialty_Id =
tbl_Facility_Specialties.Specialty_Id;

Way 2) SELECT tbl_Facility_Info.Facility_Id, tbl_Specialties.Specialty FROM
tbl_Facility_Info INNER JOIN (tbl_Specialties INNER JOIN
tbl_Facility_Specialties ON
tbl_Specialties.Specialty_Id=tbl_Facility_Specialties.Specialty_Id) ON
tbl_Facility_Info.Facility_Id=tbl_Facility_Specialties.Facility_Id;

The problem I'm having is when I go to enter a new specialty. When I try
using Way 1 from above, the following message pops up as soon as I click in
the Specialty text box:

Way #1 error) Cannot add record(s); join key of table
'tbl_Facility_Specialties' not in recordset

When I try using Way 2 from above, I am allowed to click into the Specialty
combo box (which upon doing, the Facility_Id text box is auto-populated) and
select a Specialty from the combo box. But, when i try to click somewhere
else or add another specialty, I get the following error:

Way #2 error) The changes you requested to the table were not successful
because they would create duplicate values in the index, primary key, or
relationship. Change the data in the field or fields that contain duplicate
data, remove the index, or redefine the index to permit duplicate entries and
try again.

I think that this has something to do with the record source and the way I'm
setting things up. The problem is probably staring me in the face, but I
just can't seem to figure it out. If I make queries using the same select
statements as described above, I get similar errors when trying to add info
to the queries. The only one I've been able to get to work is when I choose
to include the Specialty_Id in the queries as well as the aforementioned
Facility_Id and the Specialty fields. The problem with running my form off a
query like this is that the user has no idea what Specialty_Id (it's just an
auto-number) correlates with what Specialty, so it would be virtually
unusable.

Please help! Let me know if you need more info from me.

 

[John Vinson]

On Thu, 9 Nov 2006 14:38:03 -0800, Nathan-bfhd

Answers inline.

I'm running into a problem when I try to update a form.
Here's the situation: This database tracks medical facilities and one bit
of information it tracks is the Specialty(s) of the Facility (pediatrician,
cardiology, surgery, etc) I have 3 tables that this bit of the form is
working with; tbl_Facility_Info, tbl_Specialties, and a linking table
tbl_Facility_Specialties.

tbl_Facility_Info has a Facility_Id (which is the name of the Facility) and
the other basic info like Address, phone, fax, etc (primary key = Facility_Id
)

tbl_Specialties has a Specialty_Id (auto-number) and Specialty (name of the
specialties) (primary key = Specialty_Id )

tbl_Facility_Specialties has the Facility_Id to link with the first table
and Specialty_Id to link with the second (primary key = combination of the 2
fields and I've also tried it with no primary key) -- SIDE QUESTION -- which
is better for a linking table, no primary key or a combination of the 2
fields, or other???

I'd go with the combination, unless you are linking
tbl_Facility_Specialties to yet other tables. You REALLY should have a
primary key, and this combination is a good one.

so far so good, right?

The form I'm using allows for entry of the basic info for the
tbl_Facility_Info and has a button to click that opens another form
(frm_Facility_Specialties - which has 2 controls, a text box for the
Facility_Id and a Combo box for the Specialty) which should be used to select
and edit the specialties of the Facility. THIS IS WHERE I'M RUNNING INTO
PROBLEMS!!!

I'd really suggest making frm_Facilities_Specialties a Subform of the
facility info form, using Facility_ID as the Master/Child Link Field.
This will keep the subform in synch automatically, without ANY code.

The problem I'm having is when I go to enter a new specialty. When I try
using Way 1 from above, the following message pops up as soon as I click in
the Specialty text box:

Way #1 error) Cannot add record(s); join key of table
'tbl_Facility_Specialties' not in recordset

Use a Combo Box on frm_Facilities_Specialties, and use its Not In List
event to pop up a form to enter the new specialty.


John W. Vinson[MVP]

 

[Nathan-bfhd]

John, thanks so much for your response

You suggested: "I'd really suggest making frm_Facilities_Specialties a
Subform of the facility info form, using Facility_ID as the Master/Child Link
Field. This will keep the subform in synch automatically, without ANY code."

The problem I have with that is that I'm not actually using the linking form
frm_Facilities_Specialties to get data. What I really want is the Specialty
value, and the frm_Facilities_Specialties form only has the Specialty_Id and
the Facility_Id, so I don't really understand how that would help me --
please explain if I am mistaken.

Next you suggested: "Use a Combo Box on frm_Facilities_Specialties, and use
its Not In List event to pop up a form to enter the new specialty."

I have begun to execute this procedure and see how it could work, however,
it doesn't seem very efficent (at least not how I currently have it). I have
a button that I click to add the specialty - that button opens a form that
displays the Facility_Id in a disabled text box and a combo box that displays
the current specialties for that Facility and allows me to select the
specialty desired for a new record. As is, if I go with your suggestion,
after selecting the specialty on the first pop-up form, a second pop-up for
would be opened when the Not In List code is executed and then I would have
to select the Specialty again. Like I said, not very efficient. I'm
guessing that you are picturing the initial selection coming from the subform
you suggested earlier - as I mentioned, I don't see how that would actually
work. It seems like there should be a way for me to successfully pull off my
idea of the first pop-up form without the need for the second.
Please clarify these matters if you can.
Thanks again!

 

[Nathan-bfhd]

Well, after 2 and a half days of racking my brain, searching forums, and
doing google searches, I believe I've found the answer to my problem (would
have been great if one of the Access gurus from here could have pointed this
out right away - oh well).
My problem was multi-faceted, but ultimately came down to understanding what
exactly I was trying to accomplish. I wanted to have a way to update a
Facilities Specialty. The problem I was having was due to the fact that I
was trying to include more tables than necessary in the updating process. I
had Facility_Id on one side of a linking table and Specialties on the other
(see previous posts for specific table structures). I figured that I needed
to include Facility_Id, Specialty_Id, and Specialty in my form in order to
accomplish what I desired. I tried taking the Facility_Id from the
tbl_Facility_Info table, but that was causing problems; I had also tried to
take it from the tbl_Facility_Specialties table, but things weren't working
that way either.
Ultimately, it was through the thought process of figuring out exactly what
I was trying to accomplish that brought the answer. I was simply trying to
add, remove, or change a record in the tbl_Facility_Specialties table - which
is the table that keeps track of which Facilities are connected with which
Specialties. I wasn't trying to add a NEW facility which is what happens in
the tbl_Facility_Info table and I wasn't trying to add a NEW specialty which
is what happens in the tbl_Specialties table. Once I realized I was only
needing to modify data in one table - the tbl_Facility_Specialties table, the
answer was near.

What I did to get this to work was to create a subform with the source table
being the tbl_Facility_Specialties table and linking it with the Facility_Id
field which is on both the subform and the main form (in their source
tables). I then had to deal with the usability issue - when a user needs to
update the specialty info for a facility, on the current subform, all they
would see is the Specialty_Id, which is a number that means nothing to the
average user. So, I created a combo box who's ROW SOURCE was based on my
tbl_Specialties table, which has the Specialty_Id AND the Specialty names. I
made the CONTROL SOURCE the Specialty_Id field from the
tbl_Facility_Specialties since that's the field I want to have updated.
Since the combo box had 2 fields in it and it has a default listing of the
first one, I listed the Specialties one first so that a user could see what
Specialty they are selecting. I then bound the combo box to the 2nd column
(the Specialty_Id field) which is the value I wanted saved to my Control
Source. Doing all this solved my problem. Now I only have one table I'm
trying to update and I'm still able to SEE the name of the Specialty I'm
trying to select.