Continuous Forms 1

[Angelique21]

I have a Continuous Form and the first Field of the Form is the Order number having Input Mask "A01-07-"000;0;

I want to make the last 3 digits of this field to be written down automatically and incrementing the number, once the user goes to the next line. Thus :

Line number one in the form should show : A01-07-001
A01-07-002
A01-07-003

Any suggestions of how I can go about it?

Thanks

 

[Remou]

Let us say that your table is called Orders and your field is called OrderID, you can use code like so:

Private Sub Form_AfterInsert()
Me.OrderId.DefaultValue = "=""A01-07-"" & Format(DMax(""Right(OrderID,3)"",""Orders"")+1,""000"")"
End Sub

 

[Angelique21]

I put your code in the FORM (AfterInsert)and put the OrderID as autonumber and Primary Key in the Table. But the below is showing on Form:

A01-07-__1
A01-07-__2

and should be as:

A01-07-001
A01-07-002

However in the Table it is storing the Order No well (i.e. A01-07-001) , it is simply not showing it correctly in the Form as the correct Format

 

[Remou]

Take out the Input Mask, you will not need it as the ID is generated.

 

[Angelique21]

I took out the Input Mask, but now on the FORM only the number is being generated such as 1, 2 etc..but in the table it is well stored with the actual format A01-07-001

 

[Remou]

Please post the code as amended for your application.

 

[Remou]

Does the table have an Input Mask or Format?

 

[Angelique21]

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.OrderNo.DefaultValue = "=""A01-07-"" & Format(DMax(""Right(OrderNo,3)"",""OrderBooking"")+1,""000"")"
End Sub



OrderNo : Name of the Field (Primary Field : what type of field should I do it?)
OrderBooking : Name of Table

 

[Angelique21]

I placed it in the AFTERINSERT event not beforeinsert and eliminated all input masks and formats

now it is giving me the Order No but when I go to the next field it is not incrementing well..It is giving me the same orderno as before

 

[Remou]

You show the Before Insert event above: is this an error?

 

[Angelique21]

yes it is an error ....sorry

 

[Remou]

Sorry, but I am puzzled. Can you create a form based on the table and only add the code I suggested to see if something on your current form is causing the problem?

 

[Angelique21]

Can I attach my database where I included the form somewhere? maybe you can give it a look

thanks.

 

[Remou]

Do you have access to a website?

 

[Angelique21]

No I'm sorry I don't have a website where I can upload...


maybe by email?

 

[Remou]

Email is frowned upon translate this: my user name from above with a _ and an At sign hot mail dot the usual

 

[Angelique21]

Thanks I sent the email today...

Thanks a lot

 

[Remou]

Please zip and resend. HM does not support most extensions.

 

[Angelique21]

Sent as zip

Thanks

 

[Remou]

The problem is that OrderNo is an autonumber. You can change the way an autonumber looks, but it will still be a number. It is not a good idea to use an autonumber for something that means something, they jump and develop gaps; sometimes they can be negative or very large. An autonumber is a prerty good unique field for a system used by very few people, but that is all it is. You can either use your own order number as the key field or else add another field for order number and keep the autonumber. These will help you to decide:

Natural or Surrogate Key:

http://r937.com/20020620.html

Why AutoNumber shouldn't be used in MultiUser databases (And How to ge the Unique Number)
faq700-184