Weird thing happens. Help Please!

[delaikhi]

I put this on the form so that it can make number a new record. It stops at number 1000 and keep telling me No new record can be added because this would create a duplicate in indexed field . This is realy weird. Some one please help!!!!
Private Sub Form_Load()
Me.Order_Code = Nz(DMax("[Order_Code]", "[Orders]"), 0) + 1
End Sub

 

[CJ_London]

I don't understand what you are doing - you should really be doing this in the beforeupdate event for the form.

Is ordercode numeric or a string? If a string then the max would be 11

1
10
100
1000
11

 

[delaikhi]

Hi this is what happening:
1- I created a field Order_Code and intended to make it increasing by 1 each time we add a new entry, it is a kind of simply counting like 1,2,3.
2- So that I put the above codes in the "afterupdate event" of the Order_Code.
3- At first, it counts ok from 1-999, but stopped working at 1000.
I don't know how to fix this issue.
Thanks

 

[CJ_London]

I'm confused, in your first post you said it was in the form load event, now you are saying it is in the control after update event.

You haven't commented on whether the order code - in your table - is text or numeric and if the latter what datatype - long/integer/double etc

Have you checked you do not have some other limit on this field either in the table or in the control?

 

[ozzi]

Private Sub Form_Load()
Me.Order_Code = Nz(DMax("[Order_Code]", "[Orders]"), 0) + 1
End Sub

G'Day delaikhi,

Why the Nz. You only need it if you may encounter a Null value for Order_Code which can only happen if the Orders table is empty.

Me.Order_Code = DMax("[Order_Code]", "[Orders]") + 1

should be enough to give you an incrementing Order_Code, provided it is a numerical value.

ozzi

 

[delaikhi]

@Ozzi, thanks for the tip. Actually I set the Order_code in table Orders as text, the reason behind it this: My people at the office has been issued with a kind of pre-printed receipts (the number on that crazy thing is PRE PRINTED). However, orders come in with diferent kind and with 1 check (say, a customer can order 10 pencils, 5 errasers, etc.). So we decided that the user can put a letter after the number that indicates the Order Code, for example: 1000: 10 pencils, 1000a: 5 errasers...
So I created the above code and it works well until yesterday when the value reached number 1000 and it got me made.
@CJ_London:
Sorry for the confusion. I set the value for the Order_Code in tablOrders as Text (the reason I explained already).
As for the question about OnLoad or AfterUpdate: I actually tried both ways with the damned expression but I cannot work. whenever the value reached 1000, it just stops working.
And I check the whole database for the lock or limitation but there is none of that.

 

[pbaldy]

If the field is text, 999 is greater than 1000, because it does an alphabetic comparison rather than numeric.

 

[delaikhi]

@pbaldy: thank you sir! Is there any way to go around this situation?

 

[CJ_London]

if your order_code is text then as previously stated the max will not necessariy give you the max number. - I presume the 'a' is in as different field?

As for the question about OnLoad or AfterUpdate

my post refers to the form afterupdate event, not the control

You need to be clearer about what you actually have otherwise it is impossible to provide a possible solution.

So assuming your Order_Code is only a number, and the 'a's are in a different field, change your Order_Code in your table to numeric - you can still concat the two to combine the fields.

If this is not the case, please post more background on the problem - what do you actually have in order_code in particular

 

[CJ_London]

@Paul - already told the OP that!

 

[delaikhi]

Yeah I start to see the problem:
1- I make the property for the Oder_Code in tblOrder as text instead of number
2- I also messed it up by mixing number and text together instead of make them in separate fields.
Now, I'll try to do this:
1- Convert all values in the Order_Code to Number and change the property field to Number
2- Create a new field in tblOrder, name it as subOrder_code
3- Try to merge them together in a latter stage.
Not sure if this is right?

 

[delaikhi]

Thanks for all the tips. Here is another question: I dont want my users to mess up with the new sequesce of Order_Code (Order_Code and subOrder_Code put together), more precisely, I dont want that the users may accidentally repeat the new sequence twice, let say twice of 1000b. Is there any way to avoid this?

 

[pbaldy]

@Paul - already told the OP that!

Well, you said the max was 11.

 

[CJ_London]

Your suggested solution should work and to solve the problem of two 1000b you could make your primary key over two fields.

But before you do, try the following:

Me.Order_Code = Nz(DMax("[COLOR=red]val([/COLOR][Order_Code][COLOR=red])[/COLOR]", "[Orders]"), 0) + 1

The Val function will stop reading the string once it encounters the first non-numeric character

I haven't tested it but if it works it will save you having to change everything

 

[delaikhi]

Your suggested solution should work and to solve the problem of two 1000b you could make your primary key over two fields.

But before you do, try the following:

Me.Order_Code = Nz(DMax("[COLOR=red]val([/COLOR][Order_Code][COLOR=red])[/COLOR]", "[Orders]"), 0) + 1

The Val function will stop reading the string once it encounters the first non-numeric character

I haven't tested it but if it works it will save you having to change everything

CJ_London: where do you suggest me to put this code on? And besides, I do not realy understand your suggestion of "making primary key over two fields." Please explain a little bit more
Thanks

 

[CJ_London]

I would put the code in your form beforeupdate event

making primary key over two fields

See attachment to explain how to do this - select the two fields you want to make a primary key and then click on the primary key icon. primary keys cannot have duplicates so it is an easy way to stop users trying to adding b a second time if you go the split id route.

You should understand that although indexes are related to a table, they are a separate object in their own right - which is why when you delete an indexed field from a table you are prompted that the index will be deleted as well. Although not available in the table designer, you can use SQL to create, modify and delete indexes, including ones over multiple fields

 

[delaikhi]

Thanks CJ_London,
I have done as you suggested in your previous reply, put the code in to beforeupdate even on the form. But it still didn't work, stoped right at number 1000, although the order number is 1019
So, I corrected it by change all the Order_Code into Number, and subOrder_Code into text and call them both primary (see pictures) and close the table. Now, it seems I violated a rule in Accsess: all primary keys should NOT contain a null value, this means the subOrder_code should have some sort of value in it. What would you suggest? Should I add an 'a' into it or what?

 

[ozzi]

hi delaiki,

as i understand it you have an order_code which you need to increment for each new order and a sub code for each item in the order ie. 10 pencils, 5 errasers etc.

personally i would keep the order table and create a new table for the items and create a relationship based on the order_code.

better still, add a link table and create a many to many relationship.

ozzi

 

[CJ_London]

Yes I would - you'll need to populate the blanks with 'a' and moving forward, in your table design, set the default to 'a'

 

[delaikhi]

CJ_London, please help, I change every thing in the suborder_code, adding an 'a' to the blank filed. but still stucked with question how to make users not to repeat an order twice. What Should I do next?