SQL to VB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks in advance.

I am running an update query ("qryDeactivateInventory") to update a checkbox
in table "tblInventory" to false from a form named "frmVehicleSale".

This is the code that runs the query from "frmVehicleSale" form.
'*** De-Activate Sold Inventory
DoCmd.OpenQuery "qryDeactivateInventory", acViewNormal, acEdit

This is the SQL code for "qryDeactivateInventory":
UPDATE tblInventory SET tblInventory.active = False
WHERE (((tblInventory.stockno)=[forms]![frmVehicleSale]![stockno]));

I would like to perform the same function but without using a separate query.

I tried the following, but it didn't update the correct record in table
"tblInventory". I can't get the association of the form data
("frmVehicleSale") to the "tblInventory" table right.

Private Sub save_sale_Click()
Dim rctSellInventory As DAO.Recordset
Set rctSellInventory = db.OpenRecordset("tblInventory", dbOpenTable)
rctSellInventory.Edit
rctSellInventory!active = False
rctSellInventory.Update
DoCmd.Close
rctSellInventory.Close
db.Close
End Sub
 
The easiest way would be to:

Private Sub Save_Sale_Click()
Currentdb.Execute "UPDATE tblInventory SET Active = False WHERE stockno
= " & Me.StockNo

End Sub



Chris Nebinger
 
Thanks in advance.

I am running an update query ("qryDeactivateInventory") to update a checkbox
in table "tblInventory" to false from a form named "frmVehicleSale".

This is the code that runs the query from "frmVehicleSale" form.
'*** De-Activate Sold Inventory
DoCmd.OpenQuery "qryDeactivateInventory", acViewNormal, acEdit

This is the SQL code for "qryDeactivateInventory":
UPDATE tblInventory SET tblInventory.active = False
WHERE (((tblInventory.stockno)=[forms]![frmVehicleSale]![stockno]));

I would like to perform the same function but without using a separate query.

I tried the following, but it didn't update the correct record in table
"tblInventory". I can't get the association of the form data
("frmVehicleSale") to the "tblInventory" table right.

Private Sub save_sale_Click()
Dim rctSellInventory As DAO.Recordset
Set rctSellInventory = db.OpenRecordset("tblInventory", dbOpenTable)
rctSellInventory.Edit
rctSellInventory!active = False
rctSellInventory.Update
DoCmd.Close
rctSellInventory.Close
db.Close
End Sub
Click to expand...

Try:
*** AIR CODE ***
Private Sub save_sale_Click()
Dim rs As DAO.Recordset
Set rs = CurrentDB.OpenRecordset("tblInventory", dbOpenDynaset)
rs.FindFirst "[stockno] = " & Me.stockno
If Not rs.NoMatch Then
With rs
.Edit
!active = False
.Update
End With
Else
MsgBox "Unable to locate [" & Me.stockno & "]"
End If
rs.Close
Set rs = Nothing
End Sub
*** /AIR CODE ***
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 
I am getting runtime error 3070.
The Microsoft Jet database engine does not recognize 'XM00050' as a valid
field name or expression.

Anyone else have any ideas??



RuralGuy said:
Thanks in advance.

I am running an update query ("qryDeactivateInventory") to update a checkbox
in table "tblInventory" to false from a form named "frmVehicleSale".

This is the code that runs the query from "frmVehicleSale" form.
'*** De-Activate Sold Inventory
DoCmd.OpenQuery "qryDeactivateInventory", acViewNormal, acEdit

This is the SQL code for "qryDeactivateInventory":
UPDATE tblInventory SET tblInventory.active = False
WHERE (((tblInventory.stockno)=[forms]![frmVehicleSale]![stockno]));

I would like to perform the same function but without using a separate query.

I tried the following, but it didn't update the correct record in table
"tblInventory". I can't get the association of the form data
("frmVehicleSale") to the "tblInventory" table right.

Private Sub save_sale_Click()
Dim rctSellInventory As DAO.Recordset
Set rctSellInventory = db.OpenRecordset("tblInventory", dbOpenTable)
rctSellInventory.Edit
rctSellInventory!active = False
rctSellInventory.Update
DoCmd.Close
rctSellInventory.Close
db.Close
End Sub
Click to expand...

Try:
*** AIR CODE ***
Private Sub save_sale_Click()
Dim rs As DAO.Recordset
Set rs = CurrentDB.OpenRecordset("tblInventory", dbOpenDynaset)
rs.FindFirst "[stockno] = " & Me.stockno
If Not rs.NoMatch Then
With rs
.Edit
!active = False
.Update
End With
Else
MsgBox "Unable to locate [" & Me.stockno & "]"
End If
rs.Close
Set rs = Nothing
End Sub
*** /AIR CODE ***
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
Click to expand...
 
Is XM00050 the value of stockno? In other words, is stockno a text field,
not a numeric one?

If so, change

rs.FindFirst "[stockno] = " & Me.stockno

to

rs.FindFirst "[stockno] = '" & Me.stockno & "'"

or

rs.FindFirst "[stockno] = " & Chr$(34) & Me.stockno & Chr$(34)

or

rs.FindFirst "[stockno] = """ & Me.stockno & """"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


RIP said:
I am getting runtime error 3070.
The Microsoft Jet database engine does not recognize 'XM00050' as a valid
field name or expression.

Anyone else have any ideas??



RuralGuy said:
Thanks in advance.

I am running an update query ("qryDeactivateInventory") to update a
checkbox
in table "tblInventory" to false from a form named "frmVehicleSale".

This is the code that runs the query from "frmVehicleSale" form.
'*** De-Activate Sold Inventory
DoCmd.OpenQuery "qryDeactivateInventory", acViewNormal, acEdit

This is the SQL code for "qryDeactivateInventory":
UPDATE tblInventory SET tblInventory.active = False
WHERE (((tblInventory.stockno)=[forms]![frmVehicleSale]![stockno]));

I would like to perform the same function but without using a separate
query.

I tried the following, but it didn't update the correct record in table
"tblInventory". I can't get the association of the form data
("frmVehicleSale") to the "tblInventory" table right.

Private Sub save_sale_Click()
Dim rctSellInventory As DAO.Recordset
Set rctSellInventory = db.OpenRecordset("tblInventory", dbOpenTable)
rctSellInventory.Edit
rctSellInventory!active = False
rctSellInventory.Update
DoCmd.Close
rctSellInventory.Close
db.Close
End Sub
Click to expand...

Try:
*** AIR CODE ***
Private Sub save_sale_Click()
Dim rs As DAO.Recordset
Set rs = CurrentDB.OpenRecordset("tblInventory", dbOpenDynaset)
rs.FindFirst "[stockno] = " & Me.stockno
If Not rs.NoMatch Then
With rs
.Edit
!active = False
.Update
End With
Else
MsgBox "Unable to locate [" & Me.stockno & "]"
End If
rs.Close
Set rs = Nothing
End Sub
*** /AIR CODE ***
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
Click to expand...
Click to expand...
 
Back
Top