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

504208 20OCT2015 J5572 5 16     (this skid has 16 boxes)
504208 20OCT2015 J5572 5           (this skid - no case count which means we need to pull from DB)
504208 20OCT2015 J5572 5 16   (this skid has 16 boxes)
504208 20OCT2015 J5572 5 87   (this skid has 87 boxes)
504208 20OCT2015 J5572 150   (this skid has 150 boxes)
504208 20OCT2015 J5572 5       (this skid - no case count which means we need to pull from DB)
chris pikeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ~
0
chris pikeAuthor Commented:
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
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ~
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

chris pikeAuthor Commented:
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.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ~
0
chris pikeAuthor Commented:
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.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ~
0
chris pikeAuthor Commented:
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.
0
chris pikeAuthor Commented:
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
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ~
0
chris pikeAuthor Commented:
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.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Chris,

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

warm regards,
crystal

~ have an awesome day ~504208
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
0
chris pikeAuthor Commented:
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)
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ~
0
chris pikeAuthor Commented:
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.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ~
0
chris pikeAuthor Commented:
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
0
chris pikeAuthor Commented:
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
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ~
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ~
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
in mod_SendToDB, add this code:

Sub InitializeWorkbook()
'151119 moved code from WorkBook_Open
    Dim ws As Worksheet _
      , nLastRow As Long
    Set ws = Worksheets("InputData")
    
    Call UnABC
    
    ws.Select
    Worksheets("Data").Visible = xlSheetHidden
    nLastRow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row
    
    With ws
      If nLastRow < 4 Then
         nLastRow = 4
         .Cells(4, 1) = "skid"
         .Cells(4, 2) = 1
      End If
      If ws.Range("D" & nLastRow).Value = "" Then
         With .Range("D" & nLastRow)
             .Locked = False
             .Select
             .Interior.Color = RGB(255, 255, 0) 'bright yellow for active cell
         End With 'D
         .Range("J" & nLastRow).Locked = True
      Else
         With .Range("J" & nLastRow)
             .Locked = False
             .Select
             .Interior.Color = RGB(250, 230, 200) 'orange for active cell
         End With 'J
         .Range("D" & nLastRow).Locked = True
      End If
    End With 'ws
    
    Call ABC
Proc_Exit:
   On Error Resume Next
   Set ws = Nothing
   Exit Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   InitializeWorkbook"

   Resume Proc_Exit
   Resume
End Sub

Open in new window


In ThisWorkbook, change Workbook_Open to this:

Private Sub Workbook_Open()
'modified by crystal (strive4peace) 151119
   On Error GoTo Proc_Err

   Call InitializeWorkbook
   
Proc_Exit:
   On Error Resume Next
   Exit Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   Workbook_Open"

   Resume Proc_Exit
   Resume
End Sub

Open in new window


now that the initialize code is in its own procedure, you can also call it after the transfer.
In SendToDB, before
MsgBox "Done consolidating data", , "Done"

add this statement:
Call InitializeWorkbook

Open in new window


this solves one problem ... now onto the next ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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

Open 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
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 :)
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
btw, nice photography ~and good that you teach!
0
chris pikeAuthor Commented:
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.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
or maybe ask for a raise :)

of course, Debug, Compile, and Save before testing ...
0
chris pikeAuthor Commented:
Is it Monday already??
Hope you had a good weekend.
0
chris pikeAuthor Commented:
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??
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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?
0
chris pikeAuthor Commented:
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?
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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

Open in new window


chr(13) is CR (carriage return)
chr(10) is LF (line-feed)
0
chris pikeAuthor Commented:
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.

:(
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
did you try replacing the code behind InputData
after sValue = Trim(Target.Value)
with what I gave you above?
0
chris pikeAuthor Commented:
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
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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.
0
chris pikeAuthor Commented:
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 :(
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
try this after    Application.EnableEvents = False  
   If nRowScan Mod 2 = 1 Then
      nRowScan = nRowScan - 1
   End If

Open 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
0
chris pikeAuthor Commented:
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 :(
0
chris pikeAuthor Commented:
Another awesome solution
Great work
Thanks so much
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ~
0
chris pikeAuthor Commented:
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
0
chris pikeAuthor Commented:
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
0
chris pikeAuthor Commented:
Tuesday is done.
Heading Home for the day.
Are you working tomorrow?
Thanks
Chris
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Chris,

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

warm regards,
crystal

~ have an awesome day ~
0
chris pikeAuthor Commented:
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
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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 ~
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.