Creating incremental numbering on a page (1 Viewer)

Evon

Registered User
Joined
Apr 1, 2003
Messages
83
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 is:DocumentNo (tblProducts)


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

Evon

Registered User
Joined
Apr 1, 2003
Messages
83
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

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,654
Have you tried without including the tab page? They are not part of the reference.
 

Evon

Registered User
Joined
Apr 1, 2003
Messages
83
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

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,654
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

Registered User
Joined
Apr 1, 2003
Messages
83
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.
 

pbaldy

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,654
Which form/table did you get the error on?
 

Evon

Registered User
Joined
Apr 1, 2003
Messages
83
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

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,654
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

Registered User
Joined
Apr 1, 2003
Messages
83
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:)
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom