Invalid use of Null (1 Viewer)

Hello everyone, I have encounter a issue with my code after i make a slight amendment to my VBA code. I would like to group my code by PO, Del date then the fabrication.

Below is my code before amendment:

Private Sub Form_Load()
Set con = CurrentProject.connection
Set rsSample = New ADODB.Recordset
'retrieve data from Sample
rsSample.Open "SELECT PO,Content,Reference,DelDate,Fabrication,Width,Color,SUM(OurQty)as SumofOurQty," & _
"SUM(SupplierQty) as [SumofSupplierQty] from Sample " & _
"GROUP BY Fabrication,Width,Color,PO,DelDate,,Content,Reference;", con, adOpenDynamic, adLockOptimistic
Call Display

End Sub

Public Sub Display()

txtFabrication.SetFocus
txtFabrication.Text = rsSample!Fabrication
txtWidth.SetFocus
txtWidth.Text = rsSample!Width
txtColour.SetFocus
txtColour.Text = rsSample!Color
txtLbs.SetFocus
txtLbs.Text = rsSample!SumofOurQty
txtYds.SetFocus
txtYds.Text = rsSample!SumofSupplierQty
txtDelivery.SetFocus
txtDelivery.Text = rsSample!DelDate
txtGLGPO.SetFocus
txtGLGPO.Text =rsSample!PO
txtContent.SetFocus
txtContent.Text =rsSample!Content
txtReference.SetFocus
txtReference.Text = rsSample!Reference

End Sub

Below is my code after amendment:

Private Sub Form_Load()
Set con = CurrentProject.connection
Set rsSample = New ADODB.Recordset
'retrieve data from Sample
rsSample.Open "SELECT PO,Content,Reference,DelDate,Fabrication,Width,Color,SUM(OurQty)as SumofOurQty," & _
"SUM(SupplierQty) as [SumofSupplierQty] from Sample " & _
"GROUP BY PO,DelDate,Fabrication,Width,Color,Content,Reference;", con, adOpenDynamic, adLockOptimistic
Call Display

End Sub

Public Sub Display()

txtFabrication.SetFocus
txtFabrication.Text = rsSample!Fabrication
txtWidth.SetFocus
txtWidth.Text = rsSample!Width
txtColour.SetFocus
txtColour.Text = rsSample!Color
txtLbs.SetFocus
txtLbs.Text = rsSample!SumofOurQty
txtYds.SetFocus
txtYds.Text = rsSample!SumofSupplierQty
txtDelivery.SetFocus
txtDelivery.Text = rsSample!DelDate
txtGLGPO.SetFocus
txtGLGPO.Text =rsSample!PO
txtContent.SetFocus
txtContent.Text =rsSample!Content
txtReference.SetFocus
txtReference.Text = rsSample!Reference <Invalid use of Null error at this line>

End Sub

Does any expert here knows how to solve this? or where do i go wrong?
Thankyou

I think you will find that if you just assign the recordset values to the value of the controls (just drop the .Text) you'll get what you want and be able to assign null values. If you actually need to assign them to the the text property then you will have to convert the nulls to empty strings, e.g,

txtReference.Text = Nz(rsSample!Reference, "")

with the Nz function.. You'll need to do this for all the fields that might be null.

sneuberg said:

I think you will find that if you just assign the recordset values to the value of the controls (just drop the .Text) you'll get what you want and be able to assign null values.

Click to expand...

Very likely. And even though the values are assigned to the Text property they will automatically be converted to the Value Property when the focus moves anyway, so there isn't any point using Text.

Then you wouldn't need the SetFocus commands too.

BTW You should also include the Me keyword when referring to the controls.

Galaxiom said:

Very likely. And even though the values are assigned to the Text property they will automatically be converted to the Value Property when the focus moves anyway, so there isn't any point using Text.

Then you wouldn't need the SetFocus commands too.

BTW You should also include the Me keyword when referring to the controls.

Click to expand...

Thankyou! It works perfectly well now!