Help...'Variable Not Defined'

L

LJG

Can anyone see why this fails and gives me a error: 'Variable Not Defined'

keep going through the code and I can not find whats wrong.

Private Sub buttonClose_Click()
Dim strQty As String
Dim strWeight As String
Dim strSQl As String

Dim ctlQty As Control
Dim ctlWeight As Control

Me.txtAmt = Me.frmOrderItems!txtTotal
Me.txtbalance = Me.frmOrderItems!txtTotal

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


Set rslineitems = CurrentDb.OpenRecordset("orderlineitems",
dbOpenDynaset)<< Fails Here>>
Set rsCSInventory = CurrentDb.OpenRecordset("tblinventory",
dbOpenDynaset)


DoCmd.SetWarnings False



strSQl = "UPDATE tblinventory " & vbCrLf
strSQl = strSQl & " INNER JOIN orderlineitems " & vbCrLf
strSQl = strSQl & " ON tblinventory.productID =
orderlineitems.productID SET tblinventory.SumOfqty =
[tblinventory]![SumOfqty]-[tblorderlineitems]![qty]" & vbCrLf
strSQl = strSQl & " , tblinventory.SumOfweight =
[tblinventory]![SumOfweight]-[tblorderlineitems]![weight]" & vbCrLf
strSQl = strSQl & " WHERE
(((orderlineitems.custID)=[forms]![frmOrderPlacement]![txtcustid]) " &
vbCrLf
strSQl = strSQl & " AND
((orderlineitems.orderID)=[forms]![frmOrderPlacement]![orderid]));"
DoCmd.OpenQuery "UpdateCStbl", acViewNormal
DoCmd.RunSQL strSQl


rsCSInventory.Close
rslineitems.Close

Set rsCSInventory = Nothing
Set rslineitems = Nothing

DoCmd.Close
End Sub

Many thanks
Les
 
B

Brian Bastl

LJG,
you're missing the following:

Dim rslineitems As DAO.Recordset
Dim rsCSInventory As DAO.Recordset

HTH,
Brian
 
L

LJG

Cheers Thanks for that Brian

Going nuts here and could not see the obvious

Les

Brian Bastl said:
LJG,
you're missing the following:

Dim rslineitems As DAO.Recordset
Dim rsCSInventory As DAO.Recordset

HTH,
Brian


LJG said:
Can anyone see why this fails and gives me a error: 'Variable Not
Defined'

keep going through the code and I can not find whats wrong.

Private Sub buttonClose_Click()
Dim strQty As String
Dim strWeight As String
Dim strSQl As String

Dim ctlQty As Control
Dim ctlWeight As Control

Me.txtAmt = Me.frmOrderItems!txtTotal
Me.txtbalance = Me.frmOrderItems!txtTotal

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70


Set rslineitems = CurrentDb.OpenRecordset("orderlineitems",
dbOpenDynaset)<< Fails Here>>
Set rsCSInventory = CurrentDb.OpenRecordset("tblinventory",
dbOpenDynaset)


DoCmd.SetWarnings False



strSQl = "UPDATE tblinventory " & vbCrLf
strSQl = strSQl & " INNER JOIN orderlineitems " & vbCrLf
strSQl = strSQl & " ON tblinventory.productID =
orderlineitems.productID SET tblinventory.SumOfqty =
[tblinventory]![SumOfqty]-[tblorderlineitems]![qty]" & vbCrLf
strSQl = strSQl & " , tblinventory.SumOfweight =
[tblinventory]![SumOfweight]-[tblorderlineitems]![weight]" & vbCrLf
strSQl = strSQl & " WHERE
(((orderlineitems.custID)=[forms]![frmOrderPlacement]![txtcustid]) " &
vbCrLf
strSQl = strSQl & " AND
((orderlineitems.orderID)=[forms]![frmOrderPlacement]![orderid]));"
DoCmd.OpenQuery "UpdateCStbl", acViewNormal
DoCmd.RunSQL strSQl


rsCSInventory.Close
rslineitems.Close

Set rsCSInventory = Nothing
Set rslineitems = Nothing

DoCmd.Close
End Sub

Many thanks
Les
Click to expand...
Click to expand...
 
B

Brian Bastl

Happens to me more often than I'd care to admit!!!


LJG said:
Cheers Thanks for that Brian

Going nuts here and could not see the obvious

Les

Brian Bastl said:
LJG,
you're missing the following:

Dim rslineitems As DAO.Recordset
Dim rsCSInventory As DAO.Recordset

HTH,
Brian


LJG said:
Can anyone see why this fails and gives me a error: 'Variable Not
Defined'

keep going through the code and I can not find whats wrong.

Private Sub buttonClose_Click()
Dim strQty As String
Dim strWeight As String
Dim strSQl As String

Dim ctlQty As Control
Dim ctlWeight As Control

Me.txtAmt = Me.frmOrderItems!txtTotal
Me.txtbalance = Me.frmOrderItems!txtTotal

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70


Set rslineitems = CurrentDb.OpenRecordset("orderlineitems",
dbOpenDynaset)<< Fails Here>>
Set rsCSInventory = CurrentDb.OpenRecordset("tblinventory",
dbOpenDynaset)


DoCmd.SetWarnings False



strSQl = "UPDATE tblinventory " & vbCrLf
strSQl = strSQl & " INNER JOIN orderlineitems " & vbCrLf
strSQl = strSQl & " ON tblinventory.productID =
orderlineitems.productID SET tblinventory.SumOfqty =
[tblinventory]![SumOfqty]-[tblorderlineitems]![qty]" & vbCrLf
strSQl = strSQl & " , tblinventory.SumOfweight =
[tblinventory]![SumOfweight]-[tblorderlineitems]![weight]" & vbCrLf
strSQl = strSQl & " WHERE
(((orderlineitems.custID)=[forms]![frmOrderPlacement]![txtcustid]) " &
vbCrLf
strSQl = strSQl & " AND
((orderlineitems.orderID)=[forms]![frmOrderPlacement]![orderid]));"
DoCmd.OpenQuery "UpdateCStbl", acViewNormal
DoCmd.RunSQL strSQl


rsCSInventory.Close
rslineitems.Close

Set rsCSInventory = Nothing
Set rslineitems = Nothing

DoCmd.Close
End Sub

Many thanks
Les
Click to expand...
Click to expand...
Click to expand...
 
Top