Slickest way to count user input in VBA?

[peskywinnets]

Let's say we prompt a user for some input (let's say characters we don't know how many times he will enter a character, except he will type 0 to exit), what would be the best way to count?

For example, let say he enters the following characters

a
b
c
c
c
b
d
0 (exit)

The summary would a=1 b=2 c=3 d=1

I'm oversimplifying here (because in actual fact, the user will actually be scanning in barcodes, and it's those I wish to count), but I guess the concept remains the same...what would be the best approach in VBA?

Many thanks

 

[CJ_London]

use a group by query, group on character for one column, count on character for another column

 

[peskywinnets]

use a group by query, group on character for one column, count on character for another column

Thanks ...but I'm wanting to do this 'user input' counting on the fly in VBA ...not using an existing table/query (or have I misunderstood your suggestion?)

 

[CJ_London]

use the query and requery it after every input.

hit send to soon

not using an existing table

I presume your input is saved to a table, use that table. and the query could be the rowsource to a listbox which you requery

 

[peskywinnets]

use the query and requery it after every input.

Still not following your suggestion ...are you suggesting the use a temporary scratch table (created in VBA) to append user input to ....& then use a dcount command at the end of the procedure to summarize?

 

[CJ_London]

you haven't read my full post - check the thread, I added a bit more

 

[peskywinnets]

you haven't read my full post - check the thread, I added a bit more

Thanks...no my user input wasn't going into a table (I'd wanted the end summary data to go into a table ...i.e. once he'd finished entering input), but I've now got VBA to create a temp (scratch) table, then use an 'insert into' command to pipe the user entry into the table, I can then use a query to total up the entries at the end ...and then delete the table ...bit of a faff, long winded - but it works.

Many thanks for your help...you actually set me off in a different path (which was just the push I needed )

 

[Cronk]

I avoid saving/deleting data in temporary tables where I can because of bloat and having to manage that.

If your universe of scanned values is not too many (26 in your example), another approach would be to increment values in a 2 dimensional array.

 

[CJ_London]

I've now got VBA to create a temp (scratch) table, then use an 'insert into' command to pipe the user entry into the table

why not just base your form on the table? - seems you are over complicating it.

or if you must do it this way, keep the temp table (bound to the form), just delete the contents when the form is opened, user enters barcodes on each row, when they click on 'ok' whatever vba runs a single append query.

I kind of imagined you had a continuous form with the listbox in the header or footer so the user can see an updating summary of what they have scanned - if they've scanned an item a second time in error, they can just delete the scan record - and the listbox is requeried in the form current event,

Only 2 or 3 lines of code required

 

[The_Doc_Man]

PW, let's see if I can ask a question to understand what you want.

Are you saying that you will input arbitrary text (represented here by a, b, c, ... but the "real" data will be barcodes) and that you want to count on-the-fly how many of each distinct code you have input in a given arbitrary sequence?

To clarify, will you also be storing the individual codes or do you really just want the counts? Stated another way, AFTER one of these input sessions, what WILL be stored and is that information long-term, intermediate-term, or short-term?

 

[MarkK]

...what would be the best way to count?

Add a row to a table for every event that occurs. This is called raw data. Date/time stamp it. Done.
IMO,
Mark

 

[peskywinnets]

More meat on the bones for what I'm trying to achieve.

I run a a one man business supplying products to businesses such as Amazon (& others) ...if they overstock, they return products to me.

The products returned to me in a postal package all have both a name (SKU) & a barcode.

So here's what I'm wishing to so...

1. Scan the barcode on the outer package (a unique reference)
2. Scan the barcode on the packing label contained inside the package (another unique reference)

I have steps 1 & 2 covered, so the following is the bit I' was trying to address...

3. Scan the product barcodes contained inside the package (quantity unknown - it varies, it could be one product, it could be as many as 30-40 different products) - I have a table that maps barcodes to product names. The ultimate goal is to track which products & how many are in the package.

4. Once the scanning routing is complete, spread the collected data amongst a couple of tables (normalised) - I have this bit covered.

So it's only step 3 that was troubling me...my proposed solution works...point taken about bloat, but there won't be a huge amount of data involved here...but yes, bloat is a worry so perhaps I need to deploy a less bloaty method.

 

[CJ_London]

just seems to me you wanted the slickest way but have chosen a clunky way

 

[peskywinnets]

just seems to me you wanted the slickest way but have chosen a clunky way

I just really need to get something deployed ...when I asked the question, I didn't know how to do what I needed (I was trying to do all the summing in VBA prior to then updating tables, but couldn't work out how to do that)

I now at least have something in place...I'm all ears as to the slickest way.

 

[The_Doc_Man]

peskywinnets, I won't write the code, but I can perhaps point you to a way that gets rid of the bloat. Maybe, kind of, sort of...

Look up Dictionary Objects.

https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/dictionary-object - how to create them

https://www.experts-exchange.com/articles/3391/Using-the-Dictionary-Class-in-VBA.html - a long article about using them.

My thought is that you can create a dictionary of inputs and define each NEW input as having a value of 1. But you are allowed to do something like update the value for existing objects. So store counts in the dictionary object's value field.

When you are done, you can do a .RemoveAll and dissolve the dictionary object. Or just exit the app. The bloat is MEMORY bloat, not DB file bloat. Process exit will clean that up for you automagically.

 

[arnelgp]

Gere is a sample of counting using collection abd shiwing in listbox

 

[Cronk]

I'm currently finishing off a module that does something similar for a client. In my case, the employer issues work clothing to employees. There is a bar code for each employee and a bar code for each item of clothing identifying type(shirt, socks, trousers etc), size and for some types, gender.

The purpose is to keep track of when what was issued to whom and for stock control/re-ordering.

The bar code scanner is stand alone, storing the scanned codes in its memory for later uploading data to an office computer away from the stock room.

The uploaded data is one text file with one bar code on each line.

Processing is all in memory. Process sequence is
Step 1 Read the first line which should contain a valid employee ID
Step 2 Read the next line which should contain a valid StockID
Repeat step 2 until change of EmpID or StockID, counting the number until an new EmpID or StockID is encountered, write the record to the table containing EmpID, StockID, IssueDate, Quantity.

FWIW