How to Create a Module and How to Call It?

[jcmv007]

Using:
Windows XP SP1; Access2002 SP1;

I have a form as a datasheet where the following codes resides for every column shown, that is executed when the user doble clicks the cell.

Is it posible to put it in a module and how would I call it
in my form?

The origin form is: frmresumengastos
The destination form is: frmdetallegastosporcta

Private Sub Budget_DblClick(Cancel As Integer)
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmDetalleGastosPorCta"
    
    stLinkCriteria = "[txtCostCenter]=" & Forms!frmresumengastos.txtCostCenter _
                    & "And [txtDpto]=" & Forms!frmresumengastos.txtDpto
    DoCmd.OpenForm stDocName
    
    'Forms!frmDetalleGastosPorCta.Visible = False
    
    Forms!frmdetallegastosporcta!txtAnoD = Forms!frmresumengastos.txtAnoD
    Forms!frmdetallegastosporcta!txtAnoH = Forms!frmresumengastos.txtAnoH
    Forms!frmdetallegastosporcta!txtMesD = Forms!frmresumengastos.txtMesD
    Forms!frmdetallegastosporcta!txtMesH = Forms!frmresumengastos.txtMesH
    Forms!frmdetallegastosporcta!txtGrupoCtasID = Me.GrupoCuentas_ID
    Forms!frmdetallegastosporcta.Requery
       
    If IsNull(Forms!frmdetallegastosporcta.cboGrupoCtas) = True Then
    Forms!frmdetallegastosporcta.cboGrupoCtas.Value = Me.GrupoCuentas_ID.Value
    End If
    Forms!frmdetallegastosporcta.Visible = True
End Sub

 

[vbajock]

Do want the it to return a value ? This determines whether you are going to create a function or subroutine in your code module.

 

[jcmv007]

I guess it is a subroutine because no matter what control calls it all do the same OPEN a form and set some of its controls with values from the calling form.

Thanks,

 

[vbajock]

Well then, it would look like this:

Sub Whatever()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmDetalleGastosPorCta"

stLinkCriteria = "[txtCostCenter]=" & Forms!frmresumengastos.txtCostCenter _
& "And [txtDpto]=" & Forms!frmresumengastos.txtDpto
DoCmd.OpenForm stDocName

'Forms!frmDetalleGastosPorCta.Visible = False

Forms!frmdetallegastosporcta!txtAnoD = Forms!frmresumengastos.txtAnoD
Forms!frmdetallegastosporcta!txtAnoH = Forms!frmresumengastos.txtAnoH
Forms!frmdetallegastosporcta!txtMesD = Forms!frmresumengastos.txtMesD
Forms!frmdetallegastosporcta!txtMesH = Forms!frmresumengastos.txtMesH
Forms!frmdetallegastosporcta!txtGrupoCtasID = Me.GrupoCuentas_ID
Forms!frmdetallegastosporcta.Requery

If IsNull(Forms!frmdetallegastosporcta.cboGrupoCtas) = True Then
Forms!frmdetallegastosporcta.cboGrupoCtas.Value = Me.GrupoCuentas_ID.Value
End If
Forms!frmdetallegastosporcta.Visible = True

End Sub

In your event:

Private Sub Budget_DblClick(Cancel As Integer)
Call Whatever()

End Sub

If you wanted to pass a parameter:



Sub Whatever(byval stDocName as string)

===> this gets eliminated Dim stDocName As String

Dim stLinkCriteria As String
blah blah
End Sub

To call with the parameter:

Private Sub Budget_DblClick(Cancel As Integer)
Call Whatever("frmDetalleGastosPorCta&quot

End Sub

This is a pretty simplistic explanation. Look up SUB or FUNCTION in the help file for a more complete description.

 

[jcmv007]

Here is how my code looks like.

But I'm getting the following error message:

Run-time error '2450':

Sistema de Presupuestos can't find the form
'fsfrGastosPresup_AnoACtual_Y_Anterior'referred to in a macro expression or Visual Basic code.

Main form: "frmresumengastos"
Subform: "fsfrGastosPresup_AnoActual_Y_Anterior"

Form I want to open:"frmDetalleGastosPorCta"

Here is the code I'm using in my event, which is placed in the subform "fsfrGastosPresup_AnoACtual_Y_Anterior":

Private Sub GrupoDeCuentas_DblClick(Cancel As Integer)
 Call subOpenForm("frmresumengastos", "fsfrGastosPresup_AnoActual_Y_Anterior", "frmDetalleGastosPorCta")
End Sub

Here is the code in the Module created with your help.

Public Sub subOpenForm(OriginMain As String, OriginSub As String, Destination As String)

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim stOriginMain, stOriginSub, stDestination As String ' Local variables
    Dim frmO As Form   ' Calling Form
    Dim frmD As Form   ' Destination Form
    Dim sfrmO As Form  ' Calling SubForm
    
    stOriginMain = OriginMain
    stOriginSub = OriginSub
    stDestination = Destination
    
    Set frmO = Forms(stOriginMain)
    Set sfrmO = Forms(stOriginSub)
    
    'Open destination form
    stDocName = Destination
    stLinkCriteria = "[Centro]=" & "'" & frmO.txtCostCenter & "'" _
                    & "And [Depnum]=" & "'" & frmO.txtDpto & "'"
    
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    Set frmD = Forms(stDestination)
    'set destination form's controls with calling form's control's values
    frmD!txtAnoD = frmO!txtAnoD
    frmD!txtAnoH = frmO!txtAnoH
    frmD!txtMesD = frmO!txtMesD
    frmD!txtMesH = frmO!txtMesH
       
    frmD!txtGrupoCtasID = sfrmO!GrupoCuentas_ID
    frmD!cboGrupoCtas.Value = sfrmO!GrupoCuentas_ID
       
    frmD.Requery
    
End Sub

 

[vbajock]

What line generates the error?

Also, instead of passing the string as a parameter, you could pass the whole form like:

Public Sub subOpenForm(frmO As form, sFrm0 As Form, Destination As String)

then call it:

Call subOpenForm(Forms!form!Whatever, Forms!Form!whateversSub)
that way you could get rid of the strings. Comment out or erase the DIM and Set form stuff in the sub.

 

[jcmv007]

Here is the line that generate the error:
Set sfrmO = Forms(stOriginSub)

I tried the second part of your post but I get a type mismatch error.

 

[vbajock]

This is what your code should look like if you pass the objects:

Public Sub subOpenForm(byval frmO AsForm, byval sFrmO As Form, byval frmD As form,byval destination as string)

Dim stDocName As String
Dim stLinkCriteria As String
'Open destination form

stDocName = Destination

stLinkCriteria = "[Centro]=" & "'" & frmO.txtCostCenter & "'" _
& "And [Depnum]=" & "'" & frmO.txtDpto & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria

'set destination form's controls with calling form's control's values
frmD!txtAnoD = frmO!txtAnoD
frmD!txtAnoH = frmO!txtAnoH
frmD!txtMesD = frmO!txtMesD
frmD!txtMesH = frmO!txtMesH

frmD!txtGrupoCtasID = sfrmO!GrupoCuentas_ID

frmD!cboGrupoCtas.Value = sfrmO!GrupoCuentas_ID

frmD.Requery

End Sub

I also noticed you have frmO, which could be typed easily as Frm0. Are you sure this is not a problem?

 

[vbajock]

Correction to the code I sent you above:

Public Sub subOpenForm(byref frmO AsForm, byref sFrmO As Form, byref frmD As form,byval destination as string)