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

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



"Maxwell" <(E-Mail Removed)> wrote in message
news:79CB6301-B421-4D97-8B13-(E-Mail Removed)...
> 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