Please, please, please Assist with Code to Count Records by User. 1

[blue1914mab]

Since I am new to Access, I will take the long way around the block to ask this simple question.

At initial logon, a "Welcome/Status" page for each user (based on their UserID) is the first page they see (there is no traditional switchboard). This page is designed to show the records they have been assigned, as well as various stats based on that.

I have an unbound txt box on this page that I would like to show how many "Patient Records" they currently have assigned to them.

These patient records are stored in another table called tblPatientInventory. On "frmSetInformation" (bound to tblPatientInventory)there is a button that when selected, changes the status to "Checked-Out" from "Available", date stamps it, and based on EmployeeID, post their last name.

What would be the code for the unbound text box on the Welcome/Status page to show how many "Total" records have their EmployeeID associated with it. This would tell them how many Patient Record they currently have assinged to them.

 

[BoxHead]

Take a look at DCount in help.

John

Use what you have,
Learn what you can,
Create what you need.

 

[rubbernilly]

I thought you had on that Welcome form a subform that displayed the patient information as are assigned to that particular user. If you do, there are many ways to get the total number from there, not least of which is just to leave the navigation buttons turned on (at the bottom of the subform ..."1 of 12", and so forth).

There is also the possibility of counting the records and displaying the total in the Footer of the subform object.

HTH

 

[blue1914mab]

Hi guys!
Rubbernilly, you are correct. The subform does list all the records assigned. In an attempt to make the Welcome screen look seemless, I took out all those controls. I have a section (boxed off) at the top of the welcome screen that has "Quick Facts" info. That is where the unbound "total" field is, along with other stats about what the user is doing. I will eventually need to be able to do reporting based on those figures.

Example: Current Total # of records, total records for the month, year, average length of time record assigned, etc.

Thats why I had asked with the other question, how do you keep a history log of every "record assignment" transaction. I figured the other stats would be based on the info stored in the log. The only exception would be the one that has "current" info.

Is that a correct assumption?

 

[rubbernilly]

OK, I can definitely understand and appreciate the seamless asthetic!

Remember you will need to update this unbound field if you assign a patient and then return to this form.

Do you have your log of record transactions built, or was that something else you were asking?

 

[blue1914mab]

No, it is not built yet. I have searched and searched to no avail. Yes, assistance with that would be greatly appreciated.

This was my Thread question: I have a button on a form that when clicked, updates the status, date stamps, and post the current user who has been assigend that record. I need to be able to keep a log, like the "Name Auto Correct Log" that shows every time that button has been selected, along with date, and user who clicked it.

I also had issue with trying to get DCount to work. I used =DCount("EmployeeID","tblpatientInventory","StatusID='CHECKED-OUT'")= "CurrentUser"
When I placed that in the Control Source for the text box, I got a ?ERROR, blinking.

 

[rubbernilly]

Try something more like this for that textbox:

=DCount("EmployeeID","tblpatientInventory","StatusID='CHECKED-OUT' AND EmployeeID = '" & DLookup("LastName", "tblEmployees", "UserID = '" & CurrentUser & "'") & "'")

Although, you might need to do this in the OnCurrent event of the Form. I'm not sure about that.

........
Now, for your log, build a table with the information you want to keep

[b][blue]Field            Type[/blue][/b]
TranID           AutoNumber
PatientID        [match existing type]
EmployeeID       String
AssignDate       Date
StatusID         [match existing type]

Set TranID as the primary key, and tie this in a relationship to your tblPatientRecords. That way, you could have a form for each patient giving their data, and a subform that would pull their log out of this new table (I'll call it "PatientLog" for now.)

To make updates to the Log, put code like this in your "Assign" and "Return" buttons. (I'm working with the code you posted from your "Assign" button, which I know might be slightly different now... new stuff I will put in red.)

[red]dim rs as dao.recordset[/red]

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

[red]set rs = currentdb.openrecordset("SELECT * FROM PatientLog")

with rs
  .AddNew
    !PatientID = [blue]me.PatientID[/blue]
    !EmployeeID = me.EmployeeID
    !AssignDate = me.Check_Out_Date
    !StatusID = Me.Status
  .update
end with

set rs = nothing
[/red]

In the above, the patientid (in blue) is my guess at what that field name is on your Assign form. Change it to be what it should be.

This code will make an entry every time you assign the record. Do something similar when you return the record.

(To run this code, you will need to reference the DAO library in your code window. In the VBE window, choose Tools>References and find the Microsoft DAO library - preferably 3.6).

HTH

 

[blue1914mab]

The LOG WORKS GREAT. Only issue this has created though, is, the check-out and the "return" become two different transactions. And because we wipe out the EmployeeId on the "Return", I dont know what to reference for the "return" transaction to the "Check-Out" transacation. Is there a way to reference the "return" to find the origional Check-out", and add a "return date field" (or something like that.

Or, better yet can a statement be made, so that a record can only be checked out once, and can not be checked out again until it has been "returned"? (Currently, everytime I hit the checkout button, it logs it in the tblTransactionLog we created.

Still having problems with the DCount.

I really really really appreciate the time and commitment you have put into helping me.

Blue

*******
Here are the codes for the "Cehck-Out" button and "Return" button.

Private Sub Check_Out_Button_Click()
Dim rs As DAO.Recordset

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

Set rs = CurrentDb.OpenRecordset("SELECT * FROM TransactionLog")


With rs
.AddNew
!PatientID = Me.BPatientID
!EmployeeID = Me.EmployeeID
!AssignDate = Me.Check_Out_Date
!StatusID = Me.Status
.Update
End With

Set rs = Nothing

End Sub

*****************

Private Sub Return_Book_Click()
Dim rs As DAO.Recordset

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

Set rs = CurrentDb.OpenRecordset("SELECT * FROM TransactionLog")


With rs
.AddNew
!PatientID = Me.PatientID
!EmployeeID = Me.EmployeeID
!AssignDate = Me.Check_Out_Date
!StatusID = Me.Status
.Update
End With

Set rs = Nothing

End Sub

 

[rubbernilly]

Hey, blue,

OK, so we need to make a couple of changes. If you add a field to the Transaction Log table, you will be able to handle the Check out and Return in the same record. Add:

Field: ReturnDate
Type: Date

...to the table.

In the checkout button event, change the sql of the dao.recordset. Change this...

Set rs = CurrentDb.OpenRecordset("SELECT * FROM TransactionLog")

...to...

Set rs = CurrentDb.OpenRecordset("SELECT * FROM TransactionLog " & _
         "WHERE PatientID = '" & Me.PatientID & _
         "' AND ((ReturnDate) Is Null)")

If rs.EOF Then [green]'checkout record already exists[/green]
   rs.movefirst
   msgbox "You cannot check this record out. " & _
          "It has already been checked out by " & _
          rs!EmployeeID & ".", vbokonly, "Check Out Declined"
   set rs = nothing
   Exit Sub
End If

If the code survives this IF statement, that means that there is no record, and the rest of the code can proceed (complete with the AddNew statement).

For the Return button, we just have to change this a little bit. In that case, we want the rs.EOF statement to test against False. That is, if there is no record for the patientID where the ReturnDate is Null (meaning that every time the patient has been checked out it was returned), then we need to tell the user that they can't return a Patient they haven't returned.

Set rs = CurrentDb.OpenRecordset("SELECT * FROM TransactionLog " & _
         "WHERE PatientID = '" & Me.PatientID & _
         "' AND ((ReturnDate) Is Null)")

If rs.EOF = False Then [green]'no checkout record exists[/green]
   msgbox "You cannot return this record. " & _
          "It has not been checked out yet.", _
          vbokonly, "Check Out Declined"
   set rs = nothing
   Exit Sub
End If

This time, if the code survives this IF statement, we have a valid record. In this case, we don't want to add a new record, we want to update the one that we have. So, at the beginning of the "With" statement, change...

With rs
  .AddNew

...with...

With rs
  .MoveFirst
  .Edit

That should make it so that...
1) The check out and the return will happen on one record in the TransactionLog table
2) A record can't be checked out if it is already checked out
3) A record can't be returned unless it has been checked out

I've got to run now, so I don't have a time to look at the DCount problem. Sorry. But if you can't figure the DCount problem out, there is another way... if you are adventurous. You have plenty of examples now on how to build a DAO.Recordset that would be able to get you the total you are looking for. Give it a try, if you want, and post the results. Otherwise, I'll try to help later.

Good Luck!

 

[blue1914mab]

I placed the additional code in. I'm getting a runtime error #3021, "No Current Record". Also since, we have added the previous code, when the check-out button is selected, it does everything it is to do. But, now the only way to view the name (from EmployeeID) you have to move to the next record and back again, then it will show up.

Blue

 

[rubbernilly]

I think I might have messed up the EOF checks.

For the checkout button, you need EOF to be true so that you know there is no record already.

Therefore the IF statement (which tests for the opposite case), needs to test for False. That statement needs to be:

If rs.EOF = False Then

And the opposite holds true for the Return button. This time you want there to be a record, so the positive case is going to be where EOF = False. Therefore, the IF statement must test the opposite. That statement needs to be:

If rs.EOF Then

That should take care of the no current record error.

 

[blue1914mab]

Hi rubbernilly!!
With these changes, a couple things are happening. The check-out works good with one recored. If I go to checkout another record, I get runtime error 3464 Data Type Mismatch in Crtieria Expression, pointed at: "' AND ((ReturnDate) Is Null)")

When I click on "Return" button, I get error message: syntax error in FROM clause. Runtime error 3131

BLUE


Check Out Code:
Private Sub Check_Out_Button_Click()

Dim rs As dao.Recordset

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

Set rs = CurrentDb.OpenRecordset("SELECT * FROM TransactionLog " & _
"WHERE PatientID = '" & Me.PatientID & _
"' AND ((ReturnDate) Is Null)")

If rs.EOF = False Then 'checkout record already exists
rs.MoveFirst
MsgBox "You cannot check this record out. " & _
"It has already been checked out by " & _
rs!EmployeeID & ".", vbOKOnly, "Check Out Declined"
Set rs = Nothing
Exit Sub
End If


With rs
.AddNew
!PatientID = Me.PatientID
!EmployeeID = Me.EmployeeID
!AssignDate = Me.Check_Out_Date
!StatusID = Me.Status
.Update
End With

Set rs = Nothing


End Sub





Return Code:
Private Sub Return_Book_Click()
Dim rs As dao.Recordset

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

Set rs = CurrentDb.OpenRecordset("SELECT * FROM TransactionLog" & _
"WHERE BookID = '" & Me.BookID & _
"' AND ((ReturnDate) Is Null)")

If rs.EOF Then 'no checkout record exists
MsgBox "You cannot return this record. " & _
"It has not been checked out yet.", _
vbOKOnly, "Check Out Declined"
Set rs = Nothing
Exit Sub
End If

With rs
.MoveFirst
.Edit
!PatientID = Me.PatientID
!EmployeeID = Me.EmployeeID
!AssignDate = Me.Check_Out_Date
!StatusID = Me.Status
.Update
End With

Set rs = Nothing

 

[PHV]

If PatientID is defined as numeric then get rid of the corresponding single quotes in the WHERE clause.

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

 

[blue1914mab]

Thanks guys.

With the current corrections to the code. On checkout click, it posts the date, user, and changes the status on the table "PatientInventory" and table "TransactionLog" (That part is good.)

However, now on "Return" it's deleting the info in the table "Transactionlog". I still need it to remove the info in table "PatientInventory" so someone else can check that recored out, but we only need to post the return date in the table "TransactionLog".

Blue

CODE FOR BUTTONS:

Private Sub Check_Out_Button_Click()

Dim rs As dao.Recordset

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

Set rs = CurrentDb.OpenRecordset("SELECT * FROM TransactionLog " & _
"WHERE PatientID = " & Me.PatientID & _
" AND ((ReturnDate) Is Null)")

If rs.EOF = False Then 'checkout record already exists
rs.MoveFirst
MsgBox "You cannot check this record out. " & _
"It has already been checked out by " & _
rs!EmployeeID & ".", vbOKOnly, "Check Out Declined"
Set rs = Nothing
Exit Sub
End If


With rs
.AddNew
!PatientID = Me.PatientID
!EmployeeID = Me.EmployeeID
!AssignDate = Me.Check_Out_Date
!StatusID = Me.Status
.Update
End With

Set rs = Nothing


End Sub

Private Sub Return_Book_Click()
Dim rs As dao.Recordset

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

Set rs = CurrentDb.OpenRecordset("SELECT * FROM TransactionLog " & _
"WHERE PatientID = " & Me.PatientID & _
" AND ((ReturnDate) Is Null)")

If rs.EOF Then 'no checkout record exists
MsgBox "You cannot return this record. " & _
"It has not been checked out yet.", _
vbOKOnly, "Check Out Declined"
Set rs = Nothing
Exit Sub
End If

With rs
.MoveFirst
.Edit
!PatientID = Me.PatientID
!EmployeeID = Me.EmployeeID
!AssignDate = Me.Check_Out_Date
!StatusID = Me.Status
.Update
End With

Set rs = Nothing


End Sub

 

[rubbernilly]

OK...to fix the problem in the Return button, look at the With Statement.

Change:

With rs
  .MoveFirst
  .Edit
    !PatientID = Me.PatientID
    !EmployeeID = Me.EmployeeID
    !AssignDate = Me.Check_Out_Date
    !StatusID = Me.Status
  .Update
End With

TO:

With rs
  .MoveFirst
  .Edit
     !ReturnDate = Date()
  .Update
End With

Otherwise, when you were updating the record, you were overwriting the existing data... which, as you say, you didn't need to do.

I am so sorry to have left that many errors in the code. That will teach me to code while watching football.