How to go to a certain record in a form (MS Access 2003)

[LarryM]

I have a - to my believe - VERY basic problem but cannot find ANY solution.


Have googled and searched a lot!

I have a form that shows single records.
You can browse the records one by one.
Instead of browsing through the whole amount of posts I would like the user to assign a search value for the wanted record and have the form jump to just that record.

The name of the bound table is MyTable.
I have in the form a textbox TxtName bound to a field Name.
I would like to search the record that in the field Name has the value 'John'.
So I have a textbox TxtSearch where I can put in 'John'.
Now, In the AfterUpdate code for TxtSearch it seems that I can use at least either DoCmd.Gotorecord or DoCmd.Findrecord.
Should be very simple, something like this:
In the field Name, find 'John' and go to that record!
But it really does not seem to be that simple!

The Findrecord method looks like this:
'DoCmd.FindRecord "Smith",, True,, True'
which in my case would be 'DoCmd.Findecord TxtSearch,,True,,True'.
The odd thing is that this method does not say in WHICH field the search should happen.
And it does NOT find the record, it just randomly jumps to any record.

The Gotorecord method offers e g this form:
'DoCmd.Gotorecord acDataform, "MyTable", acgoto, 3'
which means that you can go to the record no 3, but NOT find anything based on a value in the field Name.

I cannot imagine that there is NO way to find a record on a form based on a value in a field.
It just seem to be very very well hidden in the cyberspace!

Can you please help me out?

Larry

[ItsMe]

I have never used the Docmd.FindRecord method before. However, I looked at the MSDN and it seems there is not a parameter provided for the domain (as you mentioned). So, I would attempt to Set Focus on the control that is bound to the column you wish to use. I would also make sure to set the parameter for searching that specific column with the appropriate argument. Perhaps True is the correct argument.
'DoCmd.FindRecord "Smith",, True, True, True'
https://msdn.microsoft.com/en-us/lib...ffice.11).aspx

EDIT:
I just found a better link
https://msdn.microsoft.com/en-us/lib.../ff835361.aspx
So you should be able to use True, or -1, or acCurrent for the OnlyCurrentField parameter.

[LarryM]

Hi ItsMe,
thanks for your interest!
Since the focus is in the TxtSearch control when the search event starts I set the focus to the desired TxtName in code before the Findrecord happens, like this:
"TxtSearch_AfterUpdate()
TxtName.SetFocus
DoCmd.FindRecord TxtSearch, , True, True, True"
and also:
"TxtSearch_AfterUpdate()
TxtName.SetFocus
DoCmd.FindRecord "John",,True,True,True"

But None of these examples perform the search.
The form jumps to different records after every repeated try to do the search.

'Findrecord' sounds like a method to use, but I cannot get it to work.
Maybe 'Findrecord' is not the method to use?

[ItsMe]

It might be the wrong thing to use. I took another look at the MSDN website and they do not mention if it returns a value or what the method does. It is possible it will navigate to the record (the desired affect).

I would try the following. Create a button. Give it a name, maybe cmdSearch. In that button's Click Event place ...

Me.TxtName.SetFocus
DoCmd.FindRecord "John",,True,True,True

The reason I say this is because the AfterUpdate event and other events that you have mentioned are ususally fired in the middle of a sequence of events. I do not recall the exact sequence but when a user moves focus to another control, control B, control A's events might be something like ...
LostFocus
Afterupdate
Control B - OnFocus

So if you tell the afterupdate of Control B to set focus somewhere, it will do that. However, this may be a conflict with what the User is setting focus on. So, I would test it in a better control environment like a button click event.

[Bulzie]

Most times you don't want the user to have to scroll through records on a single form. They are usually wanting to either add a new record, or find a previous record to view or edit. So can to take away the ability for them to scroll through the records and just limit the form to 1 record at a time? Put a criteria on your form recordsource for that TxtSearch field, then in the AfterUpdate on that field, Requery the form and it would just show that 1 record they searched for. I would do a combo box and not a free text box if possible so the search is limited to what is in your table.

Another thing, if the field they are searching is not unique for each record, then the system will not know which record to pull back so will give you all of them. So searching for "John" in a FirstName field for example will not pull back a single record if there are more Johns in the system.

But again, instead of trying to go to a record, I would pull back just 1 record by using criteria and a Requery.

[Bulzie]

If you do want to go to a record, try this in the AfterUpdate on txtSearch field. But again, if you have more then one "John" as your example, I guess this will stop at the first one.

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[TxtName] = " & Str(Nz(Me![TxtSearch], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

[LarryM]

Hi,
thank you for your interest!!

(TxtName bound to 'Name' is a Primary key and there is only 1 name in the table - an odd example because 'Name' is actually another String. I just called it 'Name' for simplicity. Since it is a primary key there are not many records with the same 'Name'- which could have complicated it)

And Suddenly I got it to work (!):
I could use the AfterUpdate event like this (no need for an extra button):

Private Sub TxtSearch_AfterUpdate()
    TxtName.SetFocus
    DoCmd.FindRecord TxtSearch.Value, , True, True, True
End Sub

This jumps to the wanted record ! And the browsing possibility is kept - should the user want to do that instead - because the recordset does not change. But the TxtSearch value must be exactly the same (case) as in the searched Name.

I am very glad that your commitment helped me to find this.
'TxtName.Setfocus' was the key to this. Thank you!

[ssanfu]

If you use

DoCmd.FindRecord TxtSearch.Value, , False, True, True

the search will NOT be case sensitive.


Be aware that "Name" is a reserved word in Access and shouldn't be used for object names. "Name" is a property of objects.

[LarryM]

Since many parameters in the Findrecord line are 'True' or 'False' by default you don't have to write them out.
So the code example for a search in a field 'MyName' would be like this:

Private Sub TxtSearch_AfterUpdate()
    TxtMyName.SetFocus
    DoCmd.FindRecord TxtSearch
End Sub

And the search is not sensitive to the case mode.
So searching 'john' will find 'John'.
And searching 'jo*' could find 'John'.

If this code example - where you in code set focus on the control that holds the field that you want FindRecord to search in - had been given on the sites that try to describe the FindRecord method than this thread would have been unnecessary.