Refresh Text Box on Bound Form after an Update to Control Source via a SQL update from a Combo Box selection
Posted on 2005-04-25
I have a bound form (bound to a query) and a text box on this form has a string value from this query source. When the user clicks on the text box, a combo box becomes visible giving them the values available to fill in this text box. The combobox_click event executes a SQL update command to the appropriate table (this works, it does update the appropriate foreign key). However, I can't get it to requery/refresh the text box and/or the control source. Only if I close out and reopen the form does the updated value appear. Is this only possible using an ubound control and reloading the whole recordset to the form? Or can it be done by refreshing the bound form?
Here is code:
Private Sub cboTMSType_Click()
Dim strSQL As String
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Record As Integer
Dim Selection As String
Set ws = Workspaces(0)
Set db = ws.OpenDatabase(Application.CurrentProject.FullName, False)
Me.txtTMSType.Visible = True
Me.cboTMSType.Visible = False
Selection = Me.cboTMSType.Value
strSQL = "SELECT tblTMSType.intTMSType FROM tblTMSType WHERE tblTMSType.strTMSType = '" & Selection & "';"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.AbsolutePosition <> -1 Then
Record = rs!intTMSType
strSQL = "UPDATE tblTMS SET tblTMS.intTMSType = " & Record & "" _
& " WHERE (((tblTMS.strTMS)='" & Me.txtTMS.Value & "'));"
Any help is welcome.