Can't increment field default value

[stormin_norm]

I have a subform which looks like:

MAIN FORM-----------------
Student-> Mike
-------------------------------
SubForm------------------------------------------------------------
Previous Colleges--> START END ASSESSMENT
1 Univ of Florida 1/4/1993 5/15/1993 N
2 Florida State 9/1/1994 5/14/1997 N
3 Gator U. 1/2/1999 5/20/1999 N
* N
-------------------------------------------------------------------------
When I click on new record, I easily set the default of assessment to "N". But I would also like the fourth record to auto populate the number "4" in there. I tried Default=Max[college number]+1. What function would work better? I think Dmax would not be good because for each student there are several college numbers. i.e Mike's #3 college is different than Sue's #3 college.

Thanks in advance.

 

[WayneRyan]

norm,

The DMax is what you want:

On the BeforeInsert of the insert, put:

Me.YourKey = Nz(DMax("[YourKey]", "YourTable", "Student = '" & Me.Student & "'"), 0) + 1

Wayne

 

[stormin_norm]

No go.

Because this is a subform, do I need to reference this differently in the VBA code?
I wonder if the Me.[College number] is getting confused on the main form vs. subform.

Also- I noticed the before insert event is located on the form properties, not the properties of the field I wish to increment. I imagine this is okay, since you reference Me.[field]=.......

-norm.

 

[WayneRyan]

norm,

When you reference it on the subform, the Me. restricts your
reference to the subform.

The BeforeInsert of the form is what you want.

Wayne

 

[stormin_norm]

Thanks for your assistance Wayne-

I did try the code on the beforeinsert property of the subform. I don't think I want it on the main form since I am inserting a row within the subform not the main form.

The main form has basic "header" info which does not change.
The subform contains the detail records.

What about getting explicit like:

Form!subform.field=..........etc...

I'm not sure of the exact syntax. I tried the rules Microsoft suggests for addressing fields/controls in sub forms and main forms, but their suggested addressing did not work.

-norm.

 

[WayneRyan]

Norm,

You want it on the subform.

In the confines of your subform, you refer to your controls as
Me.SomeControl

Wayne

 

[stormin_norm]

Thanks Wayne!

You pushed me in the correct direction. I did not like the before insert because you don't see the value on the screen. So I kept hacking away.

default value=NZ(DMax("[College number]","COLLEGE","[StudentID] = " & [StudentID]),0)+1

The formula did not like the Me. in front of studentID. Not sure why, because it should work. Mmm.

I also took out the single quotes around StudentID because it is a numeric value, and would not find the record unless no single quotes.

Thanks for your assistance.
Norm.