Deleting Record From Form

[marnieg]

I have a form that displays data from 1 table, but I have a join on my query to reference data from another table for conditional formating on one of the fields on the form.

When I want to delete a record from the form, it is deleting both records from both tables. I don't want the joined table deleted. My sql statement is using an INNER JOIN on the tables.

 

[pbaldy]

What is the SQL of the query?

 

[marnieg]

Here is the SQL

SELECT BallotUsage.bau_auto, BallotUsage.bau_type, BallotUsage.bau_date, BallotUsage.bau_loc, BallotUsage.bau_pre, BallotUsage.bau_low, BallotUsage.bau_high, BallotUsage.bau_first, BallotUsage.bau_last, BallotUsage.bau_spoiled, BallotUsage.bau_prov, BallotUsage.bau_total, BallotUsage.bau_lock, BallotUsage.bau_last_used, [Ballot Percent Alerts].bpa_val, [Ballot Percent Alerts].bpa_total, [Ballot Percent Alerts].bpa_init_lh_tot, BallotUsage.bau_type, BallotUsage.bau_pre, BallotUsage.bau_low, [Ballot Percent Alerts].bpa_warn
FROM BallotUsage INNER JOIN [Ballot Percent Alerts] ON (BallotUsage.bau_pre = [Ballot Percent Alerts].bpa_pre) AND (BallotUsage.bau_type = [Ballot Percent Alerts].bpa_type) AND (BallotUsage.bau_loc = [Ballot Percent Alerts].bpa_loc)
ORDER BY BallotUsage.bau_date, BallotUsage.bau_loc, BallotUsage.bau_type, BallotUsage.bau_pre, BallotUsage.bau_low;

 

[marnieg]

I changed my form to use a command button for the delete, because I need to do some other checking and updating, but now I get the following error. I have listed my code for my command button.

"The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time"


DoCmd.RunCommand acCmdSelectRecord
If Me![bau_first] > 0 Or Me![bau_last] > 0 Or Me![bau_last_used] > 0 Then
MsgBox "Can not delete ballot usage if ballots have been used"
Exit Sub
Else
' update totals on Ballot Percent Alerts
intRemBallots = Me![bau_high] - Me![bau_low] + 1
intTotal = DLookup("bpa_init_lh_tot", "[Ballot Percent Alerts]", "bpa_loc = '" & Me![bau_loc] & "' and bpa_type = '" & Me![bau_type] & "' and bpa_pre = '" & Me![bau_pre] & "'")

intNewTotal = intTotal - intRemBallots
CurrentDb.Execute "Update [Ballot Percent Alerts] set bpa_init_lh_tot = " & intNewTotal & " where bpa_loc = '" & Me![bau_loc] & "' and bpa_type = '" & Me![bau_type] & "' and bpa_pre = '" & Me![bau_pre] & "'"

DoCmd.RunCommand acCmdDeleteRecord
Me.Refresh
End If

I think I'm still running into problems with the table I'm joining to.

 

[marnieg]

Chose not to use acCmdDeleteRecord and just performed an SQL "Delete" command for the selected record. This worked great