Sub Forms and Item Number

[RICKA]

Hello All,
To keep it simple i have a purchase order form with a sub form for the items,
i need to give each position on the purchase order an item number starting with one for the first line and so on starting again at one for a new order i want the system to generate this as we all no database users can make errors, so the less thay have to do the better. Does anyone know where to start, i have tried a few things like setting the default value to be [currentrecord] but this does not work 100% of the time, any ideas will be welcome.

Regards

Rick

 

[ansentry]

I have never used this, however here is the <<Link>>

You can also download a db with sample forms - it has "Line numbers in sub form" example>> Here <<

 

[shudini]

Another idea - albeit slightly convoluted - I would create an order detail section with a composite primary key of OrderNumber (assuming you have a master order table) and detail item (whatever the item being ordered is - this is less relevant for our puporse right now). if you also have another field in your order detail section that defaults to now() (date/time), whenever a record is added, the time it is added is placed in that field automatically. Once this is the case, what you can do is create a query that selects the order number and order detail fields from the orderDetail table, and then add a column in the query that would resemble dtlNum: DCount("[items]","orderDetail","order=" & [orderNum] & " and dt<=#" & [dt] & "#")

What this basically does is create a field that returns how many records are there that are with this order number and were entered before this record. Once you enter data now (obviously not into the dtNum field), the dtnum field will automatically show you what number detail item it is. You could then set this query as the data source of your form, and then create a locked text box that is bound to the dtNum field.

I know this is not necessarily the best idea, but at least it's another option for you to consider.

 

[RICKA]

Thanks all,
I tried both solutions but ansentry had the better solution.

Rick