Entering multiple serial numbers into a single sub-form field / record

[DaveyG]

Hi,
So I have a main Form, Sub Form set-up.
This is to do with product shipments.
Main Form displays the order info, and Sub Form displays the shipments against that order.

At present, each product on an order has a Serial Number.
Lets say that Order X, has Product ABC x 20

We might do 1 shipment or multiple over a period of time until the order is complete.

At present, we can enter either a single Serial Number, or a range within a single record on the Sub Form, i.e. Start Serial | End Serial. There are only these 2 fields.

i.e. 1 Serial shipped: 0218123456 | 0218123456
i.e. Range shipped: 0218123456 | 0218123458 (3 products shipped)

Sometimes, we have to ship multiple products from the same order, but the serials are all mixed, so we might have numerous singles and multiples to load.

Rather than entering a single or 1 range per record, I would prfer to enter ALL the serials insay a List Box or something, 1 single or range per line.
I would also need this data to be searchable by the database on other search forms within the application.

i.e. on my single shipment record I might have a list box where I can populate such as:

0218123456
0218123458
0218123462
0218123464-468
0218123470-492

I need to be able to find the complete shipment info by searching for any of these single numbers or numbers including and within a range.

So, what table field type would I need, what Form Type would I need, and How could I search against this field? how would it be stored?

Many thanks in advance.

 

[Minty]

You really need a sub table for each order line.
All it really needs is the OrderLineID, and a single field for one serial number in full.

Storing 0218123470-492 as 22 serial number records is going to be a complete nightmare to search on long term, and breaks all normalisation rules.

 

[Mark_]

As a follow up to Minty's post,

Even if you were to allow your users to enter "0218123470-492" into an unbound text field then parse it to drive adding individual records, I would be very weary of this as you'll have a rather large mess to correct the first time someone makes a typo and adds several thousand entries rather than the three desired.

 

[DaveyG]

Thanks everyone for your replies, much appreciated.

At present, in our Shipment table, we have 2 fields, FirstSerial & LastSerial.
There is also the ShipmentQty in this table.
Once the qty is entered, it auto matically enters the LastSerial after your enter the FirstSerial based on this Qty - which eliminates typos.

At present in my application I can easily search for shipments based on these fields, or within the First & Last as a range.

Sometimes, If I have lots of shipments to enter, where each shipment is a partial of the complete order and has mixed singles and ranges, I was hoping there was an easier way rather than having to manually enter each single or range as a separate record in our subform for the shipments.

I just wondered whether there was a way to enter them all, line by line in a single List box (just the serials pertaining to a specific individula shipment), and then once the record gets saved, it takes them from the list box, reads them out and stores them separately in the table, i.e.

Lets say I keep the fields FirstSerial & LastSerial but no longer use them for entering the numbers.
I replace these on my shipment (sub form) with a list box into which I can enter all my relevent Serials line by line as exampled in my original post.

Upon save, if a line has an individual/single number it creates a record populating First & Last Serial with the same number.

Upon save, if a line has a range number it creates a record populating First & Last Serial with the relevant two numbers, First & Last.

So in essence it creates the same records which I would have done manually, but in far less time.

Does this make sense? Alternatively, If anyone can come up with a better solution?

The other reason I wanted this solution was because we also sell other manufacturers products which have different style serial numbers which do not really fit the data style my app was created for. Originally it ws just for our own serial number styles.

A free range field would now be much better for storing.

Thanks again for all your input, thoughts & advice.

 

[Mark_]

For your business model, how are you ensuring serial numbers are consecutive?

If you do use a system like this, you would need to do a loop through your range of serial numbers, but you'd first have to figure out how to break out what he user has typed in to create your range. After that it would be a simple loop structure.

The biggest issue with any system like this would be if you have a missing serial number. If your company is manufacturing, this would imply you do your quality control prior to putting a serial number on the product. This also requires your shipping department to always go in serial number order.