String of characters with spaces needs rule to reference data sheet if part of the string is missing.

see your question and am working on it :)

question: by pull from the db:
do you want to store case count for all records entered, or just lookup from what is already on the sheet?

thanks, Chris

warm regards,
crystal

~ have an awesome day ~

If there IS a case count in the barcode entered, then that one will split into the correct column by itself already,

If there is NO case count in the Barcode, then lookup from list, same as flavour was done.

(keeping in mind the the Pallet count can be 1 or 2 or 3 digits and the Case Count can also be 1  or 2  or 3 digits)
(so pallet numer 312 could have 15 cases) or ( pallet number 312 could be full and will have no digits after the 312 ) ( no # = Full CaseCount From DB)

I actually found the right place to put that line to hide the one sheet we wanted to hide. LOL.
Your "Splaining" is very good, LOL
Ok now going to bed, LOL
Night night

okey dokey ~ thank you, Chris -- and so happy you are getting it!

yea party party ya fa la la la faa la la la yeaaaaaaaaaaaaaaaaaaaaa

back to the topic ...
so keep them in case count until they enter it? if it is not in the barcode? and not already one the sheet?

warm regards,
crystal

~ have an awesome day ~

The bar code will either have the 3 digits already in it , or it will not.
It is built into the barcode that way, it represents what is actually on each pallet.

95% of pallets are full thats why I omitted it from the barcode, and just added the partial skid number when a skid is not complete, or less than full.

It was one less thing I had to program into 200 custom made barcodes I made.

Hi Chris,

so do you want me to add information to the DATA sheet for a particular product when pallet count is specified and not already there?

I just put code in to lookup from previous entries on the InputData sheet if it is not specified ,,, but can add it to the Data sheet if it needs to go there, and would be right to go there

warm regards,
crystal

~ have an awesome day ~

4      504208 20OCT2015 J5572 4 16            
5      504208 20OCT2015 J5572 5            
6      504208 20OCT2015 J5572 6 16            
7      504208 20OCT2015 J5572 7 87            
8      504208 20OCT2015 J5572 8            
9      504208 20OCT2015 J5572 9 16                  

Pallet 5 and pallet 8 are full pallets.
Pallets  4,6,7,9  are less than full and have the case counts already in the bar code and will split into the last column with the rest of the code automatically.

thanks, Chris

what is the pallet count for a full pallet?  Is that FULL Case Count from the Data sheet? (just noticed that column! guess I don't have to add it)

warm regards,
crystal

~ have an awesome day ~

The data sheet is only the reference, It does not need to receive any information from the scans. ..

After split if column "h" is still empty then look at Column "D" (item number) and pull Full case count Value for THAT item number from the data sheet list and fill it in.

The data sheet will have every single item we make, and I can update it when we start making  new item number long before we produce the first skid.

Yes the pallet count for a full pallet is the third column in the sheet it is with 44 88 or 120 only
We only have 3 different formations for stacking skids, so full skids will have only 44 88 120 and every item number in that list tells you the flavour and the FULL CASE COUNT

ok ... so another question:

what if they scan a barcode that is NOT in the database?

do you want it to send you an email?  If so, I will need your email address

thanks

warm regards,
crystal

~ have an awesome day ~

Nope just let it scan. Leave out the flavour name.. And leave out case count unless it has that case count already in the code. .  it should never happen unless someone messes with the actual Barcode string builder in the code maker.

Hi Chris,

okay! Got it ! No more questions ... you can go to sleep now ~

warm regards,
crystal

~ have an awesome day ~504208

Hi Chris,

Good morning! Here you go :)

attached:
114-Warehouse-Scan-151120_130a.xlsm

I was "done" but then Excel started auto-parsing the scan! So I had to add code to account for that.  If you don't know what that is ... whenever you use the Text-To-Columns feature, which I did ... Excel "learns" and figures you want to do that all the time when stuff is pasted ... so Excel was parsing instead of letting the code do it ... and selecting multiple columns for the data it parsed.  Well, I fixed that.  Hopefully this is what you want.

I renamed Protect and Uprotect to ABC an UnABC :) They are still in the same place.

warm regards,
crystal

~ have an awesome day ~
114-Warehouse-Scan-151120_130a.xlsm

Good Morning,
It's 6:20Am I am at work. I am testing the code, and it looks like there are a couple things.

I have a couple small fixes that need fixing but for the most part it is kind of working.
I think during your testing on your side to speed up the testing, you won't have a barcode reader to test it, so what you should do, if you are not already, is, have a second sheet open with a series of test codes handy including the location code.

When you are ready to test the sheet, copy the source, test code into clipboard, then select workbook with active cell ready, and then just ctrl v it, this will act the same way as a full normal scan.

I am trying to get the bugs listed out for you.
Will get back to you asap.
You are a super star :)
Chris
PS Which part of the world are you in? (for time zone and communication)

Good morning, Chris.  I am in Colorado.  I am guessing that you are in Canada (MB or SK?).

I did quite a bit of testing with the barcodes you gave me ~ but programmers aren't very good at testing their own stuff because we don't always thing of ways to break it ~~ and maybe I don't know all the specs.

warm regards,
crystal

~ have an awesome day ~

Good morning,
Time for  a coffee. Would you like me to give you t he little glitches as I find them , or do you want to be hold off until I have more than one?

Thanks.
Chris

Oh I checked out your youtube channel.
Cool stuff.

thanks, Chris -- I have a playlist called Learn Access that would be a good place to start.  

Learn Access Playlist on YouTube
http://www.youtube.com/view_play_list?p=1B2705CCB40CA4CA

I also just finished making a 40-lesson series for Access on GoSkills https://www.goskills.com/Course/Access-Basics and right now, it is very cheap! https://www.goskills.com/Course/Access-Basics/Pricing

... that is, if you want to learn Access, which of course I think is a good idea  :)

give me the list all at once please ... however, you can tell me now what you already found ~

Wow, I never ever have even opened Access, It is similar to VB?

First thingy I found, After it sends to DB an Consolidate sheet (which it does just fine), it comes back to InputData Sheet and becomes locked. If I go into the code and Unlock it in VB. But then it wont have your highlighted cell selected and locked. And it drops down to the next row labeling it Skid one when it is actually skid #2.

I'll show you in a video. Almost done uploading in a minute..


Is there a way to have it ignore anything after the very last digits in the code? whether it is a space or a extra carriage return? I don't know how to simulate the extra carriage return other than the actual barcode scanner. Hey...... I know how I can show you..... I have Youtube as well...... Ahhh ok ......Hmmmmmm. I will make a movie an upload for you on this wierd thing that happens when something extra is after the last digit.

Cheers/
Chris

I think I need an email address  o r Google PLus to share a private video on Youtube
Thanks
Try this please
:)
https://youtu.be/u0lUqt49fIA

Hi Chris,

that would be because I did not change the SendToDB code ... I can, of course.

> "ignore anything after the very last digits in the code"

I believe so ... they can be trimmed and stripped of trailing chr(13) or chr(10) -- whatever it is -- before splitting to an array

had trouble sleeping last night and only got a couple hours -- now it is catching up so I need to rest a bit.  When I come back, I'll get some popcorn and watch your movie :)

warm regards,
crystal

~ have an awesome day ~

Hi Chris,

actually watching it now ...

I can isolate the code that is running when the workbook opens and also run it after it does the transfer -- later though.  Later I can also strip extra characters from the end of the scan

warm regards,
crystal

~ have an awesome day ~

to remove CR from the end, in Worksheet_Change  (code behind Ark1 (InputData)), change:

sValue = Target.Value

to

   sValue = Trim(Target.Value)
   If Right(sValue, 1) = Chr(13) Then 'ASCII code for CR (Carriage Return)
      sValue = Left(sValue, Len(sValue) - 1)
   End If

Select allOpen in new window

TRIM is a function to trim leading and trailing spaces
RIGHT is a function to return a string from the end of the first argument that is the specified number of characters long
LEFT is a function to to return a string from the beginning of the first argument that is the specified number of characters long
LEN is a function to get the length of the specified string

you can also use the REPLACE function if CRs are in the middle of a barcode ... if that turns out to be the case, let me know and I will give you code :)

btw, nice photography ~and good that you teach!

It's the weekend. I have 2 days off. I might be able to try and input those codes. I will have even more questions for you. I keep coming up with bright ideas for process improvements. Lol. Now I just have to try to get my boss to pay for my monthly subscription to Exchange.

or maybe ask for a raise :)

of course, Debug, Compile, and Save before testing ...

Is it Monday already??
Hope you had a good weekend.

OK great, I finished playing with the code.
The Workbook now resets after it completes the SendTo sub... Awesome.

I tried the removal of the CR, It didn't work, but that's ok. It's something that really shouldn't happen anyways, and the sample label that I had laying around that had the additional CR at the end is something that I have checked ever single code for, It shouldn't really happen again. Maybe I was just being paranoid.

I went to the new warehouse on Friday and I scanned all the codes, Grrrrrrr, is all I can say, LOL
The Location bar codes are inconsistent.  I have a super easy question for some fast points.,

I can close this question out now I think.

For the next one, I will Copy the part of the code I need changed  then ask the question.

You set up the Code to accept DD-01-A for a location.
In reality the codes in the rest of the warehouse, are not all the same. They are close but not the same.

Most are DD01A with no dashes, and some have a B, or C, or D

The question will be to: Accept and change all Codes to "No Dashes" and to change the last Value at the end to letter "A" for every code, even if it says B or C.

How is that for easy??

thanks and you're welcome

what is on the end could be CR-LF, which is carriage return linefeed, in which case you'd want to strip chr(10) too

on the locations -- so you would want dashes to get added? Or scan codes with dashes and take them out?

G'Morning,
I need it to remove any dashes. Hey I was able to Add the condition if a "D" happens to also change it to "A"

That was easy. I tested the code for the A B C D it works great.

As fort the CR I am not sure how it works, when I build the string code in the QR designer, there is no indication that anything is present after the last character is programmed, you CAN however request a space or an ADDITIONAL CR afterwards. You cannot remove the default CR that is in place by default.

Does that make sense?

it would be good to test what the last character(s) of the scan really is ... or you can try this:

   sValue = Trim(Target.Value)
   do while Right(sValue, 1) = Chr(13)  or Right(sValue, 1) = Chr(10) 
      sValue = trim(Left(sValue, Len(sValue) - 1))
   loop

Select allOpen in new window

chr(13) is CR (carriage return)
chr(10) is LF (line-feed)

I am combing through the help files for the software, I can't seem to find what it is.
All I know is that it just goes down to the next line.

:(

did you try replacing the code behind InputData
after sValue = Trim(Target.Value)
with what I gave you above?

Yes I did it did not work , it still placed the cursor one cell below where it was supposed to.

oPPPPS , i did not try this new code yet. I will try now

well I guess that does make sense ... Excel has already processed the entry ...

best we can perhaps do it put it back by testing if there is a value in column A and move the activecell back up

put this into the Worksheet_change code, just to see if the target range is one or two rows:

msgbox target.rows.count,,"target row count"

I suspect it will be 1 ... but doesn't hurt to check.

Yah Tried it again, Nope, it a CR is programmed into the code, and I scan that code it still drops down.

I compiled, then saved it, closed it and re-opened the file.
Still the same :(

that means information will have to be tested to see what row it really belongs in since it could happen when scanning either value

oh! I have it ... row needs to be even.  If it is odd, then move it up one row.

try this after    Application.EnableEvents = False  

   If nRowScan Mod 2 = 1 Then
      nRowScan = nRowScan - 1
   End If

Select allOpen in new window

MOD is an operator that divides the first number (nRowScan) by the second number (2) and returns the remainder.

of course, this won't help if there are 2 CRs after the entry ...

alternately, the enter key behavior for Excel can probably be changed for the scans and then changed back for regular use of Excel ... but that would be another question

I just got bad news, Family emergency,
I have to run. I may drop  off the radar for a couple days.
My apologies.

Christopher
Sorry Crystal
Thanks so much :(

Another awesome solution
Great work
Thanks so much

Hi Christopher,

Prayers and best wishes for you and your family.  Don't worry about anything here.  Happy to help, and you are welcome.

Hope things resolve and you have a joyous thanks-giving.

warm regards,
crystal

~ have an awesome day ~

I am back at work today.
Everything is quiet and calm, Great GrandPa was 97, and struggled for the last couple months letting go.
He is resting now. There is a sense of relieve that he is not suffering any longer.

Back to work.....

I will try that last tidbit...
Talk soon.

Chris

I tried this in Ark 1

try this after    Application.EnableEvents = False  

   If nRowScan Mod 2 = 1 Then
      nRowScan = nRowScan - 1
   End If


At the end of this, Didn't seem to move the cursor up.
Hmmmm

Tuesday is done.
Heading Home for the day.
Are you working tomorrow?
Thanks
Chris

Hi Chris,

post what you have so far ... I will be here on and off ~

warm regards,
crystal

~ have an awesome day ~

I think  I am happy with how this one works now.
I can deal with the odd QR code that has a CR at the end.
I can deal with it.

Good Job.

I will post the next Question, but I want to post it when you are around so you can JJUMP on it, LOL

Hi Christopher,

Thanks.  I will be busy for the next few hours -- will post back when I have more time

warm regards,
crystal

~ have an awesome day ~