By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,880 Members | 2,279 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,880 IT Pros & Developers. It's quick & easy.

Enter a range of values into a table with other values

P: 6
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.

Mar 10 '10 #1
Share this Question
Share on Google+
11 Replies

Expert Mod 15k+
P: 31,489
This could be clearer. Are you saying that you would want, from your example :
Expand|Select|Wrap|Line Numbers
  1. SerialNo    PartNo    PONo    Date Entered
  2. abc123      A234J1    5008    3/10/2010
  3. abc124      A234J1    5008    3/10/2010
  4. ...
  5. abc454      A234J1    5008    3/10/2010
  6. abc456      A234J1    5008    3/10/2010
Mar 10 '10 #2

P: 6
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.

Mar 10 '10 #3

Expert Mod 15k+
P: 31,489
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.
Mar 10 '10 #4

P: 6
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.
Mar 10 '10 #5

Expert Mod 15k+
P: 31,489
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.
Mar 10 '10 #6

P: 6
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?
Mar 11 '10 #7

Expert Mod 15k+
P: 31,489
It makes sense I suppose, but you still need to sort it out before you can think of starting to code up the logic.
Mar 11 '10 #8

P: 6
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.
Mar 11 '10 #9

Expert Mod 15k+
P: 31,489
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.
Mar 11 '10 #10

P: 6
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?
Mar 11 '10 #11

Expert Mod 15k+
P: 31,489
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.
Mar 11 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.