Waiting Room Access DB

[barryhuizenga]

Hi

Thanks in advance for any assistance with this side project of mine.

The hospital I work for wants to improve patient interaction with a laboratory blood draw station by taking them from a crowded cooridor to a more comfortable waiting room.

The idea is to hire a greeter who will enter the patient's name and destination (more than 1 area draws patients from this room) and each destination will be able to bring up a screen showing the current list of waiting patients.

So far so good, but apparently I am the person responsible for programming and maintaining this little project once it's done and I am not an experienced programmer.

My concept was to create a simple database in Access2000 with limited interaction required from the greeter. He/She would simply type in the name on the form and the time in would be automatically provided.

Now I can get the reports to each area to display properly and everything else functions as I wish...except...

the most important function I need which is to update a record when a patient is called to remove them from the waiting list!

I have an update form in which the greeter would cycle through the current records and ideally, simply press the command button to insert a call time into the patient table and thereby remove themselves from the waiting list because I made the query only pull records whose "Call Time" field is null.

I know I need to use a recordset and update it, but I am not having much luck getting it to actually put the time into the record. My command button will put the time into the form field, but the records are not being updated.

Sorry for the wordiness, but I appreciate any guidance would be fan-freaking-tastic.

Thanks

BDH

[barryhuizenga]

More specific questions...

First do the Dim and Set declarations go into the procedure for the click event of my command button or should they be put in the declaration area?

Let's say the Time I want to be inserted is in tblPatient...what is wrong with this..

Dim SPBGreeter As Database
Dim recTimeCalled As Recordset

Set SPBGreeter = CurrentDb()
Set recTimeCalled = SPBGreeter.OpenRecordset("tblPatient")
tblPatient!PAT_Called = YES
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

recTimeCalled.AddNew
recTimeCalled("PAT_TimeSeen") = Now()
recTimeCalled.Update

When I attempt to click the update button, I get a type mismatch error. I have checked all of the time related fields and they are all set for Medium Tim so I am confuzzed as to why this might be occurring.

I feel close...so very close to figuring this out so any pointers would be appreciated.

BDH

[barryhuizenga]

Giggety!

OK...although this is more of a soliloquy ( :cheesy: ) than I had hoped, I am making some excellent progress.

I figured out my mismatch problem, which had to do with the priorities in my reference list.

So now I am able to update a record and the correct time is inserted into the table and the patient's name is taken off of the appropriate waiting list. Hooray for me!

Of course now I have to figure out why the program is only allowing me to update that single record.

I have a couple ideas, but feel free to pipe in.

BDH

[Yomet]

Hi barryhuizenga,

From what I understand this is what happens:
- You click a button to insert the time called into a patient's record
- This button specifies which patient you wish to modify
- What you want to do is simply update the time called (PAT_TimeSeen) field of that table

If this is correct then these hints might help.

If the button already 'knows' the patient's record ID, I assume you have an ID and that it is called PAT_ID, then I would do the following:

Dim recTimeCalled As Recordset

'NOTE you can use CurrentDB without setting a variable
Set recTimeCalled = CurrentDB.OpenRecordset("SELECT * FROM tblPatient WHERE PAT_ID = " & <Patient's ID>)
'This will select the ONE record specifying the current patient
recTimeCalled.Edit
recTimeCalled!PAT_TimeSeen = Now()
'NOTE recTimeCalled!PAT_TimeSeen is equivalent to recTimeCalled.("PAT_TimeSeen")
recTimeCalled.Update

This should set the time for that patient.

However, if the button does not 'know' which patient you want to update you will have to specify that somehow.

Also, if you don't have and ID then you will have to find and use the primary key in the WHERE clause.

Hope this helps

Yomet

[barryhuizenga]

Thank You Yomet

You have a close idea about my intentions with the program and I appreciate the assistance.

Your solution fixed my first issue nicely.

For my next trick, I am going to attempt to take any record that has been updated from being shown on the form.

I have thought of having an administration only feature that would allow someone (not the greeter) clear all the existing records.

This would however leave the greeter with the need to cycle through every record to get to the one to be called.

It would be preferable to have the update button somehow clear the record so that only records whose PAT_TimeCalled field is blank would be shown.

Again, thanks for the tip as it truly pushed the project forward.

BDH

[Yomet]

Hi again barryhuizenga,

In order to show only those records that have a blank TimeCalled you simply set the RecordSource of the form to select only those records.

I.e. if your current RecordSource is "tblPatient" you should change it to be
"SELECT * FROM tblPatient WHERE PAT_TimeSeen Is Null"

That should take care of showing only those records that have not been called in to see the doctor.

Happy coding

Yomet

[barryhuizenga]

Pardon my confusion, but how is this fit into my previous code

Set recTimeCalled = CurrentDb.OpenRecordset("SELECT * FROM tblPatient WHERE PAT_PatientID =" & PAT_PatientID)

I have tried a couple of different versions, but I either get an error or I get all the records returned.

I have also tried using a filter as in:

recTimeCalled.Filter = ("SELECT * FROM tblPatient WHERE PAT_TimeSeen is Null")

...but this doesn't filter out the records

I guess I am not using the right form for a multiple condition query statement so a poke in the right direction would be appreciated.

Thanks again.

BDH

[Yomet]

BDH,

The OpenRecordset("SELECT [...] PAT_PatientID = " & ) statement is used to select only the patient whose call time should be updated.

I can see two scenarios that are likely and if I'm wrong just tell me.

1) You have a form with a list box, let's call it lstWaitingPatients, displaying the patient's currently in the waiting room. When one is called the user selects that employee and clicks a button to set the time called.
In this case the list box containing all the patients should also contain the patient's ID somewhere. For ease of understanding let's assume that the ID is the value of the list box, i.e. the bound column. If so the full code should read something like
Set rstdb1 = CurrentDb.OpenRecordset("SELECT * FROM tblPatient WHERE PAT_PatientID =" & lstWaitingPatients)

After you update the records with the time called you should also add the line
lstWaitingPatients.Requery
to your code in order to remove the called patients.

And in order to not display the patients already called the list box's RecordSource should be
SELECT * FROM tblPatient WHERE PAT_TimeSeen Is Null

2) You are using a continus form with a button on each patient's record in order to remove that patient. In that case you need a text box (maybe hidden) that contains the patient's ID, let's call it txtPAT_PatientID, and the code should read:
Set rstdb1 = CurrentDb.OpenRecordset("SELECT * FROM tblPatient WHERE PAT_PatientID =" & txtPAT_PatientID)
OR
you could add a text box connected to the PAT_TimeSeen and simply set that text box to Now() and then save the record and requery the form.

What you could do is simply zip your application and add it to your next posting if you still have problems.

Hope this helps and happy coding

Yomet

[barryhuizenga]

Yomet...you rule the planet, seriously.

I have attached the file. It should be in a working condition as is since I have taken out the items that have not worked.

As my design specs are being changed by the patient task force who asked me to create the app ( :rolleyes: ), this is likely to get more complicated, but this update will be a handy feature to get down now.

My intention was to update a single record with the insert call time such it would not be included in the next time the "Call Patient" button was selected on the main screen.

I am open to more complicated solutions including the redesign of the database itself, if more elegant results can be produced.

Thanks again.

BDH

[Yomet]

Hi again BDH,

I have attached the modified version of your database, I simply made a copy of your Update Patient form and included the code I suggested earlier so you can see how to incorporate it. I called this form "frmUpdatePatient-Modified"

I also made a new form that would make the life easier for the users and I called this form "frmUpdatePatient-New". It is not complete but I think you will be able to figure out how to use it, should you decide that it is better.

Have fun and happy coding

Yomet

[barryhuizenga]

Interesting...thanks for the tips. I am genuinely thinking about incorporating the new version in the form since my "updated" design specs as requested from the task force makes your format quite handy.

To ensure I have absorbed the lesson contained in the "modified" form, I went through each to discover exactly how you changed the code. I changed the name of the hidden text box to PAT_ID and added the Me.Requery command to the procedure, but for the life of me I can't see anything else that was changed to cause it to function properly. :o

BDH

[Yomet]

BDH,

You're almost correct I also changed the record source of the form itself.
It used to be:
tblPatient
And now it is:
SELECT PAT_PatientID, PAT_PatientFName, PAT_PatientLName, PAT_ArrivalTime, PAT_DestinationID, PAT_TimeSeen FROM tblPatient WHERE PAT_TimeSeen Is Null;

These were the only things I had to change to make it work.

In order to get the ID into the SELECT query you had to use the name of the control containing it, not the field from the table. You could (should) also remove the code
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Since this has no effect any longer.

In order to reflect the changes to the data, i.e. the updated call time, you tell the form to requery itself and hence remove the last updated record.

Happy to have been of help

Happy coding

Yomet

[Kegtapper]

BDH,

You're almost correct I also changed the record source of the form itself. It used to be: tblPatient And now it is: SELECT PAT_PatientID, PAT_PatientFName, PAT_PatientLName, PAT_ArrivalTime, PAT_DestinationID, PAT_TimeSeen FROM tblPatient WHERE PAT_TimeSeen Is Null;

These were the only things I had to change to make it work.

In order to get the ID into the SELECT query you had to use the name of the control containing it, not the field from the table. You could (should) also remove the code DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70 Since this has no effect any longer.

In order to reflect the changes to the data, i.e. the updated call time, you tell the form to requery itself and hence remove the last updated record.

Happy to have been of help

Happy coding

Yomet

I also viewed the app and offer these considerations:

1. a checkbox to show that that patient has been called.
2. another checkbox after the patient results are done y/n

Otherwise it will continue to show patients either waiting or results not in.

aka Kegtapper

[barryhuizenga]

Thanks KT

I actually went with Yomet's new format and then I tweaked it a bit more to have it pull patients from selected areas.

I am currently working on adding some administrative reports to calculate and then show wait times for each area averaged over a date range. After that, I will include it to an admin function (passworded switchboard) that will allow the user to delete records in the future. I don't want the tables to get to large AND I don't want to have to maintain the application if possible.

Again thanks for all of the pointers.

BDH

[barryhuizenga]

OK so I found a problem

I created a query that will select all records of patients who have been called and I calculate the wait time duration using this expression in the query:

TimeDiff: [PAT_TimeCalled]-[PAT_ArrivalTime]

This returns a time interval, but it brings an error when a sum or an average of the expression is attempted on a report page footer.

From doing some research, I am presuming this has something to do with the time being stored as a Date/Time field type.

So my challenge is to come up with a way of formatting the date/time of the patient's arrival and call time in such a way that I can create an average wait time without losing the functionality I currently have.

I am testing some ideas out, but I am open to any suggestions.

Thanks

BDH

[Yomet]

Hi BDH,

I understand what you are talking about but have never encountered this problem. If the cause is what you say then I suggest trying the following:

Create a Public Function that calculates the difference for you and returns it in a Double data type.

Public Function CalcWaitTime(CallTime as Date, ArrTime as Date) As Double
   CalcWaitTime=CallTime - ArrTime
End Function

This should allow the report to do the Sum or Avg of the result. Then you Format the value as "hh:nn:ss" which will return it to the look of a Date/Time variable.

You can use this function straight in your query so you will not have to do anything extra, i.e. TimeDiff: CalcWaitTime([PAT_TimeCalled], [PAT_ArrivalTime])

Hope this helps

Happy coding

Yomet

[barryhuizenga]

Of all the potential solutions I have been exploring, this is without a doubt the most elegant. I am genuinely learning a great deal through this project so I truly appreciate being able to incorporate these tips.

I do get the TimeDiff to work properly and have displayed the elapsed time as both the double and in the hh:nn:ss format. However when I add a text box in the footer and attempt to average the elapsed times, I get "#Error" in the box.

This happens regardless of the way I format any of the boxes and whether I use:

=Sum([qryWaitTimes]![TimeDiff])

or

=Avg([qryWaitTimes]![TimeDiff])

in the footer text box, I still get the #Error.

Additionally, I added another public function in the module I created to put any null values to zero since I found someplace that mentioned it could be the cause of the problem.

Naturally it didn't fix it.

I have attached the current version of the application with the rough report I created to test the functionality.

Thanks for any points in the right direction.

BDH

[Yomet]

BDH,

This error ocurrs simply because a text box in a footer cannot contain an aggregate function (Sum, Avg, Max, Min, etc.).

What you need to do is the following, which I have done in the attached report.
- You add a field to the detail section and set it's Running Sum property to "Over Group", this field's control source should be the time waited and the result is the total time waited for the group.
- You add a field to the detail that has "=1" as control source, this field is only used to count the number of records in that group
- You add a third field to the detail that has the "=1" field as control source and you set the RunningSum property to "Over Group", this field will then contain the number of records in your group.

In the Footer you now add a field whose control source is the total waiting time divided by the number of records in the group.

As I said at the top, I have modified your report to reflect these changes. You will notice some green fields when in design mode, that's a 'flag' I set for myself when I have invisible fields on a report, otherwise it is very easy to overlook them.

The crux with Access reports is the folllowing:
- Every group header has access to the data contained in the first record in it's group
- Every group footer has access to the data contained in the last record in it's group

This is why you cannot use aggregate functions and why you have to set up counters etc.

Hope this helps

Yomet

[barryhuizenga]

Giggety...that worked nicely. The rest is gravy as I manipulate this information into the various uses I will need it for.

Thanks again.

BDH