Adding a tab in access

[radical86]

Hello everyone,

I am not very experienced in Access and I have extensively researched on this but couldn't find any way to do the following:
- i will be scanning a barcode which has multiple parts of information that needs to go in different fields on my form
- these fields will be separated by a semicolon ( - i can change that to anything really
- when i scan in access, i am trying to get access to recognize the semicolon and replace with a tab so that it looks like following:

one textbox where i will be scanning: textbox1;textbox2;textbox3
and i need those values to go into individual textboxes so it looks like:
textbox1 value = textbox1
textbox2 value = textbox2
textbox3 value = textbox3

i have tried using the replace function in access vba but I couldn't achieve that. I have used vba tab codes "vbTab" but it doesn't work when i scan it.

Any suggestion? Recommendations? Work around?

Thanks!

 

[sneuberg]

I attached a database that demonstrates one way. It looks for the semicolon in the on change events of the textbox, e.g., for textbox1.

If Right(Me.textbox1.Text, 1) = ";" Then
    Me.textbox2.SetFocus
End If

It takes out the semicolons in the afterupdates, e.g., for textbox1

Private Sub textbox1_AfterUpdate()
Me.textbox1 = Replace(Me.textbox1, ";", "")
End Sub

But I don't grown to dislike it already. I'll see if I can come up with something more robust. How are you intercepting the scanner input in the code you tried?

I think I'll try scanning into a single unbound textbox and have the input parsed and distributed from that. Is the last item suffixed with a semicolon,

e.g., Item1;Item2;Item3;

or it there some other way the end of the scan is marked?

 

[Gasman]

I'd be looking at the Split function if I wanted to try and do that?

 

[radical86]

Thanks! I will try that out.

From the scanner, it all goes into a single unbound textbox. From there, I want to break it apart and distribute to individual bound textbox. I don't have a semicolon at the end but that can be added. Currently my scanner is programmed to do a return after scanning is done and that's how it moves onto the next textbox.

I have tried encoding a tab into the QR barcode but access isn't recognizing that either.

 

[sneuberg]

If your scanner does a return after scanning then you could use that to determine the end of the scan and then just use Gasman's idea like:

Private Sub InputScan_Click()

Dim InputArray() As String
InputArray = Split(Me.ScanInput, ";")

If UBound(InputArray) = 2 Then 'make sure three items were scanned
    Me.textbox1 = InputArray(0)
    Me.textbox2 = InputArray(1)
    Me.textbox3 = InputArray(2)
  [COLOR="Red"]  DoCmd.GoToRecord , , acNewRec[/COLOR]
Else
    MsgBox "Error in scan"
End If
Me.ScanInput.SetFocus
Me.ScanInput = Null

End Sub

which you can find in the attached database. In this database I set the Default of the Input Scan button to Yes which causes its onclick code shown above to be execute when the return key is pressed or the code should also run when it gets a return from the scanner.

Note the line in red isn't in the code of the attached database but you might want to add something like that.

 

[radical86]

^Thanks so much sneuberg!

It works great! I actually changed the code to after update of the unbound field and it automatically puts appropriate information into the boxes I want!

Thanks!!