Need code that changes status and date stamps records 1

[blue1914mab]

I am a beginner at Access, but I am learning.
My database houses patient records and account info. I have full security set up, requiring UserID and password of employees as they log into database. The “frmPatientRecord” displays each individual patients information. I need code that (via. a button on that form) will 1) assign that specific record to the user (based on UserID), 2) change the status of the record, and 3) stamp the current day (short date style). The employee determines what record they would like to be assigned to. Once they have opened the record, they should be able to hit the “Assign” Button.

I have added three fields to the "tblPatientRecord" that deal with assigning the record to and employee. 1) “Assign Date” 2)”Assigned To” 3)”Status”

“Assign Date” – Date field that should be populated with the date the record is assigned.
“Assigned To” – This is a foreign key field related to the “tblEmployee” field “SSN”
"StatusID" -- Foreign key with three options 1)Open 2) Non-Qualify 3)Assigned. The default is "Open". The status should change form Open to Assigned.

I also need for them to be able to select another button on the same form. That does just the opposite. Once the employee has done their part, when they select the “Done” button, it un-assigns the record and puts it back to “Open” status, removes their UserID and stamping the date again.

 

[rubbernilly]

I am going to assume for the moment that the way the users select records is by highlighting a listbox (for example). If there are three columns in the listbox (PatientID, PatientName, Description), with the first column being the bound column, then you can use an update query in VBA.

dim sEmpSSN as string

sEmpSSN = nz(Dlookup("SSN", "tblEmployee", "EmpSignInField = '" & CurrentUser & "'"), "")

if sempssn = "" then
   msgbox "No such employee."
   exit sub
end if

DoCmd.RunSQL "UPDATE tblPatientRecord " & _
             "SET [Assign Date] = Date(), [Assigned To] = '" & _
             sEmpSSN & "', [StatusID] = 'Assigned' " & _
            "WHERE PatientID = '" & Me.lstPatients & "'"

Obviously, I've made some assumptions about your field names and data types, but you get the idea.

HTH

 

[blue1914mab]

Thanks HTH for your response. I appreciate you for that.

I can't figure the last line of the code "WHERE PatientID = '" & Me.lstPatients & "'". Not exactly sure how that applies, and I'm not quite sure what "EmpSignInField is. I dont have a field like that, unless it means the UserID. That, I DO have in the Employee table.

When the user is selecting the record they want, they have opened the "frmPatient Record", a single record with specific info of that patient.

I was wrong in saying that the 3 fields are on the "tblPatientRecord", because two are not. "StatusID" is a foreign key. "Assign To", is a foreign key to tblEmployee using EmployeeID as the control source, and its row source reads: SELECT Employees.LastName FROM Employees;

I can reconfigure, if you feel I need to.
Please Advise.

 

[rubbernilly]

hey, blue...

No need to reconfigure yet. I probably just don't understand enough to speak in terms that make sense for you database. The text of my message outside of the code explained some of the assumptions I was making, ie, that users selected the patient record from a listbox (Me.lstPatients), whose bound column would have uniquely identified the patient record. And, yes, the EmpSignInField was just a place holder for you replace with your own field name (UserID).

So long as you have a dynaset for your recordsource for the form you are dealing with, you can run your Update Query against that. It won't matter that they are in different tables. We can make it work.

How about you list the tables involved, their fields, and which fields are on the form in question? That might help us to speak the same language.



BTW, "HTH" = "Hope This Helps"

 

[blue1914mab]

Sorry for calling you HTH, (LOL), Remember, I'm new at this. OK, The three tables involved are 1)tblPatientRecored 2) tblEmployee 3) tblStatus.

1) tblPatientRecored applicable fields: PatientID, EmployeeID, StatusID, Barcode, Assign Date, Assign To.

2) tblEmployee applicable fields: EmployeeID, SSN, FirstName, LastName, UserID, strEmpPassword

3) tblStatus fields: StatusID, Status

Form: frmPatientInfo
Record Source: tblPatientRecord, Single Form, Dynaset.

Applicable fields: PatientID, StatusID, EmployeeID, Assign Date, Assign To, Due Date (This should be working days past Assign Date.

Im not sure about the listbox and the bound collumn part.

 

[rubbernilly]

OK, now we're getting somewhere. Since you have the form open in front of you and it has controls bound to the fields you need to update, you should just be able to set the values of those controls. In the click event of the Assign command button:

me.employeeid = nz(Dlookup("SSN", "tblEmployee", "UserID = '" & CurrentUser & "'"), "")
me.AssignDate = Date()
me.StatusID = 3 'assigned


I don't know why you had EmployeeID and Assign To as fields on the form. I thought you might have doubled that one up, and so my code above works with the EmployeeID field. If I misunderstood and the field that really needs to be set is "AssignTo", just replace that in the code where I set EmployeeID.

This should get the fields on the form populated with the proper information. Once there, you can save the record to apply the changes. I would suggest that you set these fields to be Locked and not in the tab order for the users so that they cannot enter information in them except by clicking on your button.

To do your UnAssign button, just change the code where appropriate.

...........

Now, read on only if you want to understand the situation/setup I was imagining. It is a suggestion, but not necessarily any better or worse than what you have, so take it for what it is worth.

I imagined that your form was unbound and that you had a listbox with the following properties:

Columns = 1
BoundColumn = 1
RowSource = "SELECT PatientID FROM tblPatientRecord " & _
"WHERE StatusID = 3"

The user would choose a patient from the listbox and then click on the button. You would need to update the particular record selected in the listbox. To do that, you would reference the bound column of the listbox in your UPDATE query (in the first bit of code I offered).

This way, the users would be able to see all of the patients available for assignment at once (on one form), and then assign the one they wanted to. They would have a separate form which would list all of the various patients they might have assigned to them.

If you liked this format, you might want to pull in more fields into the RowSource of the listbox ("PatientName", if it exists, for example, and maybe a "Condition" if that exists - I'm just using my imagination here). Set your column count and play with the column widths property of the listbox to get the information to display correctly (maybe even hiding the bound column).

Like I said, a suggestion. At the very least, the code at the top of this post should work for you.

Good luck!

 

[blue1914mab]

Ok, I made a few modifications to what you sent me. And it is working good. This is what I did

Me.Status = "CHECKED-OUT"
Me.Check_Out_Date = Date
Me.EmployeeID = Nz(DLookup("LastName", "tblEmployees", "UserID = '" & CurrentUser & "'"), "")
DoCmd.RunCommand acCmdSaveRecord

Now I need to know how to create another button that does just the opposite when selected. It needs to made the Status field = "Available", Blank out the Employee ID. And Populate the "User_Returned_File" with the date the button was clicked.

Also, how do I make it so that once the "Check Out" button is clicked the info is not modified by another user, and can only be modified again once the "Return" button is selected.

I forgot to mention that you have been a wonderful help to me and I appreciate that much.

 

[rubbernilly]

It is a fairly straightforward thing to change the fields again from a different button:

Me.Status = "Available"
Me.Check_Out_Date = ""
Me.EmployeeID = ""
DoCmd.RunCommand acCmdSaveRecord


As far as the information being locked down to one user, here are a couple of suggestions.

1) Anywhere information might be modified, check the UserID of the user logged in and about to make the change. Check it against the user who has it logged out:

If CurrentUser <> me.employeeID then
msgbox "Only the user who has this checked out may make changes", _
vbokonly, "Resource Checked Out"
exit sub
end if

However, that gets a little messy when you are working with a form where there are more than just the three fields you have listed available to the person to possibly change. So...

2a) On your form, in the OnCurrent event of the form, check to see if the status is "Checked-Out". If so, set the 'Locked' property of the controls to True. Only leave the "Return" button available to click on, and make that only run if the UserID matches (as per 1 above). Then, after it sets the three fields you listed, it also sets all of the controls' Locked property to False.

Or...

2b) This is probably the way I would do it. Having a form for all of the resources is good for an admin, but can be a pain to manage if you give that to all of the users. They don't need to be able to "Return" the resources checked out by another person. Since you already have security enabled, you can limit their ability to see that form. Only let the admin group see it (or, if you are doing your own form of security, a group that you can identify, either by matching a field in the Employee table, or by a UserID match)

If CurrentUser In (UserID1, ID2, ID3) Then...


However you determine if the person needs to be able to see the form un-restricted, set a string variable as a WHERE condition.

Restricted:
sFilter = "StatusID <> 'Checked-Out'"

Unrestricted:
sFilter = ""

Then open the form:
docmd.OpenForm sFormName, acNormal, , sFilter

Admins will get an unrestricted record source and be able to return resources signed out by other users, but individual users will only see new resources that they can check out.

The final part of this suggestion is that a new form would need to be created where you show all of the resources signed out by any particular user. The recordsource for that form would look something like...


SELECT * FROM tblPatientRecord WHERE EmployeeID = '" & Nz(DLookup("LastName", "tblEmployees", "UserID = '" & CurrentUser & "'"), "") & "'"

That way, each user will see the patient resources assigned to them.

............

Some different options for you to think about. Some of these ideas will work together, and some are redundant if you do them both, so decide which way you want to go and take what you need.

Good luck!

 

[blue1914mab]

Thanks once again. You are AWESOME!!!
Currently I have a page for the user that shows all their specific info. When they logon to the database it is the first page they get. I dont have a traditional "Switchboard". This page is constantly loaded (unless they close it) How do I get it to auto update, when they have hit the checkout or return buttons of the patient record file that we have place the two buttons on.
I have a subform that, shows the records they have as a result of them clicking either button. The only problem is, currently I have to close the form and open it back up to show the updated changes.

Any suggestions?

 

[rubbernilly]

If the form you are speaking of (with regards to each user) has a subform that displays in continuous-form or datasheet format the patients assigned to that employee... then you should have in the subform object's recordsource some provision that ties the patients to the User on the main form. In this case, you just need to requery the subform...

me.subformcontrolname.requery

But with selecting the resource to checkout from a different form, you might want to wait (not try to run that requery command from the other form). Put it in the OnActivate event of the main form tied to the user.

If data on that main form might also change, then you can also add a

me.requery

...to that same event.

 

[blue1914mab]

My new and dear friend Rubbernilly, as I'm sure you are aware, I am new at this. That just went over my head (LOL). You got me in the kitchen making coffee to calm my nerves, and I dont drink coffee. Just kidding.
I got the first part of your opening statment, however, I did not get the second. The answer to the first part is "Yes". Where do I add the me.subformcontrolname.requery

Im simple! I'm excellent in concept, dificulty is with application because of my lack of experinece with this type of coding. Most of my coding was with FileMaker years ago.

 

[rubbernilly]

Sorry 'bout that. Let me explain a little.

If you have a main form for each employee and a subform on that main form that represents the patients assigned to them, then the two are going to have to be tied together. You do this through the LinkChildFields and LinkMasterFields property of the subform control.

If both forms are bound, then you should get a nice little dialog box that lets you pick the fields that will link the two forms. If the main form is not bound, then you won't be able to use the wizard, but you can still reference a control on the main form:

LinkMasterField: Forms!MainFormName.ControlName
LinkChildField: FieldName

Once you do that, the data in the subform will always match the User of the main form. That will enable a requery of that object to go out and get the new data assigned to the Employee.

............

If you select and assign patients from a different form than the Employee form that opens on startup, then I am suggesting you put the Requery commands in the OnActivate event of the Employee Form.

This way, when they return to the form after having assigned patients, the form will requery.

Me.subformcontrolname.requery

will requery the subform

Me.requery

will requery the main form (if you need to do that).

Does that make it any clearer?

 

[blue1914mab]

It's me again. I am getting this error when I place the me.requery in the OnActivate on employee form. It points to the 13th line "Me.Bookmark = strBookMark"

Private Sub Form_Activate()
Me.Requery
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim rs As DAO.Recordset
Dim strBookMark As String
Dim strUserID As String
strUserID = "'" & CurrentUser & "'"
Set rs = Me.RecordsetClone
rs.FindFirst ("UserID = " & strUserID)
strBookMark = rs.Bookmark
Me.Bookmark = strBookMark
rs.Close
Set rs = Nothing
End Sub

 

[PHV]

Dim strBookMark As Variant

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886