×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

FindFirst syntax

FindFirst syntax

FindFirst syntax

(OP)
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.

CODE -->

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

RE: FindFirst syntax

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

CODE -->

dim strWhere
strWhere = "[PatientID] Like '" & txtGoTo & "*'"
debug.print strwhere
rs.FindFirst strWhere 
post the debug.print from the immediate window

RE: FindFirst syntax

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

CODE -->

rs.FindFirst "[PatientID] Like '" & txtGoTo.Text & "*'" 
and now the debug result is:

CODE -->

[PatientID] Like 'cn*' 
but it is still not finding the record on the form. Any ideas?

RE: FindFirst syntax

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

CODE -->

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?

RE: FindFirst syntax

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

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

CODE -->

[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:

CODE -->

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 

RE: FindFirst syntax

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(...
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close