updating a specific field

Topic

New Reply

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!

Reply | Quote

Replies

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

Reply | Quote

Thanks Bart!
This will give me a direction to go in and that’s all I needed.

Reply | Quote