Back again with a query related to this stuff.
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?
64
