Calling a Module

HI , I have the code in AFTERDELCONFIRM of the form and the same code goes toe each and every form i have in the AFTERDELCONFIRM event. So instead of pasting the code in each and every form.

Can i create a single module which has this code and can i call that module in the AFTERDELCONFIRM event of every form. Please Help me as soon as possible.
vihaanAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
1. In the code I posted, there was an error here:
RecordPartDeletes_Error:
   msgbox "Unexpected error " & err.number & " - " & err.desc
   RecordPartToBeDeleted = True
   Resume RecordPartDeletes_Exit
I cut and pasted from the other procedure and didn't change one line.  It should be this:
RecordPartDeletes_Error:
   msgbox "Unexpected error " & err.number & " - " & err.desc
   RecordPartDeletes = True
   Resume RecordPartDeletes_Exit
2. Is that you can put multiple procedures in a single module, so no need for mod_Delete2.  I moved everything into mod_Delete and deleted mod_Delete2
3. When your working in code, always do a compile after making changes.  This will help catch mistakes.  This is how I found the error I made as I compiled as soon as I opened your sample DB.  I also found an error in the code you had in the form from_XYZ.
4. On your forms, the main problem is that you were still calling the code in the form itself.  If you look at the property sheet and look at the OnDelete and AfterDelConfirm properties, they still read "[Event Procedure]"
  What you want them to read is:

OnDelete: =RecordPartToBeDeleted([Form])
AfterDelConfirm: =RecordPartDeletes([Form])
5. You did not create the table tblPartsDeleted as I indicated.
6. In the code, you were getting the value of the Part_ID from  'frm_part_id' .  The field on the XYZ form is part_xyz_id and the control name is frm_id.  You needed to use one or the other.  I used the control.

7. If your recording the deletion of the records in the subform, you really want the part_number field, which is the primary key of that table, not part_id.  
8. You did not create the queries I indicated.
  I've done all this and changed the design of tbl_delete and uploaded a working sample database.  You now have a working example of recording record deletions.
JimD.

sample.zip
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Two ways to do this:
1. In the property sheet, call a function  ie.

  =MyAfterDeleteConfirm([Form])
You can easily mass update the event property for forms/reports with some code.
2. Write custom classes to sink the events:
ACC2000: How to Sink Form and Report Events to Custom Class Modules
http://support.microsoft.com/kb/234907
JimD.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Put his in a vba Module:

Public Function mAfterDelConfirm()

 ' you code here

End Function

Then, on the property sheet of each form ... in the After Del Confirm event put this:

=mAfterDelConfirm()

mx
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
FYI, here's a screen shot of what the first option looks like and what a procedure might look like.
JimD.

Function StdFormCt(frm As Form, strParam As String, strParam2 As String) As Integer

  Dim rstForm As Recordset
  Dim rstFormClone As Recordset
  Dim strBM As String
  Dim UserName As String
  Dim MachineName As String

  Dim var As Variant        'Hold temp values
  Dim intRet As Integer        'Hold intReturn values
  Dim strFormTitle As String
  Dim varOpenArgs As Variant
  Dim intPage As Integer
  Dim strSQL As String

  Dim varControl As Variant
  
  ' Note rev 2.0 and up for A97 and up only
  Const Routine = "StdFromCT"
  Const Version = "2.0.0"

  If (fDebug) Then
    Debug.Print frm.FormName, strParam, strParam2
    Debug.Print "Form Mode  : "; gstrFormMode
    Debug.Print "Reset flag : "; gintResetForm
    Debug.Print "Requery Req: "; gintRequeryRequired
    Debug.Print "Switch to E: "; gintSwitchToEdit
    Debug.Print "Cont Add   : "; gintContinueAdd
    Debug.Print " "
  Else
    On Error GoTo StdFormCt_UnexpectedError
  End If

  StdFormCt = False

Select Case (strParam)
    ' Note that we assume that the following is true about the form:
    '    Has a lock property of Edited Records.
    '    All controls are locked.
    
    Case "OnOpenForm"

.....and continue

Open in new window

Region-Capture.jpg
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
And here's some ROUGH code you can use to add the call to the procedure in your forms.
JimD.

Function DoAllForms() As Integer

    ' This function loops through all Forms
    
    Dim db As Database
    Dim MyDocument As Document
    Dim i
    Dim intRet As Integer

    Set db = DBEngine.Workspaces(0).Databases(0)
    
    For i = 0 To db.Containers("Forms").Documents.Count - 1
        
        Set MyDocument = db.Containers("Forms").Documents(i)
        Debug.Print " > Document: "; MyDocument.Name;
        Debug.Print ""
        DoCmd.OpenForm (MyDocument.Name), A_DESIGN
        
        Forms(MyDocument.Name).MaxButton = False
        Forms(MyDocument.Name).HelpFile = "HRAPP.HLP"
        Forms(MyDocument.Name).HelpContextId = 99999
        
        'intRet = glrChangePermission(1, MyDocument.name, "USERS", DB_SEC_FRMRPT_EXECUTE, True)

        'If Mid$(NZ(Forms(MyDocument.name).OnClose, ""), 1, 4) = "=Std" Then
        '  Forms(MyDocument.name).OnActivate = "=StdFormCT([Form]," & Chr$(34) & "OnActivateForm" & Chr$(34) & "," & Chr$(34) & Chr$(34) & ")"
        '  Forms(MyDocument.name).OnDeactivate = "=StdFormCT([Form]," & Chr$(34) & "OnDeactivateForm" & Chr$(34) & "," & Chr$(34) & Chr$(34) & ")"
        'End If
        
        DoCmd.SetWarnings False                           'save with new settings without
        DoCmd.Close A_FORM, (MyDocument.Name)
        DoCmd.DoMenuItem 7, A_FILE, 2, , A_MENU_VER20     'sending warnings to the screen
        
    Next i

    DoAllForms = True

End Function

Open in new window

0
 
vihaanAuthor Commented:
Hi JDettman, thanks for replying but can you explain with the code  ( first point in u'r reply = MYAFTERDELCONFIRM) explain with the code please.

My code in the AFTERDELCONFIRM event is

Private Sub Form_AfterDelConfirm(status As Integer)
If Status = 0 Then

Dim strsql As String

strsql = " Insert into tbl_delete(part_table, part_del,part_update_user_id,part_update_date) values ('" & Me.RecordSource & "','" & var_part_del& "',CurrentUser(), Date() )"

DoCmd.RunSQL strsql

End If

end sub .
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Hi JDettman, thanks for replying but can you explain with the code  ( first point in u'r reply = MYAFTERDELCONFIRM) explain with the code please. >>
See what I just posted.  Your code would go in a module, which you'd then call from the event property (by modifying the property sheet manually or using the code in the last comment I posted).
If you need more information, just let me know.
JimD.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Also, if your trying to track changes, it might be worth looking at this:
Maintain a history of changes
http://www.mvps.org/access/modules/mdl0021.htm
Which is a generic approach to tracking changes in a database.  
and here's one other example of that:
http://allenbrowne.com/AppAudit.html
JimD.
0
 
vihaanAuthor Commented:
this is what i tried to do but it is not working

Public Function mod_delete()

If status = 0 Then

DoCmd.SetWarnings False

Dim strsql As String

strsql = " Insert into tbl_delete(part_table, part_del,part_update_user_id,part_update_date) values ('" & Me.RecordSource & "','" & var_part_del& "',CurrentUser(), Date() )"

DoCmd.RunSQL strsql

End If

DoCmd.SetWarnings True

End Function


and in event property of the AFTERDELCONFIRM i tried  =mod_delete() but it faield.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<and in event property of the AFTERDELCONFIRM i tried  =mod_delete() but it faield.>>
1.  Make sure you have the code in a general module
2. You need to pass in the values of the things your going to use in the procedure, or you need to pass in a reference to the form itself.  I would pass in a form reference. For example:
Event procedure would be:
 =mod_delete([Form])
and the code for the procedure would be as shown except for var_part_del    You either can:
1. Pass the variable into the procedure
2. Create a hidden control on the form and store it there.  Then reference the control in the procedure.
JimD.

 Public Function mod_delete(frm As Form)

 Dim strsql As String

If frm.Status = 0 Then
   DoCmd.SetWarnings False
   strsql = " Insert into tbl_delete(part_table, part_del,part_update_user_id,part_update_date) values ('" & frm.RecordSource & "','" & var_part_del& "',CurrentUser(), Date() )"
   DoCmd.RunSQL strsql
   DoCmd.SetWarnings True
End If

End Function

Open in new window

0
 
vihaanAuthor Commented:
Hi JDettman, Thanks for replying But this is the First time , I am trying to  create a custom module. So even if i ask questions , Please Please Understand.


I am gonna past the whole code i Have .

In the Main form  ( the code )
 
Option Compare Database
Option Explicit
Dim var_part_del As String

AfterDELCONFIRM( code)  ( this code has to be in a separate module and below i should just call that module)

Private Sub Form_AfterDelConfirm(status As Integer)
DoCmd.SetWarnings False

if Status = 0 Then

Dim strsql As String

strsql = " Insert into tbl_delete(part_table, part_del_participant,part_update_user_id,part_update_date) values ('" & Me.RecordSource & "','" & var_part_del & "',CurrentUser(), Date() )"

DoCmd.RunSQL strsql

End If

DoCmd.SetWarnings True

End Sub


ONDELETE event (code)

var_part_del= Me.frm_part_id

SO, I have provided all the code Please walk me through this. Please and thanks for replying .
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<var_part_del= Me.frm_part_id>>
  Are you allowing the user to delete multiple records at one time?  If so, this will not work properly unless you call the procedure from the OnDelete event.
  OnDelete is fired once for each record deleted.  BeforeDelConfirm and AfterDelConfirm occur after OnDelete and they occur only once even if more then one record is being deleted.
 So are you allowing them to delete more then one record at a time?
JimD.
0
 
vihaanAuthor Commented:
JDettman, Yes i am allowing them to delete more than one record at a time.
0
 
vihaanAuthor Commented:
Hi JDettman, I need help . Please Help me .
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<JDettman, Yes i am allowing them to delete more than one record at a time.>>
  Then you have a problem.  The code your writting looks like it wants to record one record for each record deleted.  However the AfterDelConfirm event only occurs once no matter how many records are deleted.  If you move this code to the OnDelete event, that's too early, as the user still has the chance of canceling the delete, in which case you would not want to write the records.  You have two options:
1. Restrict hte user to deleting one record at a time.
2. In the OnDelete event, write each part being deleted to a temporary table, which you would clear (or create) before doing so  Then in the AfterDelConfirm(), checking that the Status variable indicates that the delete actually occured (AfterDelConfirm fires even if the deletion is canceled), read the temp table and write your records out to tbl_delete.  This part would be simple as you could use a simple append query.
JimD.
0
 
vihaanAuthor Commented:
Hi JDettman, I am making them to delete one record at a time , I do now know how to pass the variable into procedure because as you see the code i have provided above i have declared the var_part_del at module level. Bascially, the point is the when i copied the code and pasted it ina module and provided its name in the event propert of a AFTERDELCONFIRM.event.

the error i am getting is that. " the expression  you entered as a function name that microsoft access can't  find"
And i have AFTERDELCONFIRM event property = mod_delete(Form)

I did not changed anything in your code.copied and pasted it in a module. Please Help me .
0
 
vihaanAuthor Commented:
Hi JDETTMAN,

 You said that in case of deleting multiple records at a time , u told me to save those records in a temporary table in the ONDELETE event . and read that table paste the records in the delete table in AFTERDELCONFIRM event of the form.

But i need to have a criteria like  temporary table contains deleted records as well as records that are not deleted. So i need a criteria in the AFTERDELCONFIRM event where i need to read and save on;y deleted records from the temporary table to the Delete table. Please Help me . Please.
0
 
vihaanAuthor Commented:
Help me GURUS, SAVANTS, EXPERTSSSSSSSSSSSS. JDettman
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<But i need to have a criteria like  temporary table contains deleted records as well as records that are not deleted. So i need a criteria in the AFTERDELCONFIRM event where i need to read and save on;y deleted records from the temporary table to the Delete table. Please Help me . Please.>>
  No, the temp table would have only the records that were being deleted and it would need nothing more then the part ID field.
  Create a new table and call it tblPartsDeleted.  Include a single field, PartID.

 now in your form, for the OnDelete method, call RecordPartToBeDeleted:
   =RecordPartToBeDeleted([Form])
Public Function  RecordPartToBeDeleted(frm as Form) as Boolean

  Dim strSQL as string

  On Error Goto RecordPartToBeDeleted_Error

   RecordPartToBeDeleted = True
   strSQL = " Insert into tblPartsDeleted (PartID) values ('" & frm.frm_part_id & "')"
   CurrentDB.Execute strSQL, dbFailOnError

RecordPartToBeDeleted_Exit:

   Exit Function

RecordPartToBeDeleted_Error:
    msgbox "Unexpected error " & err.number & " - " & err.desc
    RecordPartToBeDeleted = True
    Resume RecordPartToBeDeleted_Exit

End Function

 now in your form, for the AfterDelConfirm method, call RecordPartDeletes:


  =RecordPartDeletes([Form])

Public Function RecordPartDeletes(frm as Form) as Boolean

  On Error Goto RecordPartDeletes_Error

   RecordPartDeletes = True
   CurrentDB.Execute qryRecordDeletions, dbFailOnError
   CurrentDB.Execute qryClearDeleteTable, dbFailOnError

RecordPartDeletes_Exit:

   Exit Function

RecordPartDeletes_Error:
    msgbox "Unexpected error " & err.number & " - " & err.desc
    RecordPartToBeDeleted = True
    Resume RecordPartDeletes_Exit

End Function

  qryRecordDeletions is a seperate append query which you can write to look at tblPartsDeleted and append records into your tbl_delete.
  qryClearDeleteTable is a query that deletes all records from tblPartsDeleted.  This should also be done when the form first opens.
JimD.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Help me GURUS, SAVANTS, EXPERTSSSSSSSSSSSS. JDettman>>
 Sorry for the delay, but please understand that Experts are volunteers; this is not a job for any of us.  I happen to be very busy right now and am getting back to your question as fast as I can.
 It also does not help that your question is changing.  You asked how you could run the same code from multiple forms, which I answered.  Now, I'm helping you actually write the code to do what you want, which is something totally different then what you asked originally.
If I had known that it was going to turn into this, I would not have commented in the first place.  I thought though it was going to be a simple question.
JimD.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Also, if you do not feel that your getting help fast enough on a question, you can click the "request attention" button at the top.  A moderator may then ask for additional help.
JimD.
0
 
vihaanAuthor Commented:
I am sorry JDettman, if you felt that i am pushing you. I am really sorry. that was not my intention at all. Anyways, I will get back to you.  you can respond to my questions( if i have any after implementing the above code), respond  whenever you find time . thank you.
0
 
vihaanAuthor Commented:
HI JDettman,  whenever you find time, no rush, But please do reply because you understood the concept, what iam trying to do here. I will be very grateful , if you help me out i know that you are trying and going out of your way to help me out. But i do not why, but this is getting tricky. thank you.

I encountering the same problem which is

The expression OnDelete you entered as the event property setting produced the following error : the expression you entered has a function name that microsoft access can't find

* the expression may not result in the name of  a macro, the name of a user- defined function, or [Event Procedure]
* there may have been an error evaluating the function , event or macro
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
It is not a problem for me, I just want to make sure you understood what the situation was.  Many mis-understand what EE is all about and think that the Experts are paid by EE and sit there waiting for questions.  Instead it is simply people helping other people out.
<<The expression OnDelete you entered as the event property setting produced the following error : the expression you entered has a function name that microsoft access can't find>>
Make sure that you have:
A. Pasted the two functions into a general module.
B. You have spelled the function names correctly in the property sheet.
  If you still can't get this to work after checking that, then I would like you make a small sample DB (if possible) with just this form and the module with the code and post it to the site or e-mail it to me if it's confidential.
I leave Sunday for a one week trip and would like to have this finished up with you before the weekend.
JimD.
0
 
vihaanAuthor Commented:
general module is nothing but creating a module under the modules section in the switchboard manager . thats what i did. yes i did spelled the names correctly. and i will mail the database to you.

One more thing is that does this code work for single record deletion too. because i have continous forms as well as single forms. But I should have everything separate for continous as well as single forms then i will do that. No pressure. Thank You I will send the database  like in 2 hours. COuld you back in 2 hours.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Yes, I will still be here in 2 hours, but it is nearing the end of my day.
JimD.
0
 
vihaanAuthor Commented:
Hi JDettman,.  I mailed the database to your email id . Thanks for helping me out.
0
 
vihaanAuthor Commented:
Hi JDettman, Let me know whether the database is fine for you to implement the code. Thank You.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
" I mailed the database to your email id . Thanks for helping me out. "
That violates EE rules.  You need to upload here using the File >> Attach below so that any Expert can examine the database.

mx
0
 
vihaanAuthor Commented:
please use  the attached sample database
sample.zip
0
 
vihaanAuthor Commented:
Hi JDettman, Before you work on the database. I restricted users to delete only one record at a time even on the continous forms. I am just letting you know so that you do not have to work more on that. It brings us back to the same point. I mean there will be only one general module having the AFterDeLCOnfirm event code in it. And i will be calling that function in the event property of the AFTERDELCONFIRM event. But i get the same error which is


The expression OnDelete you entered as the event property setting produced the following error : the expression you entered has a function name that microsoft access can't find

* the expression may not result in the name of  a macro, the name of a user- defined function, or [Event Procedure]
* there may have been an error evaluating the function , event or macro
0
 
vihaanAuthor Commented:
Hi JDettman, Discard , avoid my last comment.
0
 
vihaanAuthor Commented:
Now I have been asked to capture the multiple records which are gonna delete in a temporary field but not in a physical table through vba . I dohuld o this in a loop and save the records in a temporary working storage . I do not know how to do that. ANyone who would like to help , please help me.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Now I have been asked to capture the multiple records which are gonna delete in a temporary field but not in a physical table through vba . I dohuld o this in a loop and save the records in a temporary working storage . I do not know how to do that. ANyone who would like to help , please help me.>>
 Ask another question.  This one is done.
JimD.
0
 
vihaanAuthor Commented:
No JDettman,

I  have just been told that i need to capture the multiple records which are going to get deleted in  temporary variable or temporary storage in vba ( but not in  a physical table) . I need to achieve this thrugh  aloop, i think . I have no idea how to do that. I am stuck .
0
 
vihaanAuthor Commented:
you provided that solution but we created a physical table for that to achieve. Now i have to do that without creating it.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<No JDettman,>>
  Yes, this question is finsihed.  Please click the "ask a question" to start a new question.
  That's the way the site works; you ask one question and get an answer and aceept a solution for your question.
  If you have another question, then you start a new question.  You do not continue to ask new questions in the same thread.
  This thread covers two questions already and has gone far enough.
JimD.
0
 
vihaanAuthor Commented:
First of all i am sor sorry for frustrating you ( i think ) but the solution you provided is working awesome . But again i have to do it the same thing but not creating another physical table . I created a thread for it  , If you have patience , help me out. I really reallyyyyyyyyyyyy appreciate for helping me out . you know. thanks .


http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_25889298.html?fromWizard=true
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.