Check a checkbox if not already checked and vice versa

[ayatsweid]

Hello,


I am trying to create a medical records inventory. We have physical charts which each have a barcode and I have a scanner. The idea is to have employees who want to check the chart out scan their barcode (on their badge), scan the chart's barcode and leave.
I have a table called tblCharts with the fields: ID (primarykey which is also the chart number), a checkbox, Employee ID, Date, and Time checked out.
I have Employee ID linked to my tblEmployees that simply has a listing of the employee ID's and first and last names.
I don't care about keeping a record of who has had the chart in the past.
I just can't figure out how to tell the checkbox to be checked (to indicate the chart is out) when a number is added to the ID field (via scanner).

So here's how it would go:
Scan the chart's barcode which would populate into the ID field. Search to see if the record with the same number is already existing, if not create a record. IF it does exist, check to see if the checkbox is checked. If it is checked then the chart is being checked back in (so uncheck the box). If it is not checked to begin with (still in), then check it out and associate the employee's scanned barcode with it.

Can someone help?!?!?! thank you sooo much!!

[June7]

This behavior is like a library check in/out. There are Access database templates available. You might be able to adapt code. Here is one http://office.microsoft.com/en-us/te...ai:TC010206883

That one uses embedded macros exclusively.

[ayatsweid]

Thanks so much June7!!
I actually saw that sample database but it requires the user to find the record that is already checked out, then click on the check in button or vice versa. Our operation needs to be a simple scan of barcodes. I would like to completely minimize how many clicking actions and time searching for the record that the users would take. Do you know how to manipulate the lending library to do that?

Thanks so much!!!!!

[June7]

You want code that will take the scanned bar code, search the database and if the item is out, check it in OR if item is in, check it out. Certainly doable. I have never used bar code scanning. What event would the scanning trigger? I think might still need to push a button. The code behind the button would search data for the item's status and then update record to the reverse. Do you care about history of in/out?

I expect the Lending Library could be adapted to handle the bar code input.

[ayatsweid]

Hi June,
Thanks again for helping!
The scanner part is very easy. When you press the button on the scanner to turn the red light on, it will scan a barcode and simply enter the digits into the field you are selected on. It simulates someone just typing in the digits.
So that part is taken care of.
What you described on what I want to happen is definitely correct.
What I've been occupied with is some code to put in the BeforeUpdate event of the field that the number is scanned into (ID Field). I'm having issues with it though.
First I had an issue with it saying I can't insert a duplicate primary key (because my ID field is also my primary key). Although I don't want duplicates, I want it to bring up the record of the already existing record but it thinks I'm trying to add a duplicate record.
This is the code I've been using but it gives me a syntax error in the Private Sub line which I can't figure out!


Private Sub txtID_BeforeUpdate(Cancel As Integer)

Dim SNM As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SNM = Me.txtID.Value
stLinkCriteria = "[txtID]=" & "'" & SNM & "'"
'Check Suppliers table for duplicate Supplier
If DCount("txtID", "tblCharts", _
stLinkCriteria) > 0 Then
Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning the ID: " _
& SNM & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", _
vbInformation, "Duplicate Information"
'Go to record of original supplier
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing

End Sub


I copied this from another posting so I don't really need the msgbox.
Basically here's the process I want it to follow:
1. User scans chart barcode.
2. Access searches tblCharts for the ID if already existing.
3. If it does exist, go to that record. If it doesn't, create new record.
4. If it does exist and there is an Employee barcode already associated with it, then the Checked Out checkbox is checked. I want Access to recognize that here the checkbox is already checked so this chart is being checked back in. Then I want it to uncheck the box and delete the employee's barcode.
5. If the record does exist but the employee barcode is empty and the check box is unchecked, this chart is being checked out so then I want the cursor to go to the EmployeeID field. User scans Employee's barcode, then the checkbox is automatically checked since the employee field is not not null.

I hope that made sense. Let me know if you'd like to see my database so far and I'll upload it.

Thank you so very much June7!!
And please let me know if there is a comment function or rating function for profiles here and I will definitely give you max points!!

[June7]

You need to use an unbound textbox to input search criteria. I don't know if the scanner can input to an unbound textbox.

Willing to look at your project but cannot test bar code input.

[ayatsweid]

Great thanks!
Here it is!

[June7]

This project has only one form and all of the fields are bound. There is no code. If you want a search procedure, the bar code must be input to an unbound textbox. I don't know if scan will input to an unbound textbox or if will trigger events. You need to determine these unknowns.

Put an unbound textbox in the form header. Try scan input to this control. Test the AfterUpdate event with a simple MsgBox in the procedure:
MsgBox "Yes, scanner input triggers event."
If value does input, then good. If input by scanner does not trigger the event, then will have to use button click.

Additional note, you created the form with wizard. I don't use design wizards, they do things I don't like and make it hard to edit controls. If you want to be able to move and resize the controls individually, have to 'ungroup' them. Select all the controls, right click, Layout > Remove.

[ayatsweid]

Hi June7!
Yes! The scanner input does trigger the event!
I got rid of the code I was working with when I sent this to you so I wouldn't confuse things.

I added the unbound textbox and it triggers the search. If I do have the record, it pulls it up in my form. Which is great!
But now the issue is when the record is not found and I want it to create a new record. When I scan the number, it creates a new record but when I go to the form fields to fill in, it creates another new record. Also, I need the number scanned in to be put in the ID field - which it doesn't do.

Can you help?? I've uploaded the DB with what I have now.

Thank you!

[June7]

Try adding these lines after the acNewRec:
ID = Me.txtSearchID
DoCmd.RunCommand acCmdSaveRecord
Me.Refresh

I tried to set tab order on controls so txtSearchID has focus when the form opens. Because it is in header section, setting TabOrder not helping. So I put code in form Open event: Me.txtSearchID.SetFocus

[naicha12]

There is noticeably a bundle to know about this. I assume you made certain nice points in features also
You are one talented young lady thanks for shareing with us, Jerri we love you.

[ayatsweid]

June7,
It's soo close to doing exactly what I need!
So with the code you gave me, when I scan my number, it goes into the ID field and pulls it up if already existing. If not, it creates a new record - this is PERFECT!

But now the issue is making the cursor automatically go to the Employee ID field after scanning the chart number into the txtSearchID field. This way, the user can scan the chart, then right after scan the employee's ID who is checking out the chart.
I tried to set focus to the Employee ID field in the AfterUpdate field but it gives me an "ambiguous name error". I suppose because you can't have two:
Private Sub txtSearchID_AfterUpdate()....

So then how can I tell it that after you Search the ID and save, then focus on Employee ID?

I put this code in for after we figure out the focus on Employee ID part:

Private Sub Employee_ID_GotFocus()
If IsNull(Me.Employee_ID) Then
Employee_ID.Value = ""
MsgBox "Chart checked in to Medical Records."
End If
End Sub

This way, if the Employee ID field is not null, that means it's checked out so if it's being scanned, it means its being checked back in. So I want the Employee Field to be cleared (which will make my "Checked Out" checkbox uncheck).

However, if the field is null, I just want the user to proceed in scanning and Employee barcode in there.

Thanks again June7!

[June7]

Following the Refresh code:

Me.Employee_ID.SetFocus

Don't have your project downloaded here. The employeeID textbox is bound? I don't allow empty strings in my data tables so I would not set a bound control to "". If it's null why need to set to empty string anyway?

[ayatsweid]

Hi June7!
I uploaded the db for you to take a look at. Yes the employeeID textbox is bound.
I want my Employee_ID field to empty string when there IS something in there already. I want to empty it because the chart is being returned and it won't be with that employee anymore.

I understand that having empty strings in my data tables is not the best policy but we don't have an interest in keeping record of who has had the charts before. Just an inventory of what is in the file room and what is out and if it's out, who is it with.
So basically, all my chart records will have employee id's being entered, and deleted, and entered, and deleted quite often.

What do you think? Thanks!!

[June7]

Seems to me if you always save empty string to the field, then eventually will never be Null, the condition will fail.

Will the employee who checked item out always be the one to return?

Can just set EmployeeID field to null automatically every time item is scanned and then employeeID only has to be scanned if they want to check out. Nothing so far handles instance where user walks away with the item and forgets to scan either the item or employee ID.

Probably also want to null the date and time data. Really don't need two fields for date and time. One field set to General Date format will serve. Will have to set the date when record is checked out. Default only applies to new record.

If presence of absense of EmployeeID will indicate item status, then the CheckedOut field is not needed.

If you don't want users accidentally deleting chart record, set the AllowDeletions property to No.

I found the split form annoying when it first came out with 2007. Still has issues in 2010. I tried resizing the date field in the bottom split so the full date/time will show but form won't save that. Aggravating.

Also, advise not to use reserved words as names. Date is a reserved word. Better would be DateOut. Also, spaces and special characters and punctuation (underscore is exception) should be avoided.