By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,939 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,939 IT Pros & Developers. It's quick & easy.

Working with delimiters to save multiple items to individual records : inventory

P: 23
I am currently working on an inventory database and have run into an issue dealing with bulk storage of data.

Our scanner can scan multiple items at once and store them in its memory, which can then be transported in bulk to some destination (textbox, excel, wordpad). Each item stored is separated by a TAB delimiter [item1 item2 item3].

My issue is having the program deal with the various inputs individually to save each item as a separate records...

i.e. Scanner scans: [item1 item2 item3]
These are currently being transferred to a textbox

When I hit the save button, the program is able to identify the tab delimiter to then save the items such that
record1 item1
record2 item2
record3 item3


It is fairly trivial to set up a form which allows the user to input 1 item at a time and save it to the database - by simply having a textbox that can save the record to the appropriate table. However, I have tried to expand this concept to multiple inputs with the SPLIT() function however I have made no advancements in being able to get the SPLIT() function to work. (this may the incorrect way to go about this)

I understand the SPLIT() function can easily identify the delimiter and each separate item, however I have zero idea how to implement code such that when I press 'save', the system saves each item as a separate record.

Does anybody have any input into whether this is the appropriate approach and if so, how can I go about coding the form such that it can iterate each individual item (i'm thinking of a for loop iterating from 1:LengthofInput and saving an item on each loop, but I don't know how to write this syntax in VBA - particularly with calling each item into the loop)

I have also experimented with wordpad... scanner pastes all data in wordpad, I import wordpad with the tab delimiters and this inports all the data in 1 record. I then would need to transpose the data (which I haven't been able to figure out how to do in access), and have tried exporting this to excel, transposing, then importing back... but this seems extremely tedious.

Sorry for the long post! Just trying to make it somewhat evident that i've considered a wide variety of what needs to be done but don't have the VBA coding syntax ability to implement it.
Apr 16 '17 #1

✓ answered by NeoPa

TylerBennett:
My issue with the tilde is that the scanner's built in delimiter is a tab (which I don't believe is adjustable) so adding the tilde would be purely additional work.
I expect that this will only be an issue if you want to give the operator the opportunity to enter the multi-value data directly from the keyboard. If it comes from the scanner in all cases then the TAB would work for you perfectly well.

OTOH as Phil says, converting from one to the other in a string of data is very trivial.

NB. In VBA you can use vbTab to represent the TAB character.

Share this Question
Share on Google+
5 Replies


PhilOfWalton
Expert 100+
P: 1,430
I suspect the method will work, but using a TAB as the delimiter between the items may cause problems, as the Tab Key normally moves to the next field.

I tend to use a tilde ~ as a delimiter so I guess if you use something that is unlikely to occur in your items, the split function would work in the same way, presumably by loading them into an array an reading the array to add to your table.

Phil
Apr 16 '17 #2

P: 23
Thank's for responding.

My issue with the tilde is that the scanner's built in delimiter is a tab (which I don't believe is adjustable) so adding the tilde would be purely additional work.

So you're suggesting that I can use the split function, somehow create an array based on all the inputs (each value in the array will now correspond to a singular item) and then somehow run a loop to move through the array to save each individual item?

.... when I create an array will the array automatically be built to be a square matrix? I'm thinking if it's a square matrix it will be more difficult to let the program know when to move from row 1 to row 2 based on how many columns there are. If it was possible to make a 'm x 1' array then each iteration would simply be row+1 which seems worlds easier to me if possible
Apr 16 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
I assumed from your email that you had got the scanner side working? Note the question marl.

If it is, you can use the Replace function in your input text box to replace the Tilde with a Chr$(9) TAB.

Here is a simple example of the code for the array to give you an idea

Expand|Select|Wrap|Line Numbers
  1. Dim LString As String
  2. Dim LArray() As String
  3.  
  4. LString = "Tech on the Net"
  5. LArray = Split(LString)
  6.  
  7. MsgBox LArray(0)    'Tech
  8. MsgBox LArray(1)    ' on
  9. MsgBox LArray(2)    ' the
  10. MsgBox LArray(3)    ' Net
  11.  
Phil
Apr 16 '17 #4

NeoPa
Expert Mod 15k+
P: 31,419
Hi Tyler.

You haven't explained your data very well, I'm afraid. Is this field you've talked about the only one in the table? From you post it sounds that way. Are there other fields that need to be saved for each item in your array? Your second post implies maybe there are. We need a clear understanding of your problem before we can be as helpful as we'd like to be.

Actually, you need a very clear understanding of what you want/need even before you prepare your question. And therein lies a point that is massive. We try to encourage people to think that way, but more often than not the very idea of that approach is so helpful that most people then come back and tell us that once they took that approach they realised they no longer needed any help. I'll leave that point for you to ponder while I give some advice that may be helpful generally, regardless of exactly what your question really is.

Normally, when using Access and managing data, you're better off using bound forms where each field of data is represented by a control on the form. Once you move from one record to another the data is saved from the form controls into the table directly. There are many adbvantages to this approach provided by Access as that fits in very well with what most people want to do.

Another approach, usually less sensible but maybe in your case more so, is to use the form to grab the data you need in unbound controls then add that data to the table using a recordset or some SQL. I suspect some code like this, which uses a loop to process through the multi-value item(s), is what you need to be thinking of.

Good luck.
Apr 16 '17 #5

NeoPa
Expert Mod 15k+
P: 31,419
TylerBennett:
My issue with the tilde is that the scanner's built in delimiter is a tab (which I don't believe is adjustable) so adding the tilde would be purely additional work.
I expect that this will only be an issue if you want to give the operator the opportunity to enter the multi-value data directly from the keyboard. If it comes from the scanner in all cases then the TAB would work for you perfectly well.

OTOH as Phil says, converting from one to the other in a string of data is very trivial.

NB. In VBA you can use vbTab to represent the TAB character.
Apr 16 '17 #6

Post your reply

Sign in to post your reply or Sign up for a free account.