By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,137 Members | 1,575 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,137 IT Pros & Developers. It's quick & easy.

Refresh Datasheet on text change

P: 2

I am trying to refresh datasheet records as i type anything in the text box. (i.e. as i type each letter in a text box i would like the datasheet to be refreshed accordingly).

So, to do that i wrote the query below :
Expand|Select|Wrap|Line Numbers
  1. SELECT employee.surname, employee.firstname, employee.department
  2. FROM employee
  3. WHERE (((employee.surname) Like "*" & [forms]![KPI Manager]![txtLastName] & "*"));
So far it's all good.

My datasheet is reading data from the query above.

In VBA editor i wrote the query below :
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtLastName_Change()
  2.     Forms![KPI Manager].Form!KPIManager1.Form.Requery
  3.     Forms![KPI Manager].Form!KPIManager1.Form.Refresh
  5. End Sub
It does not work.

Any help would be greatly appreciated.

Sep 7 '08 #1
Share this Question
Share on Google+
3 Replies

Expert 5K+
P: 8,692
It appears as though you wish to dynamically modify the Record Source of a Sub-Form as each Character is typed into a Text Box on the Main Form. For this logic to work you will need to use the Change() Event of the Text Box, then Update the Record Source of the Sub-Form for each Character, as in:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtLastName_Change()
  2. Dim strSQL As String
  4. strSQL = "SELECT employee.surname, employee.firstname, employee.department " & _
  5.          "FROM employee WHERE employee.surname Like '*" & _
  6.           [Forms]![KPI Manager]![txtLastName].Text & "*';"
  8. Forms![KPI Manager]!KPIManager1.Form.RecordSource = strSQL
  9. End Sub
P.S. - This code assumes that KPIManager1 is the Name of your Sub-Form Control.
Sep 7 '08 #2

Expert 2.5K+
P: 3,532
You should be aware that although you can get this to work, it is very processor intensive, requerying after each key stroke! Most people that I know who have tried it end up scrapping it. The problem is that you enter a a letter, it requeries, but before that requery is finished runnning, you enter another letter, now you have 2 requeries running, and so forth, until the system gets bogged down. You'd be far better off either typing the entire name in, then hitting a button or using a combobox with AutoExpand set to On.

Linq ;0)>
Sep 7 '08 #3

P: 2

Thanks to both of you for your reply.

I just didn't want to use SQL code in the VBA editor. I wanted to kept it as a query. Just wanted to make it little bit more neat.

But thanks anyways. It was much apprecated.

Sep 8 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.