Invalid Use of Null

[Anthony Viscomi]

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

 

[Allen Browne]

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)

 

[Anthony Viscomi]

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?

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.

Reply to group, rather than allenbrowne at mvps.org.

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

 

[Douglas J. Steele]

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

(No private e-mails, please)

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?

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.

Reply to group, rather than allenbrowne at mvps.org.

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

 

[Anthony Viscomi]

I would like for it to be:
Forms!frm_Orders_Sub!subfrm_OrderB.Form!ID_B

Which is the value of the underlying form.

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

(No private e-mails, please)

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?

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.

Reply to group, rather than allenbrowne at mvps.org.

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

 

[Anthony Viscomi]

In addition to my last reply:
I have the default value of ID_C set to =
Forms!frm_Orders_Sub!subfrm_OrderB.Form!ID_B

It would seem to me that if I can get that value to save/committ to the
underlying table, then run my code my Null issue can be resolved.

Is this possible?

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

(No private e-mails, please)

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?

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.

Reply to group, rather than allenbrowne at mvps.org.

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

 

[Douglas J. Steele]

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

(No private e-mails, please)

I would like for it to be:
Forms!frm_Orders_Sub!subfrm_OrderB.Form!ID_B

Which is the value of the underlying form.

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

(No private e-mails, please)

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?
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.

Reply to group, rather than allenbrowne at mvps.org.

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

 

[Anthony Viscomi]

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.

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

(No private e-mails, please)

I would like for it to be:
Forms!frm_Orders_Sub!subfrm_OrderB.Form!ID_B

Which is the value of the underlying form.

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

(No private e-mails, please)


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?
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.

Reply to group, rather than allenbrowne at mvps.org.

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