Re: Invalid Use of Null

• Previous message: Dirk Goldgar: "Re: Font color a inputbox"
• In reply to: Douglas J. Steele: "Re: Invalid Use of Null"
• Next in thread: Anthony Viscomi: "Re: Invalid Use of Null"
• Messages sorted by: [ date ] [ thread ]

Date: Wed, 12 Jan 2005 16:31:58 -0500

Doug,
    Essentially what I'm trying to achieve is:
Opening a Form using the following criteria
Where ID_B of the Main Form = ID_C of the Form to be opened. the Default
value of ID_C = ID_B. This value populates the ID_C field fine, but doesn't
committ to the underlying Table when the Form_Load event is ttriggered, thus
my original line of code:

intPos = DMax("[Position]", "tbl_SubPosition", "[ID_C] =
Forms!frm_Orders_Sub!subfrm_OrderB.Form![ID_B] And
[PosID]=Forms!frm_Orders_Sub!subfrm_OrderB.Form![Position]")

Returns Null...I think.

After I have the correct ID_C, I need to check for the MAX Position for that
ID. Basically the user is adding a Sub-position(Accessory) to a cabinet that
has a Main Position (whole #).

Once there is a record with an ID_C value present within the table the above
code works fine.

I hope that I didn't confuse the issue with the above translation.

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:uLoVCuO%23EHA.2984@TK2MSFTNGP09.phx.gbl...
> But that value is Null!
>
> Since you're trying to assign a value to intPos, which is a Double, it's
> not
> possible to return Null. The only data type that can be assigned a value
> of
> Null is a Variant.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (No private e-mails, please)
>
>
> "Anthony Viscomi" <anthonyviscomi@msn.com> wrote in message
> news:#8xnnVO#EHA.3236@TK2MSFTNGP15.phx.gbl...
>> I would like for it to be:
>> Forms!frm_Orders_Sub!subfrm_OrderB.Form!ID_B
>>
>> Which is the value of the underlying form.
>> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
>> news:e75eiRO%23EHA.2788@TK2MSFTNGP15.phx.gbl...
>> > That's correct: the Nz function is converting the Null to 0, and you're
>> > then
>> > adding .1 to it.
>> >
>> > What do you want if it's Null?
>> >
>> > --
>> > Doug Steele, Microsoft Access MVP
>> > http://I.Am/DougSteele
>> > (No private e-mails, please)
>> >
>> >
>> > "Anthony Viscomi" <anthonyviscomi@msn.com> wrote in message
>> > news:#iwGwqN#EHA.3504@TK2MSFTNGP12.phx.gbl...
>> >> Allen,
>> >> Thanks for your reply. The Nz method seems to work, but when the
>> >> Forms!frm_Orders_Sub!subfrm_OrderB.Form!ID_B value is Null the code
> only
>> >> returns the Me.Position = intPos + 0.1 portion. In other words 0.1.
>> >>
>> >> Any thoughts?
>> >> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
>> >> news:uIjPzRH%23EHA.2600@TK2MSFTNGP09.phx.gbl...
>> >> > Two possible causes:
>> >> > 1. If the control is null, you need to supply some impossible
>> >> > values.
>> >> > 2. If the DMax() call retuns null, assigning to a Double will cause
> an
>> >> > error.
>> >> >
>> >> > It's also better to concatenate the value of the text box into the
> 3rd
>> >> > argument.
>> >> >
>> >> > Try:
>> >> > intPos = Nz(DMax("[Position]", "tbl_SubPosition", _
>> >> > "[ID_C] = " &
>> > Nz(Forms!frm_Orders_Sub!subfrm_OrderB.Form![ID_B],0)),0)
>> >> >
>> >> > --
>> >> > Allen Browne - Microsoft MVP. Perth, Western Australia.
>> >> > Tips for Access users - http://allenbrowne.com/tips.html
>> >> > Reply to group, rather than allenbrowne at mvps.org.
>> >> >
>> >> > "Anthony Viscomi" <anthonyviscomi@msn.com> wrote in message
>> >> > news:ux2ShlF%23EHA.3368@TK2MSFTNGP15.phx.gbl...
>> >> >>I have the following VBA:
>> >> >>
>> >> >> Private Sub Command32_Click()
>> >> >> Dim intPos As Double
>> >> >>
>> >> >> intPos = DMax("[Position]", "tbl_SubPosition", "[ID_C] =
>> >> >> Forms!frm_Orders_Sub!subfrm_OrderB.Form![ID_B]")
>> >> >> Me.Position = intPos + 0.1
>> >> >>
>> >> >> End Sub
>> >> >>
>> >> >> My problem is that I receive an "Invalid use of Null" error. I know
>> >> >> the
>> >> >> reason, but I don't know the solution.
>> >> >>
>> >> >> The ID_C field is has the Default Value set to:
>> >> >> =[Forms]![frm_Orders_Sub]![subfrm_OrderB].[Form]![ID_B]
>> >> >>
>> >> >> The cmdButton that triggers this event is on the same form that
>> > contains
>> >> >> the ID_C field.
>> >> >>
>> >> >> My problem is that the record is be committed to the
> tbl_SubPosition,
>> >> >> thus there may not be a ID_C value to match the ID_B.
>> >> >>
>> >> >> How can I get the record to committ to the underlying to prior to
> this
>> >> >> event?
>> >> >>
>> >> >> Thanks Again,
>> >> >> Anthony
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

• Previous message: Dirk Goldgar: "Re: Font color a inputbox"
• In reply to: Douglas J. Steele: "Re: Invalid Use of Null"
• Next in thread: Anthony Viscomi: "Re: Invalid Use of Null"
• Messages sorted by: [ date ] [ thread ]