Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Access 2007 - Subform refresh fun

Posted on 2011-10-15
9
Medium Priority
?
318 Views
Last Modified: 2012-05-12
Hello all,

I would like to refresh a subform on a form every time I enter a letter of someones surname into a textbox.  If I use the "me.refresh" command everything works a treat. However, it selects the textbox and highlights all of the text so when the user goes to enter the second letter of their search it overtypes the first one.  Ideally I would just like the subform refreshed each time a key is pressed in the textbox mentioned above, if it is possible?

Form is: frmStaffToTask
Subform is: frmMemberAreaDetails
Textbox is: txtSurnameQuickSearch

Any help would be appreciated, many thanks in advance, Alan.

PS

I have a workaround which keeps the me.refresh and then sets focus of the textfield and then goes to the end of the textfield but it just does not feel clean.
0
Comment
Question by:Canders_12
  • 5
  • 3
9 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 36973899
I guess I'm wondering why you do not use a combo box with the autocomplete property set on?  Can you explain why the subform?
0
 

Author Comment

by:Canders_12
ID: 36974020
this subform is a complex pivot which will not be able to be recreated in a combo box.

Many thanks, Alan.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 36974106
If you can specify a field in a recordset for a text box, you can do the same thing for that field in a combo box.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:Canders_12
ID: 36974112
GRayL:

It is not a standard recordset.  It is a subform, which is a pivot and not a standard for, that is linked to a query.  If you need evidence I can upload the database but am still keen to understand how I can do as previously asked as I feel I will need this for future forms.

Many thanks, Alan.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 36974169


In an unbound textbox on my form I entered this code:

Private Sub tbxTableName_Change()
  MsgBox Me.tbxTableName.Text
End Sub

Every time i entered a letter I got a message containing the letters type to that point.  I think you should use the Change() event of your subform's textbox to 'process' your data - note there is no refresh required.
0
 

Author Comment

by:Canders_12
ID: 36974191
GRayL:

I am sorry, I have obviously not explained myself clearly.  Whatever is in that textbox is what is driving the query that is driving the subform pivot.  Therefore I need the subform to refresh as the textbox field is being updated letter by letter.

Many thanks, Alan.
0
 
LVL 19

Accepted Solution

by:
MINDSUPERB earned 2000 total points
ID: 36975148
Alan,

"PS
I have a workaround which keeps the me.refresh and then sets focus of the textfield and then goes to the end of the textfield but it just does not feel clean. "

With the last statement of your question, you did not mention your workaround, so I decided to post the approach that I used which might be similar with yours. I got this function through google search a year ago.

Call the function with the following code:

Private Sub txtSurnameQuickSearch_Change()
Me.Refresh
pfPositionCursor Me.txtSurnameQuickSearch, Len(Me.txtSurnameQuickSearch & "")
End Sub

Sincerely,
Ed
Public Function pfPositionCursor(ctl As Control, lngWhere As Long)
 
    Select Case ctl.ControlType
        Case AcControlType.acTextBox, AcControlType.acComboBox
 
            ctl.SelStart = lngWhere
            ctl.SelLength = 0
 
        Case Else
            'Do Nothing
    End Select
 
End Function

Open in new window

0
 

Author Comment

by:Canders_12
ID: 37047497
Many apologies all,

A personal family issue has meant all projects went on hold.  I appreciate all your responses and will be going with the MindSuperb options.

Once again, many apologies, Alan.
0
 

Author Closing Comment

by:Canders_12
ID: 37047519
Useful solution to difficult problem.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question