Adding a Range of Numbers from a form to the Table

[tai.cabrera]

What I would like to do is input a range of serial numbers in a form
and have that range populate in the table without me having to put them
in one at a time manually. The numbers do not exist yet, either. I'm
not sure how else to explain it.

The tables I use have the following Fields:
Table 1
(PK) Serial Number
Date Sold
Sold By

Table 2
(PK) Serial number
Date Redeemed
Redeemed By

This is for a gift certificate database. Sometimes we sell more than
one gift certificate at a time by the same seller on the same date.
Sometimes we sell up to 1000 to 1500 gift certificates a day so you can
see that inputting the certificate number can get tedious at times. I
have 2 different forms aswell

Here are the following fields for both forms:

Form 1:

From Number

To Number
Date Sold
Sold By

Form 2:

From Number

To Number
Date Redeemed
Redeemed By

I also want to be able to detect if a serial number in the redeemed
database, doesn't exist in the sold database. As well as catch
duplicate serial numbers sold and redeemed. Does this make any sense
and is it doable. Thanks for your help
~tai

 

[Guest]

I think this will do what you want to accomplish.

Table 1
(PK) Serial Text – primary key will not allow duplicates
Date Sold - datetime
Sold By - text

Table 2
(PK) Serial number – Text
Date Redeemed
Redeemed By

Create a one-to-one relationship between Table 1 and Table 2. Allow all in
Table 1 and only those in Table 2 that match Table 1.

Create a table named Count with numbers from 1 to however many you think you
will enter in a single block of numbers – 100 – 500 – 1000.

Create an append query like this for entering sold certificates –
INSERT INTO [Table 1] ( ID, [Date Sold], [Sold By] )
SELECT Format([ID]+[Count],"00000") AS Sold,
[Forms]![Form1]![TextBoxDateSold] AS [Date Sold],
[Forms]![Form1]![TextBoxSoldBy] AS [Sold By]
FROM [Table 1], [Count]
WHERE (((Format([ID]+[Count],"00000")) Between
[Forms]![Form1]![TextBoxStartNum] And [Forms]![Form1]![TextBoxEndtNum]));

The Format([ID]+[Count],"00000") allows leading zeros in the serial
number. Use as many zeros as you have digits in your serial number.

Create another append query for the redemption recording.