dmax issue

[bone head]

I know there are several threads dealing twith this issue but i must have mised something.

Am trying to change my database for Autonumber to finding the next highest No using the following code


Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.txtNumberField = DMax("[complaint No]", "customer contact") + 1
End Sub

Compaint No being the feild and Customer Contact being the form.

have put the code in as an event before update

I have set the feild in the tabel to Number from Autonumber type.

what am i doing wrong please as this does not seem to work.


Cheers

Paul

 

[Mile-O]

Compaint No being the feild and Customer Contact being the form.

CustomerContact should be a table, not a form.

 

[bone head]

the table is also called customer contact so that would not really make any difference would it.

 

[Mile-O]

enclose Customer Contact in square brackets.

 

[bone head]

Still returning 0 as value

could it be something to do with the fact that previous records were created using autonumber ? and bususe of this there are gaps

 

[namliam]

the table is also called customer contact so that would not really make any difference would it.

2 things, just for future sake....

1) Use some kind of naming convention like tbl for all tables,qry Query, frm Form Never have 2 different things (in this case a forma and a table) having the same name.

2) Dont use spaces in names. Its gonna bug ya....

Allthought it doesnt help your imediat problem .... for which i dont have a sollution... I am sorry to say..

Regards

The Mailman

 

[Mile-O]

Private Sub Form_BeforeUpdate(Cancel As Integer) 
    MsgBox DMax("[complaint No]", "customer contact") 
    Me.txtNumberField = DMax("[complaint No]", "customer contact") + 1 
End Sub

What does the message box say if you add in the extra line and run the code?

 

[bone head]

Oh dear oh dear.

i do not get any message box, put you code in and aslo tried with square brackets ie: Private Sub complaint_No_BeforeUpdate(Cancel As Integer)
MsgBox DMax("[complaint No]", "[customer contact]")
Me.txtNumberField = DMax("[complaint No]", "[customer contact]") + 1
End Sub

when i open form and try to operate it still getting a 0 value in the box gone back and checked everything which seems to be in order, even deleted feild from form and re inserted it as thought that may be the problem.

i have a feeling it is something preaty obvious i have done wrong usually is.

to recap should the feild be a general number format, the feild is a text box and i have put the code as a before update.

any further suggestions

Cheers

Paul

 

[Mile-O]

Does the form's event have [Event Procedure] ?

 

[bone head]

No, No events are on the form itself

 

[Mile-O]

Ok, if no events are on the form, how do you expect the Form's BeforeUpdate event to work?

Put [Event Procedure] in the form's BeforeUpdate event.

 

[bone head]

I think this may be the problem Me.txtNumberField = as when i complete a record an try to move on the new record i get the message.
"Method of data member not found error" message.

should me relate to something ? probably a dumb question.

Also do i put code in form event and feild event procedures.

Cheers

 

[bone head]

Doh ! Silly me.

Now sorted i misunderstood yu and put code in form event procedure.

removed that and just marked as event procedure and works fine.

well you learn something every day they say.

Many thanks for you help on this problem.

Cheers

Paul

 

[Mile-O]

Now sorted i misunderstood yu and put code in form event procedure.

removed that and just marked as event procedure and works fine.

 

[bone head]

Last message a bit premature, have created small database attached with settings as I have in my database, still not working could someone take a look and see what I am doing wrong.

I got it working once in main database but then obviously somewhere cocked it up again.

TIA

 

[WayneRyan]

bh,

Just set the default value of the contract number to the
DMax + 1, and lock the field.

Wayne

 

[Mile-O]

Can't see Wayne's example as I'm using A97 but I think it would be best to put the DMAX +1 in the BeforeUpdate() event of the form.

The reason is that you could have two users that open up the form within seconds of each other and, if you were to do put the DMAX +1 in the form on loading then both users would have the same MAX ID which will cause a conflict when saving to a field that is Indexed and does not allow duplicates.

In the BeforeUpdate event you can check your form and once everyon elooks great you can though in the DMAX+1 and there will be no conflicts.

 

[bone head]

The example Wayne sent still keeps the before update and also puts the code in the default value of the text box.

Do not know if this is the done thing, but put in it my database and it work a dream.

We only have about 30 users and as the database is mainly for customer complaints etc ( hopefully) it is not used by too many at any one time, so again hopefully the risk of 2 users getting a number at the same time is minimal

Am just glad to get rid of the auto number and the problems that has caused me.

Many thanks again to you both for your help