Increment a field using DMax

[craw]

Still new to this site and VBA, but found this post which handles a problem similar to mine
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=52918&highlight=increment

Here's the code for my application, but my output is zilch!

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.RevisionNumber = DMax("[RevisionNumber]", "tbl_TitleBlockEngineeringChanges", _
    "tabon = '" & Me.TabOn & "'") + 1

End Sub

Background: I have a main form with drawing information and a subform with revision numbers. The two forms are linked on a field called TabOn. My supporting table looks something like

TabOnA rev1
TabOnA rev2
TabOnA rev3
TabOnB rev1
TabOnC rev1

I tried a variation of the code above but DMax returned the highest incremented value of all the TabOn's (rev4) not the intended result...for example, rev2 for TabOnC.

Thanks in advance

 

[craw]

hmm, now i'm really confused. TAbOn is not a numeric field, it contains text and numbers, i.e. 11ABCD. I'm trying to get the rev number to increment. It seems like this should be so easy <frustration>

Me.RevisionNumber = DMax("[RevisionNumber]", "tbl_TitleBlockEngineeringChanges", _ 
"tabon = '" & Me.TabOn) + 1

I understand this to be stating, For the revisionnumber on the current form, find the highest value of revisionnumber in the orignal table, tbl_TitleBlockEngineeringChanges, where the tabon from that table equals the tabon from the main form.

 

[craw]

Pat,
Maybe I have my code in the wrong place. It's in the BeforeUpdate event of the subform.
I tried the print feature that you recommended... I put that infront of the code that I already have BUT I got an 'Argument not optional' which highlighted the DMAX when I ran it.
I also tried the square brackets on the table name - no dice.
This is not a terrible important problem... I'm just trying to add a few bells and whistles to this thing.

 

[FoFa]

Um, just a stupid question, is your RevisionNumber field a numeric field? I am only asking because keep showing it as rev1 rev2 etc.

 

[craw]

hey there fellow texan.. yes, i have it defined as a long interger. you had me worried for a second, had to go ck it out. what a silly mistake that would be. i've been know to do far worse.

 

[craw]

Pat,
i've found the immediate window for debugging. However, I do not know how to use it. I've tried a few combinations.
1) I put

Print DMax; "[RevisionNumber]", _
"tbl_TitleBlockEngineeringChanges", _
"tabon = Forms!frm_tool!tabon)"

in the module after the DMax code and typed the name of my private sub "Form_BeforeUpdate" in the debug pane. I get 'Sub or Function Not defined'

2) I put the Print code in the debug pane and removed from my module and removed the name of my sub from the debug pane. I get 'Argument not optional'

3) I put

debug.Print form_beforeUpdate()

in the debug window and removed the Print code from my module and get 'Run-time error 9, subscript out of range'