FindFirst syntax

[WalkieTalkie]

Hi
I am trying to use FindFirst to find a record on a form, by entering the ID into a textbox called txtGoTo. The form is bound to a table containing the record. I have searched this site and found the required syntax but still it wont work. What am I doing wrong? Thanks in advance for any help.

rs.FindFirst "[PatientID] Like '" & txtGoTo & "*'"

 

[MajP]

Can you add a debug so we can see how this resolves

dim strWhere
strWhere = "[PatientID] Like '" & txtGoTo & "*'"
debug.print strwhere
rs.FindFirst strWhere

post the debug.print from the immediate window

 

[WalkieTalkie]

Thanks, MajP.
The first time I tried, it was just resulting in "*". So I modified the code like so:

rs.FindFirst "[PatientID] Like '" & txtGoTo.Text & "*'"

and now the debug result is:

[PatientID] Like 'cn*'

but it is still not finding the record on the form. Any ideas?

 

[MajP]

Is rs a recordset or a recorsetclone. If you do this
dim rs as dao.recordset
set rs = me.recordsetclone
the findfirst will not move in the form
if you do this
set rs = me.rcordset
it will move

You can also check to see if a record was found

d

im rs as dao.recordset
dim strwhere
set rs = me.recordset 'do not use a clone
me.txtgoto.setfocus
strWhere = "[PatientID] Like '" & txtGoTo.text & "*'"
rs.FindFirst strWhere
debug.print strWhere & " was not found: " & rs.nomatch

what is the value of the record you are trying to find?

 

[WalkieTalkie]

Hi again
You're correct: I was using recordsetclone.

I've pasted your code, and the debug.print produces this:

[PatientID] Like 'c*' was not found: False

And, the first matching record is found. So that's great. Thanks!
I repeated the txtGoTo.setfocus so it triggers after the record is found (so the user can enter more than one character into the txtGoTo). However, after each character of the ID is entered into txtGoTo, the whole of txtGoTo is now selected, so the user is likely to unwittingly writeover what is already there. I hope that makes sense? How can I keep the cursor at the end of the string in txtGoTo?

Here's my code so far:

Dim rs As dao.Recordset
        Dim strwhere
        Set rs = Me.Recordset 'do not use a clone
        Me.txtGoTo.SetFocus
        strwhere = "[PatientID] Like '" & txtGoTo.Text & "*'"
        rs.FindFirst strwhere
        Me.txtGoTo.SetFocus
Debug.Print strwhere & " was not found: " & rs.NoMatch

 

[MajP]

there are two properties of a text box. The starting postion "selstart" of the selection and the length "selLength" of the selection.

https://msdn.microsoft.com/en-us/library/bb256206(v=office.12).aspx

If you want it at the end you have to set the selstart to the length of the text.
In the on enter event you can do something like
ctrlName.selStart = len(ctrlName.text)
Might have to add 1 to that
ctrlName.selStart = len(ctrlName.text)+1
So using those two properties and setting the start and length you could do something like selecting a part of a memo field.
I added the setfocus originally because you can only call the .text property of a control if it has focus. You can call the value property at anytime. But as you showed with an unbound textbox you can have a point in time where the text property and value are not the same. The text has not been committed yet.

One thing you may want to consider is using a combobox of all patient ID versus a textbox. Comboboxes have autofind and will ensure the user does not enter a value that does not exist.