Recording how many records are on a form at a given point in time

[Robjenj]

Hi guys! I'm working pro bono on an Access db system for a wildlife hospital. We need to be able to record from the system how many patients (animals) we have every night. We have a form that shows the number of animals in-house as we admit and discharge animals, but I need to be able to record the result of the form's Count() function in a Table whenever we open the "In-house" Form. I want to have a Table showing the time the Form was opened (easy!) and the number of patients then in-house. But recording the Count function always produces 0 of course. I need a function that will record (when the form is opened) the number that the Count() function shows! Any suggestions would be MUCH appreciated!

 

[Galaxiom]

Me.Recordset.Recordcount

 

[MarkK]

This is an awkward solution that you store a count of records that happen to be in a form at an arbitrary time. Each critter presumably has an intake and a discharge date, so it would always be safer, simpler, and more reliable to calculate this number from that data when you need it. Storing it is a pain, and almost certainly guarantees that someone will change the data on which it was based, and then it will simply be incorrect.

Consider something like this to count the attendance as required . . .

Function GetCritterCount(d1 as date) as Long
   dim criteria as string
   criteria = "Intake<=#" & d1 & "# AND (Discharge>#" & d1 & "# Or IsNull(Discharge))" 
   GetCritterCount = DCount("*", "tCritter", criteria)
end function

. . . so your raw data is free to be updated with impunity, and your summary is always recalculated on demand. Safe, simple, reliable.

 

[Galaxiom]

it would always be safer, simpler, and more reliable to calculate this number from that data when you need it. Storing it is a pain, and almost certainly guarantees that someone will change the data on which it was based, and then it will simply be incorrect.

I think that is obvious but clearly the OP has a need to permanently record the number of patients each night.

We need to be able to record from the system how many patients (animals) we have every night.

There are certainly other ways to do it but getting it directly from the form is not an unreasonable choice.

 

[Robjenj]

Many thanks MarkK, and Galaxiom! Mark, Galaxiom is correct, since the objective is to produce historical records for trending and budgetary purposes. I appreciate that simply calculating your results "live" is better than recording a calculated number, but in this case we need to record the trend over several weeks. The easiest way to do this is to record the total in-house patients Count() when the In-house Form is opened each day. But how to record the number calculated by the Count?

 

[Robjenj]

The problem with MarkK's method is that it does not take into account the animals held in the hospital under observation.

 

[Robjenj]

OK, thanks again! Almost there, but not quite. We now have:-
Private Sub Form_Current()
DoCmd.OpenForm "FRM_InHouseCount", acNormal, , , acFormAdd
Forms!FRM_InHouseCount.CountTime = Now()
Forms!FRM_InHouseCount.InHouseCount = Me.Recordset.RecordCount
DoCmd.Close acForm, "FRM_InHouseCount", acSaveNo
Me.cbbOrder.SetFocus
End Sub

But this gives the erroneous answer of 19 against the correct number of 37. Any ideas why I get the wrong number?

 

[Galaxiom]

I have no idea why the count would be wrong but the numbers are suggestive. Nineteen sounds like the New Record plus half of 36 (ie 37 including the NewRecord).

I wouldn't open a form just to get the count. Just get it directly from the table or query with a DCount and write to the overnigh numbers table with an insert query.

 

[Robjenj]

Thanks, Galaxiom! Your help greatly appreciated. My use of the opening of the table was in order to generate only one count per day. (The In-House List is used by the vets to select which animal gets their attention next, so once opened it stays open for the day). However, I have to find another trigger because the In-House List is updated every five minutes. I would greatly prefer a straight time-based trigger such as "each day at 17:30" but I don't know how to write such an expression!

 

[Galaxiom]

Technically the properly normalized way to achieve this is to include the time in the Addmission and Discharge records and calculate it in the style suggested by MarkK. This allows the number of patients to be calculated for any time and date at any time and date.

This avoids having to store the count but it is relatively complicated to generate the range of answers when required because it would require a loop in code to populate a recordset.

If storing it is preferred, one way to do it daily at a particular time is to use the Windows Scheduler to open an Access database with an autorun macro to run the query.

 

[Robjenj]

Thanks again, Galaxiom! Based on what you and MarkK say, I think I'm going to go with Windows Scheduler, which will have to open the Master Client file on the server, which will then run the query, and close down on the timer. Your advice much appreciated!

 

[Galaxiom]

You could close down at the end of the procedure.