From a Form.....second issue...activating code to delete a selected record. 2

[puforee]

Yesterday I learned how to update a table from a form to modify information.
Today...I want to delete a record from the same table...based on a checkbox on the same form.

So, in an access query I built this:
Dim strSql As String

strSql = "DELETE [Dup Temp Major ATA Table Create].Course, [Dup Temp Major ATA Table Create].MajorID, [Dup Temp Major ATA Table Create].Model, [Dup Temp Major ATA Table Create].ATA, [Dup Temp Major ATA Table Create].Subject, [Dup Temp Major ATA Table Create].[Teach Time], [Dup Temp Major ATA Table Create].Chapter, [Dup Temp Major ATA Table Create].Comments, [Dup Temp Major ATA Table Create].Hours, [Dup Temp Major ATA Table Create].Minutes, [Dup Temp Major ATA Table Create].[Remove Ch], [Dup Temp Major ATA Table Create].[Remove Comments], [Dup Temp Major ATA Table Create].[Remove Time]"
FROM [Dup Temp Major ATA Table Create]
WHERE ((([Dup Temp Major ATA Table Create].MajorID)=[Forms]![Dup Major Adjust Form]![MajorID]));" CurrentDb.Execute strSql
Me.Requery
But the WHERE statement fails. I thought I was following the syntax we used yesterday.
Also, I am not sure I need to identify all the fields. When I click the check box...bound to the table...I want that record to be deleted from the table.

Please help again. And, yes, I know my table naming convention is bad..I am working on that.

Thanks,

 

[dhookom]

Your syntax is clearly wonky. I don't know if you didn't copy and paste correctly or if Tek-Tips messed it up.

Dim strSql As String
[COLOR=#4E9A06]' assuming MajorID is numeric[/color]
strSql = "DELETE * FROM [Dup Temp Major ATA Table Create] WHERE MajorID= " & Me.[MajorID]
debug.print strSQL
CurrentDb.Execute strSql, dbFailOnError
Me.Requery

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

I did not get it from tek tips. I got it from creating a delete query and coping it's SQL. Thanks for your help...I will let you know how it goes.

 

[dhookom]

puforee,
My comment about your code pertains to its syntax. Did you attempt to compile it? All quotes should be in pairs (beginning and ending). I only see three "s. Also the " CurrentDb.Execute strSql" should be on its own line. I don't know how you got the code you provided.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

OK. I have a check box in the record. When I clicked it the code ran. It said record deleted but also said runtime error. The VBA showed yellow for Me.Requery. Is this because the focus was still on the check box? Or????

Dim strSql As String
' assuming MajorID is numeric - it is
strSql = "DELETE * FROM [Dup Temp Major ATA Table Create] WHERE MajorID= " & Me.[MajorID]
' Debug.Print strSql
CurrentDb.Execute strSql, dbFailOnError
Me.Requery

 

[MajP]

I think there may be an issue if you click in a checkbox and that fires the code. The issue may be that you have an update to a field that has not been committed yet, then you delete, then you requery. So you may need to force saving the record first

Dim strSql As String

me.dirty = false 'Save the record
doEvents  'May not be necessary but ensure any events that have not been processed are processed

' assuming MajorID is numeric - it is
strSql = "DELETE * FROM [Dup Temp Major ATA Table Create] WHERE MajorID= " & Me.[MajorID]
' Debug.Print strSql
CurrentDb.Execute strSql, dbFailOnError
Me.Requery

What is the exact error message and number?

 

[MajP]

Also if you simply are deleting the current record you can use the runcommand
docmd.RunCommand acCmdDeleteRecord

 

[puforee]

MajP.

Now it works great...thanks....I am learning a lot. I could use the simpler code but...I still have a little more complexity to add. I will now also delete other records from another table that have the same MajorID.

Wish me luck.

 

[puforee]

OK...this is the first try to delete records from two tables based on the form. The first table worked as I said...above.
I then modified the code to:

Dim strSql As String
Me.Dirty = False 'Save the record
DoEvents 'May not be necessary but ensure any events that have not been processed are processed

' assuming MajorID is numeric - it is
strSql = "DELETE * FROM [Dup Temp Major ATA Table Create] WHERE MajorID= " & Me.[MajorID]
' Debug.Print strSql
CurrentDb.Execute strSql, dbFailOnError

strSql = "DELETE * FROM [Dup Temp Minor ATA Table Create] WHERE [Dup Temp Minor ATA Table Create].MajorID= " & Me.[MajorID]
CurrentDb.Execute strSql, dbFailOnErrorMe.Requery

The failure is as shown. I was trying to duplicate the strSql = to a new Delete statement...messed up again.
MajP...can you help with this? Note: There will also be a third table comming.

 

[MajP]

Is that a typo or is the "me.requery" on the same line?
CurrentDb.Execute strSql, dbFailOnError
Me.Requery

 

[puforee]

Typo. Me.requery is on the next line down.

 

[puforee]

Also...you will note the second table has Minor in the name...instead of Major.

 

[dhookom]

puforee,
I'm not sure how you can have so many typos. Aren't you simply copy and pasting into your replies? Also, consider the TGML code tags like MajP and I are using for the sake of readability.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

is there an error message?

 

[puforee]

Ahhh...the light bulb turned on. I have to remove the records from the Minor table first....and then the Major table. The way I was doing it, I removed the major table record...which included the MajorID. Then I tried to remove the Minor records...but there was no longer a MajorID for the Where statement.

So, this works.

Dim strSql As String
Me.Dirty = False 'Save the record
DoEvents 'May not be necessary but ensure any events that have not been processed are processed

' assuming MajorID is numeric - it is
strSql = "DELETE * FROM [Dup Temp Minor ATA Table Create] WHERE MajorID= " & Me.[MajorID]
' Debug.Print strSql
CurrentDb.Execute strSql, dbFailOnError

strSql = "DELETE * FROM [Dup Temp Major ATA Table Create] WHERE MajorID= " & Me.[MajorID]
CurrentDb.Execute strSql, dbFailOnError
Me.Requery

 

[dhookom]

If the minor table has referential integrity set to cascade deletes then you only need to delete from the major table and the minor records will be deleted automatically.


Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Or you can save it first in a variable

Dim strSql As String
dim ID as integer

ID = me.MajorID
Me.Dirty = False 'Save the record
DoEvents 'May not be necessary but ensure any events that have not been processed are processed

strSql = "DELETE * FROM [Dup Temp Minor ATA Table Create] WHERE MajorID= " & ID
' Debug.Print strSql
CurrentDb.Execute strSql, dbFailOnError

strSql = "DELETE * FROM [Dup Temp Major ATA Table Create] WHERE MajorID= " & ID
CurrentDb.Execute strSql, dbFailOnError
Me.Requery

 

[puforee]

Thanks guys. I attempted to set the referential relationship...but my form was coming up blanke...don't know why but I got it working this way for all three tables.

Good help...I appreciate it.

Over and out.