Default Value (Access 2002)

Topic

New Reply

Hi,

I have the following code in the OnEnter event of a field in my form:

Dim intRelNo As Integer

intRelNo = DMax(“[RelRev]”, “Table1”, “[JobNo] = ‘” & Forms!SubFormName!JobNo & “‘”) + 1

Me!RelRev = intRelNo

It works just fine. However, I would like to know of a way to set the Value to Zero if the JobNo is a new number in the JobNo field.

How do I need to modify the code to accept “0” if the JobNo is new or Run if the JobNo is already a number in the JobNo field?

Thanks

Reply | Quote

Replies

>>It works just fine. However, I would like to know of a way to set the Value to Zero if the JobNo is a new number in the JobNo field.

How do I need to modify the code to accept “0” if the JobNo is new or Run if the JobNo is already a number in the JobNo field?<<

You need to use the NZ function (Null-to-zero). Depending on what you want, you can change your formula to either of these (I've abbreviated):

intRelNo = NZ( DMax(….yada, yada…),0) +1
intRelNo = NZ( DMax(….yada, yada…) + 1), 0)

Reply | Quote

Thanks for the quick response. However, I must have made a mistake in my post.

If a JobNo is entered in a new record that ISN’T in the JobNo field then I want the RevRel to be zero(0), if the JobNo entered in the new record IS in the list then I want the DMax+1 expression to run …. i.e. 1st record for 1st JobNo entered is 0, the next time that JobNo is entered it would be 1 and so on.

I put the code in the AfterUpdate Event of JobNo because it didn’t work in the OnEnter Event either….should I put it somewhere else?

Again, Thanks

Reply | Quote

>>If a JobNo is entered in a new record that ISN’T in the JobNo field then I want the RevRel to be zero(0), if the JobNo entered in the new record IS in the list then I want the DMax+1 expression to run …. i.e. 1st record for 1st JobNo entered is 0, the next time that JobNo is entered it would be 1 and so on.<>I put the code in the AfterUpdate Event of JobNo because it didn’t work in the OnEnter Event either….should I put it somewhere else?<<

Any time I'm going to create a number like that, I usually try to put it in the Form's BeforeUpdate event. This may not apply to this situation, but I want to minimize the chance someone else could try to use same number.

Reply | Quote