How to apply a MID to a field????

[orange]

So I created a form (frmEmployeesWithBarcode) using the Form wizard. Made it a single record form.

Remember My test table was tblEmployees, I added a MIDTestNumber field to represent your desiredCode format.
I then added a line to the afterUpdate evnt ontxtIncomingBarcode. Here is the revised code behind the form.

You will have to adjust the openForm command with your Form's name and your field name (to replace my MidTestNumber) but it should work. I adjusted what we have built because, if you scan a card, you have to get the barcode, then the DesiredCode in order to find the correct record in Table1.

Private Sub txtIncomingBarcode_AfterUpdate()
On Error GoTo txtIncomingBarcode_AfterUpdate_Error



Me.txtDesiredCode = Mid([txtIncomingBarcode], 2, 6)
Debug.Print Me.txtDesiredCode & " at " & Now()
DoCmd.OpenForm "frmEmployeesWithBarcode", , , "MidTestNumber ='" & Me.txtDesiredCode & "'"
On Error GoTo 0
Exit Sub

txtIncomingBarcode_AfterUpdate_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure txtIncomingBarcode_AfterUpdate of VBA Document Form_Form13"
End Sub

Private Sub txtIncomingBarcode_BeforeUpdate(Cancel As Integer)
'This is where the Original Barcode gets placed when the employee's
'card is scanned.

On Error GoTo txtIncomingBarcode_BeforeUpdate_Error

If Me.txtIncomingBarcode = vbNullString Then
MsgBox "There must be a barcode in txtIncomingBarcode"
End If

On Error GoTo 0
Exit Sub

txtIncomingBarcode_BeforeUpdate_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure txtIncomingBarcode_BeforeUpdate of VBA Document Form_Form13"
End Sub

SO now, you scan the persons card, it puts the barcode into the IncomingCode textbox; You move out of that textbox and it puts the desiredCode in txtDesiredCode,
and Opens the form with the correct person's info in that form.

[brandonze]

The DesiredCode is already in the Table, Under the field of MyCode.

The goal is to have the form open already, the cursor would be in the txtIncomingBarcode txet Box. The user would then scan the employees card, and then the record would be pukked up on the form.

in the beginging the form is open with my defualt values displalying in the boxes.

I am trying to make this as automated as possible.

[orange]

Why were you asking about the mid function if the desiredCode is in the Table?

If the card has to be scanned to identify the person, when does the scanned barcode go? It has to be some where if you're going to parse it for the 6 positions you need.

It seems you are now saying:

I have a table of employee information. Each employee is uniquely identified by a DesiredCode. Each employee has a badge/card?? that contains several digits. We scan the card and get this long set of digits, BUT we only care about positions 2-7 because that's the same code we stored in the Table to identify the employee.

We does the card contain all the digits, or why does the desiredCode only have a subset of the digits?

Did you try adjusting the form as mentioned in my post 31?

[brandonze]

I think we just were writing at the same time. It worked perfectly. I could not have done it without your help. Thank you much.

I will be adapting the to the large DB tomorrow!!!

[orange]

I'm glad you have it working. Sometimes it takes going thru this process of talking to someone who knows nothing of your environment, but trying to explain/describe a process or procedure. It's a learning in communications if nothing else.

Anyway, good luck with your project, it could still be refined. Just cause it puts up the correct form doesn't necessarily mean it's good code, or an efficient procedure. It seems to work -- great.

If your table has lots of different "things" in it, you might want to read the first 3 topics at this site.

http://www.rogersaccesslibrary.com/forum/topic238.html
Good luck.