Continuous Form - Quick Find by Typing

[CraigBFG]

I have a continuous form and want to be able to find an entry by starting to type it.
So If I have a list of 300 items, and I start to type in "Re", the form would zip down to the "Re...." part of the list.

Any ideas how I do this?
Thanks.

 

[ColinEssex]

There is an example in the Northwind database of a form with alpha letters at the bottom so that you select say "R" and you get all the entries beginning with "R" listed then select from there.

Not sure if this helps but I've used it several times and its very user friendly.

Col

 

[CraigBFG]

That's kind of the idea, but I'd prefer if the user could, effectively type in thin air, and then goto the appropriate part of this list.

Thanks

 

[namliam]

Private Sub Form_KeyPress(KeyAscii As Integer)
'allow for 2 secs between keystrokes
    Dim rs As DAO.Recordset
    If Time() - timePrevKey > CDate("00:00:02") Then txtAllKey = ""
    timePrevKey = Time()
    txtAllKey = txtAllKey & Chr(KeyAscii)
    Set rs = Me.RecordsetClone
    rs.FindFirst ("[Your Field] like '" & txtAllKey & "*'")
    Me.Bookmark = rs.Bookmark
End Sub

Will do what you want, i dont know if it will ZIP down (depends on CPU and table size) Furthermore it prevents from any changes beeing made in anything in this form...

Regards

 

[CraigBFG]

Hi

That looks great but I cannot get it to function, I've made the only change "field name".

Private Sub Form_KeyPress(KeyAscii As Integer)
'allow for 2 secs between keystrokes
Dim rs As DAO.Recordset
If Time() - timePrevKey > CDate("00:00:02") Then txtAllKey = ""
timePrevKey = Time()
txtAllKey = txtAllKey & Chr(KeyAscii)
Set rs = Me.RecordsetClone
rs.FindFirst ("[Site] like '" & txtAllKey & "*'")
Me.Bookmark = rs.Bookmark
End Sub

Is there anything else that I should be doing?

 

[SforSoftware]

Create an additional textbox on your form, named txtAllKey
Here the user can see what (s)he was typing.

 

[namliam]

Dim timePrevkey as date
Dim txtAllKey as string

Private Sub Form_KeyPress(KeyAscii As Integer)


Add the 2 dims before the sub (form variables) sorry bad copy/paste on my part...

Regards

 

[CraigBFG]

Hi

Thanks, tried that but if I put those 2 DIMS before the keypress sub, then Access places them in the previous routine.

If I place them inside the sub, then it doesn't work either.

Any ideas?

Thanks

 

[namliam]

Place them at the top of the module ...

Option Compare Database
Option Explicit
Dim timePrevkey as date
Dim txtAllKey as string

Private Sub Form_KeyPress(KeyAscii As Integer)

You must also set Key preview (in the form properties) to Yes
and have the On key press event set to [Event Procedure]

Regards

 

[CraigBFG]

Sorry, I must be thick but I can't get it to function. Here is the code that is attached to the "OnKeyPress" function.

I get a "user defined type not defined" error

Option Compare Database

Dim timePrevkey As Date
Dim txtAllKey As String

--------------------------------
Private Sub Form_KeyPress(KeyAscii As Integer)
'allow for 2 secs between keystrokes

Dim rs As DAO.Recordset

If Time() - timePrevkey > CDate("00:00:02") Then txtAllKey = ""
timePrevkey = Time()
txtAllKey = txtAllKey & Chr(KeyAscii)
Set rs = Me.RecordsetClone
rs.FindFirst ("[Site] like '" & txtAllKey & "*'")
Me.Bookmark = rs.Bookmark
End Sub

 

[Mile-O]

1. Open a code module

2. Goto Tools -> References

3. Find Microsoft Data Access Objects 3.6 and check it

4. Move its priority above Microsoft ActiveX Data Objects (near the top)

It should work now.


It's because DAO is the main data manipulation tingy method in Access 97 and ADO is the preferred method in later versions which, it appears you are using.

 

[CraigBFG]

That works an absolute treat - thanks to everyone.

Now, a twist, can the code be modified so that it searchs the entire field not just the beginning?

I have to ask, because I'll be asked - you know what these users are like ;-) BOFH!

 

[CraigBFG]

Mile - does this DAO 3.6 only have to be moved once, in the db or on each and every pc that's accessing the db?

 

[SforSoftware]

Now, a twist, can the code be modified so that it searchs the entire field not just the beginning?

Change this:

rs.FindFirst ("[Site] like '" & txtAllKey & "*'")

into this:

rs.FindFirst ("[Site] like '*" & txtAllKey & "*'")

The story about DAO is amazing me in this case, because rs is declared as DAO.Recordset, so always DAO is used undependend of the position in References? Or am I missing something?

 

[Mile-O]

Mile - does this DAO 3.6 only have to be moved once, in the db or on each and every pc that's accessing the db?

I don't know as I've never had experience of using a later version of Access than 97 (other than my home computer which isn't linked to see how things go on more than one computer.

I'd guess that the db itself would store the details.

 

[CraigBFG]

In the words of Alan Partridge - "back of the net!"

 

[namliam]

In my experience its stored by the DB. It will even upgrade if you upgrade Office (finaly something done right by M$ )

The story about DAO is amazing me in this case, because rs is declared as DAO.Recordset, so always DAO is used undependend of the position in References? Or am I missing something?

That is true however in general since ADO is new to 'the scene' most people use DAO commands but do not do DAO.Recordset. Thus moving it to the top will prevent them from running into trouble. I know i am still not constantly doing DAO.
&%&*$%^%^$ Darnation.... will i ever learn???

Regards

The Mailman