Weird thing happens. Help Please!

[CJ_London]

providing you have populated all the blanks with 'a' and you have set the primary key across both the fields you will not now be able to add a new record with the same ordercode and character as one that already exists.

I'm struggling to understand why you are sticking at 1000 - which I presume implies the highest number in the table is 999 - are you sure you do not have any restrictions like a validation rule limiting input?

Can you post a sample of your db with some test data and the form you are having a problem with

 

[delaikhi]

CJ_London, Here is the sample database as you suggested,

 

[CJ_London]

You have that code pasted all over the place which is one of the problems - it should only be in one place. You are also giving the user the ability to enter (and change) an order number as well as enter a new one - I don't understand the logic but I would think a risky strategy

The user also can only enter one item per order so if a customer orders two things the user has to enter the customer again - wouldn't using a subform be a better way?

You also had not done as I suggested which was to make the default value for SubOrder_Code 'a'.

Anyway, I digress, to solve your problem:

Remove all occasions where you have put

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

and put it in the form Current event - then when the user clicks on the new order button it will be populated there.

See the attached images, I manually completed a few orders and then clicked on new order

The only problem with this is if you have multiple users inputting at the same time - you run the risk that they will both try to allocate the same number.

One final thing, I see you are using lookups in your table definitions, this is a bad idea and will cause you many problems in the future when you are trying to work out why something isn't working and I recommend you remove them.

 

[delaikhi]

You have that code pasted all over the place which is one of the problems - it should only be in one place. You are also giving the user the ability to enter (and change) an order number as well as enter a new one - I don't understand the logic but I would think a risky strategy

The user also can only enter one item per order so if a customer orders two things the user has to enter the customer again - wouldn't using a subform be a better way?

You also had not done as I suggested which was to make the default value for SubOrder_Code 'a'.

Anyway, I digress, to solve your problem:

Remove all occasions where you have put

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

and put it in the form Current event - then when the user clicks on the new order button it will be populated there.

See the attached images, I manually completed a few orders and then clicked on new order

The only problem with this is if you have multiple users inputting at the same time - you run the risk that they will both try to allocate the same number.

One final thing, I see you are using lookups in your table definitions, this is a bad idea and will cause you many problems in the future when you are trying to work out why something isn't working and I recommend you remove them.

Originally, I did not think I'd give user the right to enter a new order number, CJ. Problem is we were given a kind of preprinted receipt with the numbers already there on the form, and each users would get a different bunch of them although the receipts are incrementally counting. That's why I designed that way, so ech user can enter his/her own nuber but in the end those numbers add up by 1.

Thanks very much for the code. I will fix the database right away.

As for the look up issue, actually I dont know what to do with relationship and I am kinda lost with the idea how to create a kind of cascading two combo boxes on the form and (product and product details, I did a bunch of research on this matter) but still cannot get it. Any suggestion?

 

[CJ_London]

First you would need to normalise your data. In simple terms, normalise means design your tables so any piece of data is only entered once.

In your case, this at least means splitting the orders table into at least two tables, a OrderHeader table which contains as a minimum the orderID as an autonumber primary key, order code and the customer id, and a OrderLine table which contains as a minimum the OrderLineID as autonumber primary key, orderID as long family key (to link back to the header record) the productID, quantity and product price. Whether subordercode needs to be in the header or line table is for you to decide which works best.

Order date - normally in the header table but depends on where you put the suborder code

Not sure the meaning of return date - if someone orders 3 and returns 1 what do your do? This probably wants to be in a different table altogether together with service used to return where it can link on OrderLineID

Instruction - you probably need one of these in both the header and the line tables - the former referring to the who order and the latter for one line only.

Amount should be calculated on the form or report by multipling quantity times product price so is not required

Paymenttype probably wants to be in the header record (depends on where you put the ordersubcode)

OrderName is probably not required - it is only used to limit the order detail

Having do that, I would split your form into two parts and amend the recordsources accordingly.

The first part - the main form would have the order 'header' table as a recordsource.

The second part would be a subform in continuous view with the 'line' table as recordsource. You would set the subform control linkchild and linkmaster fields to OrderID the subcode is in the Line table

As good developer practice I would not use space or non alpha numeric characters in table and field names, nor use lookups in table definitions

 

[ozzi]

Originally, I did not think I'd give user the right to enter a new order number, CJ. Problem is we were given a kind of preprinted receipt with the numbers already there on the form, and each users would get a different bunch of them although the receipts are incrementally counting. That's why I designed that way, so ech user can enter his/her own nuber but in the end those numbers add up by 1.

if your users are entering the order number from hardcopy then the incrementation of the order_code is already taken care of. you don't need to increment it in code, you just need to test for any missing order numbers. you can index it to find records quicker, but it is better to use your autonumber orderID as the primary key.
It is the sub code that needs to be incremented automatically to prevent the user from using it twice for any given order number.
you also need to separate your order details into a new table. use an order_detailID as the primary key, the orderID as the foreign key, and the sub_code as a test to prevent double entry of the same detail data.

and everything thet CJ said.

ozzi

 

[delaikhi]

Ozzi, thanks
Problem is the damned printed number started with 800. Damn the Headquarters! That's why I am worrying about loosing the sequence of number and avoiding using the Autonumber as the PK in Orders Table.
Anyway, thanks guys. Probably, I'll try to redesign the database and return in a few days.