Altering Access to use either tab/return as a delimiter for split() function

[TylerBennett]

Currently I am working on a serial number inventory database. When I was testing functionality, I was using the split function with the space as a delimiter, shown below:

Dim InputString() As String
Dim i As Integer
InputString = Split(InputName, "    ")
For i = 0 To UBound(InputString)
    CurrentDb.Execute "INSERT INTO InventoryInputT(InputID) VALUES ('" & InputString(i) & "')"
Next i
 

Thus, an entry of

data1 data2 data3

In the textbox of the form resulted in a table input of

record1   data1
record2   data2
record3   data3
 

Which is desired. The scanner we purchased uses either a 'tab' delimiter or a 'return'... it can also use 'add jump line' but I don't know what that means and it seems to be similar to return (on the surface)

Is there any way I can alter my code to either:

1) use the tab function
- the issue here is if the data in the scanner is represented as: 'data1 [tab] data2 [tab] data3'

and then I upload that to the textbox, access seems to read the data as:
-input 'data1'
-execute [tab] (which means it navigates out of my textbox to whatever the next button is)
... because it has navigated out of the textbox it can no longer enter 'data2' in the textbox... as such I don't know if this can be solved

2) Can I use the return setup as it allows each data entry to have its own unique line in the textbox
- can I adjust my code so it sets each line of the textbox to a value of i and then runs through my execute code? Example:

data entered (as displayed in textbox):

data1
data2
data3
 

Get code to read it as:
data1 = line1 = i=0 - execute to table
data2 = line2 = i=1 - execute to table
data3 = line3 = i=2 - execute to table

or something along those lines?

Thank you so much!

[NeoPa]

Hi Tyler.

The second parameter of the Split() function is called Delimiter. This can be set to a single space, multiple spaces (as in your example) or even a tab character (vbTab in VBA). It's very flexible.

[TylerBennett]

Thanks NeoPa, I was aware of that usage but the tab didn't seem to work with my system. Turned out it was more of the scanner's issue than it was an error with access.

This was fixed by recognizing all serial numbers started with an 's' which allowed me to use the 's' as the delimiter.

[NeoPa]

Good to hear you've resolved your issue anyway Tyler :-)