Using updating recordsets? (1 Viewer)

[Taz]

Hi, I'm still a beginner in programming and the .Recordset stuff is troubling me a bit. Hope you guys can help a bit.

I have a database in which patient appointments are stored in a Table called 'appointments'. Some days we have more than one patient and on others, we have only one or none at all.

For those days with multiple patients, I'd like the database to automatically assign an ID number to each patient on that day. For example, if someone adds three patients, Adam, Bob, and Carl on May 4, Adam would have a value of 1 in the field called 'ID', Bob 2, Carl 3. If Bob is the only one to show up on May 5, [ID]=1.

I've made a count query that shows the number of patients per given appointment date. The query's name is 'DateTally' and that number is displayed in a field called 'Totals'.

I think the way to do this in vB is to execute a FOR EACH NEXT loop somehow, but my syntax ability is terrible.

Dim n as integer
n = 1
FOR EACH [Totals] IN [datetally]
IF [Totals] > 1 THEN
FOR EACH [Patient] IN [appointment]
[appointment].[ID] = n
n= n + 1
NEXT
END IF
NEXT
EXIT FOR

I know this doesn't work, but I believe Recordsets would be more useful. Any ideas? Thanks.

 

[Travis]

Try this instead:

Public Sub SetAppointmentID(byVal dDate as Date)
On error Resume Next
Dim n as Long
Dim rst as Recordset '(Dao.Recordset for 2000)

Set rst = Currentdb.Openrecordset("Select [ID] from [Appointments] where [ApptDate] = " & dDate)
rst.movefirst
If Err<>0 then Exit Sub
With rst
n = 1
do while not .EOF
.Edit
!ID = n
.Update
.MoveNext
n = n + 1
loop
End Sub

 

[Taz]

Travis

Hey, thanks for responding. I tried out your code, added an END WITH statement following LOOP, and got

Error 91 (Object VARIABLE NOT SET).

I wonder if this module has to be directly in the form from which the table derives its data.

If you have any other input, please let me know. Thanks much.

Todd