Can't get Dmax +1 to work

[avalon60]

In Access 2007, I have tried using this line of code to number the next record on a form in the BeforeUpDate Event Procedure, but nothing happens:

CountID = DMax("[CountID]", "[tblBalance]") + 1

Instead of using AutoNumber, I am using Number as the data type, and I have read that using AutoNumber is not a good idea, as it is only used for systems.

Thanks

 

[RainLover]

Does tblBalance currently have any records.

Especially CountID.

Autonumber is the Best to use as a Primary Key.

DMax + 1 is best for things like Invoice Number.

 

[avalon60]

tblBalance has 12 records for now, and CountID has 1, so I would have expected the next record to be assigned to 2, or does that only occur if the next record is blank. (No it doesn't, I just tried it on a blank record.)

The said table has what you could call invoices, as it has currency values in all of the fields.

 

[spikepl]

Which BeforeUpdate? Hopefully, the form's?

 

[avalon60]

Which BeforeUpdate? Hopefully, the form's?

Yes, sorry, I should have said that in my initial post.

I have been googling various examples, and one of them leaves out CountID before the = sign, but that gives a compile error in my case.

Thanks

 

[spikepl]

Please paste the code of the entire sub

 

[avalon60]

Ok, this is the entire sub code:

Private Sub CountID_BeforeUpdate(Cancel As Integer)
CountID = DMax("[CountI]", "[tblBalance]") + 1

End Sub

 

[spikepl]

This is not the Form's BeforeUpdate, but the control's. Delete it. Open the form's property sheet, and edit the Form's BeforeUpdate event

 

[avalon60]

Ok, I have deleted the code from the Control BeforeUpdate, and put it in the Form's BeforeUpdate:

Private Sub Form_BeforeUpdate(Cancel As Integer)
CountID = DMax("[CountID]", "[tblBalance]") + 1
End Sub

For some reason, it still does not work.

Thanks

 

[spikepl]

"Doesn't work" is meaningless for anyone not watching your screen. What do you do, and what happens or does not happen?

 

[avalon60]

"Doesn't work" is meaningless for anyone not watching your screen. What do you do, and what happens or does not happen?

In or on the said form, for the first record I have inserted 1 into the CountID text box, which is for the first record of figures. I then move onto the next record and the CountID text box is blank, as in nothing is entered in it at all, but there are figures for the actual record, which I previously entered.

 

[spikepl]

It will remain blank because it is only updated just before the record is saved. And the record will only be saved if you change anything in it yourself - ie.e when you "dirty" the record. Move on to a new record, and type something there and close the form. Then open your table and have a peek.

 

[RainLover]

Try

Me.CountID = DMax(NZ("[CountID]", "[tblBalance]"),0) + 1

 

[avalon60]

It will remain blank because it is only updated just before the record is saved. And the record will only be saved if you change anything in it yourself - ie.e when you "dirty" the record. Move on to a new record, and type something there and close the form. Then open your table and have a peek.

After I entered 1 in or for the first record, then moved on to the second record I made a slight change to it, then saved it, closed down the form, looked in the table, but no change to the count of the number of records, ie it did not show 2 for the altered record.

 

[avalon60]

Try

Me.CountID = DMax(NZ("[CountID]", "[tblBalance]"),0) + 1

I get a compile error using the above code:
Method or data member not found at Me.CountID

Using the Quick watch from the Debug menu, it gives an error saying it can't compile the module

 

[RainLover]

Sorry about that.

Try again.

Me.CountID = NZ(DMax("[CountID]","tbBalance"),0)+1

 

[avalon60]

Sorry about that.

Try again.

Me.CountID = NZ(DMax("[CountID]","tbBalance"),0)+1

Now the watch window says the above line is out of context.

I have googled this error, but not found a solution as yet.

 

[RainLover]

Can you copy your table into a new database, then post that.

It must be a name problem.

I tested mine and it worked, so I am not sure what is going on.

 

[RainLover]

Please make sure your posted database is in 2003.

That is the latest version I have.

 

[avalon60]

I have just copied or exported the said table from the working database into a new database.