?
Solved

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
6
Medium Priority
?
883 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:1burke
  • 3
  • 2
6 Comments
 
LVL 39

Expert Comment

by:thenelson
ID: 13860143
Use Me.Requery instead of Me.Refresh
0
 

Author Comment

by:1burke
ID: 13860218
thenelson,

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

Author Comment

by:1burke
ID: 13860256
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Accepted Solution

by:
Arji earned 1000 total points
ID: 13861043
Is this Access 2k3?  You can also try Me.Repaint but it probably won't work.
I think if you have a new value selected from the combobox, you could assign that value to the text box and the save the record.

Me.[txtTMSType] = Me.[ComboBox].Column(1)  '<------ this assumes that you have the primary key in column 0 and the description in column 1

DoCmd.RunCommand acCmdSaveRecord
Me.Refresh

0
 
LVL 39

Assisted Solution

by:thenelson
thenelson earned 1000 total points
ID: 13863297
Try puting the Me.Requery in front of the  Me.txtTMSType.Requery.  (I'm assuming "txtTMSType" is the text box you want to requery.)

Requerying a form is supposed to requery the query is bound to.  Then requerying the text box should bring up the new value.  At least that's my reasoning.

If that doesn't work, try binding the form directly to tblTMS instead of the query (the query does reference tblTMS - yes?) to see if the problem is in the query or before it.
0
 

Author Comment

by:1burke
ID: 13867114
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Implementing simple internal controls in the Microsoft Access application.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question