What is the best way?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have been VBA programming for a while now, and i have noticed that i do a
lot of things multiple times over and over again.

for instance, the whole thing of opening a form to find a related record.
the code usually goes like this:

Private Sub lstIssue_DblClick(Cancel As Integer)
On Error GoTo Err_cmdIssueForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmManageIssues"

stLinkCriteria = "[IssueID]=" & Me![lstIssue]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdIssueForm_Click:
Exit Sub

Err_cmdIssueForm_Click:
MsgBox Err.Description
Resume Exit_cmdIssueForm_Click

End Sub

now i am wondering, is there a way to make a routine that would do it for
any button i decide to call it. i think that a routine to do that would be
very complicated because it would have to know the linking criteria for each
form and related for combination. the example i gave has this line:
stLinkCriteria = "[IssueID]=" & Me![lstIssue], but on other forms where i
have a similar button the criteria would be some 2 controls on each form that
are different.

is there any catch all routine that would be able to figure it out for any
form and related forms? and if there was would it be benificial to do it with
a global routine that figured it out and then opened the appropriate forms
with the appropriate link criteria? would this make my database smaller, more
effcient and faster?

second, would it be benificial to globably dim the variables used in this
code? again the same question applies, would it make my database better?
 
Using GLOBAL variables is usually not a good idea. The problem is that they
can be changed in multiple places and if that is so, they may be changed by
one routine and then called by another with unexpected results.

Consolidating code can be a good idea - it means you have only one place to
change the code's behavior. For instance I have a generic routine I use for
adding deleting records that covers about 95% of my needs. I can call this
from a button on any form with
sCmdDelete Me

Public Function sCmdDelete(frmAny As Form, Optional strCaption As String) As
Boolean
'Delete the currently selected record on a form
Dim tfAllowDeletions As Boolean
Dim tfReturn As Boolean

tfReturn = True 'Assume success

On Error GoTo ERROR_sCmdDelete

With frmAny
If strCaption = vbNullString Then
strCaption = .Caption
End If

If strCaption = vbNullString Then
strCaption = .name
End If

If .CurrentRecord > 0 Then 'make sure there is a record

If .NewRecord = True And .Dirty = False Then
Exit Function
End If

If MsgBox("Delete selected " & strCaption & " record?", _
vbYesNo + vbCritical, "Delete") = vbYes Then

If .NewRecord = False Then
tfAllowDeletions = .AllowDeletions
If .AllowDeletions = False Then .AllowDeletions = True
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
.AllowDeletions = tfAllowDeletions
Else
.Undo
End If 'Delete existing records only

End If 'Confirm delete
End If 'Current Record
End With

EXIT_sCmdDelete:
sCmdDelete = tfReturn
DoCmd.SetWarnings True
Exit Function

ERROR_sCmdDelete:
Select Case Err.Number
Case 2501
'action cancelled
Case Else
MsgBox Err.Number & ": " & Err.Description, , "Error:
modButtons.sCmdDelete"
End Select

tfReturn = False

Resume EXIT_sCmdDelete
End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

DawnTreader said:
i have been VBA programming for a while now, and i have noticed that i do a
lot of things multiple times over and over again.

for instance, the whole thing of opening a form to find a related record.
the code usually goes like this:

Private Sub lstIssue_DblClick(Cancel As Integer)
On Error GoTo Err_cmdIssueForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmManageIssues"

stLinkCriteria = "[IssueID]=" & Me![lstIssue]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdIssueForm_Click:
Exit Sub

Err_cmdIssueForm_Click:
MsgBox Err.Description
Resume Exit_cmdIssueForm_Click

End Sub

now i am wondering, is there a way to make a routine that would do it for
any button i decide to call it. i think that a routine to do that would be
very complicated because it would have to know the linking criteria for
each
form and related for combination. the example i gave has this line:
stLinkCriteria = "[IssueID]=" & Me![lstIssue], but on other forms where i
have a similar button the criteria would be some 2 controls on each form
that
are different.

is there any catch all routine that would be able to figure it out for any
form and related forms? and if there was would it be benificial to do it
with
a global routine that figured it out and then opened the appropriate forms
with the appropriate link criteria? would this make my database smaller,
more
effcient and faster?

second, would it be benificial to globably dim the variables used in this
code? again the same question applies, would it make my database better?
Click to expand...
 
Back
Top