Full Version: how to set field to 1 again
UtterAccess Forums > Microsoft® Access > Access Forms
I have a form order that handles sales orders
The itemno for each order starts at 1 and goes to 2...with the button "Next"
I use "Nz(DMax("[Item]","Order"),0)+1" to increment the value for itemno.
The problem is that i am not able to set item back to "0" when i add a new sales order or select a different one.
I need it to start at 1 everytime.
Any suggestions?
is there anything to do with rollback?
You are going to need a WHERE clause in there to limit the records it is looking at to only the current order. Something like:

Nz(DMax("[Item]","Order","[OrderID]=" & Me.txtOrderID),0)+1

This assumes you have a numeric field ORDERID in your table, and a control txtOrderID on your form.

Otherwise, you are just pulling the biggest number for [Item] for ANY order, and adding 1.
The WHERE clause is to be defined under what field?
In your DMAX, as I specified in my previous post.
is <b>Nz(DMax("[Item]","Order","[OrderID]=" & Me.txtOrderID),0)+1</b> in addition to <b>Nz(DMax("[Item]","Order"),0)+1</b> under the field Item in form Order?

Because i've added it to item (modified it to what you stated) but the problem is that i have subform that deals with the items called Items. The subform is inside form Order. So, the field OrderID is in table Order and Item is in table SalesItems.

How do i manage it with this?

Edited by: audiracer on Wed Dec 7 17:22:35 EST 2005.
This isn't making a lot of sense, so I am beginning to suspect you have issues with your table structure.
typical ORDER database would have tables like:
ProductID (PK, autonumber)
OrderItemID (PK, autonumber)
OrderID (FK)
ProductID (FK)
It's the third table, where ITEMS are joined to ORDERS, that makes this all work.
In reference to: Nz(DMax("[Item]","Order","[OrderID]=" & Me.txtOrderID),0)+1
Your above setup of tables is exactly what i have.
However, the field txtOrderID on my form comes from tblOrder.
The field Item only exists in tblOrderItems, so I'm not sure if putting all that in one function will work because
when you say "Me.txtOrderID", that field is in form Order, and the Item field is all in a subform within the form
Orderce to: Nz(DMax("[Item]","Order","[OrderID]=" & Me.txtOrderID),0)+1
Your above setup of tables is exactly what i have.
However, the field txtOrderID on my form comes from tblOrder.
The field Item only exists in tblOrderItems, so I'm not sure if putting all that in one function will work because
when you say "Me.txtOrderID", that field is in form Order, and the Item field is all in a subform within the form
ORDER. So its like calling outside its form when we say txtOrderID The function is:
Nz(DMax("[Item]","OrderItems","[OrderID]=" & Me.txtOrderID),0)+1
Don't reference the control, then. Since your subform is based on your OrderItems table, it should have an ORDERID property underlying it (that refers to the field in the record), even if you aren't displaying it in a control. Just do:
z(DMax("[Item]","OrderItems","[OrderID]=" & Me.OrderID),0)+1
Its giving me an error for some reason..in the field it displays #Name.
And at other times, it will not increment, just stays at 1 and thats it
before with: Nz(DMax("[Item]","Order"),0)+1 , it incremented, but didnt go back to one once i clicked cmdaddnewrecord.
I am confused..can you see what im doing wrong?
Not unless you zip up and post a sample MDB for me. Otherwise, it's all just basic guesswork, since there are a lot of things that can affect this.
Try this, just for kicks:
Nz(DMax("[Item]","OrderItems","[OrderID]=" & Me.Parent.txtOrderID),0)+1
I refrain from posting whole MDB on the site, could i send it to you elsewhere?
What about...
I think I see the issue here. Basically what you seem to be doing is assigning an arbitrary number to each item in an order. So you want the item numbering to start over again with each new order.
herefore you need to use what Peter gave you, but maybe I can help you understand it. You said; "However, the field txtOrderID on my form comes from tblOrder". The thing is you don't have fields on forms, you have Controls. I'm assuming you have a main form bound to orders and a subform bound to details linked on OrderID. Which means you have a control on your subform that holds the OrderID named txtOrderID.
So the default value of your Item control (txtItem I assume) should have the following expressions:
=Nz(DMax("[Item]","OrderDetails","[OrderID]=" & Me.txtOrderID),0)+1
This will find the highest number in the Order details for the order and add 1 to it. You might have to replace Me.txtorderID with Forms!mainform!txtOrderID so that it pulls the ID from the main form.
But my question is WHY bother? Why do you need an arbitrary number assigned to each line item? What do you think that gives you? I've done several order entry apps and I've never seen the need for this.

if i dont keep track of item #, then how else can i keep on adding items to new sales orders and keep track of them like you mentioned you had done previously.

also, the control OrderID does not exist on subform. The subform just has item details (item, partnum, description etc..), however the field OrderID does exist in the tblItems.

Oappreciate you clarifying the problem in details..Thanks.
You just add a new record in the subform. if you need to go back and add new Items to an order, you bring up the order on the main form, then add a new record in the subform. The Items table should have an autonumber as a PK to uniquely identify the detail record. I don't see what purpose the item number serves. From your description its not a unique number its simply a count of items for that specific order. So why do you need that? You can always get a count of the records for an order.
Thanks for your idea..i did just that and it seems to make a lot more sense.
one thing however is that, when i go from order to order, the autonumber stays at that and goes to next, i need it to start at 1 everytime.
Autonumbers are used to uniquely identify records to the database, not necessarily the user. You shouldn't be displaying it. The DB (and you, the coder) can use it as necessary; your users don't need to see it.
That none of us understand is WHY you feel you need this. What you were attempting to do before amounts to a LINE NUMBER for each details record in an order. What purpose would/does that serve?
Let's say I have a ORDER #500. It has three detail records, containing a product #, a quantity, and a total charge:
Product X ... 500 ... $49.95
Product Y ... 20 ... $205.37
Product Z ... 1 ... $19.99
How would numbering these 1...3 (permanently!) add anything? In fact, it will cause you issues; for example, what happens if you go back and delete the second item from the order? Your numbering will be messed up!
Now, behind the scenes in this table you ALSO need an autonumber PK (for the database to uniquely identify each record), as well as a field to hold the OrderID (so you can join these detail records to their parent order).
The ONLY purpose for a Primary key is to uniquely identify a record. That is why autonumbers are so perfectly suited for use as a PK. I differ from my colleagues somewhat in that I don't see a problem with using the autonumber as a visible identifier as long as you realize it is not a sequential number that its only use is to identify the record.
Is Peter and I are saying, we see no value whatsoever to have a sequential number for each line item in the order.
okay..i guess i am not thinking the same about autonumbers.
ets start with this: if i dont have an item number whatsoever to keep track of the # of items in one sales order, then how can i just keep on adding more items? (by using autonumber without allowing the user to see it?)
if i am getting this, you all are telling me that there really isnt any use of keeping track of #'s of items. Right?
the reason i've been trying to assign a # to items is because the current system (created in FoxPro) does that.. it gives the user a visual of item #. But, in doing that in access, it seems like an unecessary thing to do.
You need a primary key. That PK will be used internally by your database to uniquely identify the record. It is recommended you should use an Autonumber for the PK.
You can, if you wish, display this value to the user. Some of my colleagues will disagree with that, but as long as you understand the value is used only for ID, its not sequential, then I see no problem.
Because you are using an autonumber, you do not need to identify the record manually. The autonumber is automatically assigned when you add a record to the table.
Therefore arbitrarily assigning a value to indicate the number of records in a single order is unnecessary and superfluous. Why it was used in the FP app, I don't know. It was unnecessary there too, unless it was used as a compound key along with the OrderID. but even then I wouldn't have done it that way.
thanks for your suggestions...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.