Updating a bound textbox from another form

[DavidAustin]

Following on from http://bytes.com/topic/access/answer...nbound-textbox

It's going to be complicated to explain but I'll give it a go and hope you guys understand what I mean.

So with this same data entry form, when the user enters into the textbox a location that is not already in my tblLocation, a dialog box opens up giving the user the option to add the new location to the table or to select an existing location from a list box. Once the user does either of these options, I then need the txtLocationID textbox to update to the new LocationID of the new/existing location and also need the unbound textbox to update to the new/existing location. Unfortunately, I can't get this to work... hopefully that's where you guys can help me!

The first function below is what I am using to determine whether or not the entered location already exists in the table. If it does, it updates the txtLocationID box (this all works fine. It it doesn't already exist, the new dialog box pops up. This also works fine for now.

Private Function fQryGetLocationID(Optional Cancel As Integer)
    Dim db As Database
    Dim qdf As QueryDef
    Dim rs As DAO.Recordset
    Dim intX As Integer
 
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryUpdateLocationID")
    qdf.Parameters("test") = Me.txtLocation
    Set rs = qdf.OpenRecordset()
 
    If rs.RecordCount <> 0 Then
        With rs
            .MoveFirst
            Me.txtLocationID.Value = .Fields("LocationID")
        End With
    Else
        MsgBox "No matching location!"
        glTestNoLocation = Me.txtLocation.Text
        Cancel = True
        DoCmd.OpenForm "frmNoLocation", , , , acFormAdd, acDialog
    End If
    rs.Close
    db.Close
    Set rs = Nothing
    Set qdf = Nothing
    Set db = Nothing
End Function
 

The popup box has a textbox called txtUnsavedLoc, a save button for the new location called cmdSave, a list box populated with similar locations to the originally entered location and an ok button to select an option from the list box.

On loading, the list box is populated using a query where the unbound text box from the data entry form is tested using a Levenshtein function and produces locations with <5 differences.

Private Sub Form_Load()
 
txtUnsavedLoc = glTestNoLocation
 
Dim db As Database
Dim qdf As QueryDef
Dim rs As DAO.Recordset
Dim intX As Integer
 
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryLevLocation")
Me.txtUnsavedLoc.SetFocus
qdf.Parameters("LevLocation") = glTestNoLocation
Set rs = qdf.OpenRecordset()
 
'remove all items in the list box before querying to add them
With rs
    With Me.lstResults
        For intX = .ListCount - 1 To 0 Step -1
            Call .RemoveItem(intX)
        Next intX
    End With
End With
Me.lstResults.Requery
 
If rs.RecordCount <> 0 Then
    With rs
        .MoveFirst
        While Not .EOF
            Me.lstResults.AddItem .Fields("LocationID") & ";" & .Fields("FileLocation")
            .MoveNext
        Wend
    End With
End If
 
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
 
End Sub
 

If the user selects an option from this box and then presses the Ok button, I need the selected option to populate the unbound textbox with the text and the bound textbox with the locationID. I have used the following. It replaces the unbound textbox but the bound textbox doesn't change.

Private Sub cmdOK_Click()
 
If Me.lstResults.ListCount <> 0 Then
    If Me.lstResults.ListIndex <> -1 Then
        If CurrentProject.AllForms("frmAddFile").IsLoaded = True Then
            [Forms]![frmAddFile]![txtLocationID] = Me.lstResults.Column(0)
            [Forms]![frmAddFile]![txtLocation] = Me.lstResults.Column(1)
        End If
        DoCmd.Close acForm, "frmNoLocation"
    End If
End If
End Sub
 

Any ideas where I'm going wrong?

[Seth Schrock]

Lets try something. Between lines 6 and 7 of your last block of code, try putting

Debug.Print Me.lstResults.Column(1)

When you run it, it will put the value in the immediate window in your code editor. If you don't see it on the bottom of your code editor, then press Ctrl + G to view it.

[DavidAustin]

The value is Cirencester, which is the text value I selected in the list box. Also apparently I have a runtime error on line 7:

error 2115: the macro or function set to the before update or validation rule property for this field is preventing microsoft access from saving data in this field.

I thought it might just be me doing something wrong with my function, so I just copied the code into the before update section and deleted the function line but it's still appearing with this error!