N
Nathan-bfhd
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.
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.