Simply way to use the same code in different places? (1 Viewer)

Singh400 · Dec 8, 2008

Simpler way to use the same code in different places?

I find myself using the same piece of code in different places. Just wondering is there a way I can store the code, and call it later?

So for example, I have alot of Date fields on my forms...

And I'm using the following code in txtDateOfBooking

Code:

Private Sub txtDateOfBooking_BeforeUpdate(Cancel As Integer)
 
    If Weekday(Me.txtDateOfBooking, vbSaturday) <= 2 Then
 
        Cancel = (MsgBox("The Booking Date is on a " & _
                         Format(Me.txtDateOfBooking, "dddd") & vbCrLf & vbCrLf & _
                         "Do you want to keep this date?" _
                         , vbYesNo + vbQuestion) = vbNo)
 
        If Cancel Then Me.txtDateOfBooking.Undo
 
    End If
 
    If Me.txtDateOfBooking < Date Then
 
        Cancel = (MsgBox("The Booking Date takes places in the past" & vbCrLf & vbCrLf & _
                        "Do you want to keep this date?" _
                        , vbYesNo + vbQuestion) = vbNo)
 
        If Cancel Then Me.txtDateOfBooking.Undo
 
    End If
End Sub

I have atleast a couple of more txt date fields (like txtDateOfEnquiry & txtDateOfCancel).

Now can I store the above code, and then change the above to

Code:

Private Sub txtDateOfBooking_BeforeUpdate(Cancel As Integer)
Call Date_Check_Fun
End Sub

Code:

Private Sub txtDateOfEnquiry_BeforeUpdate(Cancel As Integer)
Call Date_Check_Fun
End Sub

Code:

Private Sub txtDateOfCancel_BeforeUpdate(Cancel As Integer)
Call Date_Check_Fun
End Sub

I'd find it "cleaner" and less "bloated" to use this method. It's also alot easier to manage, if I want to tweak/change code.

Hopefully, you know what I mean, and I'm not talking gibberish.

Cheers.

jal · Dec 8, 2008

Move the code to a sub or function and then pass the textbox in question to the sub or function. For instance you can pass in the value Cancel ByRef to the sub so that it returns with the correct value.

Singh400 · Dec 8, 2008

Sorry, I don't know what you mean. Could you please expand?

jal · Dec 8, 2008

Something like this, maybe

Private sub Process(ByRef txt as Textbox,ByRef Cancel as Integer)
If Weekday(txt, vbSaturday) <= 2 Then
Cancel = (MsgBox("The Booking Date is on a " & _
Format(txt, "dddd") & vbCrLf & vbCrLf & _
"Do you want to keep this date?" _
, vbYesNo + vbQuestion) = vbNo)

If Cancel Then txt.Undo
End If

If txt < Date Then

Cancel = (MsgBox("The Booking Date takes places in the past" & vbCrLf & vbCrLf & _
"Do you want to keep this date?" _
, vbYesNo + vbQuestion) = vbNo)

If Cancel Then txt.Undo

End If
end sub

Singh400 · Dec 9, 2008

Yeah that doesn't help. Where do I put this code and how do I apply to 3-4 date/time fields at once(txtDateOfBooking, txtDateOfEnquiry and txtDateOfCancel). Instead of having to copy and paste the code several times and change it accordingly?

jal · Dec 9, 2008

Put the code anywhere you want, for example after this sub:

Private Sub txtDateOfBooking_BeforeUpdate(Cancel As Integer)
Call Process (txtDateOfBooking, Cancel) <---- how to call the code
End Sub

Sorry I don't have time for a full tutorial on VBA. I was just trying to point you in the right direction. Also, I don't know all the specifics of your deal. In some cases code can be condensed to one sub called multiple times, in other cases you will need multiple subs - I don't really know your deal, but it looks doable, at first glance anyway.

MSAccessRookie · Dec 9, 2008

JAL is on the right track, but the proposed resolution might not work in every case. If you need to have a common Subroutine or Function, then set it up in a Common Module that is not related to any existing Form. In the database window, you can move to the Modules Section and either select an existing Module to put the code in, or create a new Module.

Once the Code is in a Separate Module, it becomes a Public item (and therefore "Private Sub" should become "Public Sub", etc). A Public Sub should be very close to what you are looking for, and can be accessed from any Module or Form in the project in the same way that you access other Subs. The same applies for Functions

Singh400 · Dec 9, 2008

jal said:
Put the code anywhere you want, for example after this sub:

Private Sub txtDateOfBooking_BeforeUpdate(Cancel As Integer)
Call Process (txtDateOfBooking, Cancel) <---- how to call the code
End Sub

Sorry I don't have time for a full tutorial on VBA. I was just trying to point you in the right direction. Also, I don't know all the specifics of your deal. In some cases code can be condensed to one sub called multiple times, in other cases you will need multiple subs - I don't really know your deal, but it looks doable, at first glance anyway.

Ahhh that makes alot more sense. Will Access automatically change the respective enteries in the code for each individual field?

@MSAccessRookie: Thank you for the information!

jal · Dec 9, 2008

Singh400 said:
Will Access automatically change the respective enteries in the code for each individual field?

@MSAccessRookie: Thank you for the information!

The code will apply to whatever textbox that you passed in during the call. For instance:

txt.Undo

will apply to the textbox currently passed in.

Not sure if that was your question.

jal · Dec 9, 2008

MSAccessRookie said:
JAL is on the right track, but the proposed resolution might not work in every case. If you need to have a common Subroutine or Function, then set it up in a Common Module that is not related to any existing Form. In the database window, you can move to the Modules Section and either select an existing Module to put the code in, or create a new Module.

Once the Code is in a Separate Module, it becomes a Public item (and therefore "Private Sub" should become "Public Sub", etc). A Public Sub should be very close to what you are looking for, and can be accessed from any Module or Form in the project in the same way that you access other Subs. The same applies for Functions

You are right of course. The trouble with me is that I try to do all my projects using only a single form (typically with a tab control). This allows me to put all my code in a single form - no pressing need for a module in my case.

MSAccessRookie · Dec 9, 2008

jal said:
You are right of course. The trouble with me is that I try to do all my projects using only a single form (typically with a tab control). This allows me to put all my code in a single form - no pressing need for a module in my case.

No worries here. In your case, your handling of it would be what you need. Being out of school for a few years (nearly 30 by now), I do not usually see such a small project. In fact, most my smaller projects have had 20-40 Forms to contend with. I just finished up one with 70+ forms and am starting a new one that has 35 Forms so far (and we are just starting). I guess that is one difference between school and business.

Regardless, the advice was exactly correct. I was just offering an alternative in the event that Singh400 could use the code in more than one form.

Simply way to use the same code in different places? (1 Viewer)

Singh400

Registered User.

jal

Registered User.

Singh400

Registered User.

jal

Registered User.

Singh400

Registered User.

jal

Registered User.

MSAccessRookie

AWF VIP

Singh400

Registered User.

jal

Registered User.

jal

Registered User.

MSAccessRookie

AWF VIP

Similar threads

Users who are viewing this thread