DMAX returns same number

[jacc14]

I have used the following code before set as the default value on a numeric field and it has always worked. Now it just returns no 1 everytime and i can figure what is different.


=DMax("[thesection]","tbl_sections","[id]=[forms]![frm_jobheader]![urn]")+1


thanks
Christine.

[jacc14]

Ive just realised that it is probably because this is set on a subform which is a continuous form (datasheet). Therefore thesection need requerying?? If i set a requery though the cursor just jumps to the top of the page.

[missinglinq]

Where are you trying to set this? If this is Continuous or Datasheet form (they're not the same thing!) and the field Control you're setting this as the Control Source for is Unbound, this is normal behavior. While it may look like X number of controls it is actually X number instances of the same control. The Control would have to be bound for this to work, and I'm not sure that an aggregate function like this would work placed in a Control Source.

Linq ;0)>

[jacc14]

Hi.

Sorry I should be more clearer. It is a continuous form. I am setting it on a bound text box. It normally works but this is first time i have done it on a continuous form. I cant help but think that its because i need to requery it.

The result i am after is to get a unique number relating to that job starting at 1. I could use autonumber but after thousands of records the figure is too high and I am using it as part of the job number.

thanks
Christine,.

[jacc14]

@jacc14
Hi All

I have now amended and i force the user to enter as text but have set it so they can't duplicate the entry. Works better as the text has a meaning.


Christine.