Creating incremental numbering on a page

[Evon]

Can some one help me with this code. I have 2 page tabs on a form, and I want an incremental number assigned to each new record on that page.

Been doing some research and this is what I came up with:

Private Sub Form_Current()
If Me!tabSupplier.NewRecord = True Then
Me!tabSupplier!DocumentNo = Nz(DMax("DocumentNo", "Product"), 1000) + 1
End If
End Sub

The page nane is: Supplier Page Index is:0
The control to display the incremental number isocumentNo (tblProducts)


Only problem is, it does not work and I am getting no error message.

 

[Evon]

This works on a regular form without page tabs:

If Me.NewRecord = True Then
Me.YourCounterField = Nz(DMax("YourCounterField", "YourTableName"), X) + 1
End If
Just cant modify it to work in a page tab of a form.

 

[pbaldy]

Have you tried without including the tab page? They are not part of the reference.

 

[Evon]

Have you tried without including the tab page? They are not part of the reference.

Yes, I tried it without the tab reference in another form I created specifically to test the code within the same database and also some other forms in another database. The code worked perfectly.

However, when I run it on this particular form, it works for the first increment then I get a pop up window saying that:

"the changes requested were not successful because they would create duplicate values In the index, primary keys or relationship. Change the data in the field or fields that contain duplicate data, remove the index or redefine the index to permit duplicate entries and try again."

Of course that led me to recheck all the index properties of the number fields in the table. Spent hours, testing different angles to tackle the problem, but no luck yet.

What I want to know though, based on the error message, is there a way to examine the underlying code that set off that message? Because I could then pinpoint the fields, index or relationship causing the problem.

As a matter of interest, the code I am using that works on the other forms I mentioned earlier is as follows:

If Me.NewRecord = True Then
Me.DocumentNo = Nz(DMax("DocumentNo", "Product"), 5000) + 1
End If

 

[pbaldy]

It sounds like the error is coming from the database engine, and that field is a key or something. Can you post the db here?

 

[Evon]

Thanks, no it is too big. The design is kind of cumbersome. I had not really planned it out before and kept on changing as I went along.

I have decided to build it over from scratch. So I created a new database and started importing and editing some of the tables. The table with the index problem I have split in 3, and just generally building a better, more flexible foundation.

The problem really arose when I decided to split a set of controls between two pages on a form. I am planning to build separate tables and us them as subforms, 1 each per page, nested in a main form.

It would be interesting though to find out what prompted that message from the database. Just might upload it and send you the link, because there is just sample data in it.

 

[Evon]

Okay Sir p, this is the database. I will remove it shortly.

 

[pbaldy]

Which form/table did you get the error on?

 

[Evon]

Highten Main Form from the switchboard or Highten Trading from the Nav. Pane. On opening you will see the Purchases and Sales pages. The incremental number control is "text721 at the top of the form on the Sales Page.

 

[pbaldy]

The error is actually coming from the ControlNumber field. It defaults to "HT", but that value already exists in the data and the field is set to "Indexed (No Duplicates)". Hopefully you're normalizing the database?

 

[Evon]

The error is actually coming from the ControlNumber field. It defaults to "HT", but that value already exists in the data and the field is set to "Indexed (No Duplicates)". Hopefully you're normalizing the database?

Yes Doc, you are unbelievable! I actually played around with that control's properties but somehow, I never got through.

Everything is working like a charm.

Yes, the database as I said was kind of clumsily built so I may just continue the new one in parallel.

But once again, hats of to you sir!

Of course, I know I will soon find something to test your mettle with again

 

[pbaldy]

PDready strikes again!

Happy to help.