Private sub won’t work

[Liz James]

apologies for double posting (getting error)
A main menu opens 30 different forms. For each form there is a command
button with code to cancel the open event if there are no records.
I have tried to create a private sub that can be used in the code for each
command button to save typing the routine out 30 times, but it doesn’t work.
What am I doing wrong?

Here is the code:

Private Sub OpenForm()
Dim stDocName As String
Dim sSQL As String

If DCount("*", sSQL) = 0 Then
MsgBox "There is no data for this asset/item."
DoCmd.CancelEvent
Else
DoCmd.OpenForm "stDocName"
DoCmd.Close acForm, "frmAssetRegister"
End If
End Sub
- - -

Behind each button I put the following code:
This example is for command called “cmdOpenA2â€

Private Sub cmdOpenA2_Click()
Dim stDocName As String
stDocName = "frmTrack1"
Dim sSQL As String
sSQL = "qryTrack1"
Call OpenForm
End sub
- - -

 

[doyapore]

I think you would need to pass the parameters to your OpenForm() procedure.
These parameters would have to be the recordset and the name of the
particular form from which you would be calling the OpenForm() procedure.
For example, you could try with this:

*************** Code behind command button on each Form begins
***************
Private Sub cmdOpenA2_Click()
Dim stDocName As String
Dim sSQL As String

stDocName = Forms(0).NAME
sSQL = "qryTrack1"

Call OpenForm(stDocName, sSQL)
End sub
**************************** Code Ends *********************************

************************** Common Code for All ***************************
Private Sub OpenForm(stDocName as String, sSQL as String)
If DCount("*", sSQL) = 0 Then
MsgBox "There is no data for this asset/item."
DoCmd.CancelEvent
Else
MsgBox "There are data for this asset/item."
End If
End Sub
**************************** Code Ends *********************************

 

[John Vinson]

apologies for double posting (getting error)
A main menu opens 30 different forms. For each form there is a command
button with code to cancel the open event if there are no records.
I have tried to create a private sub that can be used in the code for each
command button to save typing the routine out 30 times, but it doesn’t work.
What am I doing wrong?

Here is the code:

Private Sub OpenForm()
Dim stDocName As String
Dim sSQL As String

If DCount("*", sSQL) = 0 Then
MsgBox "There is no data for this asset/item."
DoCmd.CancelEvent
Else
DoCmd.OpenForm "stDocName"
DoCmd.Close acForm, "frmAssetRegister"
End If
End Sub
- - -

Behind each button I put the following code:
This example is for command called “cmdOpenA2”

Private Sub cmdOpenA2_Click()
Dim stDocName As String
stDocName = "frmTrack1"
Dim sSQL As String
sSQL = "qryTrack1"
Call OpenForm
End sub

There's a sneaky trick you can use to use a single bit of code from
multiple forms. What you need to do is code it as a public Function
(it must be public in order to be called from multiple forms); and if
you make it a Function you can avoid putting any code in the Form's
module at all. Try:

Public Function OpenForm(frm as Form)
Dim rs As DAO.Recordset
Set rs = frm.RecordsetClone
If rs.RecordCount = 0 Then
MsgBox "There is no data for this asset/item"
DoCmd.Close acForm, frm.Name
End If
Set rs = Nothing
End Function

In each form's Open event you can then type

=OpenForm([Form])

I'm a bit concerned about your design, though! It sounds like you have
thirty different forms to display thirty different items. Unless each
item is based on a completely different table, with a different table
structure, this seems odd; might it not be better to use *ONE* form
based on a parameter query which returns the data for an item?

John W. Vinson[MVP]