Reusable Form & Code

[oldblindpew]

I've been trying to create a dialog form for finding a record and displaying
it to a form. I have several forms that can use this same dialog form. It
was all working fine based on the FindRecord method until I got the bright
idea of doing away with my user-defined alphabetical key field in favor of an
autonumber surrogate key.

With help from this forum, I finally got things working again, and without
resorting to hard-coding any object names. Here is the code:

Option Compare Database 'Use database order for string comparisons.
Option Explicit 'Requires variables be declared before use.

Private Sub cmdOkFindFirm_Click()
'Find record for main form based on selection in dialog form.
On Error GoTo Err_OkFindFirm
Dim lngSelect As Long
Dim rst As DAO.Recordset
Dim frm As Form
Dim strArgs As String

'Check to see if no selection was made.
If IsNull(Me!lstFirm) Then
MsgBox "Make a selection or click Cancel", , "No Selection"
GoTo Exit_OkFindFirm
End If

'Store the selection (firm ID number from list box) in a variable.
lngSelect = Me!lstFirm

'Create recordset clone for the main form's recordset.
'Note: Simply closing the dialog form will not make "Me" refer
'back to the main form. "Me" refers only to the form in which
'the code is running (the dialog form).
'When the dialog form was opened, the name of the main form was
'passed to the dialog form as the OpenArgs argument of the
'DoCmd.OpenForm method. That name now goes into a string variable
'(strArgs),
strArgs = Me.OpenArgs
'so the form can be put in a form variable (frm),
Set frm = Forms(strArgs)
'so the recordset clone can be created.
Set rst = frm.RecordsetClone
'All this seems needlessly complicated, but is apparently necessary.


'Find the selected record in the recordset clone.
rst.FindFirst "[aIDFirm] = " & lngSelect

'Display the selected record in the main form.
frm.Bookmark = rst.Bookmark

Exit_OkFindFirm:
On Error Resume Next
DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name
DoCmd.GoToControl "cmdSearchFirm"
rst.Close
Set rst = Nothing
Exit Sub

Err_OkFindFirm:
MsgBox "Error #: " & Err & Chr(13) & Err.Description
Resume Exit_OkFindFirm

End Sub

My question now is, how do I make this sub available to all my different
forms. I know it has to do with Public vs. Private, but my reference books
don't seem to clearly discuss this major consideration. Thanks.

 

[Tom van Stiphout]

On Tue, 10 Mar 2009 15:14:02 -0700, oldblindpew

Without reading your entire code: the general concept is to move the
procedure to a Standard Module. Then make it Public, and give it a
form variable as an input parameter:
Public Sub cmdOkFindFirm_Click(frmIn as Form)

Then anywhere in your procedure you change "Me" to "frmIn".

Then you can call this from any form:
cmdOkFindFirm_Click(Me)

-Tom.
Microsoft Access MVP

I've been trying to create a dialog form for finding a record and displaying
it to a form. I have several forms that can use this same dialog form. It
was all working fine based on the FindRecord method until I got the bright
idea of doing away with my user-defined alphabetical key field in favor of an
autonumber surrogate key.

With help from this forum, I finally got things working again, and without
resorting to hard-coding any object names. Here is the code:

Option Compare Database 'Use database order for string comparisons.
Option Explicit 'Requires variables be declared before use.

Private Sub cmdOkFindFirm_Click()
'Find record for main form based on selection in dialog form.
On Error GoTo Err_OkFindFirm
Dim lngSelect As Long
Dim rst As DAO.Recordset
Dim frm As Form
Dim strArgs As String

'Check to see if no selection was made.
If IsNull(Me!lstFirm) Then
MsgBox "Make a selection or click Cancel", , "No Selection"
GoTo Exit_OkFindFirm
End If

'Store the selection (firm ID number from list box) in a variable.
lngSelect = Me!lstFirm

'Create recordset clone for the main form's recordset.
'Note: Simply closing the dialog form will not make "Me" refer
'back to the main form. "Me" refers only to the form in which
'the code is running (the dialog form).
'When the dialog form was opened, the name of the main form was
'passed to the dialog form as the OpenArgs argument of the
'DoCmd.OpenForm method. That name now goes into a string variable
'(strArgs),
strArgs = Me.OpenArgs
'so the form can be put in a form variable (frm),
Set frm = Forms(strArgs)
'so the recordset clone can be created.
Set rst = frm.RecordsetClone
'All this seems needlessly complicated, but is apparently necessary.


'Find the selected record in the recordset clone.
rst.FindFirst "[aIDFirm] = " & lngSelect

'Display the selected record in the main form.
frm.Bookmark = rst.Bookmark

Exit_OkFindFirm:
On Error Resume Next
DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name
DoCmd.GoToControl "cmdSearchFirm"
rst.Close
Set rst = Nothing
Exit Sub

Err_OkFindFirm:
MsgBox "Error #: " & Err & Chr(13) & Err.Description
Resume Exit_OkFindFirm

End Sub

My question now is, how do I make this sub available to all my different
forms. I know it has to do with Public vs. Private, but my reference books
don't seem to clearly discuss this major consideration. Thanks.

 

[oldblindpew]

Hi Tom, and thanks for replying.

Looks like I win the Dumb Question award. The code is in the dialog form.
I can freely open this same dialog form from any suitable main form that
needs a Firm to be picked. The dialog form is intrinsically reusable, duh!

I'm about to decide, however, that if I can merely reconcile myself to
displaying the autonumber key on my main form, I can use FindRecord to find
and display the desired record. This would do away with all the contortions
of OpenArgs, RecordsetClone, FindFirst, and Bookmark. Any thoughts/warnings
on this?

If only one had the option of specifying the field to be searched by
FindRecord, rather than having to depend on the field being represented on
the form, visible, and with focus.

Thanks!

On Tue, 10 Mar 2009 15:14:02 -0700, oldblindpew

Without reading your entire code: the general concept is to move the
procedure to a Standard Module. Then make it Public, and give it a
form variable as an input parameter:
Public Sub cmdOkFindFirm_Click(frmIn as Form)

Then anywhere in your procedure you change "Me" to "frmIn".

Then you can call this from any form:
cmdOkFindFirm_Click(Me)

-Tom.
Microsoft Access MVP

I've been trying to create a dialog form for finding a record and displaying
it to a form. I have several forms that can use this same dialog form. It
was all working fine based on the FindRecord method until I got the bright
idea of doing away with my user-defined alphabetical key field in favor of an
autonumber surrogate key.

With help from this forum, I finally got things working again, and without
resorting to hard-coding any object names. Here is the code:

Option Compare Database 'Use database order for string comparisons.
Option Explicit 'Requires variables be declared before use.

Private Sub cmdOkFindFirm_Click()
'Find record for main form based on selection in dialog form.
On Error GoTo Err_OkFindFirm
Dim lngSelect As Long
Dim rst As DAO.Recordset
Dim frm As Form
Dim strArgs As String

'Check to see if no selection was made.
If IsNull(Me!lstFirm) Then
MsgBox "Make a selection or click Cancel", , "No Selection"
GoTo Exit_OkFindFirm
End If

'Store the selection (firm ID number from list box) in a variable.
lngSelect = Me!lstFirm

'Create recordset clone for the main form's recordset.
'Note: Simply closing the dialog form will not make "Me" refer
'back to the main form. "Me" refers only to the form in which
'the code is running (the dialog form).
'When the dialog form was opened, the name of the main form was
'passed to the dialog form as the OpenArgs argument of the
'DoCmd.OpenForm method. That name now goes into a string variable
'(strArgs),
strArgs = Me.OpenArgs
'so the form can be put in a form variable (frm),
Set frm = Forms(strArgs)
'so the recordset clone can be created.
Set rst = frm.RecordsetClone
'All this seems needlessly complicated, but is apparently necessary.


'Find the selected record in the recordset clone.
rst.FindFirst "[aIDFirm] = " & lngSelect

'Display the selected record in the main form.
frm.Bookmark = rst.Bookmark

Exit_OkFindFirm:
On Error Resume Next
DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name
DoCmd.GoToControl "cmdSearchFirm"
rst.Close
Set rst = Nothing
Exit Sub

Err_OkFindFirm:
MsgBox "Error #: " & Err & Chr(13) & Err.Description
Resume Exit_OkFindFirm

End Sub

My question now is, how do I make this sub available to all my different
forms. I know it has to do with Public vs. Private, but my reference books
don't seem to clearly discuss this major consideration. Thanks.