Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Continuous Forms 1

Status
Not open for further replies.

Angelique21

Programmer
Jun 21, 2006
31
MT
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
 
Let us say that your table is called Orders and your field is called OrderID, you can use code like so:

Code:
Private Sub Form_AfterInsert()
Me.OrderId.DefaultValue = "=""A01-07-"" & Format(DMax(""Right(OrderID,3)"",""Orders"")+1,""000"")"
End Sub
 
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
 
Take out the Input Mask, you will not need it as the ID is generated.
 
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
 
Please post the code as amended for your application.
 
Does the table have an Input Mask or Format?
 
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
 
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
 
You show the Before Insert event above: is this an error?
 
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?
 
Can I attach my database where I included the form somewhere? maybe you can give it a look

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


maybe by email?
 
Email is frowned upon translate this: my user name from above with a _ and an At sign hot mail dot the usual :)
 
Please zip and resend. HM does not support most extensions.
 
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: Why AutoNumber shouldn't be used in MultiUser databases (And How to ge the Unique Number)
faq700-184
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top