Adding a range of numbers from a form to a table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tai.cabrera@gmail.com - 28 Dec 2005 18:55 GMT

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 do-able. Thanks for your help
~tai

Reply to this Message

pietlinden@hotmail.com - 28 Dec 2005 19:54 GMT

I'm working on a similar problem. The way I would handle it is
something like this:

CREATE TABLE GiftCertificate(
SerialNumber varchar2(25) PRIMARY KEY,
DateSold date,
SoldBy VARCHAR2(50),
DateRedeemed Date,
RedeemedBy VARCHAR2(50)
);

If you do it this way, you can't add duplicate Serial numbers, because
the PK takes care of that for you.

Okay, adding a series of SerialNumbers...

You'd have an *unbound* form (doesn't write directly to the table) with
a few fields on it.
Something like:

txtStartSerialNo: [ ]
txtEndSerialNo: [ ]

then you could have code along the lines of
Sub cmdCreateCertificates(byval lngStartSerial as Long, byval
lngEndSerial As Long)

dim lngCurrent as Long
dim rsSerials as DAO.Recordset
set rsSerials
=dbEngine(0)(0).OpenRecordset("tblSerials",dbOpenTable,dbAppendOnly)
for lngCurrent=lngStartSerial to lngEndSerial
rsSerials.AddNew
rsSerials.Fields("SerialNo")=lngCurrent
rsSerials.Update
next lngCurrent

rsSerials.Close
set rsSerials=Nothing

Reply to this Message

Steve - 28 Dec 2005 20:46 GMT

Are your tables in two different databases or did your words just get
mixed up? It looks to me like Table1 is the 'sold' table and Table2 is
the 'redeemed' table, both in the same database.

The VBA code for inserting a range of records into your 'sold' table is
such:

Dim intStartNum as Integer
Dim intEndNum as Integer
Dim strDateSold as String
Dim strSoldBy as String
intStartNum=CInt(txtStartNum)
intEndNum=CInt(txtEndNum)
strDateSold=txtDateSold
strSoldBy=txtSoldBy

Dim x as Integer
For x=intStartNum To intEndNum
DoCmd.RunSQL "INSERT INTO tblSold (SerialNbr, DateSold, SoldBy) VALUES
(" & x & ", '" & strDateSold & "', '" & strSoldBy & "');"
Next x

Untested.

Do the same thing for your 'redeemed' table.
Checking to see if a serial nbr is in both tables can be done using a
query and a join between thet two tables on the serial nbr field.

This code has no error-checking or input validation: those are your
responsibility to furnish.

Reply to this Message

Peter Sutton - 29 Dec 2005 21:40 GMT

>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
[quoted text clipped - 37 lines]
>and is it do-able. Thanks for your help
>~tai

Firstly, you don't need separate tables as there is a one to one
relationship on the primary key in both tables ie just add fields Date
Redeemed and Redeemed By to Table 1

In your Form 1, I'd have the input as From Number and Number Sold. Use
a Do Loop to append the Sold certificates. Similarly for Form 2, but
use an update query to automate the changes.

P

Reply to this Message