Serial numbers to products

[Slippery_1 via AccessMonster.com]

I have an input on a form that I want to update other text boxes based on a
serial number on the same form . My problem is that my serial numbers are not
a fixed size and have different prefixes to identify the product.

Example:
USxxxxxx (where US is the product and xxxxx is number made)
ULPxxxx (Where ULP is the product and xxxx is the number made)
ESS3xxxx (Where ESS3 is the ......etc, etc... )

I have 45 products. My form has 4 text boxes:
1) Serial number (Input searches SerialData table)

gives me output data in text boxes:
Form1
2) Product Type = (bound to ProductList table)
3) Date Installed = (from Serial numbers table)
4) Still under warranty? = (Yes/no - 3 yrs from date installed)

Table1:
SerialData (Table)
SerialNumber (Key Field)
DateInstalled (Field)
WarrantyExp (Field)

Table2:
ProductList (Table)
Prefix (Key Field - identifies product type from serial number)
Product (Field - product description)
ProductCode (Field - Product code)

With 40 plus products, how can I output the identity of the product in my
form based on so many different prefixes? My serial number have prefixes of
2 to 4 alphanumeric characters and a 4 or 5 digit number sequence it was made.
Some type of an array maybe? If then statements? Possible? I believe all
serial numbers are either 7 or 8 characters total if that helps.

My goal:
I input a serial number, it tells me what product, what date it was installed
and is it still under warranty.

TIA for any help at all

 

[Klatuu]

If your product code prefixes are always Alpha and the number made is always
numeric, this function will pull them apart into a 2 element array.
Element(0) being the prefix and element(1) being the number made. Now, the
problem is you state "prefixes of 2 to 4 alphanumeric characters and a 4 or 5
digit number sequence ", but you show a prefix of ESS3 which contains a
number. So, I don't know which is correct. This function will not work if
the prefix has a number in it.

Function SplitProdCode(strProdCode As Variant) As Variant
Dim lngX As Long

For lngX = 1 To Len(strProdCode)
If IsNumeric(Mid(strProdCode, lngX, 1)) Then
Exit For
End If
Next lngX
SplitProdCode = Array(Left(strProdCode, lngX - 1), _
Right(strProdCode, Len(strProdCode) + 1 - lngX))
End Function

If, in fact you have prefixes with numbers, I would suggest you create a
table containing all your valid prefixes. Then you could use this approach:
strNumberMade = Replace(strProdCode,rstPrefixes![PREFIX],"")

 

[Slippery_1 via AccessMonster.com]

Klatuu wrote:

, in fact you have prefixes with numbers, I would suggest you create a
table containing all your valid prefixes. Then you could use this approach:
strNumberMade = Replace(strProdCode,rstPrefixes![PREFIX],"")

Thanks for the reply. Could you help me a bit with that line please. Trying
to learn VBscripting on the fly...I am having to change focus from
programming microcontrollers to this...don't ask!

"Replace" does just what it says I'm thinking.

"(strProdCode,rstPrefixes![PREFIX],"")" Now that part hurts my head!

The "(rstPrefixes!" changes the result of the "strProductCode" to the "
[PREFIX],"") " that matches in my table? What does "rst" do? I understand if
you don't have time to school me...

I have table with the prefixes..so could I just look at the first 4 digits in
the SN and compare it to them for a match? I have seen code to add a digit to
a number (eg a zero in front of) to make it a fixed length. Would that help?

I could deal with it if I could do lot's of if thens.
"IF SN=US##### THEN ProCode=US"
"IF SN=ESS3#### THEN PorCode=ESS3"
Etc...

Can that be done in VB? Crude yes, but....

TIA

 

[Klatuu]

See below for brilliant, insightful answers

Klatuu wrote:

, in fact you have prefixes with numbers, I would suggest you create a
table containing all your valid prefixes. Then you could use this approach:
strNumberMade = Replace(strProdCode,rstPrefixes![PREFIX],"")

Thanks for the reply. Could you help me a bit with that line please. Trying
to learn VBscripting on the fly...I am having to change focus from
programming microcontrollers to this...don't ask!

"Replace" does just what it says I'm thinking. Yes, See Help for more info
"(strProdCode,rstPrefixes![PREFIX],"")" Now that part hurts my head!

That's why I usually wear a helmet when I do this. In this case,
strProdCode is the combination of the prefix and the number made. I think
this is what you are calling serial number. rstPrefixes![PREFIX] I made up as
having a table with the prefix in it. The "" means replace the value of
strRrefixes![PREFIX] with a zero length string everywhere the prefix is found
in strProdCode.

The "(rstPrefixes!" changes the result of the "strProductCode" to the "
[PREFIX],"") " that matches in my table? What does "rst" do? I understand if
you don't have time to school me...

rst is just good naming convention. rst means the variable referrs to a
recordset object, like str in strProdCode means it is a string variable.

I have table with the prefixes..so could I just look at the first 4 digits in
the SN and compare it to them for a match? I have seen code to add a digit to
a number (eg a zero in front of) to make it a fixed length. Would that help?

Yes, but since the length of the prefix is not known, it would be difficult.
I see you have the prefix in Table2. If you can position to the correct
record in Table2, you could use that in the Replace statement.

I could deal with it if I could do lot's of if thens.
"IF SN=US##### THEN ProCode=US"
"IF SN=ESS3#### THEN PorCode=ESS3"
Etc...

Bad Idea. Products come and go. That would mean changing your code every
time there is a change in your product line. It is always best to avoid hard
coding data.

Can that be done in VB? Crude yes, but....

TIA

Not hard to learn. I have no idea how to control a micro and the thought of
uncontrolled micros running around is scary.

 

[Slippery_1 via AccessMonster.com]

See below for brilliant, insightful answers

Bad Idea. Products come and go. That would mean changing your code every
time there is a change in your product line. It is always best to avoid hard
coding data.

I rarely have to deal with new products. Maybe 1 or 2 a year...still a bad
idea?

There is no "CASE SELECT"

case1
If a=XXX Then b=blah!
case2
If a=yyy Then b=argh!

Or something similar In VBscript?

Not hard to learn. I have no idea how to control a micro and the thought of
uncontrolled micros running around is scary.

I haven't found an online tutorial for VBscript for Access (or VBA?), book or
link you would recommend maybe? Or do I need Visual Basic. I have 6.0 but
never used it. Haven't had time.

 

[Klatuu]

It is Select Case in VBA
Select Case SN
Case is = something
ProCode = whatever
Case is = something else
ProCode = younameit
Case is = anotherthing
ProCode = somethingcompletly different
Case Else
ProCode = whoknows
End Select

But, in a moment of rare inspiration, I think I have a solution.
******Amazingly Brilliant Code**************
strSN = "ESS39876"
ProCode = dlookup("[Prefix]","ProductList","Left('" & strSN &
"',len([Prefix]))=[Prefix]")
******Amazingly Brilliant Code Ends*********

 

[Slippery_1 via AccessMonster.com]

It is Select Case in VBA
Select Case SN
Case is = something
ProCode = whatever
Case is = something else
ProCode = younameit
Case is = anotherthing
ProCode = somethingcompletly different
Case Else
ProCode = whoknows
End Select

But, in a moment of rare inspiration, I think I have a solution.
******Amazingly Brilliant Code**************
strSN = "ESS39876"
ProCode = dlookup("[Prefix]","ProductList","Left('" & strSN &
"',len([Prefix]))=[Prefix]")
******Amazingly Brilliant Code Ends*********

I'm moving the fax lines under the building today....too many hats I tell you!
Too many hats! I wish someone wanted my job! <jk>

Oh well....I'm going to try to figure out what it does and see how to use it
tommorrow. What do I owe you?
Very much thank you for the help! I'm a newbie pretending to be a novice at
everything I do! LOL

 

[Slippery_1 via AccessMonster.com]

But, in a moment of rare inspiration, I think I have a solution.
******Amazingly Brilliant Code**************
strSN = "ESS39876"
ProCode = dlookup("[Prefix]","ProductList","Left('" & strSN &
"',len([Prefix]))=[Prefix]")
******Amazingly Brilliant Code Ends*********

Okay, I see that "ProCode = dlookup..." line compares the Serial number to
the prefix of my ProductList table for a match. Particularly the left side of
the SerialNumber string, right?

Once I have assigned a Productcode (Uniform 5 digit number) based on the
serial number input, I can then extract the data I'm looking for (Product).
Right? Yes I think. Do I update my SerialData Table with a "ProductCode"
column? Yes I think (Should be a lookup from the ProductList table?)

My modified code is:
*********Botched code************
Private Sub SerialNumber_Change()
strSerialNumber = "ESS39876"
ProductCode = DLookup("[Prefix]", "ProductList", "Left('" & strSerialNumber &
"',len([Prefix]))=[Prefix]")
End Sub
********End botched code*******

My confusion is in the strSN = "ESS39876" part of the code. What if my
SerialNumber "string" starts with US instead of ESS3? Not sure what this line
does exactly. Could I trouble you for an explanation?

I must also figure out to pass the ProductCode # in the script to my
SerialData table so that my serial number input puts the appropriate
ProductCode with the Serial number entered. Can I add a line to update my
SerialData table to with the PorductCode #

 

[Klatuu]

strSN = "ESS39876"
ProCode = dlookup("[Prefix]","ProductList","Left('" & strSN &
"',len([Prefix]))=[Prefix]")

I used the names in your current table. I believe you describbed the
ProductList table as having a field for the prefix. So, what this does is
compare the left side of the SN for the length of the prefix in the table.
So if it finds a match, then that should be your prefix.

But, in a moment of rare inspiration, I think I have a solution.
******Amazingly Brilliant Code**************
strSN = "ESS39876"
ProCode = dlookup("[Prefix]","ProductList","Left('" & strSN &
"',len([Prefix]))=[Prefix]")
******Amazingly Brilliant Code Ends*********

See below for brilliant, insightful answers

Okay, I see that "ProCode = dlookup..." line compares the Serial number to
the prefix of my ProductList table for a match. Particularly the left side of
the SerialNumber string, right?

Once I have assigned a Productcode (Uniform 5 digit number) based on the
serial number input, I can then extract the data I'm looking for (Product).
Right? Yes I think. Do I update my SerialData Table with a "ProductCode"
column? Yes I think (Should be a lookup from the ProductList table?)

My modified code is:
*********Botched code************
Private Sub SerialNumber_Change()
strSerialNumber = "ESS39876"
ProductCode = DLookup("[Prefix]", "ProductList", "Left('" & strSerialNumber &
"',len([Prefix]))=[Prefix]")
End Sub
********End botched code*******

My confusion is in the strSN = "ESS39876" part of the code. What if my
SerialNumber "string" starts with US instead of ESS3? Not sure what this line
does exactly. Could I trouble you for an explanation?

I must also figure out to pass the ProductCode # in the script to my
SerialData table so that my serial number input puts the appropriate
ProductCode with the Serial number entered. Can I add a line to update my
SerialData table to with the PorductCode #