Booking number help

mohiddin52 · April 27th, 2010, 08:51 AM

[FONT='Verdana','sans-serif']Hi,

I have an Access form linked to a table where the key in the table is an autonumber field. My table field is sno(autonumber,PK), name, rdate, order_number now my problems is When adding a new record to the table using the form I want insert into order_number field if rade is now() e.g. “A +sno” like “A001,A002,A003….” next day adding a new record I want from starting like “A001….” [/FONT]

mohiddin52 · May 5th, 2010, 10:37 AM

I have an Access form linked to a table where the key in the table is an autonumber field. My table field is sno(autonumber,PK), name, rdate, order_number now my problems is When adding a new record to the table using the form I want insert into order_number field if rade is now() e.g. âA +snoâ like âA001,A002,A003â¦.â next day adding a new record I want from starting like âA001â¦.â

SerranoG · May 14th, 2010, 11:11 AM

I understand what it is you want, but my advice is do NOT use the autonumber field "sno" for anythying. Autonumbers are generated by the computer and only have meaning behind the scenes to link a primary key to a foreign key. Autonumbers should not be used in calculations or concatenations for anything.

That said, if you want to use an order number that starts at no. 1 each day, that's not hard to do. I assume that you have one table with the companies and their info, including an autonumber for each record, say "sno." I also assume you have a second table for orders, e.g. "Orders", where you have a foreign key "sno" that links many orders to each company via "sno." In this order table you have a date the order was placed.called "rdate."

You can do something like this for the order number as you enter them into the second table via a subform. Note that Now() has a time associated with it. If you don't care what time it is, use Date().

Code:

If DCount("[sno]","Orders","[sno] = " & Me.sno & " And [RDate] = #" & Date() & "#") = 0 Then
    'There are no orders for sno today.
    Me.Order_Number = "A001"
Else
    'There are orders existing for today for sno.
    Me.Order_Number = "A" & CInt(Right(DMax("[Order_Number]","Orders","[Sno] = " & Me.sno & " And [RDate] = #" & Date() & "#"), 3)) + 1
End If