Tags: access, atomicand, autonumberfield, boxes, combinations, combo, considered, create, database, julianthe, key, microsoft, mysql, oracle, point, primary, sql, system, table, unique

How to create unique combinations in a table using combo boxes

On Database » Microsoft Access

3,369 words with 1 Comments; publish: Fri, 30 May 2008 12:18:00 GMT; (25062.50, « »)

Julian:

The point of a primary key is that it must be atomic

and unique. Have you considered using a system autonumber

field for your primary key? That would leave your users

free to change the 3 fields thus making your job

considerably easier.

Hope this helps

>--Original Message--

>I have a continuous form in which the user is able to

specify 3 fields

>(Category, Transport and operator) using combo boxes.

However, these

>fields are used as the primary key for the underlying

(bound) table.

>Consequently, everytime the user adds or modifies one of

these fields

>I need to be able to populate the combo list with only

those values

>that will not create a duplicate record.

>e.g. if my form contains:

> Category Transport Operator

>1. Air 747 Monarch

>2. Air 737 Monarch

>3. Air 737 Lufthansa

>when I click on the the operator combobox for record 3,

it shouldn't

>show me Monarch, because I have that combination

already. Similarly,

>the transport dropdown for record 1 shouldn't show me

737, because I

>already have that.

>The transport values are stored in one table, with their

associated

>categories, and the operators are stored in a separate

table.

>I keep thinking I've nearly cracked it, but I'm still not

there yet.

>One problem I have is that, if you change the transport

for one record

>and then click the operator combo for the same record the

selection

>list is wrong because the underlying table has not been

updated.

>I'm also struggling to identify which event to use for

the code that

>changes the combo recordsource.

>Any thoughts on this gratefully received!

>Julian

>.

>

All Comments

Leave a comment...

  • 1 Comments
    • Unfortunately, the requirements of the system are such that the table

      must not contain more than one record with the same combination of

      Category, Transport and Operator, hence the need to adjust the combo

      box lists dynamically.

      Julian.

      "aday" <anonymous.ms-access.todaysummary.com.discussions.microsoft.com> wrote in message news:<0c0301c4a7eb$25b30960$a501280a.ms-access.todaysummary.com.phx.gbl>...[vbcol=seagreen]

      > Julian:

      > The point of a primary key is that it must be atomic

      > and unique. Have you considered using a system autonumber

      > field for your primary key? That would leave your users

      > free to change the 3 fields thus making your job

      > considerably easier.

      > Hope this helps

      > specify 3 fields

      > However, these

      > (bound) table.

      > these fields

      > those values

      > it shouldn't

      > already. Similarly,

      > 737, because I

      > associated

      > table.

      > there yet.

      > for one record

      > selection

      > updated.

      > the code that

      #1; Fri, 30 May 2008 12:19:00 GMT