Delete Command Button Event (1 Viewer)

mba_110 · Feb 7, 2019

Hi Everyone,

I am writing cmd button event for delete in my data entry form before deleting the records i am storing the information in delete table and updating it with delete date in order to do that i have divided the sql in three portions (INSERT INTO, DELETE, UPDATE).

Please review the below code, I think i am missing some delimiters in the code which generate the errors of syntax & Too few parameters.

Code:

Private Sub CmdDeleteEntry_Click()

Dim VBAnsw As String

If Me.cboEntryNo > 0 Then

VBAnsw = MsgBox("You are about to delete the entry and its underlined transactions" & vbCrLf _
& "If you click yes, you won't be able to undo this delete operation. " & vbCrLf & vbCrLf _
& "Are you sure you want to delete this records?", vbYesNo, "Warning")

If VBAnsw = vbYes Then
'Append the Entry details before deleting.
CurrentDb.Execute " INSERT INTO tblEntries_Deleted EntryNo, EntryDate, EntryType, EntryHeading" & _
" SELECT tblEntries.EntryNo, tblEntries.EntryDate, tblEntries.EntryType, tblEntries.EntryHeading" & _
" FROM tblEntries " & _
" WHERE EntryNo=" & [Forms]![frmCashBankEntry]![cboEntryNo]

'Append the transactions underlined the entry
CurrentDb.Execute " INSERT INTO tblTransactions_Deleted TransactionID, TransactionDate, EntryNo, TransactionType, AccountNo, TransactionMemo, Category, [Currency], CurDebit, CurCredit, ExchangeRate, Posted, PostingDate, PostedBy, Void, VoidBy, VoidDate " & _
" SELECT tblTransactions.TransactionID, tblTransactions.TransactionDate, tblTransactions.EntryNo, tblTransactions.TransactionType, tblTransactions.AccountNo, tblTransactions.TransactionMemo, tblTransactions.Category, tblTransactions.Currency, tblTransactions.CurDebit, tblTransactions.CurCredit, tblTransactions.ExchangeRate, tblTransactions.Posted, tblTransactions.PostingDate, tblTransactions.PostedBy, tblTransactions.Void, tblTransactions.VoidBy, tblTransactions.VoidDate " & _
" FROM tblTransactions " & _
" WHERE EntryNo =" & [Forms]![frmCashBankEntry]![cboEntryNo]

'Delete Entry from tblEntries (Main table)
CurrentDb.Execute " DELETE tblEntries.EntryNo, tblEntries.EntryDate, tblEntries.EntryType, tblEntries.EntryHeading " & _
" FROM tblEntries " & _
" WHERE EntryNo= " & [Forms]![frmCashBankEntry]![cboEntryNo]

'Delete Transactions from tblTransactions which is associated with above entry number
CurrentDb.Execute " DELETE tblTransactions.TransactionID, tblTransactions.TransactionDate, tblTransactions.EntryNo, tblTransactions.TransactionType, tblTransactions.AccountNo, tblTransactions.TransactionMemo, tblTransactions.Category, tblTransactions.Currency, tblTransactions.CurDebit, tblTransactions.CurCredit, tblTransactions.ExchangeRate, tblTransactions.Posted, tblTransactions.PostingDate, tblTransactions.PostedBy, tblTransactions.Void, tblTransactions.VoidBy, tblTransactions.VoidDate " & _
" FROM tblTransactions " & _
" WHERE EntryNo =" & [Forms]![frmCashBankEntry]![cboEntryNo]

'Update the deleted tables with dates & user
CurrentDb.Execute " UPDATE tblTransactions_Deleted LEFT JOIN tblEntries_Deleted ON tblTransactions_Deleted.EntryNo = tblEntries_Deleted.EntryNo SET tblTransactions_Deleted.DateDeleted = Now(), tblEntries_Deleted.DateDeleted = Now() " & _
" WHERE EntryNo =" & [Forms]![frmCashBankEntry]![cboEntryNo]
End If
    End If
    Debug.Print VBAnsw
End Sub

Many thanks in advance for your help and cooperation.

Gasman · Feb 7, 2019

You don't learn do you.:banghead:

On numerous occasions, by numerous people you have been advised to put the sql string into a variable and Debug.Print that variable.

That will almost always highlight all these errors that you keep getting, but rather than do the work yourself, you would rather someone else do it for you.

essaytee · Feb 7, 2019

For starters, you are missing the trailing semi-colon ";"

I haven't dissected the rest of it.

Gasman · Feb 7, 2019

essaytee said:
For starters, you are missing the trailing semi-colon ";"

I haven't dissected the rest of it.

I think that is optional Steve, I never use it unless I have pasted code from the QBE SQL window.

MajP · Feb 7, 2019

I see several possible mistakes, but follow Gasman's advice and print back and we would all have a chance to debug.

essaytee · Feb 7, 2019

Gasman said:
I think that is optional Steve, I never use it unless I have pasted code from the QBE SQL window.

Hmm, it's something I've always done, but I may stand corrected. I'll now have to test it, but won't be until tomorrow.

isladogs · Feb 7, 2019

The semicolon is definitely optional though I still always include it. Old habits …

mba_110 · Feb 7, 2019

I can understand you guys are professionals and can tell anything you like if its not presented correctly or missed something, but every effort need time to get mature not immediately once its initiated, also please note i don't pressurize anyone to do my work.

Below is my try to make as you guys suggested, if not correct then let me know i will change it.

Code:

Private Sub CmdDeleteEntry_Click()
Dim Asql As String
Dim Bsql As String
Dim Csql As String
Dim Dsql As String
Dim Esql As String

Dim VBAnsw As String
If Me.cboEntryNo > 0 Then

VBAnsw = MsgBox("You are about to delete the entry and its underlined transactions" & vbCrLf _
& "If you click yes, you won't be able to undo this delete operation. " & vbCrLf & vbCrLf _
& "Are you sure you want to delete this records?", vbYesNo, "Warning")

If VBAnsw = vbYes Then
'Append the Entry details before deleting.
        Asql = " INSERT INTO tblEntries_Deleted EntryNo, EntryDate, EntryType, EntryHeading" & _
        " SELECT EntryNo, EntryDate, EntryType, EntryHeading" & _
        " FROM tblEntries" & _
        " WHERE EntryNo=" & [Forms]![frmCashBankEntry]![cboEntryNo]
Debug.Print Asql

'Append the transactions underlined the entry
            Bsql = " INSERT INTO tblTransactions_Deleted TransactionID, TransactionDate, EntryNo, TransactionType, AccountNo, TransactionMemo, Category, [Currency], CurDebit, CurCredit, ExchangeRate, Posted, PostingDate, PostedBy, Void, VoidBy, VoidDate " & _
            " SELECT TransactionID, TransactionDate, EntryNo, TransactionType, AccountNo, TransactionMemo, Category, Currency, CurDebit, CurCredit, ExchangeRate, Posted, PostingDate, PostedBy, Void, VoidBy, VoidDate " & _
            " FROM tblTransactions " & _
            " WHERE EntryNo =" & [Forms]![frmCashBankEntry]![cboEntryNo]
Debug.Print Bsql

'Delete Entry from tblEntries (Main table)
                    Csql = " DELETE EntryNo, EntryDate, EntryType, EntryHeading " & _
                    " FROM tblEntries " & _
                    " WHERE EntryNo= " & [Forms]![frmCashBankEntry]![cboEntryNo]
Debug.Print Csql

'Delete Transactions from tblTransactions which is associated with above entry number
            Dsql = " DELETE TransactionID, TransactionDate, EntryNo, TransactionType, AccountNo, TransactionMemo, Category, Currency, CurDebit, CurCredit, ExchangeRate, Posted, PostingDate, PostedBy, Void, VoidBy, VoidDate " & _
            " FROM tblTransactions " & _
            " WHERE EntryNo =" & [Forms]![frmCashBankEntry]![cboEntryNo]
Debug.Print Dsql

'Update the deleted tables with dates & user
    Esql = " UPDATE tblTransactions_Deleted LEFT JOIN tblEntries_Deleted ON tblTransactions_Deleted.EntryNo = tblEntries_Deleted.EntryNo SET tblTransactions_Deleted.DateDeleted = Now(), tblEntries_Deleted.DateDeleted = Now() " & _
    " WHERE EntryNo =" & [Forms]![frmCashBankEntry]![cboEntryNo]
Debug.Print Esql

DoCmd.RunSQL Asql, Bsql, Csql, Dsql, Esql
        End If
    End If
End Sub

The Immediate window show this result.

Code:

INSERT INTO tblEntries_Deleted EntryNo, EntryDate, EntryType, EntryHeading SELECT EntryNo, EntryDate, EntryType, EntryHeading FROM tblEntries WHERE EntryNo=2
 INSERT INTO tblTransactions_Deleted TransactionID, TransactionDate, EntryNo, TransactionType, AccountNo, TransactionMemo, Category, [Currency], CurDebit, CurCredit, ExchangeRate, Posted, PostingDate, PostedBy, Void, VoidBy, VoidDate  SELECT TransactionID, TransactionDate, EntryNo, TransactionType, AccountNo, TransactionMemo, Category, Currency, CurDebit, CurCredit, ExchangeRate, Posted, PostingDate, PostedBy, Void, VoidBy, VoidDate  FROM tblTransactions  WHERE EntryNo =2
 DELETE EntryNo, EntryDate, EntryType, EntryHeading  FROM tblEntries  WHERE EntryNo= 2
 DELETE TransactionID, TransactionDate, EntryNo, TransactionType, AccountNo, TransactionMemo, Category, Currency, CurDebit, CurCredit, ExchangeRate, Posted, PostingDate, PostedBy, Void, VoidBy, VoidDate  FROM tblTransactions  WHERE EntryNo =2
 UPDATE tblTransactions_Deleted LEFT JOIN tblEntries_Deleted ON tblTransactions_Deleted.EntryNo = tblEntries_Deleted.EntryNo SET tblTransactions_Deleted.DateDeleted = Now(), tblEntries_Deleted.DateDeleted = Now()  WHERE EntryNo =2

Gasman · Feb 7, 2019

I am not a professional, enthusiastic amateur at the best. Which is probably why I am more critical of your postings, as I am in the same position, perhaps just a little ahead of you in usage of Access.

So when I offer you some code, sometimes it is because I have created something similar to what you are trying to do, note 'similar', not the same. It is offered to show you how I approached the problem, and might well not be the best way, but it works.

On other occasions I myself need to Google to get the correct syntax, or a way to do what you are asking. In fact that is how a lot of my code is obtained, snippets here and there and then amended to suit, with a lot of help here.

If I can do it, I see no reason why you cannot.? You are after all being quite adventurous in your projects.?

Re your current query, I would post each of those generated sql statements into the QBE SQL window and then switch to the design window, or a see what it complains about.

I would also try each in turn and not throw them all together and hope for the best. You were, quite a while back, shown the the SQL2VBA utility to assist in coding statements like these.

My SQL is certainly not up to identifying what you have got wrong in that last code window.

I have also learnt to use F8 to step through the code and set breakpoints to inspect variables as well as copious use of Debug.Print.

Carrying out these steps will allow you to become more proficient, rather than getting someone else to do the work all the time. You will learn very little with that approach.

That is just my 5 cents.

Minty · Feb 7, 2019

To add to Gasmans's very good advice - this line won't work;

DoCmd.RunSQL Asql, Bsql, Csql, Dsql, Esql

What you need is

Code:

CurrentDb.Execute Asql 
CurrentDb.Execute Bsql 
CurrentDb.Execute Csql 
CurrentDb.Execute Dsql 
CurrentDb.Execute Esql

However you could avoid all this if you added a field called DateDeleted and simply added that to the header record. Exlcude any records from all your reporting, processing etc where the DateDeleted field is not null.

No need for additional tables, complicated updates and deletes. And if someone "deletes" a record by mistake, you simply remove the deleted date and it magically gets included back into all your systems.

I also thought from another thread you were marking records as VOID - which also seems to make this entire process a bit fruitless ?

mba_110 · Feb 7, 2019

Thank you very much for all your comments and suggestions.

each button has its rule to play DELETE,EDIT,VOID etc and all these are required to give user options in database.

I have to complete this code by any mean, sql2vba is not that much effective in sql this work fine for list box events or select event etc, i did try this sql also but it is giving too many parameters error, I am not a full time access mady nor learned or take any classes for that all this what I have created is on my own and offcourse support of Google guru.

I don't know how this feels but I am not feeling good right now.

Minty · Feb 7, 2019

Firstly although I do this (and a load of other "stuff") for a living, I'm not professionally trained in any way. I have learnt everything through trial , error (lots of errors) , google and reading here. What you have undertaken is not something a beginner should ever start with as a project, but you have your reasons.

Let's assume you want to continue to move these records to another table.

This is a step by step "How to debug your SQL guide"

You are already producing the SQL strings required in the immediate window.

Copy and paste the first of the individual queries into a new blank query designer SQL window. Run it. What error do you get?

If it makes no sense to you even with some thoughts about the error, paste that one query back here with the error code. And we'll see what we can see.

Rinse and repeat, one at a time!

Gasman · Feb 7, 2019

mba_110 said:
i did try this sql also but it is giving too many parameters error,

Well there you go, that is a start.

Count the fields both sides, those being updated, with what is updating them.

Delete query only needs * not field names as you are deleting the record, not individual fields.

Get one query working at a time, then move on to the next.
You might want to use the second parameter as well, to try and keep the data intact on failure.?,
https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/database-execute-method-dao

It might be slower, but will be more beneficial in the end, those are my thoughts on the issues.

isladogs · Feb 7, 2019

Just like Minty, I've never had any training in the use of Access.
I picked it up over many years by trial and error and lots of research.
Where I worked, nobody knew anything about Access.
It was also never my main job but I voluntarily took on a huge whole school task to organise an enrichment week which had 200 staff and 1500 students dependant on me succeeding. There were many times when I regretted doing so and many sleepless nights...but I did succeed and never looked back.

However as I was learning in my own time as well as doing a full time job, it was several years before I began to feel completely confident in Access.

Unlike you I had nobody to warn me that my first project was far too big a task to start with.
In your case, everyone here recommended you should not do your project at all but purchase a ready made application.

You have had a huge amount of help here from various people, not least myself. I stepped back because I felt you were being far too reliant on forum members to do much of the work. That may sound harsh to you but I know I'm not alone in that view

I recommend you put this project to one side until you are more experienced and work on more manageable projects for the immediate future.

Good luck whatever you decide

mba_110 · Feb 8, 2019

There is no question on giving up or keeping aside, I could run a append and update queries but in order to avoid bunch of queries in my project I decided to go with sql, every achievement need time and patience if I where struct like an ice from the beginning then probably it was better to give up, but its not like that in my case I have learned lot through Google and offcourse from you guys I can't through away just like that I have friends back in India who develop the software I could ask them also and it will be done easily, but why I am not doing so because my project will be mine with limited help from others and i do love to take challenges this project is not an required for anything, rather its my personal satisfaction and learning which I love to do.

I am not getting that much time due to my double Jobs in a day otherwise it wouldn't be a matter, I am not that much young now to give separate time for learning and developing the project life is so short now days you have to make efforts rather than waiting for correct time.

MajP · Feb 8, 2019

I never took a course on DBs or even developed any applications for anyone else but myself or coworkers. Do not even work in the industry. I have over 10 thousands post on the internet because I actually read this book cover to cover and then started to answer peoples questions. This is the best 3 dollars you can spend for more advanced Access concepts. Some of it is out of date because of new features, but the concepts and code are still relevant for the most part. VBA has barely changed since the first days of Access Not sure what now a days compares, but you can get good used Access books for a couple of bucks.

https://www.barnesandnoble.com/p/ac...9bet0NrtDdTbVhFukQug_nynoiAn4XfhoC-oAQAvD_BwE

Delete Command Button Event (1 Viewer)

mba_110

Registered User.

Gasman

Enthusiastic Amateur

essaytee

Need a good one-liner.

Gasman

Enthusiastic Amateur

MajP

You've got your good things, and you've got mine.

essaytee

Need a good one-liner.

isladogs

MVP / VIP

mba_110

Registered User.

Gasman

Enthusiastic Amateur

Minty

AWF VIP

mba_110

Registered User.

Minty

AWF VIP

Gasman

Enthusiastic Amateur

isladogs

MVP / VIP

mba_110

Registered User.

MajP

You've got your good things, and you've got mine.

Similar threads

Users who are viewing this thread