Seems like I must just be missing something simple here. I have a form Order, with a subform, Order_Line, and one of the required fields in the subform is the Line #. Is there a function I can add to the default value so that this will auto number with each line for each order?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
I have been hesitant to try that. I am working with a table link to our ERP's test database, this is a project to see if we can improve the purchase requisition interface to accept additional information but the underlying source data is not an access table. I honestly don't know enough about how that will impact other ERP operations to be confident in that option.
Also, wouldn't that just create an ever increasing number for every line without resetting with a new order? Order 1 Line 1,2,3, Order 2, Line 4,5,6, Order 3, Line 7,8,9? Thats another reason I was thinking I needed a function, so it would be Order 1, Line 1,2,3, Order 2, Line 1,2,3, Order 3, Line 1,2,3
Also, wouldn't that just create an ever increasing number for every line without resetting with a new order? Order 1 Line 1,2,3, Order 2, Line 4,5,6, Order 3, Line 7,8,9? Thats another reason I was thinking I needed a function, so it would be Order 1, Line 1,2,3, Order 2, Line 1,2,3, Order 3, Line 1,2,3
Upvote
0
Yes it would behave this way. An autonumber also has to be unique in it's field, but surely this table contains the order id, thus multiple orders are represented and each one will have at least one value in common as a line item number (1).
What you want is a calculated control on the form. How you might do this depends on how you want it to work in conjunction with your design. F'rinstance, if you want the order item line value when someone starts typing in the order details, you can simply add one to the prior value for that order. But what if they remove a row after it has been saved? Do you want to maintain the other numbers to show that an item was removed? Do you want to re-number the line items for that order? To work with a volatile set of records on a subform usually requires that you work with a recordset. It would be easier to create the line item numbers after the order has been saved using a single record form, which should then close. You might then want a Save button if you want to allow intermittent saves as well as a Save/Close button (and maybe Cancel too). Lots of variable to consider. Maybe even a composite index or primary key whereby OrderID and LineNo have to be unique in order to ensure there's no duplication.
What you want is a calculated control on the form. How you might do this depends on how you want it to work in conjunction with your design. F'rinstance, if you want the order item line value when someone starts typing in the order details, you can simply add one to the prior value for that order. But what if they remove a row after it has been saved? Do you want to maintain the other numbers to show that an item was removed? Do you want to re-number the line items for that order? To work with a volatile set of records on a subform usually requires that you work with a recordset. It would be easier to create the line item numbers after the order has been saved using a single record form, which should then close. You might then want a Save button if you want to allow intermittent saves as well as a Save/Close button (and maybe Cancel too). Lots of variable to consider. Maybe even a composite index or primary key whereby OrderID and LineNo have to be unique in order to ensure there's no duplication.
Last edited:
Upvote
0
Similar threads
