473,320 Members | 2,035 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Refresh Datasheet on text change

Hi,

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
  4.  
  5. End Sub
It does not work.

Any help would be greatly appreciated.

Thanks
Rashed
Sep 7 '08 #1
3 3408
ADezii
8,834 Expert 8TB
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
  3.  
  4. strSQL = "SELECT employee.surname, employee.firstname, employee.department " & _
  5.          "FROM employee WHERE employee.surname Like '*" & _
  6.           [Forms]![KPI Manager]![txtLastName].Text & "*';"
  7.  
  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
missinglinq
3,532 Expert 2GB
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
Hi,

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.

Cheers
R
Sep 8 '08 #4

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

Similar topics

6
by: laura | last post by:
I'm doing a page which gathers some text in the form of a text box in a <form>. This text is saved to a text file, notices.txt and I want to be able to display the saved text on the page, as soon...
0
by: Akinia | last post by:
Hi every body I've got a little problem with my form ("frm_Company"). It is divided in two parts: - first one is filled with some text fields over the company. - second one is filled with a tab...
2
by: deko | last post by:
I'd like to be able to do in-cell editing of a datasheet and have other fields automatically update. I know... the easy answer is to use Excel, but I'm wondering if I can pull this off in Access...
5
by: Andrew Chanter | last post by:
I have a situation where I am using an unbound dialog form to update data in an Access 2002 split back end / front end scenario. The data update is done via an ADO call (direct to the back end...
2
by: Robert Mileski | last post by:
I've made some program in Visual Basic 2005 that works with changing files and folders. After I've finished it, the main problem is to refresh the Windows OS. I mean the same thing as when we press...
0
by: angiemc | last post by:
Hi, I have a question about AJAX and ASP.Net controls. I'm working on a system that requires pages to be generated dynamically based on XML to provide the layout (eg text boxes, dropdowns, save...
7
AccessIdiot
by: AccessIdiot | last post by:
This is driving me nuts. I have an unbound textbox (txt_SumSize) in my subform that holds the total of a field (Debitage_Size_Quantity)seen in datasheet view (control source =Sum(). I have an...
7
by: seanmatthewwalsh | last post by:
Hi I have a page (default.aspx) that pulls it's HTML from a database. I then have a "content management" page (editpage.aspx) that allows the user to edit the HTML in the database. When the...
5
by: tshad | last post by:
I have an interface I am using to get access to some of the objects on my form: a textbox (Status) and my statusbar (StatusBar). In my class, which is actually in another class from my form I have...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.