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

[brandonze]

I have just done that. I get the full in a text box and have applied the mid function to get the desired code in the other box, but how do I get that box to look up the record now that it is bound to the first box? Im playing around with buttons now, but not having much success.

[brandonze]

Thanks again for all the help

[orange]

I have acc2003 and I'm building a form to try to get this working. My Form is Form13

On your form --- code behind the Form I have the following code

Private Sub txtIncomingBarcode_AfterUpdate()
On Error GoTo txtIncomingBarcode_AfterUpdate_Error

Me.txtDesiredCode = Mid([txtIncomingBarcode], 2, 6)
Debug.Print 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)
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

[brandonze]

Beginer question,

Where exactly do I put the code?

[brandonze]

I figured it out, I have the code installed, works great to convert the barcode to the desired code. Thanks.

But how does it look up the record after that?

I'm sorry, but I am really new to access and kind of in over my head.

[orange]

Where exactly are the records to look up? It should be a table, and a field in that table that has a code similar to you desired code.
Look at your table design and see if that code is indexed, or is the primary key.

[brandonze]

Primary key on the table is ID. Table name is table1

The fields are txtDesiredCode, SSN, FirstName, LastName.

I am working off of a generic table that I have created so I don't mess up the actual DB that is currently in use. Once I have an example that works, I can pretty much understand what is going on.

Thanks again.

[orange]

Well you don't want the field txtDesiredCode in the table --- if you're trying to match records with values in the txtDesiredCode on the form.

Just to remove confusion, change the name of the field in your test table to MyCode.

Then create a query like this:


Do you know the query wizard??

If not go to Create a new query-- I have Acc2003 so your version may have different GUI.
Select your table1 as the table you are querying against.
Select the fields you want to see values for.

!!!! Whatever the name of your Form is -- make sure it is OPEN.

Now under the field MyCode in the criteria, Right click and choose BUILD.

In the popup select Forms, LoadedForms then select your Form
Then select the txtDesiredCode in the next column, then OK

And you should have a query.

[brandonze]

I followed your instructions, and I think we are getting somewhere. Only thing is, it populated the query and not the form. Any thoughts?

[orange]

Let's step back again.
The query uses the desiredCode to select (get) records from your table.

Tell me more about "populating the Form". What did you think was going to be there?
Right now all the data from the table is selected for that /those records that have MyCode = txtDesiredCode.

[brandonze]

Sorry, I must have gave you some misleading information.

you know how you can use a combo box to lookup the record on a form? That is what I am trying to do with this scanner thing.

[orange]

So, step by step

What would be in the combo? OK so there's some number in the combo -- is that the desiredCode?

Are you going to display data in the form? If so, what fields, how many records etc?

What is the process? You scan a barcode, then look up a record?
Or, all barcodes are in a table?? And you put those barcodes in a combo, select one, and then get records???

All very confusing, but we'll get there.

Usually if you have a combo, you have a table with values and you put those values "In the combo". Actually the source of those codes is the record source of the combo.

[brandonze]

you are correct, DesiredCode or MyCode would be in the combo.

Data will be displayed in the form and open for editing ie adress change on family member no show. 1 record will be displayed perform. in the actual DB there is quite a bit of data to display.

The old process that was used is to type the employee's last name into a combo box and pull up the record that way, But in the new process, typing was too much I guess, the Employee will get his ID card scanned and information will be brought up in the form.

Since the Identifier is the 2-7 of the scan, we are halfway there. I tried builing a a macro on the searchRecord action and could not get it to do what I asked. I don't think the code was wrong, as I didn't get any error messages.

[brandonze]

I aslo tried making txtDesiredCode a combo box checking the appropriate blocks on the wizard, but had no luck that way. The desired code was in the box, But I still had to manually choose from the drop down menu.

[orange]

Even with a combo, you would have to select the entry in order to pull up the record from the table.

Let's step back.
The desiredCode is already in the Table. Right?
So, once the code is scanned, you open a Form with all of the person's info and you use the desiredCode to know which record to put in/on the Form.