Recordsource of subform

[tempk]

Hi everybody,
I have a form with its tabbed subforms' recordsource determined by the code:

Private Sub childTvl_Enter()

Me.childTvl.Form.RecordSource = "SELECT * FROM tblTvlInput WHERE tblTvlInput.Dept = """ & Forms.frmBudget.txtDept & """"

End Sub

This is to filter the huge data by department and only allowing the specified department to view their code. However, I find it slow and pesky as it only shows the required information when the user clicks on the child. I have tried putting the code above elsewhere but to no avail.

Is there a better way of speeding the filter as well as showing the user his own records when he clicks on the tab?

Thanks in advance. =)
You guys here are saviours!

 

[tempk]

Refresh

There is also an issue of refreshing the data automatically if changes are made..I have to click on another tab and return to the page where changes were made to see the refreshed changes...Been bugging me for quite some time..

 

[MarkK]

Try using the Change event of the tab control. You'll need to use the Value property of the control to determine which tab is current, but it seems preferable to the Enter event of the subform control.

You may find a speed improvement in the query if you don't return all the fields using

"SELECT * FROM tblWhatever;"

.
It's my understanding that queries are quicker if you explicitly state the fields you want returned, and hopefully this is fewer than all the fields in the table...

"SELECT OnlyThe, FieldsYou, ReallyNeed FROM tblWhatever;"

 

[tempk]

Hey..didn't know tab on change could be used. Seems noticeably faster but I need to test it over the network. Had tried using gotfocus, lostfocus, blah blah...

That would solve the problem of the refresh too, I think.

Thanks a lot!