Can not refresh a form - what am I doing wrong?

[RedGoneWILD]

I have a form that is populated with employee records from a query. I have a button that I want to use to show/hide inactive employees. When it is clicked, the query is updated correctly. But I can not get the form to refresh. I have tried Me.refresh, Me.repaint, Me.requery but none of those seem to work. When I close the form, then reopen it, it's displayed correctly since the query is correct. But I can't get it to refresh automatically without closing the form. Any suggestions?

[pbaldy]

Requery should work. What exactly is your code?

[RedGoneWILD]

I can post the exact code later when I get home, but essentially it's:

sub button_click()
dim db as dao.database
dim qdf as dao.querydef
set qdf = db.querydefs
dim sqlstr as string
sqlstr = "SELECT ....."

qdf.sql = sqlstr
'at this point, the query is properly updated. the last thing i need to do is refresh form.

'here is where i tried me.refresh/requery/repaint with no success.
end sub

[pbaldy]

Try with the Requery, and maybe throw a DoEvents right before in case it's a timing issue. If that doesn't work, post the exact code and maybe we'll see a hole in the process.

[RedGoneWILD]

Tried DoEvents as well, but never used that before so didn't really understand what I found on the web for it. Form view is "Continuous Forms", each row has two textboxes (1 for ID, 1 for name), a checkbox (for inactive/active) and a button that opens up the employee detail form. Code is below:

Private Sub btnInactives_Click()
On Error GoTo Err_btnInactives_Click
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryLeasedDriverList")
    
    Dim sqlStr As String
    sqlStr = "SELECT [Leased Drivers].EmployeeNumber, [Leased Drivers].LastName, [Leased Drivers].FirstName, [Leased Drivers].Inactive " & _
            "FROM [Leased Drivers] "


    'default button caption is "Show Inactives"
    'if button reads "Hide Inactives" then add WHERE clause to query
    If Me.btnInactives.Caption = "Hide Inactives" Then
        sqlStr = sqlStr & "WHERE((([Leased Drivers].Inactive) = False)) "
        Me.btnInactives.Caption = "Show Inactives"
    Else
        Me.btnInactives.Caption = "Hide Inactives"
    End If


    sqlStr = sqlStr & "ORDER BY [Leased Drivers].EmployeeNumber;"


    qdf.SQL = sqlStr
    
    DoEvents


    Me.Requery


Exit_btnInactives_Click:
    Exit Sub


Err_btnInactives_Click:
    MsgBox Err.Description
    Resume Exit_btnInactives_Click
End Sub

[pbaldy]

I would expect that to work, presuming the form the code is in is bound to that query. There isn't a subform involved here, is there? Can you post the db here?

Another direction you might consider is a checkbox with code like this behind it:

  If Me.chkShowRetired = True Then
    Me.FilterOn = False
  Else
    Me.Filter = "Retired = False"
    Me.FilterOn = True
  End If

Which in your case would be

Me.Filter = "Inactive = False"

[RedGoneWILD]

Can't post the database. no subform. form is bound to query. I basically took over a 12 year old program, and am adding new features and removing the junk (btw it's .mdb, not .accdb if that makes any difference). This is a pretty minor addition, but it would be nice if this form could be refreshed to show only the records that the user will want to see.

I will try the checkbox filter to see if that works. Thanks again for your help.

[pbaldy]

accdb or mdb shouldn't matter. I can only imagine it's some sort of timing issue, though I thought the DoEvents would account for that. If you don't like the filter method, this should certainly work because I do it a lot:

Me.RecordSource = sqlStr

In essence you're changing the record source of the form from the query to the SQL while it's open.

[RedGoneWILD]

Me.RecordSource = sqlStr worked exactly as I wanted. I kept thinking I should try that, not sure why I didn't.

The odd thing is I have Me.requery set to the Got_Focus event. When you open the Employee file from that form, and then change an employee from active to inactive, then close the Employee form and return to the one this one, it immediately removes their name from the list (provided Hide Inactives is true). Oh well, I'll figure it out I guess when I have more time, but your solution worked like a charm.

Cheers!

[pbaldy]

Happy to help!