Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2012

    Unanswered: How to pull/push a value from the current form to a Module using VBA

    Using the code below I am able to open an explorer window from access and select most of a file path. The part I am not able to get is "LN" which is a field in my forms. I need to be able to pull the current LN number into my Modules file path. How do I do this? Is it easier to go by ID or the actual field I am looking for?

    Function Loan_Folder_Search3()
          Dim rs              As Recordset
          Dim LN              As String
          Dim Client_Name     As String
          Dim RetVal          As String
          Dim LFPath          As String
          Set rs = CurrentDb.OpenRecordset("SELECT ID, LN, Client_Name FROM [Loan_Info_local]")
          LN = rs![LN]
          Client_Name = rs![Client_Name]
          Select Case Client_Name
          Case "Bank of David, N.A."
            LFPath = "\\MAXSERVER\Max Default\BOD\"
            RetVal = Shell("explorer.exe " & LFPath & LN, vbNormalFocus)
          End Select
          Set rs = Nothing
    End FunctionCurrently, it keeps pulling the first reccord, no matter what reccord I'm on.
    Thank you,

  2. #2
    Join Date
    Feb 2004
    New Zealand
    why not just pass the LN
    to the function

    Function Loan_Folder_Search3(LN)

    delete the Dim LN

    some where in your form code

    aaa = Loan_Folder_Search3(formtextname)
    hope this help

    See clear as mud

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 4
    Myle's suggestion is the one id go with...

    But for the record
    To pull a value
    myvariable = forms!thatformname!thatcontrolname
    To push a value
    forms!thatformname!thatvariable = thatvariable
    You can do this in forms or reports
    You can use controls, variables or objects on either side and you are not constrained to use on both sides. So you can set a control using a variable.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2012
    Thank you for your suggestion. I have implimented it in our database and the bosses seam to like it.

    Thank you,

Posting Permissions

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