Deleting Record doesn't work

[MKammerer]

I have a form with a subform that searches my main table and applies a filter.
I use the subform to allow the user to select a record in the filtered list
from the table. Once selected I have several buttons on the main form. Each
of these buttons saves the PK from the subform's table to a table called
global variables on the main form and then allows me to run a select query
based on this "global variables" stored PK and open a new form with the
record's data in it that the user selected from the filtered list. Reason I
do this is the table has too much data to display all together and allowing a
search, select and action button is the easiest way to allow editing of the
records. Anyway my "edit record" button seems to work, it opens the "edit"
form as a popup with the selected record's data and allows for editing and
correctly saves the information back to the original table. The problem
comes when I tried to setup a delete button in the popup "edit" form. I have
used standard coding for the deletion (RunCommand acCmdDeleteRecord) and I
have tried using the wizard's coding:
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Problem is that with any of these things the delete action properly deletes
the data from the popup "edit" form's display but the data is still in the
original table. After trying numerous fixes I have discovered that each time
I select the delete button the data being deleted is the record in the
"global variables" table that is storing my PK selected from my search box.
Not sure if this is just a fluke or if that can be used to fix my problem.
Nonetheless any help in this area would be much appreciated.

 

[Marshall Barton]

I have a form with a subform that searches my main table and applies a filter.
I use the subform to allow the user to select a record in the filtered list
from the table. Once selected I have several buttons on the main form. Each
of these buttons saves the PK from the subform's table to a table called
global variables on the main form and then allows me to run a select query
based on this "global variables" stored PK and open a new form with the
record's data in it that the user selected from the filtered list. Reason I
do this is the table has too much data to display all together and allowing a
search, select and action button is the easiest way to allow editing of the
records. Anyway my "edit record" button seems to work, it opens the "edit"
form as a popup with the selected record's data and allows for editing and
correctly saves the information back to the original table. The problem
comes when I tried to setup a delete button in the popup "edit" form. I have
used standard coding for the deletion (RunCommand acCmdDeleteRecord) and I
have tried using the wizard's coding:
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Problem is that with any of these things the delete action properly deletes
the data from the popup "edit" form's display but the data is still in the
original table. After trying numerous fixes I have discovered that each time
I select the delete button the data being deleted is the record in the
"global variables" table that is storing my PK selected from my search box.
Not sure if this is just a fluke or if that can be used to fix my problem.

That's a standard issue with RunCommand, DoMenuItem, and
several DoCmd methids.

The reliable approach is to execute a Delete query using
code like:

DbEngine(0)(0).Execute "DELETE * FROM table WHERE PK=" &
PK, dbFailOnError

 

[MKammerer]

Since I don't have experience using this would you mind showing which parts
of the code are supposed to be my variables?
is this correct:

DbEngine(0)(0).Execute "DELETE * FROM [my table name] WHERE PK=" & [my PK
name], dbFailOnError

I have a form with a subform that searches my main table and applies a filter.
I use the subform to allow the user to select a record in the filtered list

[quoted text clipped - 19 lines]

"global variables" table that is storing my PK selected from my search box.
Not sure if this is just a fluke or if that can be used to fix my problem.

That's a standard issue with RunCommand, DoMenuItem, and
several DoCmd methids.

The reliable approach is to execute a Delete query using
code like:

DbEngine(0)(0).Execute "DELETE * FROM table WHERE PK=" &
PK, dbFailOnError

 

[MKammerer]

Actually now that I look at, it looks like it should be more like this:

DbEngine(0)(0).Execute "DELETE * FROM [my table] WHERE [my PK]=" & [global
variables value], dbFailOnError

Since I don't have experience using this would you mind showing which parts
of the code are supposed to be my variables?
is this correct:

DbEngine(0)(0).Execute "DELETE * FROM [my table name] WHERE PK=" & [my PK
name], dbFailOnError
[quoted text clipped - 10 lines]

DbEngine(0)(0).Execute "DELETE * FROM table WHERE PK=" &
PK, dbFailOnError

 

[Marshall Barton]

If [my PK] is a numeric type field and is bound to a control
on the same form as the button, then it could be:

DbEngine(0)(0).Execute "DELETE * FROM [my table name] WHERE
[my PKname]=" & Me.[my PKname], dbFailOnError

If the button is on the main form and the numeric [my PK]
field is bound to a subform control, then use:

DbEngine(0)(0).Execute "DELETE * FROM [my table name] WHERE
[my PKname]=" & Me.[subform control name].Form.[my PKname],
dbFailOnError
--
Marsh
MVP [MS Access]

Actually now that I look at, it looks like it should be more like this:

DbEngine(0)(0).Execute "DELETE * FROM [my table] WHERE [my PK]=" & [global
variables value], dbFailOnError

Since I don't have experience using this would you mind showing which parts
of the code are supposed to be my variables?
is this correct:

DbEngine(0)(0).Execute "DELETE * FROM [my table name] WHERE PK=" & [my PK
name], dbFailOnError

I have a form with a subform that searches my main table and applies a filter.
I use the subform to allow the user to select a record in the filtered list

[quoted text clipped - 10 lines]

DbEngine(0)(0).Execute "DELETE * FROM table WHERE PK=" &
PK, dbFailOnError

 

[MKammerer via AccessMonster.com]

Thanks

Worked Perfectly. Now all I have to do is create the code to ask the user
for confirmation since this is a delete query and not actually a delete
record command.

If [my PK] is a numeric type field and is bound to a control
on the same form as the button, then it could be:

DbEngine(0)(0).Execute "DELETE * FROM [my table name] WHERE
[my PKname]=" & Me.[my PKname], dbFailOnError

If the button is on the main form and the numeric [my PK]
field is bound to a subform control, then use:

DbEngine(0)(0).Execute "DELETE * FROM [my table name] WHERE
[my PKname]=" & Me.[subform control name].Form.[my PKname],
dbFailOnError

Actually now that I look at, it looks like it should be more like this:

[quoted text clipped - 13 lines]

 

[Marshall Barton]

Worked Perfectly. Now all I have to do is create the code to ask the user
for confirmation since this is a delete query and not actually a delete
record command.

If [my PK] is a numeric type field and is bound to a control
on the same form as the button, then it could be:

DbEngine(0)(0).Execute "DELETE * FROM [my table name] WHERE
[my PKname]=" & Me.[my PKname], dbFailOnError

If the button is on the main form and the numeric [my PK]
field is bound to a subform control, then use:

DbEngine(0)(0).Execute "DELETE * FROM [my table name] WHERE
[my PKname]=" & Me.[subform control name].Form.[my PKname],
dbFailOnError

If MsgBox("are you sure?", vbOKCancel, "confirm") = vbOK
Then
DbEngine(0)(0).Execute "DELETE . . .
Emd If