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