CODEWITHSUNDEEP
vbaformsms-accessreferencecontrols
Scole
Scole

Reputation: 97

Update value in another form's textbox via VBA

Hopefully this makes sense. I'm frustrated that I cannot figure this out. I have a simple Access 2010 database. I have a simple form inside it that helps the user input some specific information. This data entry situation can occur on two other forms in the database. Rather than have two copies of the "helper" form where the VBA code has hard-coded control references, I wanted to make it more universal by passing the name of the form that calls it by using the openArgs parameter.

When the time comes to transfer the values BACK to the form that needs the information, the helper form attempts to do this like so:

Private Sub cmdOk_Click()
    Dim theFormName As String
    Dim theForm As Form

    theFormName = Me.OpenArgs
    Set theForm = Forms.Item(theFormName)

    If Not IsNull(theForm.Name) Then
        theForm.txtLongitude.Value = Me.lblLongitude.Caption
        theForm.txtLatitude.Value = Me.lblLatitude.Caption
    End If

    DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

The variable theForm is populated correctly and theForm.Name returns the correct name of the form so that part works fine. The problem is that theForm.<controlName>.Value does not. When I run the code, I get an

application-defined or object-defined error (Run-time Error 2465)

I've tried all sorts of permutations for the control reference from the current open form to a second open form but I cannot get the syntax right. I've tried:

theForm!txtLongitude.Value ("..can't find the field txtLongitude..")
theForm.Controls("txtLongitude").Value ("..cant find the field...")

Upvotes: 1

Views: 22295

Answers (1)

LauraNorth
LauraNorth

Reputation: 182

I have two suggestions. If one works, let me know and I'll edit my answer to only include the one that works.

  1. Try changing theForm.txtLongitude.Value = Me.lblLongitude.Caption to Forms!theForm!txtLongitude.Value = Me!lblLongitutde.Caption and theForm.txtLatitude.Value = Me.lblLatitude.Caption to Forms!theForm!txtLatitude.Value = Me!lblLatitude.Caption

  2. If you've already tried that or it doesn't work, try declaring variables and "pulling" the values out of one form before putting them in the other form. (Also make sure the data type of both are the same.)

    Private Sub Command4_Click() Dim theFormName As String Dim theForm As Form

        Dim txtLong As String
    Dim txtLat As String

    txtLong = Me.lblLongitude.Caption
    txtLat = Me.lblLatitude.Caption

    theFormName = Me.OpenArgs
    Set theForm = Forms.Item(theFormName)

    If Not IsNull(theForm.Name) Then
        theForm.txtLongitude.Value = txtLong
        theForm.txtLatitude.Value = txtLat
    End If

    DoCmd.Close acForm, Me.Name, acSaveNo

    End Sub

Upvotes: 1

Related Questions