autonumber/dmax??

  • Thread starter Thread starter Michelle
  • Start date Start date
M

Michelle

I am attempting to create a database to sell coupons. The
coupons are pre-printed with the numbers already on them.
I need to add these coupon number's to the database so
that I can sell them to the customer's. There are
thousand's of 3 different types of coupons that I will
sell this year. I need to know how I can populate the
coupon type field with the coupon number. I.E. A customer
comes in today and wants to purchase 25 coupons. I need
to go to the form and it should know what coupon is next
in line to sell, and I can tell it to sell 25, and the
database will show me what is the last number in the group
to sell???? HELP!
 
Michelle,

It is not 100% clear from your post what the reporting requirements
might be as regards these coupon numbers. And I presume it was a typo
when you suggested that the coupon number goes in the Coupon Type field?
But on the face of it, it seems to me that if you have a Purchases
table, you would only need the CouponType, BeginningCouponNumber, and
QuantityPurchased fields in the table. Then, on the form, you could
have code like this on the AfterUpdate event of the CouponType (probably
a Combobox or an Option Group)...

Dim rst As DAO.Recordset
Dim NextNumber As Long
Set rst = CurrentDb.OprnRecordset("SELECT BeginningCouponNumber,
QuantityPurchased FROM Purchases WHERE CouponType ='" & Me.CouponType &
"' ORDER BY BeginningCouponNumber"
With rst
.MoveLast
NexNumber = !BeginningCouponNumber + !QuantityPurchased
End With
Me.BeginningCouponNumber = NextNumber

.... and then you could have an unbound textbox on the form to show
ending coupon number, with its Control Source set to...
=[BeginningCouponNumber]+[QuantityPurchased]-1
 
Back
Top