Using Requery to update a Text Box on a form

[speedball]

Hello

I have a tabular form called WorkOutstandingForm, which looks at a query called WorkOutstandingQuery. On the form are two combo boxes (including one called ClientNameCombo) used to filter out certain records from the query.

When I select a client from ClientNameCombo, the other combo box updates based on my selection as intended. However, the rest of the form, i.e. the numerous text boxes, do not update. If I close and reopen the form, then the whole form updates, but I'd like it to update everything without having to open and close.

Here is my code...


Private Sub ClientNameCombo_AfterUpdate()

Dim myQueryDefTemp As DAO.QueryDef
Dim mySQL As String

' Change myQueryDefTemp
Set myQueryDefTemp = CurrentDb.QueryDefs("WorkOutstandingQuery")

mySQL = "SELECT * " & _
"FROM WorkOutstanding " & _
"WHERE ClientName = '" & ClientNameCombo.Value & "' "
myQueryDefTemp.SQL = mySQL

' None of these seem to work!
Forms!WorkOutstandingForm!ClientName.Requery
Me.Refresh
Me.Recalc
Me.Requery

myQueryDefTemp.Close
Set myQueryDefTemp = Nothing

End Sub


I've searched on numerous forums for a solution and everyone seems to suggest Me.Requery, but this doesn't seem to work for me.

Any help would be really appreciated

Thanks

 

[MarkK]

You haven't applied the query to the RecordSource property of the form. You open it, modify it, and close it without using it.
This might be all you need...

Private Sub ClientNameCombo_AfterUpdate()
  Me.RecordSource = _
    "SELECT * " & _
    "FROM WorkOutstanding " & _
    "WHERE ClientName = '" & ClientNameCombo.Value & "' "
End Sub

 

[speedball]

Thanks lagbolt! I've just tried it and it works a treat. I can now go to bed a happy man.

Do you know why my previous code didn't work? Is there an issue with using Requery to update Text Boxes? Or was I just way off with the way I was trying to amend the query that the form is based on?

Thanks again
speedball

 

[MarkK]

You're welcome.

Do you know why my previous code didn't work?

Read my last post again. It's all there.
Cheers,

 

[speedball]

Okay, thanks.