Search code examples
ms-accessinputtextboxdelimiterinventory

Trying to save multiple input data separated by a delimiter into singular records in database

I am currently trying to create an inventory database for my company. The issue is that inputting data one item at a time is inefficient. Thus, we bought a scanner. The scanner can hold multiple items in its memory, and when transferred to a location (textbox, Excel cell), all the data is input in that one cell separated by a tab delimiter.

Is there a way (split() function maybe) where I can essentially "paste" all the memory from the scanner into a single textbox on a form (so that a textbox has "data1 data2 data3 data4" and then save it such that my table now contains the data in separate records?

record1 - data1
record2 - data2
record3 - data3

I'm just learning Access so the syntax understanding is generally where I struggle.

Solution

  • Having read all the data into your text box, you need a button to click, with the OnClick event code looking something like this:

    Dim astrInput() As String
Dim i As Integer

astrInput = Split(txtData, " ")
For i = 0 To UBound(astrInput)
    CurrentDb.Execute "INSERT INTO myTable(DataColumn) VALUES ('" & astrInput(i) & "')"
Next i