How can I change the value of checkbox using VBA?

[Guest]

Thanks in advance.

Here is what I am trying to accomplish...

How can I change the value of the "active" checkbox in tblInventory for a
vehicle that just sold by just clicking on the "save" button on the
frmVehicleSale.

I am using frmVehicleSale to add a recordset (containing all the sale
details) to the tblVehicleSale. After updating the recordset I want to go in
to tblInventory and change the value of the "active" checkbox for the vehicle
that just sold from being true to being false, to filter it out of active
inventory.

The field "stockno" is the common field in both tables;however it is not the
primary key in tblInventory. The primary key in tblInventory is "ctrlno".

The code for the "save" button in "frmVehicleSale" looks like this:

Private Sub save_sale_Click()
Dim db As DAO.Database
Dim rctSaveSale As DAO.Recordset

'*** Create a pointer to the database and a pointer to the table ***
Set db = CurrentDb()
Set rctSaveSale = db.OpenRecordset("tblVehicleSale", dbOpenTable)

rctSaveSale.AddNew
rctSaveSale!ctrlno = ctrlno
rctSaveSale!saledate = saledate
rctSaveSale!dlrname = dlrname
rctSaveSale!cusname = cusname
rctSaveSale!stockno = stockno
rctSaveSale!vsprice = vsprice
rctSaveSale!vstrade = vstrade
rctSaveSale!vsstax = vsstax
rctSaveSale!vsinvtax = vsinvtax
rctSaveSale!vsaufee = vsaufee
rctSaveSale!vscrbafee = vscrbafee
rctSaveSale!vscsfee = vscsfee
rctSaveSale!vsdocfee = vsdocfee
rctSaveSale!vsrebfee = vsrebfee
rctSaveSale!vsregfee = vsregfee
rctSaveSale!vsreffee = vsreffee
rctSaveSale!vstitfee = vstitfee
rctSaveSale!vstrafee = vstrafee
rctSaveSale!vswinstk = vswinstk
rctSaveSale!vsvehins = vsvehins
rctSaveSale!vsothr01 = ChangeNulltoBlank(vsothr01)
rctSaveSale!vsothr02 = ChangeNulltoBlank(vsothr02)
rctSaveSale!vsothr03 = ChangeNulltoBlank(vsothr03)
rctSaveSale!vsothr04 = ChangeNulltoBlank(vsothr04)
rctSaveSale!vsdep01 = ChangeNulltoBlank(vsdep01)
rctSaveSale.Update
DoCmd.Close
rctSaveSale.Close
db.Close
End Sub

 

[Guest]

The following should do it - I've added a couple of Dims which should join
your exising ones then, after your record's been updated, I've set the query
definition object's SQL statement to an update query (I just copied this from
the SQL view of an update query and substituted the value of your stockno
into it - beware of the quote marks). This query is then executed (by giving
it no name it only exists in the macro and doesn't appear as a real query in
your database).


Dim qry As DAO.QueryDef
Dim str As String

[your code down to:]
rctSaveSale.Update

str = "UPDATE tblInventory SET chkActive = False WHERE (((stockno)=" &
rctSaveSale!stockno & "));"
Set qry = db.CreateQueryDef("")
qry.SQL = str
qry.Execute

[rest of your code]