Matching part of a variable with a list of codes

[Brian Martin]

I have a form on which the user adds in the part details and then clicks a button to add this new part to the parts table. Each part starts with a certain code eg. HP-A = the code and HP-A-1001 = a part number based on this code. There are many other different codes. I have a table that stores the codes themselves. I want to add code to my button that says something like:

If NewPartNumber doesn't begin with any of the part codes in the partcodes table THEN
MessageBox"That isn't an existing part code!!"

I have a listbox that displays all of the partcodes from the partcodes table so either this or the actual table could be used to match the part number with a code (or not).

I've not done much vba coding

Any help would be appreciated!!

 

[Wicklund]

Is your code always 4 characters (i.e. "HP-A")?

 

[Brian Martin]

Not always! It's sometimes codes like HP-A-1 but I don't see how it matters cos I search for parts like code"*" in a query and it's ok!

 

[Wicklund]

If you code was always the same number of characters, let's say four, then you could use the LEFT function to truncate your entry, and compare to your list.

However, since the code can be of different lengths, I am afraid this one is beyond my capabilities.

Sorry, and good luck.

 

[dennis g]

Brian:

Could you not use the len function to determine the length of the code, then the Left function (using the number returned by Len() to get the correct string to match to your code list?

codeLength = Len(code.yourdb)
codeA = Left(part.yourdb,codeLength)

Something like that?

 

[OPMCoordinator]

how about..

if your form was for adding new parts only and not viewing existing records...

if the beginnings of your codes (lets call ValidPrefix) was in a table and you had one combo box, limit to list based on the data in ValidPrefix, and a text box for the second part of the new part number.
dont let the user add a record unless the ValidPrefix is OK and there is something in the text box.
when button is clicked, concatenate (join) the strings from the combo and text box into your table along with the (presumably) descriptions etc. do the same (join the strings) to search for duplicates first.

if there was a set format to your part numbers - ie if the bit you wanted to check was ALWAYS toward the left of a " - " eg apple-red, orange-orange, grape-green.
in this case you can examine the string for the "-", get its position in the string, use the left function combined with this and compare to your list.


hope this helps
Mike C

there is no such thing as cant