refresh a form

[RedGoneWILD]

i have a split form which is a list of students. the top half has some text boxes, labels, drop down boxes...whatever. the bottom half is a recordset of all students.



right now i have a combobox with a list of statuses, and when a selection is made, then code runs to update the query with the new "WHERE" criteria. the query is the source for the form. All I'm looking for is a statment or code that will update the recordset when the combobox selection is made.

As of right now, I have to close the form and reopen it for the changes to take affect.

Any ideas on what im doing wrong?

[NTC]

have you tried me.refresh? one potential issue with this is that it will open generically - and won't re-open to a specific record depending on how the underlying record source is established. This can be managed via a creative way to stash the record ID temporarily and use it as part of the re-opening argument.

if a subform is involved you can also experiment with me.requery of that subform.

explore these two approaches.

[RedGoneWILD]

no, there's no subform. The default view is "split form" and the detail portion just looks like a table or spreadsheet. The form header has a title label, an exit button and some other commands. One combobox has a list of school years. When that is selected, it runs a query with a "WHERE" clause of the school year, and updates a saved query called "qryStudentList" that is the record source for the form. Everything works fine up to this point.

It's just that the detail portion (the recordset) of the form doesn't update. If I close the form, then reopen it, then the detail changes because the code updated the query its linked to.

I have tried .refresh, .requery, .repaint, and whatever else i thought might work, but i just dont know enough about vba to really know what im doing right or wrong. Me.refresh might be correct, but maybe i'm just using it in the wrong place.

[RedGoneWILD]

Here's what i have right now...i tried putting Me.refresh as the last statement but that does nothing

Private Sub cboStatus_AfterUpdate()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryStudentList")
Dim Status As String
Dim sqlStr As String

Status = Me.cboStatus.Value
sqlStr = "SELECT tblGradStudents.* FROM tblGradStudents WHERE tblGradStudents.Status = '" & Status & "' ORDER BY tblGradStudents.LastName, tblGradStudents.FirstName;"

qdf.SQL = sqlStr

Set qdf = Nothing
Set db = Nothing

End Sub

[Rawb]

If the .Refresh and .Requery Methods aren't working for you, you can always just close and re-open the Form again.

  DoCmd.Close acForm, "MyForm"
  DoCmd.OpenForm "MyForm"

[RedGoneWILD]

closing and opening the form works, but is there something i'm doing wrong with the .refresh method?

also i have a form that opens when this form is closed...so if i were to do the close & open method, then the other form will open every time that the filter is applied

[Rawb]

.Refresh only updates the currently shown Records. If you want to update the list - and add new/remove old Records - then you need to use .Requery instead.

If you're still not seeing the changes then you can go really extreme and do the following:

RunCommand acCmdSaveRecord ' Save the Record you're looking at
Me.Requery ' Force the Recordset of the Form to update
Me.Repaint ' Force the Form to refresh visually

[RedGoneWILD]

okay, i dont think .refresh or .requery are meant for what i'm trying to do.
Me.Repaint doesn't want to work for me either.

I think maybe because i'm not looking at one record? The detail section lists ALL students, one on each line, just like a spreadsheet.

closing the form and opening it back up sort of works, but it complicates other things such as my form_open & form_close statements.

there has to be a way to update the form once a filter is applied.

[Rawb]

.Requery should work for what you're trying to do: Essentially it does the same thing that closing and opening the Form does - requests all the information again from the database.

Sure, you're performing the .Requery on the entire Form, but it should still be catching the one Record that's changed.

So I'm not sure why it's not working. . . I'll try playing around with it a little and see if I can come up with anything.

[Rawb]

Oh wait. . . Are you trying to change the contents of a Combo Box?

If so, you might want to change the Combo Box to an Unbound Object and do the refresh on it programmatically. Combo Boxes don't store information the same way a Text Box does - They use .RowSource instead of .Value - and that may be why it's not working.

[RedGoneWILD]

no, i am using a combo box to change the contents of a recordset. It is a split form if that makes a difference. No records are actually changing.

When the form opens, it is defaulted to "All" Students...so lets say its 100 records. They are listed in the "detail" section of the form and it looks just like a table. each row is one student record with other fields such as address & phone number. There is 100 students, so there is 100 rows. If 75 of the 100 students have a status of "Active", then when "Active" is selected, the 100 rows should change to 75

The combo box is located in the "form header" section, and it has 5 status choices ("All", "Active", "Inactive", "Graduate" & "Applicant"). Since it's defaulted to "All", then if the user selects something different like "Active" I want the form to refresh and only display the "Active" students in the "detail" section. Right now it is just applying the SQL statement to the qryStudentList, which is the query that i have listed as the source for the form. I have to close the form and then reopen it to see the "detail" section change. I want it to change as soon as the status selection in the combo box changes, without having to close and reopen the form to accomplish it.

So i think the reason .refresh, .requery, & .repaint aren't working is because no record is actually changing. Just the number of records that are returned and displayed is all that should be changing.

[Rawb]

Ah-HAH!

With a situation like this, you'll want to use a Subform. That way you can just change it (the Subform) with a Filter to only show the Records you want whenever your selection criteria changes.

[RedGoneWILD]

Well, My initial thought was to use a subform. But what's the point of access having a split form if you need subform anyway to have it refresh. Plus now I have to create a 2nd form just to put it back on the first form the way i had it already...lol, oh well

I just hope my controls will still work after I do all this

[Rawb]

Split Forms are to give you two different views of the same data. I don't think they were designed to allow you to Filter contents like you're trying to do.

I don't have Access 2k7 though (and thus have never used a Split Form), so who knows

[RedGoneWILD]

Alright, so I have no idea why this works and nothing else seems to, but here's how I got the datasheet portion to refresh in a split form.

When the combo box is changed by the user, it triggers the after update event...in there i placed these 3 lines of code

    Me.Filter = "Status = " & cboStatus.Value
    Me.FilterOn = True
    Me.FilterOn = False

If i use just the first line, nothing happens...if I use just the first two lines, nothing happens...for some reason it only works when i set .FilterOn = true, and then set .FilterOn = False right after it

again, im stumped, but it works...and that has made me happy