VBA error in OnCurrent event due to Null value 1

[postermmxvicom]

I have a form and a subform, NewOrder and NewOrderSubform1.

The tables for these forms are Order and OrderRelease, respectively.

Order has fields OrderKey, OrderNumber
OrderRelease has fields OrderReleaseKey, FKOrderKey, ReleaseNumber

What I need is to have the field ReleaseNumber in NewOrderSubform1 to be 1 more than the max ReleaseNumber for whichever Order is referenced in NewOrder.

I have accomplished this with the following VBA in the OnCurrent Event for the subform:

Me.ReleaseNumber.DefaultValue = DMax("ReleaseNumber", "OrderRelease", "FKOrder = " & DLookup("OrderKey", "Order", "OrderNumber = " & Forms!NewOrder.OrderNumber.Value)) + 1

The problem is when I take the NewOrder form to a new record (and the OrderNumber is blank on the form) I need the ReleaseNumber to be 1.

Typically, my attempted solutions to this have thrown "invalid use of null" or "Syntax error (missing operator) in query expression 'FKOrder ='."

I have tried both Nz and IsNull in various ways to no avail.

I can build an expression that checks for Null and does something simple like pop up a message box saying whether or not it is null, but then when I try that same switch but use it to set ReleaseNumber to 1 (for null cases) or to the max (for other cases)...it gives me one of those errors.

Any insight would be greatly appreciated.

Aaron

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[ProgramError]

Me.ReleaseNumber.DefaultValue = DMax("ReleaseNumber", "OrderRelease", "FKOrder = " & DLookup("OrderKey", "Order", "OrderNumber = " & [red] IIf(IsNull(Forms!NewOrder.OrderNumber), 12345, Forms!NewOrder.OrderNumber)[/red])) + 1

Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[postermmxvicom]

Thanks PrpgramError.

Your code still produced the following error when I went to a new record:

Run-time error '94':

Invalid use of Null

But, you sent me down the right path. (I had tried many things like that, but you gave me the shove I needed)

I still don't understand the behaviour of the now-working code. I was wondering if you could lend some insight. Let me take you through the attempts up to the working sample, then ask you the question.

I still I have a question about one bizarre circumstance along the way. I put it in [red]red[/red], so it'd be easy to pick out.

After testing your code, I first changed the 12345 to 3465 (an actual order). Now the code doesn't throw an error, but of course it refers to a specific record which I can't depend on being there.

So I merged it with one of my other old ideas:

If IsNull(Forms!NewOrder.OrderNumber) Then

Me.ReleaseNumber.DefaultValue = 1

Else

Me.ReleaseNumber.DefaultValue = DMax("ReleaseNumber", "OrderRelease", "FKOrder = " & DLookup("OrderKey", "Order", "OrderNumber = " & IIf(IsNull(Forms!NewOrder.OrderNumber), 3465, Forms!NewOrder.OrderNumber))) + 1

End If

So, now it works well and doesn't 'use' a specific record, even though it is in the code. But I wanted better, so I replaced your IIf IsNull with a Nz (same effect). That worked, so I decided to replace the specific record with Null - and it worked!

If IsNull(Forms!NewOrder.OrderNumber) Then

Me.ReleaseNumber.DefaultValue = 1

Else

Me.ReleaseNumber.DefaultValue = DMax("ReleaseNumber", "OrderRelease", "FKOrder = " & DLookup("OrderKey", "Order", "OrderNumber = " & Nz(Forms!NewOrder.OrderNumber, Null))) + 1

End If

This was the crazy part.

If I replace Nz(Forms!NewOrder.OrderNumber, Null) with just Forms!NewOrder.OrderNumber, it throws the same error on new records that your code did. But as is, it works.

[red]What?? Why? How does telling it to replace a null value with a null value satiate anything?!?!?[/red]

But, this error put me on the path to the final, correct answer. Because it worked so well, I caught a circumstance in which caused failures which I had overlooked:

The code also fails when an Order exists, but no OrderRelease exists, because no FKOrder exists, hence a null.

The issue this time was with the null produced by the whole statement on new records - not a null within the statement.

final code:

If IsNull(Forms!NewOrder.OrderNumber) Then

Me.ReleaseNumber.DefaultValue = 1

Else

Me.ReleaseNumber.DefaultValue = Nz(DMax("ReleaseNumber", "OrderRelease", "FKOrder = " & DLookup("OrderKey", "Order", "OrderNumber = " & Forms!NewOrder.OrderNumber)) + 1, 1)

End If

Here, the Nz could be moved from inside the statement to around the whole thing. This works wonderfully.

Thanks again for the kick I needed to get this sorted out!

Also, I would very much like to understand that question in [red]red[/red].

Aaron

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[ProgramError]

The Signature can be change by going into your personal profile. I'd hate to have to type the bottom bit every time. Keep it short and sweet.

Forms!NewOrder.OrderNumber may have a value which is not in your database ie 0 or 12345 hence the error. Replacing it with the expression changes it to null, which is a different thing to being zero. I'm not sure why it should work with a null value either.

Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.