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

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

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

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:


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

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

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

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



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:



...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

BDH,

The OpenRecordset("SELECT [...] PAT_PatientID = " & <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

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

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