AddNew with variable field names

[KMKelly]

My goal. I have a list of Physician names with SVDT, appt time, duration, type, and status. I need to get it into a report where I can say for each specific date that a physician saw patients - how many did they see, how many were originally scheduled etc. (easy) - but then it needs to go across horizontally with each time period scheduled and with each time period say the appointment type, duration, and status. Across it needs to be in order by time and sequential ( I can't just leave gaps where they had an hour off). There can be up to 15 different scheduled appointments and sometimes more that one scheduled at the same time, which I would show one after the other across.

Dr. A 8:00AM 8:30AM 9:30AM
45 FU Arr 30 NE Can 15 FU Arr

Dr. B 11:00AM 1:00PM
90 FU Arr 30 NE Nos

Above is the final desired result. At 8:00AM Dr. A had a 45 minute follow-up that was arrived and at 8:30 had a 30 min new patients that was cancelled.

My way of doing this was to create a table with repeating column groups for the four elements (ie column for Time1, Time2, etc. Dur1, Dur2 etc..) I am trying to add records to a table using the AddNew command within a Do While loop that will move through the original recordset. My plan was to use a integer variable to track which of the columns it should edit. If intx = 1 then T1 is the appropriate time field, but if intx = 3 then T3. I can't figure out a way for it to accept the variable field names though. Below are some attempts.

With rst2 'this is the table I am adding to
.AddNew
!Name = vProv
!SVDT = vSVDT
!PT_Tot = intx
1 !Fields(testint) = rst1(2)
2 !fld2 = rst1(3)
3 !("A"&trim(str(intx)) = Left(rst1(4), 2)
.Update
End With

1 - Tried calling the position number of the field from the Fields collection, T1 is the 6th over starting at zero. So testint in this case would be 6.

2- Tried setting a field variable in advance even setting it directly to the name

3- Tried putting in the string name in this case A1 for appointment type

Nothing seems to work. It wants me to put in the actual field name and with 60 fields that is not a good thing. I always get an Item not in this collection error.

Any ideas would be appreciated.

Kristin

 

[psemianonymous]

Re-work your table. The end result (your "table of appointments by day/week" list) can be achieved via a crosstab query. Store your information like so:

[Appointment table]:
    -ID (autonumber)
    -Name
    -SVDT
    -TimeIn  --stores hours and minutes only
    -TimeOut --stores hours and minutes only
    -ApptDate -stores date only

Now, also make an "appointment block" table which is filled with all the available appointment times, each block being 30 minutes. i.e. the first block would begin at 8:00AM and the last at 4:30PM, assuming an 8-5 practice.

[TimeBlocks table]:
    -ID (autonumber)
    -TimeStart (i.e. 8:00AM)
    -TimeEnd   (i.e. 8:30AM)

Now make a query which selects * From the timeblocks table. This will link to the Appointment table WHERE "[Appointment.TimeIn] BETWEEN [TimeStart] AND [TimeEnd] OR TimeOut BETWEEN [TimeStart] AND [TimeEnd]"

This should produce a list of ALL blocks occupied by a single appointment. This assumes that one appointment will last 30 minutes.


This looks harder than what you have set up right now, but I think if you get the gist of what I'm saying, you'll do better off with a "single entry" solution like I have proposed. What you have will be troublesome down the road--especially if you are ever going to have to write a report based on this information.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm