By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,654 Members | 1,580 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,654 IT Pros & Developers. It's quick & easy.

Updating a bound textbox from another form

P: 64
Following on from

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.

Expand|Select|Wrap|Line Numbers
  1. Private Function fQryGetLocationID(Optional Cancel As Integer)
  2.     Dim db As Database
  3.     Dim qdf As QueryDef
  4.     Dim rs As DAO.Recordset
  5.     Dim intX As Integer
  7.     Set db = CurrentDb()
  8.     Set qdf = db.QueryDefs("qryUpdateLocationID")
  9.     qdf.Parameters("test") = Me.txtLocation
  10.     Set rs = qdf.OpenRecordset()
  12.     If rs.RecordCount <> 0 Then
  13.         With rs
  14.             .MoveFirst
  15.             Me.txtLocationID.Value = .Fields("LocationID")
  16.         End With
  17.     Else
  18.         MsgBox "No matching location!"
  19.         glTestNoLocation = Me.txtLocation.Text
  20.         Cancel = True
  21.         DoCmd.OpenForm "frmNoLocation", , , , acFormAdd, acDialog
  22.     End If
  23.     rs.Close
  24.     db.Close
  25.     Set rs = Nothing
  26.     Set qdf = Nothing
  27.     Set db = Nothing
  28. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  3. txtUnsavedLoc = glTestNoLocation
  5. Dim db As Database
  6. Dim qdf As QueryDef
  7. Dim rs As DAO.Recordset
  8. Dim intX As Integer
  10. Set db = CurrentDb()
  11. Set qdf = db.QueryDefs("qryLevLocation")
  12. Me.txtUnsavedLoc.SetFocus
  13. qdf.Parameters("LevLocation") = glTestNoLocation
  14. Set rs = qdf.OpenRecordset()
  16. 'remove all items in the list box before querying to add them
  17. With rs
  18.     With Me.lstResults
  19.         For intX = .ListCount - 1 To 0 Step -1
  20.             Call .RemoveItem(intX)
  21.         Next intX
  22.     End With
  23. End With
  24. Me.lstResults.Requery
  26. If rs.RecordCount <> 0 Then
  27.     With rs
  28.         .MoveFirst
  29.         While Not .EOF
  30.             Me.lstResults.AddItem .Fields("LocationID") & ";" & .Fields("FileLocation")
  31.             .MoveNext
  32.         Wend
  33.     End With
  34. End If
  36. rs.Close
  37. db.Close
  38. Set rs = Nothing
  39. Set db = Nothing
  41. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOK_Click()
  3. If Me.lstResults.ListCount <> 0 Then
  4.     If Me.lstResults.ListIndex <> -1 Then
  5.         If CurrentProject.AllForms("frmAddFile").IsLoaded = True Then
  6.             [Forms]![frmAddFile]![txtLocationID] = Me.lstResults.Column(0)
  7.             [Forms]![frmAddFile]![txtLocation] = Me.lstResults.Column(1)
  8.         End If
  9.         DoCmd.Close acForm, "frmNoLocation"
  10.     End If
  11. End If
  12. End Sub
Any ideas where I'm going wrong?
Jan 28 '15 #1
Share this Question
Share on Google+
2 Replies

Seth Schrock
Expert 2.5K+
P: 2,951
Lets try something. Between lines 6 and 7 of your last block of code, try putting
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 28 '15 #2

P: 64
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!
Jan 28 '15 #3

Post your reply

Sign in to post your reply or Sign up for a free account.