Find Record

[Karen Hart]

Re Access XP - method for locating desired record:

I have an unbound field in a continuous form where the user types the first
few letters of the last name and presses enter.
Then the following code brings the user to that record:

Private Sub Lookup_AfterUpdate()

On Error GoTo Quit
With CodeContextObject
DoCmd.GoToControl "LAST"
DoCmd.FindRecord .Lookup, acStart, False, , False, , True

End With

Quit:
End Sub

This code works fine. The control source of LAST is the LastName field of
the underlying table.
The problem comes when I change the control source of LAST to

=[LastName] & ", " & [FirstName]

My FindRecord command can't seem to find the record once I change the
control source to this.

Many thanks in advance.
Cheers,
Karen

 

[Penguin]

Not sure if this will help but try this:

DoCmd.ApplyFilter , "LastName = '" & Me![MyFieldName] & "'*"

Then make a control called LastName as set its visible property to No.

Hope this helps

 

[Karen Hart]

Penguin,
I'm sorry but I don't follow you. My unbound field where the user types the
first few characters of the last name they are searching for is called
[Lookup]. The field that contains the desired match is called [Last]. What
are you suggesting?
Thank you,
Karen

Not sure if this will help but try this:

DoCmd.ApplyFilter , "LastName = '" & Me![MyFieldName] & "'*"

Then make a control called LastName as set its visible property to No.

Hope this helps

Re Access XP - method for locating desired record:

I have an unbound field in a continuous form where the user types the
first
few letters of the last name and presses enter.
Then the following code brings the user to that record:

Private Sub Lookup_AfterUpdate()

On Error GoTo Quit
With CodeContextObject
DoCmd.GoToControl "LAST"
DoCmd.FindRecord .Lookup, acStart, False, , False, , True

End With

Quit:
End Sub

This code works fine. The control source of LAST is the LastName field of
the underlying table.
The problem comes when I change the control source of LAST to

=[LastName] & ", " & [FirstName]

My FindRecord command can't seem to find the record once I change the
control source to this.

Many thanks in advance.
Cheers,
Karen

 

[Penguin]

Try this:

Private Sub Lookup_AfterUpdate()
DoCmd.ApplyFilter , "Last = '" & Me![Lookup] & "'*"
End Sub

Hope this helps. Happy New Year

Penguin,
I'm sorry but I don't follow you. My unbound field where the user types the
first few characters of the last name they are searching for is called
[Lookup]. The field that contains the desired match is called [Last]. What
are you suggesting?
Thank you,
Karen

Not sure if this will help but try this:

DoCmd.ApplyFilter , "LastName = '" & Me![MyFieldName] & "'*"

Then make a control called LastName as set its visible property to No.

Hope this helps

Re Access XP - method for locating desired record:

I have an unbound field in a continuous form where the user types the
first
few letters of the last name and presses enter.
Then the following code brings the user to that record:

Private Sub Lookup_AfterUpdate()

On Error GoTo Quit
With CodeContextObject
DoCmd.GoToControl "LAST"
DoCmd.FindRecord .Lookup, acStart, False, , False, , True

End With

Quit:
End Sub

This code works fine. The control source of LAST is the LastName field of
the underlying table.
The problem comes when I change the control source of LAST to

=[LastName] & ", " & [FirstName]

My FindRecord command can't seem to find the record once I change the
control source to this.

Many thanks in advance.
Cheers,
Karen

 

[Karen Hart]

Sorry, Penguin, it doesn't find or filter the desired match. Anyone else
have any ideas? Please note, I'm trying to "Find" a record in a continuous
form, not "Filter." This is why I am trying to use the "DoCmd.FindRecord"
instead of "DoCmd.ApplyFilter."

Try this:

Private Sub Lookup_AfterUpdate()
DoCmd.ApplyFilter , "Last = '" & Me![Lookup] & "'*"
End Sub

Hope this helps. Happy New Year

Penguin,
I'm sorry but I don't follow you. My unbound field where the user types
the
first few characters of the last name they are searching for is called
[Lookup]. The field that contains the desired match is called [Last]. What
are you suggesting?
Thank you,
Karen

Not sure if this will help but try this:

DoCmd.ApplyFilter , "LastName = '" & Me![MyFieldName] & "'*"

Then make a control called LastName as set its visible property to No.

Hope this helps

Re Access XP - method for locating desired record:

I have an unbound field in a continuous form where the user types the
first
few letters of the last name and presses enter.
Then the following code brings the user to that record:

Private Sub Lookup_AfterUpdate()

On Error GoTo Quit
With CodeContextObject
DoCmd.GoToControl "LAST"
DoCmd.FindRecord .Lookup, acStart, False, , False, ,
True

End With

Quit:
End Sub

This code works fine. The control source of LAST is the LastName field
of
the underlying table.
The problem comes when I change the control source of LAST to

=[LastName] & ", " & [FirstName]

My FindRecord command can't seem to find the record once I change the
control source to this.

Many thanks in advance.
Cheers,
Karen

 

[Doug Taylor]

If you're searching for a last name then why not just search that field for
the match? Also, I would search the recordset for a matching LastName using
a bookmark instead of DoCmd.FindRecord. If for some reason this is not an
option the following should correct your problem.

If you want to search a control with combined fields (LastName & ", "
FirstName) then you will need to combine the fields within the RecordSource.

Sql Statement (example):
SELECT LastName & ", " & FirstName AS LAST, FirstName, LastName
FROM SomeTable;

Now your "Lookup_AfterUpdate()" will work

Sorry, Penguin, it doesn't find or filter the desired match. Anyone else
have any ideas? Please note, I'm trying to "Find" a record in a continuous
form, not "Filter." This is why I am trying to use the "DoCmd.FindRecord"
instead of "DoCmd.ApplyFilter."

Try this:

Private Sub Lookup_AfterUpdate()
DoCmd.ApplyFilter , "Last = '" & Me![Lookup] & "'*"
End Sub

Hope this helps. Happy New Year

Penguin,
I'm sorry but I don't follow you. My unbound field where the user types
the
first few characters of the last name they are searching for is called
[Lookup]. The field that contains the desired match is called [Last]. What
are you suggesting?
Thank you,
Karen


Not sure if this will help but try this:

DoCmd.ApplyFilter , "LastName = '" & Me![MyFieldName] & "'*"

Then make a control called LastName as set its visible property to No.

Hope this helps

Re Access XP - method for locating desired record:

I have an unbound field in a continuous form where the user types the
first
few letters of the last name and presses enter.
Then the following code brings the user to that record:

Private Sub Lookup_AfterUpdate()

On Error GoTo Quit
With CodeContextObject
DoCmd.GoToControl "LAST"
DoCmd.FindRecord .Lookup, acStart, False, , False, ,
True

End With

Quit:
End Sub

This code works fine. The control source of LAST is the LastName field
of
the underlying table.
The problem comes when I change the control source of LAST to

=[LastName] & ", " & [FirstName]

My FindRecord command can't seem to find the record once I change the
control source to this.

Many thanks in advance.
Cheers,
Karen

 

[John Vinson]

Sorry, Penguin, it doesn't find or filter the desired match. Anyone else
have any ideas? Please note, I'm trying to "Find" a record in a continuous
form, not "Filter." This is why I am trying to use the "DoCmd.FindRecord"
instead of "DoCmd.ApplyFilter."

Try using the Form's RecordsetClone:

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "Last = " Chr(34) & Me![Lookup] & "*" & Chr(34)
If rs.NoMatch Then
MsgBox "This name not found"
Else
Me.Bookmark = rs.Bookmark 'synch the form to the found record
End If


John W. Vinson[MVP]

 

[Guest]

why don't you create a macro to use find command from access...
you can create the macro as follow
new macro
then in the action column choose RunCommand
then in the command line type find
Save the macro and give it the the name Find
go to your form. create a command botton and Name it as Find
then set its Event to

Private Sub Find_Click()
On Error GoTo Err_Find_Click
Dim stDocName As String
stDocName = "Find"
DoCmd.RunMacro stDocName
Exit_Find_Click:
Exit Sub
Err_Find_Click:
MsgBox Err.Description
Resume Exit_Find_Click
End Sub
after that save your form and run it. when you click on the find botton then
you will get the access find and replace window where you can use it to find
anything in the form.
hope that will help.
with respection to all opinions.
Paul.

Sorry, Penguin, it doesn't find or filter the desired match. Anyone else
have any ideas? Please note, I'm trying to "Find" a record in a continuous
form, not "Filter." This is why I am trying to use the "DoCmd.FindRecord"
instead of "DoCmd.ApplyFilter."

Try using the Form's RecordsetClone:

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "Last = " Chr(34) & Me![Lookup] & "*" & Chr(34)
If rs.NoMatch Then
MsgBox "This name not found"
Else
Me.Bookmark = rs.Bookmark 'synch the form to the found record
End If


John W. Vinson[MVP]