UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Making Private Sub Code Into Module Code To Reuse In Sub Forms, Access 2016    
 
   
brastedhouse
post Nov 2 2019, 01:07 PM
Post#1



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


Greetings, I am trying to use a piece of code repeatedly (24 sub forms) and I thought I would save the more than 70 lines of code (and time to copy it in) and reduce it to Call No_Dupes in each sub form. But I cannot make it work. First I do not know if I should use Public Sub or Public Function. Second, either way I get errors.

Here is the code I use now in the 24 sub forms:

CODE
Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3022 Then
      MsgBox "You have tried to enter duplicate information." _
      & vbCrLf & "Please click OK and then tap Escape" _
      & vbCrLf & "and add NEW information."
      Response = 0
End If
  
End Sub


This works fine.

But when I do this:

Module = modNoDupes

CODE
Public Sub No_Dupes

If DataErr = 3022 Then
      MsgBox "You have tried to enter duplicate information." _
      & vbCrLf & "Please click OK and then tap Escape" _
      & vbCrLf & "and add NEW information."
      Response = 0
End If
  
End Sub


I get a variable not defined error.

or

CODE
Private Function No_Dupes

If DataErr = 3022 Then
      MsgBox "You have tried to enter duplicate information." _
      & vbCrLf & "Please click OK and then tap Escape" _
      & vbCrLf & "and add NEW information."
      Response = 0
   End If
  
End Sub

Same thing.

If I put the (DataErr As Integer, Response As Integer) after the No_Dupes(insert) It compiles, but I get an Argument Not Optional compile error in the form code if I use Call No_Dupes.

Here is the form code using No_Dupes:

CODE
Private Sub Form_Error(DataErr As Integer, Response As Integer)

Call No_Dupes
  
End Sub


If I take the DataErr as Integer, Response As Integer out of the form code I get a Procedure Declaration Error about having the same name.

So I am confused. What am I missing her or not understanding. I have read a lot about Public Subs and Functions and quite honestly, most it does not make a lot sense to me.

Many thanks, Scott
This post has been edited by brastedhouse: Nov 2 2019, 01:08 PM
Go to the top of the page
 
cheekybuddha
post Nov 2 2019, 01:38 PM
Post#2


UtterAccess Moderator
Posts: 11,921
Joined: 6-December 03
From: Telegraph Hill


The form's sub (Form_Error()) has two arguments which you use in the original procedure.

So, to wrap the logic in separate code you will need a way to get these values to the new procedure...

... which can be done also by passing them as arguments:
CODE
Public Sub No_Dupes(DataErr As Integer, Response As Integer)

  If DataErr = 3022 Then
    MsgBox "You have tried to enter duplicate information." _
    & vbCrLf & "Please click OK and then tap Escape" _
    & vbCrLf & "and add NEW information."
    Response = 0
  End If
  
End Sub


Then, in the calling code (ie in each Form_Error()) you pass the variables as arguments:
CODE
Private Sub Form_Error(DataErr As Integer, Response As Integer)

  Call No_Dupes(DataErr, Response)
  
End Sub


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
brastedhouse
post Nov 2 2019, 01:43 PM
Post#3



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


Oh, I see. smile.gif

Many thanks.
Go to the top of the page
 
AlbertKallal
post Nov 2 2019, 01:54 PM
Post#4


UtterAccess VIP
Posts: 2,903
Joined: 12-April 07
From: Edmonton, Alberta Canada


Well, you can generally move code out to a standard code module.

The problem in this case is you not just calling an external routine from the form, but it has parameters. It is (unfortunately) an event code stub.

In a form, when you setup a [Event procedure] routine, then the routine that runs will be in the form.

So you could do this:

CODE
Private Sub Form_Error(DataErr As Integer, Response As Integer)

Call No_Dupes(DataErr, Response)

  
End Sub


And then in the standard module, you could have this:

CODE
Public Function No_Dupes(DataErr As Integer, Response As Integer)


If DataErr = 3022 Then
      MsgBox "You have tried to enter duplicate information." _
      & vbCrLf & "Please click OK and then tap Escape" _
      & vbCrLf & "and add NEW information."
      Response = 0
   End If
  
End Sub


Of course this STILL means you MUST have the code stub in the form. So it “saves” a wee bit of coding, but not much.

So when you call a sub, “if” it has parameters then the code stub you create MUST match the parameters exact (both in the number of parameters, and also the data type (in this case both are integers).

However, the code stub STILL has to be in the form, else how would all the other 50+ forms know which routine to call? The one in the form, or the global one you placed in a sub?

Do keep in mind that any event code that DOES NOT have parameters, you can in fact get access to run your event code in a global routine, and you do NOT have to create a code stub in the form. This trick STILL requires you to set the event (say click event) to fire and call such a routine. You can do this by creating a Public function in a standard code module, and then place an expression in the [Event code] property setting like this:

=MyGlobalClickCode()


Now, in a standard global module (not forms), you would have

CODE
Public Function MyGlobalClickCode()

Code goes here

End Function.


The above thus means you don’t have to create a code stub in the form, but you WILL HAVE to enter the expression for any click event (say for a button) as instructed above. So it saves you have to place the code stub in the actual form.

However, in your case? Because the event code ALSO has parameters, then you can’t use the above trick. (But it does work for click() events).

So, you can move out the code as per first example, but you STILL will have to have a code stub in each form. So, you save a few lines of code, and you only have “one place” for your error message, but you not saving a lot of code, but it is the only real option you have here.

You could also I suppose use “with events”, and that allows you to sink a code event to a public global routine, but every form on start-up will require some additional code, so once again, you not save much in the way of coding.

Regards.
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th December 2019 - 10:06 PM