microsoft access serial number database trouble

[Buddy]

Hi everyone,
I'm in the very beginning stages of developing a database that will
develop serial numbers for a company. I've used Access in the past,
but have never had to use modules before and so have never had to
program in vba, although I do have some programming experience.
Please bare with me....

In essence, I need to develop a db that will make a serial number
based on the following layout:

FACILIY YEAR MONTH DAY SHIFT PACKINGAREA - UNITNUMBER
1 04 01 13 1 01 - 0001

When finished, the number should look like this: 1040113101-0001

I need to extract the year, month, and day from the computer's clock.
Facility, shift, and packing area will come from tables I've created
in the database. Unit number is a four-digit autonumber that will
increment by one. Since they do not make nearly 9999 products a day,
no serial numbers will be identical.

All I'm trying to do right now is get all of these fields to combine
in the above format, store itself in a separate table for query
purposes, and print out onto a small label to be placed on finished
goods.

Factory employees will be using a touch screen to log in and choose
the facility, shift, and packing area as appropriate via drop down
lists (I believe they are called "combo boxes" in Access) in a form.
Then, they will press a button on the bottom of that form that will
print out a label with the serial number.

I have no problems creating the tables and necessary forms, but I am
having serious issues extracting all this information and putting it
all into one combined number, with no spaces between save for the
single hyphen. I imagine modules will have to be created and
implemented, although I have no experience with them whatsoever.
I would be extremely grateful for any and all help. Could someone,
anyone, help lead me in the right direction or show me some similar
code that may help?
Please feel free to e-mail with suggestions.

Thank you all so very much in advance,
Buddy ([email protected])

 

[Tim Ferguson]

(e-mail address removed) (Buddy) wrote in

FACILITY 1
YEAR 04
MONTH 01
DAY 13
SHIFT 1
PACKINGAREA 01
UNITNUMBER 0001

When finished, the number should look like this: 1040113101-0001


All I'm trying to do right now is get all of these fields to combine
in the above format, store itself in a separate table for query
purposes, and print out onto a small label to be placed on finished
goods.

All fine except for the bit about storing the string. Keep the thing as
separate fields (actually, I'd use a proper date, so you'd only have a
five-field unique index) and display the full string in a textbox as one.
It's very easy to create a ControlSource like

= Format([Facility],"0") & Format([CreateDate],"yymmdd") & _
Format[Shift],"0") & Format([PackingArea],"00") & _
Format([UnitNumber],"0000")

[1] make sure it's all on one line
[2] are there really people out there stupid enough to ignore century-
digits/

I imagine modules will have to be created and
implemented, although I have no experience with them whatsoever.
I would be extremely grateful for any and all help

Nothing of the sort: just put the formula into the ControlSource of the
textbox on the report, or whatever you are using to print these labels, and
it's all fine.


Hope that helps


Tim F

 

[Buddy]

Tim - I really appreciate the quick response. I'm going to try that
later this afternoon when I get a chance.
By the way, I failed to mention this database is to be used in a
multi-user environment with 4 or 5 front-end clients. Would this
method be secure in such an environment? Thanks again...I'll post
back soon if and when I get stuck again.

(e-mail address removed) (Buddy) wrote in

FACILITY 1
YEAR 04
MONTH 01
DAY 13
SHIFT 1
PACKINGAREA 01
UNITNUMBER 0001

When finished, the number should look like this: 1040113101-0001


All I'm trying to do right now is get all of these fields to combine
in the above format, store itself in a separate table for query
purposes, and print out onto a small label to be placed on finished
goods.

All fine except for the bit about storing the string. Keep the thing as
separate fields (actually, I'd use a proper date, so you'd only have a
five-field unique index) and display the full string in a textbox as one.
It's very easy to create a ControlSource like

= Format([Facility],"0") & Format([CreateDate],"yymmdd") & _
Format[Shift],"0") & Format([PackingArea],"00") & _
Format([UnitNumber],"0000")

[1] make sure it's all on one line
[2] are there really people out there stupid enough to ignore century-
digits/

I imagine modules will have to be created and
implemented, although I have no experience with them whatsoever.
I would be extremely grateful for any and all help

Nothing of the sort: just put the formula into the ControlSource of the
textbox on the report, or whatever you are using to print these labels, and
it's all fine.


Hope that helps


Tim F

 

[Tim Ferguson]

(e-mail address removed) (Buddy) wrote in

By the way, I failed to mention this database is to be used in a
multi-user environment with 4 or 5 front-end clients. Would this
method be secure in such an environment?

The "method" does not have any problems in a multiuser setup. The most
likely difficulty will be how you create the UnitNumber.

The usual way of doing this with DMax(...). If two users will read the same
DMax at the same time, the slightly later one will try to create the same
record that the slightly earlier already did. You can either test for this
specifically or else trap the (probably fairly infrequent) error and
recover gracefully. Either way, you don't want your poor user to be facing
a Key Violation error without explanation.

All the best


Tim F