Question In between serials

Hi everyone, I need help on my issue:
I have a table where I write serial numbers of insurance policies (the blank forms), for instance I write the first serial number and the last serial number (example 001 - 010). I have to fields where I write the serials, field FIRSTSERIAL and LASTSERIAL.

Is there any chance to store (in the table) the serials between 001 and 010 as particular records.

When I write 001 in the FirstSerial field, and 010 in the LastSerial field, I would like to have the chance of storing the other serials (002;003;004;...) as a particular record.

I have no clue, how this could be done, so please help with some ideas, how can I get this thing to work.

I hope I made my self clear on this.


Thanks a lot.

It's actually fairly easy. Open a recordset on the target table (you could also execute SQL). Start a For/Next loop using your two values. Within the loop, use the AddNew method of the recordset to add a new record, using the loop counter value. Give it a try and see how it goes.

Not that I'd necessarily endorse the pre-emptive creation of records (it's an imperfect practice that I have employed myself when it's actually necessary ;-) but you can also query it if you maintain a convenient table for the purpose.

A simple table such as "tblNumbers" with a single column "Num" of Integer type.
Fill it with the maximum number of records you'll ever want with incrementing values. (e.g. 100 rows with the first row 1 and so on up to 100).
This table can just sit locally forever servicing any request of this nature.

Insertions then become as easy as

INSERT INTO tblTargetTable (FieldSerialNo)
SELECT Format(Num, "000") FROM tblNumbers WHERE Num BETWEEN Val([FIRSTSERIAL]) AND Val([LASTSERIAL])

Obviously you can replace those implicit parameters with for references or whatever process you prefer. And whatever other fields required, such as a foreign key value etc...
e.g.
INSERT INTO tblTargetTable (FKField, FieldSerialNo)
SELECT Forms!FormName!FKField, Format(Num, "000")
FROM tblNumbers
WHERE Num BETWEEN Val(Forms!FormName!FIRSTSERIAL) AND Val(Forms!FormName!LASTSERIAL)

And so on.

Cheers.

That's nowhere near as much fun as my idea. Simpler, but not as much fun.

By the way - that doesn't mean you can only enter serial values between 1 and 100.
It means that 100 is the most records you can enter at once.
If you wanted to allow for, say records between 151 and 250 the slightly different query will still work...

INSERT INTO tblTargetTable (FieldSerialNo)
SELECT Format(Val([FIRSTSERIAL]) + Num - 1, "000")
FROM tblNumbers
WHERE Val([FIRSTSERIAL]) + Num - 1 BETWEEN Val([FIRSTSERIAL]) AND Val([LASTSERIAL])

You could even cartesian query your way into larger numbers without adding more base records - but that just seems silly by then really (performance would tend to drop too).

>> not as much fun

What? Slightly out of the norm querying less fun than writing some iterating VBA? :-O
Surely not.

Great suggestion Leigh! Thanks for sharing. BTW, I think Paul's would be more fun as well. Merry Christmas you guys.