Limiting combo box selections.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables
tblProfile
tblFacilitie

I've successfully created a one-to-many relationship. I can have one profile in many facilities

I have a third table to store data
tblProfilesFacilitie

I have a combo box in a subform for my parent frmProfiles in order to select a facility from tblFacilities. This works fine, however, I'm able to select a warehouse more than once for any particular Profile

How can I select a facility only once for any particular Profile?
 
If I understood you correctly, you may try setting your primary key in
the Facilities table to a combination of FacilityID and ProfileID.

I am totally at loss as to where tblProfilesFacilities comes into play.
If it is a junction table, please post all relevant field names for all
tables involved.

Pavel
 
Thanks, Pavel

Yes - tblProfilesFacilities is a junction table

Here's the relevant tables and field names
tblProfile
txtProfilesID

tblFacilitie
txtFacilityID
FacilityNam
Cit

tblProfilesFacilitie
numProfilesFacilitiesID
txtProfilesI
txtFacilityI

* indicates primary ke

I have a subform with tblProfilesFacilities as its record source. A combo box in this subform queries txtFacilityID*, FacilityName and City. I want to select MANY txtFacilityID's for each txtProfileID but only ONCE. As it is now I can select the same txtFacilityID countless times for the same Profile

Thanks


----- Pavel Romashkin wrote: ----

If I understood you correctly, you may try setting your primary key i
the Facilities table to a combination of FacilityID and ProfileID

I am totally at loss as to where tblProfilesFacilities comes into play
If it is a junction table, please post all relevant field names for al
tables involved

Pave

JohnLute wrote
 
Try making the primary key in the tblProfilesFacilities a compound key
that includes all three fields:

numProfilesFacilitiesID
txtProfilesID
txtFacilityID

This is a brute force approach, which will trigger Access warning that
you are trying to create a duplicate primary key and will disallow that.
A more user friendly approach would be to include code in the form to
check for existing foreign key combination in the BeforeUpdate event:

Dim Rst as ADODB.Recordset
Set Rst = New ADODB.Recordset
Rst.Open "SELECT numProfilesFacilitiesID FROM tblProfilesFacilities
WHERE txtProfilesID = '" & Me.txtProfilesID "' AND txtFacilityID = '" &
Me.txtFacilityID & "'", CurrentProject.Connection, adOpenKeyset, adLockReadOnly
IF Rst.RecordCount > 0 then
MsgBox "This facility already exists for this profile."
Cancel = True
end if

This code assumes that you have text boxes named txtProfilesID and
txtFacilityID on the form; if not, change these names accordingly. Note
that this will not stop the user from entering the duplicate information
directly in the table, so I would do both this and the compound PK.
Hope this helps,
Pavel
 
Thanks, Pavel

I made the compound key but can still create duplicate records

I want to try the code you provided but my subform only uses one combo box (Combo6) with txtFacilityID as its control source

I need the txtFacilityID field to be indexed and disallow duplicate records - no problem doing that. However, this also disallows me from choosing the same txtFacilityID for other Profiles

I hope you can help me through this because it's really the last big issue I have with my database structure and I'm anxious to resolve it

Thanks

----- Pavel Romashkin wrote: ----

Try making the primary key in the tblProfilesFacilities a compound ke
that includes all three fields

numProfilesFacilitiesI
txtProfilesI
txtFacilityI

This is a brute force approach, which will trigger Access warning tha
you are trying to create a duplicate primary key and will disallow that
A more user friendly approach would be to include code in the form t
check for existing foreign key combination in the BeforeUpdate event

Dim Rst as ADODB.Recordse
Set Rst = New ADODB.Recordse
Rst.Open "SELECT numProfilesFacilitiesID FROM tblProfilesFacilitie
WHERE txtProfilesID = '" & Me.txtProfilesID "' AND txtFacilityID = '"
Me.txtFacilityID & "'", CurrentProject.Connection, adOpenKeyset, adLockReadOnl
IF Rst.RecordCount > 0 the
MsgBox "This facility already exists for this profile.
Cancel = Tru
end i

This code assumes that you have text boxes named txtProfilesID an
txtFacilityID on the form; if not, change these names accordingly. Not
that this will not stop the user from entering the duplicate informatio
directly in the table, so I would do both this and the compound PK
Hope this helps
Pave

JohnLute wrote
 
Duh! Its my fault. I am very sorry, I got sidetracked and forgot that
numProfilesFacilitiesID is an AN - of course the compound of it with
anything will be unique.
Is using just

txtProfilesID and txtFacilityID

(the two FKs) as the compound key will be a problem in your design? This
would definitely disallow duplicates. You can still use
numProfilesFacilitiesID as the unique identifier for the records in the
junction table, index it and everything.
As far as adapting the code I suggested to use with a combo box, I think
this will do:

Dim Rst as ADODB.Recordset
Set Rst = New ADODB.Recordset
Rst.Open "SELECT numProfilesFacilitiesID FROM tblProfilesFacilities
WHERE txtProfilesID = '" & Me!txtProfilesID "' AND txtFacilityID = '" &
Me.Combo6 & "'", CurrentProject.Connection, adOpenKeyset, adLockReadOnly
IF Rst.RecordCount > 0 then
MsgBox "This facility already exists for this profile."
Cancel = True
end if
Rst.Close
Set Rst = Nothing

This assumes that you do have the field txtProfilesID in the form's
record source, and this code goes into the BeforeUpdate event of Combo6.
Good luck,
Pavel
 
Back
Top