New to VBA Need help on an SQL boolean update from form

[Lancezos]

Hey guys,

Our database here at work needs updated and I'm trying to teach myself VBA. After reading Access 2003 with the Smart Method I have a pretty good understanding of what I'm doing and used a lot of VBA so far so good.

This one has got me stuck. It's just a simple button to mark a product as inactive. Rather then having the checkbox on the form I would like to do it this way. All I want to do is set the ProductActive field to false with the press of the button after a vba yes/no.

Private Sub cmdProductInactive_Click(Response As Integer)
Dim mbxResponse As VbMsgBoxResult
Dim strSQL As String

mbxResponse = MsgBox("Are you sure you want to mark product as inactive?" & _
vbQuestion + vbYesNo)

If mbxResponse = vbYes Then
strSQL = "UPDATE Product SET ProductActive = False " _
"WHERE Me.ProductID.Value = ProductID.Value"
Call DoCmd.SetWarnings(False)
Call DoCmd.RunSQL(strSQL)
Call DoCmd.SetWarnings(True)
Call MsgBox("The product has been marked as inactive", vbInformation)
Response = acDataErrAdded
Else

End If
End Sub


Thanks a lot!

 

[Mr. B]

Replace the sql part of your code with this:

If mbxResponse = vbYes Then
strSQL = "UPDATE Product SET ProductActive = False " _
"WHERE Product = " & Me.ProductID.Value & ";"
currentdb.execute strsql
Call MsgBox("The product has been marked as inactive", vbInformation)

To make it possible to see the value of the ProductId value before actually using it in the sql statement, you can also do the same thing this way:

If mbxResponse = vbYes Then
Dim lngProductID as Long
lngProductID = Me.ProductId
strSQL = "UPDATE Product SET ProductActive = False " _
"WHERE Product = " & lngProductID & ";"
currentdb.execute strsql
Call MsgBox("The product has been marked as inactive", vbInformation)

 

[Lancezos]

Awesome that worked! Thanks for your time!