Allowing user to enter a range of values in a table

[ahuffman24]

Hi,

I have a question that I think may be a little complicated. I have a form called "Customer Order Information" with a subform inside it (the subform is in the format of a table). This form allows the user to enter customer order information. The upper part of the from (not the subform) contains fields from a table called "Customer/Order Info Table," and the subform contains fields from a table called "Inspection Table." These two tables are linked with the relationship shown in the picture below. A picture of the Customer Order Information form is also shown below.






My question is regarding the subform. In the subform, the user will enter item numbers for each item that is ordered as well as the serial numbers corresponding with those items. The quantity of each item can be more than one, depending on what the customer orders. This would mean that, for example, if a customer orders five of item 1, there would be five serial numbers (a serial number is assigned to each order of item 1). The serial numbers have both letters and numbers (example: D-596 could be a serial number), and, sticking with the previous example, each order of item 1 would have a numerically ordered serial number (the first order would have serial number D-596, the second would be D-597, third would be D-598, fourth would be D-599, and fifth would be D-600). So, when the user enters this information into the subform, I want them to be able to enter the item number in the Item column and enter a range of the serial numbers in the Serial Numbers column; instead of typing each serial number out individually, they could type "D-596, D-600" and the table would automatically link that item to the 5 serial numbers. I know this will require some VBA code, but how should I go about doing that? Thanks!

[rpeare]

If your serial numbers are always sequential, and your prefix is part specific can you not just have a routine that would automatically number them for you rather than having to rely on a person hand entering a starting and/or ending value?

I do not like bound forms so someone may have a better suggestion than me but I would look at the BEFORE INSERT and/or BEFORE UPDATE events and just look up the most recent part number and increment it by 1 for each element they enter and not have them touch the serial number at all.

So let's say Part A has a serial number prefix of AA, Part B has a serial number prefix of BB and your standard formatting for all part numbers is <prefix>-<incrementing number>

in your BEFORE INSERT event you would have something like

dim sOldSN, sNewSN as string
dim iSeq
'this domain function assumes ITEM NUMBER is actually a number, insert ' marks before and after the field reference on your form if it's actually a text value
sOldSN = dmax("[Serial_Number]", "Inspection Table", "[Item Number] = " & [Item Number]) 
'sOldSN = dmax("[Serial_Number]", "Inspection Table", "[Item Number] = '" & [Item Number] & "'") 'THIS IS FOR TEXT VALUE ITEM NUMBERS
iSeq = clng(right(sOldSN, len(sOldSN) - instr(sOldSN, "-"))) + 1
sNewSN = left(sOldSN, instr(sOldSN, "-")) & iseq
[Serial Number] = sNewSN

Secondly a couple of items here.
Do not name your database objects with special characters ( spaces, /, #, %, etc) this can really screw up the functionality of your database over time, spaces are less offensive but any place you have a space you should use an underscore (_).
The way your tables are set up does not look properly normalized to me. Your INSPECTION table should be carrying the ID field of the Customer/Order Info Table, you should not link these two on three fields when 1 will do. You're essentially carrying three times the data as you need to to establish a link.
Finally, do all of your parts require the same tests be done? The way your table is set up they do. If that's not the case, in other words if your parts require different tests but you are attempting to put all possible tests on the same table even if they don't apply, that also is probably not a great setup.

[ahuffman24]

@rpeare, thank you for the reply. Where is the BEFORE INSERT event and where do I type the code?
Also, all parts do require the same tests to be done. I changed the names of the tables/fields as well.

[rpeare]

if you bring up the design view of your subform and open the properties window for the form itself, if you have the ALL tab selected and scroll down in the properties window you'll find the BEFORE INSERT property. If you click that row you'll see three dots on the far right hand side of the row, click those and select the one that says something about 'code' (I forget what it is, I've set access up to default to a code window).

[ahuffman24]

Thank you for the reply. I have figured out a different way to accomplish this, but if I have any further questions I will let you know. Thank you for your help!