asked on

Refresh Text Box on Bound Form after an Update to Control Source via a SQL update from a Combo Box selection

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.cmdOpenTMSReport.SetFocus
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
rs.MoveFirst
Record = rs!intTMSType
End If

strSQL = "UPDATE tblTMS SET tblTMS.intTMSType = " & Record & "" _
& " WHERE (((tblTMS.strTMS)='" & Me.txtTMS.Value & "'));"

db.Execute (strSQL)
Me.txtTMSType.Requery
Me.Refresh
End Sub

Any help is welcome.

thenelson

Use Me.Requery instead of Me.Refresh

1burke

ASKER

thenelson,

Yes, tried that too - nothing. Need another option.

1burke

ASKER

These are the last lines in the code:

db.Execute (strSQL)
Me.txtTMSType.Requery
Me.Refresh

I have also tried " Me.Form.Requery" and "Forms!Form_frmTMSTypeValidation.Requery" and various other ways. The "Requery" and "Refresh" options just are working.

ASKER CERTIFIED SOLUTION

Arji

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

thenelson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

1burke

ASKER

Thanks guys. Between the two, it's figured out.

I tried Arji's suggestions, but it still didn't work. Then it occured to me that I was trying to update the tables via a Query. I redid the data sources - the Text boxes (which I'm trying to udpate) are now connected to the table; the Combo Boxes (with the available choices) come from Queries. One SQL query connects them via a Primary Key. New Code:

Private Sub Set_TMS_Type_Text()
Dim strSQL As String
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Selection As Integer
Dim Acronym, Description As String

Set ws = Workspaces(0)
Set db = ws.OpenDatabase(Application.CurrentProject.FullName, False)

Selection = Me.txtTMSTypePK.Value

strSQL = "SELECT tblTMSType.strTMSType, tblTMSType.strTMSTypeDesc FROM tblTMSType WHERE tblTMSType.intTMSType = " & Selection & ";"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

If rs.AbsolutePosition <> -1 Then
rs.MoveFirst
Acronym = rs!strTMSType
Description = rs!strTMSTypeDesc
End If

Me.txtTMSType.Value = Acronym
Me.txtTMSTypeDesc.Value = Description
Me.Refresh

Set rs = Nothing
Set db = Nothing
Set ws = Nothing
End Sub

Thanks for the help. The points will be split.