Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Unique Number (Office 97 SR2)

    Hi

    I have a database in which we enter customer machine details one of the fields is a serial no for the machines each machine has a unique serial no.

    The serial number is entered into a bound text box is there anyway I can make this field accept unique entries only,
    as there are already 300 records.

    PS What about null entries.

    Novice User

    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unique Number (Office 97 SR2)

    Open the table containing the machine details in design view.

    One way to accomplish what you want, is to make the serial number the primary key. Click in the serial number field, then click the primary key button on the toolbar (it looks like a key). The primary key can't contain duplicate values or null values.

    If you want to allow null values, create a unique (but not primary key) on the serial number field:
    Open or activate the Indexes window (View/Indexes).
    If an index on the serial number field has already been defined, set its Primary property to No and its Unique property to Yes.
    If such an index doesn't exist yet, create one by typing a name for the index in the first column of an empty row in the Indexes window (for instance SerialNo), then select the serial number field in the second column. Set the Unique property to Yes.

    If you don't want to make the serial number field into the primary key, but still don't want to allow null values, you can set the validation rule for the serial number field to Is Not Null, and enter a suitable explanation ("You <img src=/w3timages/censored.gif alt=censored border=0> fool!" or something like that) for the user in the Validation Text property.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Unique Number (Office 97 SR2)

    Hi Hans

    I tried the first two of your suggestions and got the error below. Can you elaborate please
    If you are a fool at forty, you will always be a fool

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Unique Number (Office 97 SR2)

    Hi

    Hans once again you have my undying gratitude, especially as you make it simple for us novices to understand.

    Many Thanks

    Braddy <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    If you are a fool at forty, you will always be a fool

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unique Number (Office 97 SR2)

    Hello Braddy,

    This means that errors have already crept into the table. Apparently, there are records with the same serial number. You will have to repair this manually before you can set the primary key.

    The easiest way to find the offending records is to use the Find Duplicates Query wizard:
    <UL><LI>In the database window, switch to the Queries tab.
    <LI>Click New.
    <LI>Select Find Duplicates Query Wizard, then click OK.
    <LI>In the first step of the wizard, select the machine details table, then click Next.
    <LI>In the second step, select the Serial number field, and click > to move it to the list of fields to be searched for duplicates; then click Next.
    <LI>In the third step, specify which other fields you want to display; usually you want them all, so click >> to move all fields to the right hand list; then click Next.
    <LI>In the fourth and final step, you can specify a name for the query (the suggested default name is probably fine). Click Finish.[/list]You will get a list of records. You will have to inspect them all to see which serial numbers must be modified - we can't help you there. Close and reopen the Find Duplicates query to see the result. When there are no more duplicates, the query will return an empty record set. Then you can try to set the primary key.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •