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

 

[Pat Hartman]

If TabOn is numeric, you need to get rid of the delimiters around it:

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

 

[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.

 

[Pat Hartman]

Your first syntax was correct (it had the trailing quote) assuming that the field names are correct. Have you checked for typos? Try opening the immediate window and printing your results.

Print DMax"[RevisionNumber]",
"tbl_TitleBlockEngineeringChanges",
"tabon = 'somevalue')

also try:

Print DMax"[RevisionNumber]",
"tbl_TitleBlockEngineeringChanges",
"tabon = Forms!YourForm!YourField)

You may need to enclose the table name in square brackets because of the underscore in its name.

 

[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.

 

[Pat Hartman]

You need to run the print command in the immediate window, not in your code module.

 

[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'

 

[Pat Hartman]

Try this. Sorry, I left off the trailing quotes on the previous samples:

Print DMax("[RevisionNumber]", "tbl_TitleBlockEngineeringChanges",
"tabon = '" & Forms!frm_tool!tabon & "'")
Also try:
Print DMax("[RevisionNumber]", "tbl_TitleBlockEngineeringChanges",
"tabon = 'xyz'")

after replacing 'xyz' with a valid value for tabon.