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]
"RIP" wrote:
> 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
>
>
|
Similar Threads
