Private sub doesn't work

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

Guest

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
- - -
 
Try putting your common code in a Public (not Private) Function in a
standard Module, then refer to it by name in the Click event property of the
Command Button. On the other hand, would it not be simpler to call the Sub
or Function in the NoData event and avoid the need for the user to click a
Command Button?

Larry Linson
Microsoft Access MVP
 
Liz said:
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:
Click to expand...

You have stDocName and sSQL declared as local in both
procedures so that one has no access to the other.
You need to pass your variables from the click event to
your OpenForm procedure. Something like:

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
DoCmd.OpenForm "stDocName"
DoCmd.Close acForm, "frmAssetRegister"
End If
End Sub


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