How to pull/push a value from the current form to a function using VBA

[David92595]

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
      
      rs.Close
      Set rs = Nothing
  End Function

Currently, it keeps pulling the first reccord, no matter what reccord I'm on.
Thank you,
David92595

[ssanfu]

Not sure what you are trying to return, but returning a value from a function is like this:

Function Loan_Folder_Search3()
   Dim rs As Recordset
   Dim sSQL As String

   'create the SQL statement
   sSQL = "SELECT ID, LN, Client_Name FROM [Loan_Info_local] "
   sSQL = sSQL & " WHERE [Client_Name] = '" & Me.LN & "'"

   'open the recordset
   Set rs = CurrentDb.OpenRecordset(sSQL)

   'should check for records in recordset first
   Loan_Folder_Search3 = rs![Client_Name]


   rs.Close
   Set rs = Nothing
End Function

Provide control names and an example of what data you are trying to get.



ex. I want to get the client name from table [Loan_Info_local] where LN equals the control on the form named LN

[orange]

David,
You r example just opens the recordset and positions itself on the first record (assuming there are records in the table).
You get the LN from that record only.

As Steve is showing you can search for records with a particular LN (using a WHERE clause) and use data from that/those records.

A clear statement of WHAT you are trying to accomplish - in plain English - would help.

[David92595]

The coding above is supposed to open a folder in windows explorer. The path of the folder is dependant on the Client Name and the Loan Number (LN) associated with that record the current Access form. The form will always be open when the button is clicked to open the folder.

Case "Bank of David, N.A."
LFPath = "\\MAXSERVER\Max Default\BOD\"
RetVal = Shell("explorer.exe " & LFPath & LN, vbNormalFocus)

When Client Name is "Bank of David"
then the code opens a explorer shell and follows LFPath (which is working) and the loan number off the current form I am using (having problems pulling the information from the open form to the function in the module)

[orange]

I'm not exactly sure where the difficulty is. I mocked up a situation that seems similar (to me anyway)

I created a table called Load_info_Local

FileId	LN	MyName
1	traffic.xml	jed
2	xmlCars.xml	Sammy
3	DenmarkBank.xml	Gustav

And modified your procedure as follows

Function Loan_Folder_Search3()
      Dim rs              As DAO.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]
     
       Set rs = CurrentDb.OpenRecordset("SELECT FileID, LN,MyName FROM [Loan_Info_local]")
       Do While Not rs.EOF
       LN = rs!LN
       Client_Name = rs!MyName
      'Case "Bank of David, N.A."
       ' LFPath = "\\MAXSERVER\Max Default\BOD\"
       ' RetVal = Shell("explorer.exe " & LFPath & LN, vbNormalFocus)
       Select Case Client_Name
            Case "Sammy"
                 LFPath = "C:\Documents and Settings\Garay\My Documents\"
                 RetVal = Shell("notepad.exe " & LFPath & LN, vbNormalFocus)
            Case Else
       
        End Select
      rs.MoveNext
      Loop
      rs.Close
      Set rs = Nothing
  End Function

I just happened to be working on an XML file, when I used explorer.exe it gave me a warning message so I chose NotePad.exe to open the xml file without issue.

I did put the process inside a Do Loop to get to another record ie not the first record.

Is it possible you chose to use Select case instead of an IF? I'm not sure how you would get past the first record.

Then again, I may have misunderstood your issue.

[ssanfu]

OK, so I am confused. (My normal state)

What are you trying to do? Open a file to get info? Write to a file? What type of file? Text?
Why open a folder using Win Explorer?

[orange]

Steve,
I have no real idea of what the poster is doing/wanting either. I made a guess, based on the code, and created an example.
And as I said the the poster, I may have totally misunderstood the request.

It seems more and more you have to guess what the underlying question/issue is. I continue to ask someone to pretend they're in a line at McDonalds - turn to the person behind you who doesn't know you, or Access or database and tell him what your problem is.

Anyway let's see what David has to say.

[David92595]

@ Steve: I am tring to open a folder, that is all. I do no need to edit anything in the folder. My users use the button as a quick way to access the folder for the record they are working on so they can mail merge word documents.

@ Orange: I have tried Select case and IF statements, both with the same results.

[orange]

Did the sample help, not help, and do you still have a problem?

[ssanfu]

@ Orange - I use Burger King.

@ David - I think I might have got it...

Will the path always be "\\MAXSERVER\Max Default\BOD\"?

Just to be sure of the terminology, Forms have controls, controls are bound to fields (if the form is a bound form).

How is the user selecting the bank name? Combo box, list box, typing the name in a text box?
Same for the loan number. How is it selected?

Could you post a screen shot of the form? - Put in fake data - no sensitive data please...

I did a test using one line:

RetVal = Shell("explorer.exe " & "C:\AccMDB\OLD", vbNormalFocus)

and it opened the folder. So now it is just a matter of creating the correct syntax -seeing where your data comes from.

[David92595]

I've attached a screen shot of the area in disccussion. Both the Client Name and Loan number are entered into text fields. The information is actually entered in on a different form, but are manually typed in at the time of entry.

Hope this helps, I'm very anxious to find the correct syntax for this. I should note that this is only one of many forms that will use this coding...