Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2007
    Posts
    18

    Unanswered: Passing Value to Module

    I have an application where Form1 calls Form3 and, Form2 calls Form3. When Form3 is closed, I want to return to the actual form that called Form3. Before I call each of the forms, I set a publicly defined value that is in a module to a unique value to represent which form is doing the call. When I click the button to close Form3, the app accesses the module but the publicly defined value is blank. Can anyone tell me why this is happening or, point me in the direction to resolving this problem? Thanks.

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Rather than using a public variable, you may want to look at open args.

    As to why the public variable isn't working, I would need to see your code before answering.

  3. #3
    Join Date
    Apr 2007
    Posts
    18
    Thanks Rogue. I forgot to mention that the openargs already contain a value that is being passed to Form3 from both Form1 and/or Form2.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Regarding functions...

    If you want a function to return a value, here are some examples:
    (Note: these examples use ADO code.)
    Ex: calling function retIsAdmin returns True/False depending on if the user is found in dbo_AdminTable or not.
    Function retIsAdmin() as Boolean
    Dim LID As Variant
    LID = GetUser() <- get the user's login name function routine from example in Access Code Bank
    Dim strSQL As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    strSQL = "Select * from dbo_AdminTable where LoginID = ' " & LID & " ' "
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    If rs.EOF And rs.BOF Then
    retisAdmin = False
    Else
    retisAdmin = True
    End If
    rs.Close
    Set rs = Nothing
    End Function

    Ex: Return user's security value (integer) from dbo_AdminTable:
    Function retUserSecLevel() as Integer
    Dim LID As Variant
    LID = GetUser() <- get the user's login name function routine from example in Access Code Bank
    Dim strSQL As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    strSQL = "Select * from dbo_AdminTable where LoginID = ' " & LID & " ' "
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    If rs.EOF And rs.BOF Then
    retUserSecLevel = 0 <- No permissions
    Else
    retUserSecLevel = rs!UserSecLevel
    End If
    rs.Close
    Set rs = Nothing
    End Function

    Ex: Passing a value (user's login name selected on the form or gotten via the getuser() routine) to the function, return user's security value (integer) from dbo_AdminTable:
    Function retUserSecLevel(UserLoginID as String) as Integer
    Dim strSQL As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    strSQL = "Select * from dbo_AdminTable where LoginID = ' " & UserLoginID & " ' "
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    If rs.EOF And rs.BOF Then
    retUserSecLevel = 0 <- No permissions
    Else
    retUserSecLevel = rs!UserSecLevel
    End If
    rs.Close
    Set rs = Nothing
    End Function

    Ex: Dimensioning a Public variable (CID representing ContractID) and calling a form in the midst of a function where CID is populated from the form and the function continues when the form is closed.

    Note: This is saved as a module.
    Option Compare Database
    Public CID As Variant
    Function DoSomething()
    CID = 0
    ....
    ....
    DoCmd.OpenForm "frmMatchCompany", acNormal, , , , acDialog
    (Note...frmMatchCompany opens and there is code on this form to populate the variable CID if a certain Contractor is selected in a listbox on the form. When the form closes, this function resumes to the next lines after opening the form.)
    If CID <> 0 Then
    msgbox "Company ID selected on frmMatchCompany = " & CID
    ....
    Else
    msgbox "No contractor was selected on frmMatchCompany...."
    ....
    End If
    End Function

    Hope this helps. Use these as a guidance. I just wanted to show you some sample functions on returning values and passing arguments.
    Last edited by pkstormy; 10-05-07 at 13:12.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Apr 2007
    Posts
    18
    Hi pkstormy
    I am new at this and I really do not understand as well as I should. I am attaching a text file of my code. Please tell me where I am going wrong. Thanks.
    Attached Files Attached Files

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    this addresses a slightly different issue, but might help:

    open an arbitrary number of forms with
    FormStackOpen "formName"
    subsequent calls to FormStackClose backtrack thru the open sequence

    Code:
    Option Compare Database
    Option Explicit
    Option Base 1 '<<<<<<<<<<<< REQUIRED !!!!!!!!!!!!!!!
    Dim FormStack() As String
    Public Sub FormStackOpen(strFormName As String)
        On Error GoTo err_formStackOpen
        ReDim Preserve FormStack(UBound(FormStack) + 1)
        FormStack(UBound(FormStack)) = strFormName
        DoCmd.OpenForm strFormName
    exit_formStackOpen:
        Exit Sub
    err_formStackOpen:
        If Err.Number = 9 Then  'subscript out of range
            ReDim FormStack(1)
            FormStack(1) = strFormName
            DoCmd.OpenForm strFormName
        Else
            MsgBox Err.Number & ": " & Err.Description, vbExclamation, "Error in formStackOpen"
        End If
        Resume exit_formStackOpen
    End Sub
    Public Sub FormStackClose()
        On Error GoTo err_formStackClose
        DoCmd.Close acForm, FormStack(UBound(FormStack))
        ReDim Preserve FormStack(UBound(FormStack) - 1)
        DoCmd.OpenForm FormStack(UBound(FormStack))
    exit_formStackClose:
        Exit Sub
    err_formStackClose:
        If Err.Number = 9 Then  'subscript out of range
            'do nothing
        Else
            MsgBox Err.Number & ": " & Err.Description, vbExclamation, "Error in formStackClose"
        End If
        Resume exit_formStackClose
    End Sub
    izy
    Last edited by izyrider; 10-06-07 at 11:02. Reason: corrected err-msg title
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    "I have an application where Form1 calls Form3 and, Form2 calls Form3. When Form3 is closed, I want to return to the actual form that called Form3. Before I call each of the forms, I set a publicly defined value that is in a module to a unique value to represent which form is doing the call. When I click the button to close Form3, the app accesses the module but the publicly defined value is blank. Can anyone tell me why this is happening or, point me in the direction to resolving this problem? Thanks" I'm thinking you use the getformname function to get the name of the calling form.

    An easy way to accomplish the same thing is you can also store the calling form name in an unbound field on the form 3, writing to that field after you've opened the form, and when Form 3 closes, test to see what that value is.

    ==========================================
    Private Sub cmdEdit_Click()
    docmd.openform "frm3"
    Forms!frm3!SomeUnboundTextField = "Called From Form1"

    and then in the onClose of Frm3 test to see what's in the value of "SomeUnboundTextField"...

    Private Sub Form_Close()
    Select case me!SomeUnboundTextField
    case "Called From Form1"
    msgbox "Form 1 called this form."
    ....
    case "Called From Form2"
    msgbox "Form 2 called this form."
    ....
    End Select
    End Sub

    (or you may need to put the above select case code in the button which closes the form BEFORE the form is closed.)

    You could also write the form name in SomeUnboundTextField and have code like this...

    docmd.OpenForm me!SomeUnboundTextField <- Open form which is saved in SomeUnboundTextField
    or (using the function isFormLoaded which checks to see if a form is open - found in the Calendar example below...)
    If Not isLoadedForm(me!SomeUnboundTextField) then docmd.openform me!SomeUnboundTextField

    In the Access Code Bank, there is a Calendar example which demonstrates this technique where you open the Calendar form (by double-clicking on the date field in the form), it writes the name of the form which called the calendar form in an unbound field on the calendar form (and the fieldname for the date field in another unbound field and a subform name if it's called from a subform within a form), and once a date has been selected on the calendar form, the calendar form closes and updates the date field on the calling form/subform. This way the same calendar form can be re-used over and over again for any date field on any forms/subforms. You may want to download and look at this example if you get a chance (http://www.dbforums.com/showpost.php...&postcount=30).

    It's a quick easy way without a lot of code.
    Last edited by pkstormy; 10-06-07 at 19:20.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Apr 2007
    Posts
    18

    Thumbs up

    Thanks everyone for your advice and direction.
    I've got it working.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •