Help with DMax!!!!

[KingRudeDog]

Okay...I know the basic syntax for Dmax (or at least I thought I did) but I cannot get this statement to do what I want!

In the <before update> event I tyoed this:

Dim RNum As Long
Me.txtRNumber = DMax("[Request_Num]", "test_request") + 1
Me.txtRnumber = RNum

txtRNumber is the field on the form
Request_Num is the field on the table
test_request is the table

yes...I know the name syntax is bad (it's an older db I'm using as a test for a newer one).

Obviously, I want to automatically increment the field value by 1 on every new record (an if newrecord clause is included). But I just can't get the stinkin thing to work!!!

 

[KeithG]

Your syntax looks correct. Try putting this code in the After Update event.

 

[RuralGuy]

Did you mean:

Dim RNum As Long
[b]RNum[/b] = DMax("[Request_Num]", "test_request") + 1
Me.txtRnumber = RNum

 

[KingRudeDog]

Yes...that's what I meant. Thanks. And I have tried the ,after update. event as well. I'm totally stumped over this.

 

[RuralGuy]

Your code should be in the BeforeUpdate event of the form. Can I assume that the txtRnumber control is bound to the [Request_Num] field?

 

[KingRudeDog]

Yes. You can assume that.

 

[RuralGuy]

Have you single stepped the code so you can examine the values? I would think you should *only* execute this code if Me.NewRecord is true!

 

[KingRudeDog]

All that is true.

So here I've tested out a seperate test db to simplify things. 1 table, 1 field, 1 form.
The table is tblBlah
The field in the table is Blah1
the control on the form is also Blah1

So this should work:

In the BeforeUpdate event for either the form or the control:


If Me.NewRecord Then
Dim RNum As Long
RNum = DMax("[blah1]", "tblBlah")
Me.blah1 = RNum + 1
End If
End Sub

Well....it's not. And my mind is fried as to why. I've also applied:

If Me.NewRecord Then
Dim RNum As Long
RNum = DMax("[blah1]", "tblBlah") + 1
Me.blah1 = RNum
End If
End Sub

which is the same thing, slightly different syntax.

Any ideas?

 

[RuralGuy]

How about posting the test db and let me have a look at it?

 

[RuralGuy]

BTW you do need to dirty at least one other field/control for the BeforeUpdate event to trigger.

 

[KingRudeDog]

here is the simple db i just made:

 

[KingRudeDog]

Thanks...I just figured it out (thanks to your comment)...need to dirty the form first....

Sigh.

 

[RuralGuy]

Try this one. The code needs to be in the BeforeUpdate event of the *FORM*! I also added another field so you can dirty the record without messing with the field we are trying to serialize.