Results 1 to 7 of 7

Using VBA code to get a value from a form

  1. #1
    JDummy is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jun 2018
    Posts
    4

    Using VBA code to get a value from a form

    Here is the situation I am trying to use a list of forms in a list box, I want to be able to select a form from the list then using VBA code get the value of a property from that form. Here is the code thus far:
    Private Sub List0_AfterUpdate()


    Dim stFrmName As String 'name of form from list0
    Dim stRECSorce As String ' name of form and the property requested

    stFrmName = Me.List0.Column(1) ' get form name fromlist

    DoCmd.OpenForm stFrmName 'open the form



    stRECSorce = "[Forms]!" & stRECSorce & ".[Width]" 'get value of property of width



    MsgBox "The form name is " & stFrmName & " and the Property is " & stRECSorce 'display info


    DoCmd.Close acForm, stFrmName 'close form

    End Sub
    all code runs as expected except the Msgbox what it returns is "The form name is Client and the Property is [Forms]![Width]".
    What is wrong with stRECSorce variable. I have tried several other forms of the variable format like: stRECSorce = "[" & stFrmName & "." & "[" & "recordsource" & "]" and stRECSorce = stFrmName & stRECSorce & ".[Width]". None of these work.

  2. #2
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    1,362
    Code:
    stRECSorce = "[Forms]!" & stRECSorce & ".[Width]" 'get value of property of width
    stRECsorce is never given a value before the above line is executed.
    Last edited by davegri; 06-12-2018 at 07:39 AM. Reason: color

  3. #3
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    1,362
    From outside of the form's class code module, to get the width (in inches) of a control on the form do it like this:
    Code:
    Public Function fcnProp()
        Dim frm As Object
        Dim fld As Object
        Set frm = Forms("frmGuestInformation")
        Set fld = frm("txtFirstName")
        fcnProp = fld.Width / 1440
        Debug.Print fcnProp
    End Function
    The code could be modified to have the function receive argument strings for the form name, control name and property name, like this:
    Use would be like:
    Code:
    MyControlProp = fcnProp("frmMyForm","aTextBox","Width") / 1440 'twips returned, divide by 1440 to get inches
    
    Public Function fcnProp(fName As String, fFld As String, fProp As String)
        Dim frm As Object
        Dim fld As Object
        Set frm = Forms(fName)
        Set fld = frm(fFld)
        fcnProp = fld.Properties(fProp) 
        Debug.Print fcnProp
    End Function
    Last edited by davegri; 06-12-2018 at 09:33 PM. Reason: edit

  4. #4
    JDummy is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jun 2018
    Posts
    4
    Quote Originally Posted by davegri View Post
    Code:
    stRECSorce = "[Forms]!" & stRECSorce & ".[Width]" 'get value of property of width
    stRECsorce is never given a value before the above line is executed.
    I apologize for being late on a response been very busy. I noticed what I had done after reading your post. Sometimes eyes and minds don't work together. Here is the code after fixing my error:

    Private Sub List0_AfterUpdate()

    Dim stFrmName As String 'Form from list
    Dim stRECSorce As String 'the value of the property

    stFrmName = Me.List0.Column(1) 'get form name from list box

    DoCmd.OpenForm stFrmName 'open said form
    stRECSorce = "[Forms]!" & stFrmName & ".[Width]" 'request for value

    MsgBox "The form name is " & stFrmName & " and the width Property is " & stRECSorce 'display values


    DoCmd.Close acForm, stFrmName 'close the form

    End Sub
    Even after I corrected stRECSorce = line I still do not get the output i want. The display is not correct. I get this when run: "The form name is Client and the width property is [Forms]!Clients.[Width]". What I would like to see is this: "The form name is Client and the width property is 3.234"." I am sure the problem is in the concatenation of the line stRECSorce = "[Forms]!" & stFrmName & ".[Width]" 'request for value . I can't get it right.

  5. #5
    JDummy is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jun 2018
    Posts
    4
    Thank you for your reply. I understand where you are coming from with the code you showed. The property value request will not always be width. I am planning on having a second list that includes a list of properties associated to forms, then the person can select the property and the form to get the value. I am just using this to be able to understand concatenation better. I may have to rethink what I am doing and try it a different way. Again thank you for your response.

  6. #6
    JDummy is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jun 2018
    Posts
    4
    Thank you all for your assistance and reply's. Here is how I fixed my problem:
    Private Sub List0_AfterUpdate()

    Dim stFrmName As String 'Form from list
    Dim stRECSorce As String 'the value of the property
    Dim stPropName As String
    stFrmName = Me.List0.Column(1) 'get form name from list box
    stPropName = "Width"
    DoCmd.OpenForm stFrmName 'open said form

    stRECSorce = Forms(stFrmName).Properties(stPropName)

    MsgBox "The form name is " & stFrmName & " and the width Property is " & stRECSorce 'display values


    DoCmd.Close acForm, stFrmName 'close the form
    End Sub
    I received the guidance from another forum but I do appreciate all your responses. Keep up the good work that all of you do on these forums to help others. GOD bless you all.

  7. #7
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    1,362
    Edit: Saw reply above - times overlap...
    Nice concise solution.
    Your method can also be adapted to a function that receives the parameters as strings.


    You can't do it that way
    stRECSorce = "[Forms]!" & stFrmName & ".[Width]" 'request for value
    You're simply giving stRECSorce the value of the string.

    Use this method:
    Code:
    Option Compare Database
    Option Explicit
    
    
    '---------------------------------------------------------------------------------------
    ' Method : fcnProp
    ' Author : Davegri
    ' Date   : 6/12/2018
    ' Purpose: function to return form properties
    ' Example use:
    ' myText = fcnProp("frmGuestInformation", "width", "txtFirstName") / 1440
    ' fFld is optional
    '---------------------------------------------------------------------------------------
    Public Function fcnProp(fName As String, fProp As String, Optional ByVal fFld As String)
        Dim frm As Object
        Dim fld As Object
        Set frm = Forms(fName)
        If Len(fFld) = 0 Then
            fcnProp = frm.Properties(fProp)
            Exit Function
        Else
            Set fld = frm(fFld)
            fcnProp = fld.Properties(fProp)
        End If
    End Function
    
    Public Sub List0_AfterUpdate()
        Dim stFrmName As String 'Form from list
        Dim stControlName as string  'text box name    
        Dim stRECSorce As String 'the value of the property
        stFrmName = Me.List0.Column(1) 'get form name from list box
        DoCmd.OpenForm stFrmName 'open said form
        'stRECSorce = "[Forms]!" & stFrmName & ".[Width]" 'request for value
        stRECSorce = fcnProp(stFrmName,"Width")
        MsgBox "The form name is " & stFrmName  _
            & " and the width Property is " & stRECSorce 'display values
        DoCmd.Close acForm, stFrmName 'close the form
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 07-03-2015, 04:03 PM
  2. Replies: 3
    Last Post: 10-16-2014, 07:49 AM
  3. Replies: 16
    Last Post: 12-12-2013, 12:23 PM
  4. Replies: 6
    Last Post: 09-02-2012, 03:30 PM
  5. Replies: 3
    Last Post: 07-20-2012, 10:41 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums