Can VBA amend data in tables

[Maxwell]

Hi,

I have a subform which is linked to a Link Table (A) which controls
relationships between two parent tables (B) and (C). On the subform is a
button to delete record, which would delete the record in table (A).

What I want to do is add a line of code to the delete button that changes a
field in one of the parent tables (say, B) before it deletes the record in
table A.

I have been playing with the SQL UPDATE record but have not managed to get
it to work yet.

So, in the below code:

Table A is: not mentioned but is called LinkBedStd
Table B is: TableBed
Common Field is : BED#
Field on Table B is: Bed_Occupied?

Could it be that the '#' and '?' are causing the problems?

Current code for delete button is:

Private Sub Command27_Click()
On Error GoTo Err_Command27_Click
Dim x As String
x = Me!BED
y = MsgBox(x, vbInformation)

Dim mySQL As String
mySQL = "UPDATE TableBED"
mySQL = mySQL & "SET TableBED.BED_Occupied_ = False"
mySQL = mySQL & " WHERE TableBed.BED_ = x"

DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DpCmd.SetWarnings True

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord

Exit_Command27_Click:
Exit Sub

Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click

End Sub

Many thanks,
Max

 

[Alex Dybenko]

Hi,
try this:

mySQL = "UPDATE TableBED"
mySQL = mySQL & "SET TableBED.[BED_Occupied?] = False"
mySQL = mySQL & " WHERE TableBed.[BED#] = '" & x & "'"

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com