Enter a range of values into a table with other values

[technet]

I am using Access 2007. I created a serial number database that imports excel into a table. I have now been asked if they can import a range of numbers directly into access. I have a single entry form which works but how do I do it for a range of serial numbers and include some values that don't change?
Here is an example of what they want to do:
Serial number range: abc123 - abc456
Part Number: A234J1
PO Number: 5008
DateEntered: 3/10/2010

Part numbers are in their own table and those won't change much and if they do they can be entered on their own existing form - but these need to be verified against the part number table so make sure they are correct
The rest of the fields are all in a serialnumber table.

Serial numbers are of course unique and if there was a pop up that said one of them being entered is already used that would be a bonus.

Thanks!

[NeoPa]

This could be clearer. Are you saying that you would want, from your example :

SerialNo    PartNo    PONo    Date Entered
abc123      A234J1    5008    3/10/2010
abc124      A234J1    5008    3/10/2010
...
abc454      A234J1    5008    3/10/2010
abc456      A234J1    5008    3/10/2010

[technet]

Yes that's exactly what I'm looking for. There are a couple other fields to be added but I can do those once I get an example.

Thanks.

[NeoPa]

You'd need to be clearer about which characters are allowable in your sequence and which parts of the value should be considered as sequencing and which parts static. For instance, what about abc123 to def456? Would the last three characters be limited to simple digits but the 1st 3 characters be limited to alpha characters? That would be my guess, but hasn't been stated anywhere. It would have a completely different outcome if the whole ASCII range were included.

Whatever the rules are, you're likely to need a loop in your code that goes through all the possible variants between the two values specified. For each iteration a record would need to be added. This can be done either using Recordset processing (Access VBA DAO Recordset Loop Using Two Recordsets), or an INSERT INTO query can be executed with the required values.

There is nothing I'm aware of in SQL that allows you to add a range of records in a single instruction I'm afraid.

[technet]

There are no limits on what the serial numbers can be as they are given to us by our supplier. no character limits, no validation data other than they can't be used again if they are already in the table.
They could be 1b123 or 123AGK32J2. No limits other than not more than 15 characters and a-z,A-Z and 0-9.

[NeoPa]

So, for your example (abc123 - abc456), instead of there being 333 records in the sequence, there are actually 11,721? Is that what you're saying?

You won't get anywhere close to getting the code to work properly until you have this detail thoroughly understood.

[technet]

It could be up to 500 different serial numbers in some kind of sequence. Don't now what that sequence is until they are issued to us by the vendor. The example abc123 is probably too short. I think they are currently using about 10 characters in some type of sequence. Ranges could be 10 or 100 or 400 no idea or control over that part. I can see why this could be a programming issue not knowing how many characters or how many numbers.
Does that make sense?

[NeoPa]

It makes sense I suppose, but you still need to sort it out before you can think of starting to code up the logic.

[technet]

Not sure what else to sort out. I don't have any more specifics as they come from someone else out of my control and can change with the next batch they issue.
How about entering the fixed part (1b23) then enter the range (200-400) to append on the end? It could also be 1b23 then a45-d90.
I can try and ask the vendor tomorrow if they have more to give me for specs.

[NeoPa]

Sounds like a plan. Specs are like the base of a pyramid to a project. Something everything else rests upon. Unless that is sorted out entirely robustly the structure will crumble. Code doesn't work like humans do. It can't think for itself, that must be done in the logic before it runs, and if the logic isn't finitely and clearly stated the code won't even get to first-base.

[technet]

The most I info I can get is that the text part (abc) won't ever change for a particular batch. They will change the number part (100-200) part. So if the end-user can key in the abc then a range of numbers and have those increased and joined together I think that would work. abc100, abc101...abc199, abc200

Just my thoughts on it.
They will also be keying in static info - POnumbers, date, order#, part#, etc. Those won't change for the entire batch input.
Does that make more sense?
Thanks!

[NeoPa]

Yes it does. That takes us back to the state we were in after you confirmed my understanding of post #2.

In that case you'd want three controls on your form. One each for Base Alpha, Start Numeric Sequence and End Numeric Sequence.

This would then take you back to the second paragraph of post #4. I believe the instructions there are now appropriate and will enable you to proceed with getting this done. Let us know if you have any further problems, or better yet, that you've successfully built the code to do the job.

Good luck. We're here if you need us further.