Deleting records from a table thru a form button event

[edson]

Dear fellows, good afternoon. Please, I need help on this one:

I have a FrmCadastro which updates my master table TblCadastro. Primary key of the TblCadastro is an auto numbered field named CADID.
Fields CPF, NAME, FIN, NAT, TIP, MOT, and INSTREQ of the FrmCadastro are mandatory. My rule is that If any of these fields is null then the record cannot be saved (cannot be added into TblCadastro).
FrmCadastro has two distinct buttons, as follows: BtSave (to save either newly included record or changed record) and BtDelete (to delete a chosen record from my master table).
To delete a record has not been a problem because under the click event of the BtDelete I use the following command lines to successfully eliminate an existing record (previously saved) from the TblCadastro:
Dim numRecord As Integer
numRecord = Me.CADID


Dim SQL As String
DoCmd.SetWarnings False
SQL = "DELETE * FROM tblcadastro WHERE cadid = " & numRecord
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

To save a complete record (properly filled in) has not been a problem either since the code takes care of that very well.

Here is what I am struggling with:
When the user is in the process of adding a new record through the FrmCadastro two different user behaviors can take place:
1. Irregular entering - The user for some reason just give up saving the record (he decides to add the new record later, for instance) by selecting the option Save = No, or
2. Incomplete record - By mistake the user try to save an incomplete record (a record that shows any mandatory field as null).

I coded the following command lines under the click event of the BtSave to take care of deleting those records... BUT it does NOT work. The system reads each line of the code, find the record properly, close the form as expected, but the incomplete record STILL REMAINS in the TblCadastro. I do not understand why, since the code is pretty much the same of the one in the BtDelete.

Private Sub bt_save_Click()
If MsgBox("The record was changed. Do you want to save it?", vbQuestion + vbYesNo, Me.Caption) = vbYes Then
    If MsgBox("WARNING! Incomplete record will be automatically deleted from database. If you did not fill in the fields CPF, NAME, FIN, NAT, TIP, MOT, and INSTREQ, the record will not be saved.", vbOKOnly, Me.Caption) Then
        If Not IsNull(CPF) And Not IsNull(Name) And Not IsNull(FIN) And Not IsNull(NAT) And Not IsNull(TIP) And Not IsNull(MOT) And Not IsNull(INSTREQ) Then
            DoCmd.RunCommand acCmdSaveRecord
            DoCmd.RunCommand acCmdRefresh
            Me.LstTotAtendDia.Requery
            MsgBox ("The record of the person:" & Me.Name & vbCrLf & "was successfully saved !"), vbOKOnly, Me.Caption
            DoCmd.Close
            Exit Sub
        Else
            GoTo CLEANING
        End If
CLEANING:
        Dim numRecord As Integer
        numRecord = Me.CADID
        Dim SQL As String
        DoCmd.SetWarnings False
        SQL = "DELETE * FROM tblcadastro WHERE cadid = " & numRecord
        DoCmd.RunSQL SQL
        MsgBox ("The record was not saved!"), vbOKOnly, Me.Caption
        DoCmd.SetWarnings True
        DoCmd.Close
    End If
Else
    GoTo CLEANING
End If
End Sub

I also tried to use:
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
It did not work out either.
Please, what is wrong ? What should I code to exclude both the incomplete record and irregular entering thru the button Save (options Yes and No) ?
Thank you all very much.
Edson

[June7]

Consider:

Private Sub bt_save_Click()
Dim booCancel As Boolean
If Me.Dirty Then
    If IsNull(CPF) Or IsNull(Name) Or IsNull(FIN) Or IsNull(NAT) Or IsNull(TIP) Or IsNull(MOT) Or IsNull(INSTREQ) Then
        If MsgBox("WARNING! One or more required fields (CPF, NAME, FIN, NAT, TIP, MOT, and INSTREQ) not entered." _
                 & "Incomplete record will not be saved. Do you want to complete?", vbYesNo, Me.Caption) = vbNo Then
            booCancel = True
        End If
    ElseIf MsgBox("The record was changed. Do you want to save it?", vbQuestion + vbYesNo, Me.Caption) = vbNo Then
        booCancel = True
    End If
End If
If booCancel = True Then
    SendKeys "{ESC}", True
    SendKeys "{ESC}", True
End If
DoCmd.Close
End Sub

This is the only situation where I have used SendKeys. SendKeys is not supposed to be best practice but I could not get Me.Undo to work in Click event. Only alternative I know of involves a module declared Boolean variable and form BeforeUpdate event in conjunction with the button Click.

Note that I edited your post to include CODE tags and indentation so code is easier to read.

[thebigthing313]

If you already have the table open when testing the form button, sometimes you have to hit "Refresh" for the table to see the expected changes.

Also, consider replacing your SQL variable with Dim strSQL as String. SQL is a reserved word in Access VBA and can be throwing off the expected outcome.

[edson]

If you already have the table open when testing the form button, sometimes you have to hit "Refresh" for the table to see the expected changes.

Also, consider replacing your SQL variable with Dim strSQL as String. SQL is a reserved word in Access VBA and can be throwing off the expected outcome.

Thank you for your suggestions. I did change the name of my SQL variable. Also I run the system as the final user having tables, queries, etc. refreshed. The problem is still there, meaning non saved items or incomplete items remains in the table.

[edson]

Thank you for your effort June7. I copied and pasted your code into my Project and the problem is still there, meaning both non saved and incomplete records remain in the TblCadastro.
I am sorry but I am still lost in the dark: As a novice I did not understand what you mean by "a module declared Boolean variable and form BeforeUpdate event in conjunction with the button Click.". I did some internet research but was not able to figure out what you meant. Is there any additional code that I must include in the form before update event? Any additional module to include in my code?

[June7]

Is this a form/subform arrangement?

If you want to provide db for anlaysis, follow instructions at bottom of my post.

[edson]

Hello June7. I have attached a portion of my db. I had to decrease it in size to fit 2Mb however it is still functional for the purpose of my thread.
Please use FrmBuscaNome (name searching) to access FrmCadastro. Just type in an * (wildcard) to access all records of the data base. The form FrmMostraNome will popup. To add a new record click on ADICIONAR AVALIADO button, or click in the list to inquire a particular record.
Please keep in mind there are mandatory fields as per my thread.
For test purpose CPF field can be populated with 12345678909 which is valid CPF number.
To save a record use the SALVAR INCLUSÃO button. Please, try to save (yes) an incomplete record and also try to save (no). You will see that in both cases the record remains in the TblCadastro.
Use the button EXCLUIR to delete a record. Password will be required. Type in "personas". Remember: Deleting works fine.
You can also inquire a record by typing the registry ordinary key number (CADID) in the form FrmBuscaID.

Hope this is enough for your analysis.
I'm looking forward to have a solution for my thread revealed.

Thank you once more and have a blessed day.
Edson

[June7]

When I first open db, get an error about broken reference to file MSCAL.OCX. I okay through that and get a login form. So I quit and reopened with shift key bypass.

Same error when I open frmBuscaNome.

Why such tiny font on buttons?

I used the * but the listbox on frmMostraNome is empty.

I click Adc Avaliado and get error "Cannot find project or library" and debugger stops on Time(). Maybe because of missing MSCAL.OCX. But now the debugger errors on Date.

This is a very complicated db. The language barrier doesn't help.

Sorry, too many issues to deal with.

[edson]

Understand. I'll do my best to fix it and still keep it under 2Mb. Then I will send it to you again IF that is OK for you. Just let me know, please.
Thank you for your interest so far.

[June7]

I fixed the MSCAL.OCX error - just had to unselect library reference. Now I get errors when forms try to load images I don't have. Commented out the lines. Closing forms wants to go back to login form which I never opened because of shift key bypass.

What should I use for a login? I tried admin/silvio00. Then I get an input popup for LOGIN!USER (why?) so I enter admin again. Menu Principal form opens. I select Nome Buscar and this looks like where need to be. Now the listbox has records. I click the first one and frmCadastro opens but Salvar Inclusio button is not available. How do I quit this form?

Now I click Adc Avaliado button to open frmCadastro and Salvar Inclusio button is available.

Why does frmCadastro have a RecordSource that is a query with an INNER JOIN? What is tblNumLdAFG for?

This is really involved. I found out this much. Code is setting value of QTEDIAS field when frmCadastro opens to new record. This initiates the record but even with this commented out and only user input to form, SendKeys is still not working. I confirmed that manually hitting ESC key does reverse the code setting of QTEDIAS and user input and record does not commit. I don't know why the SendKeys won't work. It always has for me. So back to looking at your original code.

Keep getting that annoying LOGIN!USER popup when close frmCadastro.

Think I forgot something in my code (which is moot since the SendKeys won't work):

        If MsgBox("WARNING! One or more required fields (CPF, NAME, FIN, NAT, TIP, MOT, and INSTREQ) not entered." _
                 & "Incomplete record will not be saved. Do you want to complete?", vbYesNo, Me.Caption) = vbNo Then
            booCancel = True
        Else
            Exit Sub
        End If

This code works to not save new record if it is incomplete:

If Me.Dirty Then
    If IsNull(CPF) Or IsNull(Name) Or IsNull(FIN) Or IsNull(NAT) Or IsNull(TIP) Or IsNull(MOT) Or IsNull(INSTREQ) Then
        If MsgBox("WARNING! One or more required fields (CPF, NAME, FIN, NAT, TIP, MOT, and INSTREQ) not entered." _
                 & "Incomplete record will not be saved. Do you want to complete?", vbYesNo, Me.Caption) = vbNo Then
            DoCmd.RunCommand acCmdSaveRecord
            CurrentDb.Execute "DELETE FROM tblCadastro WHERE CADID=" & Me.CADID
        Else
            Exit Sub
        End If
    ElseIf MsgBox("The record was changed. Do you want to save it?", vbQuestion + vbYesNo, Me.Caption) = vbNo Then
        'trying to undo edits to existing record but this button is not available for existing record so cannot test
        SendKeys "{ESC}", True
        SendKeys "{ESC}", True
    End If
End If
DoCmd.Close

My next step would be to use BeforeUpdate event but you have custom function call in that event property. I would change the Function to a Sub (because it does not return a value to the calling procedure) and call the Sub in BeforeUpdate VBA event procedure. That way can have other code in the BeforeUpdate event as well.

Forms opened in popup or modal state really get in the way of debugging. I rarely use those and only after everything works properly. I prefer Overlapping Windows and never use Tabbed Documents.

I would not save SEXID for the gender - I would delete that field and just save M/F.

[edson]

Good morning June7. Yes! My database has a lot of functions built in. I feel mixed emotions with it. First, I believe it could/should be optimized... but I have no knowledge enough YET to get thru this. Second, I feel myself kind of proud because this is my very first db. I had never used VBA or other similar programming language before. Started programming 6 months ago and had my first remote begginer lessons from a friend of mine.
To run the system portion I sent to you, just ignore the error messages by clicking cancel to them. Buttons will be enabled/disabled depending on the action you are performing. Ex. A dummy enduser should not have any other button available to click by mistake but "save" when entering a new record. I had to cut-off several functions to fit 2Mb in size, otherwise I could not send it to you. However, db should work fine for the purpose of my thread (as per my previous test). Yes, you do not have photo library since I deleted it (confidential) and it is just a matter to ignore the error message.

Now let me give you a good news: I found my way around it without using SendKeys. Amaizingly, all I had to do was to create a button called Undo by using the "Undo" Access built-in macro (available in the wizard when you create a new button). I dont know yet what its written-in code is... but it works fine. So now, the enduser will have only this button enabled when entering a new record. If he types everything correctly, then he will have Save button enabled VbOkOnly. It worked as a charm.

I will also consider your last suggestion (exit sub).
I would like to thank you once more for helping me facing such challenge.
Any way, I hope you let me go back to you in case of further problems.

By the way, I'm just curious: In what country are you located at? I'm from the city or Piracicaba, São Paulo state, in Brazil.
Hugs and have a blessed day.

Edson

[June7]

I am in U.S. I am interested in the UNDO solution. I have never been able to get this to work in VBA and I never use macros, hence resorting to SendKeys. Glad you have a solution.

[edson]

Yes... I am too. Is there a way to find out what are the built-in vba coding lines behind the scene when we use access macros wizard? I did some research in Microsoft sites but was not able to find anything to clarify me. If I find something, I'll let you know, OK?

[thebigthing313]

You can use the "Convert Form's Macros to Visual Basic" button in Design View.

[edson]

Hello up there. How are you doing Mr. Thebigthing313?
Where is this button again, please? I can't see it at all. Well, I am a novice... but no blind yet (eh, eh, eh). Looked in Design view, Preferences Options, menu bars config, etc.both in the form and in the coding page. Never found it.
Listen, this could be an opportunity for both (I and June 7) to find out what VBA code is able to perform "undo" effectively, I mean, by using pure VBA command lines with no SendKeys instead of Access built-in macros.
He is interested on that matter too. See above.