Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2003
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    updating a specific field

    I have set up an appointment db in Access 97 sr1. One of the fields is for a unique PIN number. This PIN number is given to the customer at appointment creation time so they can give us the PIN number when they arrive for their appointment. Initially the users wanted to create their own PIN number. Now after entering a few thousand appointments, I have been asked to automate the generation of this number. I have free reign on the contents of the PIN number and I can generate PIN numbers for all appointment slots in advance. My question is this. How can I populate the PIN number field only in records where an appointment hasn't been made? The appointments that are already entered already have pIN numbers so I don't want them to change. I am game for any suggestions including coding. I just don't know where to start. Thanks in advance for your suggestions![img]/w3timages/icons/grin.gif[/img]

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating a specific field

    Use a recordset (type dynaset) and populate the recordset with the records that have no PIN yet.

    SELECT * FROM tblYourTable WHERE PIN <> Null;

    Loop through this recordset and update the records with the new PIN.
    Generate the PIN using a counter and check before updating the record whether the new generated PIN already existst. If it exists generate a new PIN

    In pseudocode

    counter = 1
    open recordset
    while records in recordset
    newpin = "PIN-" & cstr(counter)
    while exists new pin in database
    counter = counter + 1
    newpin = "PIN-" & cstr(counter)
    loop newpin
    update current record with new pin code
    counter = counter + 1
    next record in recordset
    loop recordset

    I hope this helps

    Bart
    Software designer

  3. #3
    New Lounger
    Join Date
    May 2003
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating a specific field

    Thanks Bart!
    This will give me a direction to go in and that's all I needed.[img]/w3timages/icons/smile.gif[/img]

Posting Permissions

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