28-Jul-2006, 08:27 AM
|
 | Guest | | | | | | | | |
| Re: Record Find Problem
Graham,
You are a genius. Thank you for your help. I was really stumped. I now have Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12100-record-find-problem.html
a supplemental question. I have to figure out how to input the find. I am
using an Input mask, and so teh data looks like this (203) 334-9086 when i
just search for the numbers it fails to find a match. If i input it as (203)
334-9086 it works. How do I get around this?
Thanks in advance???
Bob Smith
"Graham Mandeno" wrote:
> Hi Robert
>
> You didn't answer my question about whether this is an MDB or an ADP. I am
> now assuming the latter.
>
> This means that the form's Recordset and RecordsetClone properties will be
> ADODB Recordsets, not DAO Recordsets. The two objects have markedly
> different properties and methods. Specifically, the differences that are
> pertinent to your code are:
>
> 1. DAO has a FindFirst method, while with ADO you must use MoveFirst
> followed by Find.
>
> 2. DAO has a NoMatch property, while with ADO you must use EOF.
>
> 3. With ADO, each reference to RecordsetClone returns a new instance, while
> with DAO each reference returns the same instance. Therefore with ADO you
> must assign the RecordsetClone to a Recordset variable.
>
> So, your code should look like this:
>
> Private Sub cmdFind_Click()
> Dim strHome_Phone_Number As String
> Dim rsc As ADODB.Recordset
> strHome_Phone_Number = InputBox( _
> "Enter the Home phone Number for the Customer you want to locate")
> Set rsc = Me.RecordsetClone
> With rsc
> .MoveFirst
> .Find "[Home_Phone] = '" & strHome_Phone_Number & "'"
> If .EOF Then
> MsgBox "Home Phone Number " & strHome_Phone_Number & " Not
> Found!!"
> Else
> Me.Bookmark = .Bookmark
> End If
> End With
> Set rsc = Nothing
> End Sub
>
> --
> Good Luck!
>
> Graham Mandeno [Access MVP]
> Auckland, New Zealand
>
> "robert.f.smith@sbcglobal.net"
> wrote in message
> news:173F777D-EE84-44F1-B3BF-195F88D76668@microsoft.com...
> > Here is the latest code!
> >
> > Private Sub cmdFind_Click()
> > 'Dim strClientID As String
> > Dim varBookmark As Variant
> > Dim strHome_Phone_Number As Variant
> > 'Store the book of the current record
> > varBookmark = Me.Recordset.Bookmark
> >
> > 'Attempt to locate another client
> > strHome_Phone_Number = InputBox("Enter the Customers Home phone Number
> > for the Customer you want to locate")
> > Dim rst As ADODB.Recordset
> > Set rst = New ADODB.Recordset
> >
> > ' Establish the connection and cursor type,
> > ' and open active recordset
> > rst.ActiveConnection = CurrentProject.Connection
> > rst.CursorType = adOpenDynamic
> > rst.Open "Select * from tblCustomers"
> > With Me.RecordsetClone
> > .FindFirst "[Home_Phone] = '" & strHome_Phone_Number & "'"
> > If .NoMatch Then
> > MsgBox "Home Phone Number " & strHome_Phone_Number & " Not Found!!"
> > Else
> > Me.Bookmark = .Bookmark
> > End If
> > End With
> >
> >
> >
> >
> >
> >
> > 'Me.Recordset.Find "LastName = " & strCustomerName, Start:=1
> >
> > 'If client not found, display a message and return to
> > 'the original record
> > If Me.Recordset.EOF Then
> > MsgBox "Customer Name " & strHome_Phone_Number & " Not Found!!"
> > Me.Recordset.Bookmark = varBookmark
> >
> > 'If client found, synchronize the form with the
> > 'underlying recordset
> > Else
> > Me.Bookmark = Me.Recordset.Bookmark
> > End If
> > End Sub
> >
> > "robert.f.smith@sbcglobal.net" wrote:
> >
> >> Graham,
> >> I actually do want to compare phone numbers but was doing some debugging
> >> on
> >> my own and changed it to last name for a search. I will use phone number,
> >> but
> >> when I try you code I get a metthod error.
> >> Object does not support this property or method.
> >> Whats up with that?
> >>
> >>
> >> "Graham Mandeno" wrote:
> >>
> >> > Actually, having read a bit more of your original post, I can't see how
> >> > the
> >> > code is going to work at all. You are trying to equate bookmarks from
> >> > two
> >> > entirely different recordsets. If it's an MDB application then
> >> > furthermore
> >> > you are equating bookmarks from an ADODB recordset and a DAO recordset.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12100
> >> >
> >> > You don't say if this is an MDB or an ADP. If it's an MBD, then try
> >> > this
> >> > code:
> >> >
> >> > With Me.RecordsetClone
> >> > .FindFirst "[HomePhoneNumber] = '" & strHome_Phone_Number & "'"
> >> > If .NoMatch Then
> >> > MsgBox "Home Phone Number " & strHome_Phone_Number & " Not
> >> > Found!!"
> >> > Else
> >> > Me.Bookmark = .Bookmark
> >> > End If
> >> > End With
> >> >
> >> > Once again, note the single quotes.
> >> >
> >> > This might also work for an ADP but I'm not sure.
> >> > --
> >> > Good Luck!
> >> >
> >> > Graham Mandeno [Access MVP]
> >> > Auckland, New Zealand
> >> >
> >> >
> >> > "Graham Mandeno" wrote in message
> >> > news:OKAuCOWmGHA.4212@TK2MSFTNGP03.phx.gbl...
> >> > > Hi Robert
> >> > >
> >> > > For a start, you appear to be trying to compare the phone number that
> >> > > has
> >> > > been entered with the LastName field, not the HomePhoneNumber field:
> >> > >
> >> > >> strsql = "Select * from tblCustomers WHERE [LastName] =
> >> > >> ('strHome_Phone_Number')"
> >> > >
> >> > > Also, you are not actually comparing with what the user has typed in,
> >> > > but
> >> > > with the string "strHome_Phone_Number". Presumably there are no
> >> > > records
> >> > > with that as a phone number!
> >> > >
> >> > > So, probably what you want is something like this:
> >> > >
> >> > >
> >> > > strsql = "Select * from tblCustomers WHERE [HomePhoneNumber] = '"
> >> > > _
> >> > > & strHome_Phone_Number & "';"
> >> > >
> >> > > (Note the two single quotes - one after the = and one before the 
> >> > > --
> >> > > Good Luck!
> >> > >
> >> > > Graham Mandeno [Access MVP]
> >> > > Auckland, New Zealand
> >> > >
> >> > > "robert.f.smith@sbcglobal.net"
> >> > > wrote in
> >> > > message
> >> > > news:4AEC3F62-46BE-4769-800D-5097EF9FF4FF@microsoft.com...
> >> > >> Hi,
> >> > >> Im trying to do a record find in access on a form i created.
> >> > >> It seems to work fine when i click the find button and input
> >> > >> something to
> >> > >> search for, but it doesnt seem to change to the correct record.
> >> > >> here is my form code
> >> > >>
> >> > >> Private Sub cmdFind_Click()
> >> > >> 'Dim strClientID As String
> >> > >> Dim varBookmark As Variant
> >> > >> Dim strHome_Phone_Number As Variant
> >> > >> Dim strsql As String
> >> > >>
> >> > >> 'Store the book of the current record
> >> > >> varBookmark = Me.Recordset.Bookmark
> >> > >>
> >> > >> 'Attempt to locate another client
> >> > >> strHome_Phone_Number = InputBox("Enter the Customers Home phone
> >> > >> Number
> >> > >> for
> >> > >> the Customer you want to locate")
> >> > >>
> >> > >> Dim rst As ADODB.Recordset
> >> > >> Set rst = New ADODB.Recordset
> >> > >> strsql = "Select * from tblCustomers WHERE [LastName] =
> >> > >> ('strHome_Phone_Number')"
> >> > >> ' Establish the connection and cursor type,
> >> > >> ' and open active recordset
> >> > >> rst.ActiveConnection = CurrentProject.Connection
> >> > >> rst.CursorType = adOpenDynamic
> >> > >> rst.LockType = adLockOptimistic
> >> > >>
> >> > >> 'rst.Open "Select * from tblCustomers WHERE [LastName] =
> >> > >> ('strHome_Phone_Number')", Options:=adCmdText
> >> > >> ' rst.Open "Select * from tblCustomers "
> >> > >> rst.Open strsql
> >> > >> MsgBox ("I am here")
> >> > >>
> >> > >>
> >> > >>
> >> > >> 'Me.Recordset.Find "LastName = " & 'strHome_Phone_Number', Start:=1
> >> > >>
> >> > >> 'If client not found, display a message and return to
> >> > >> 'the original record
> >> > >> If Me.Recordset.EOF Then
> >> > >> MsgBox "Home Phone Number " & strHome_Phone_Number & " Not Found!!"
> >> > >> Me.Recordset.Bookmark = varBookmark
> >> > >>
> >> > >> 'If client found, synchronize the form with the
> >> > >> 'underlying recordset
> >> > >> Else
> >> > >> Me.Bookmark = Me.Recordset.Bookmark
> >> > >> End If
> >> > >> End Sub
> >> > >>
> >> > >> Private Sub cmdNext_Click()
> >> > >>
> >> > >> 'Move to the next record in the recordset
> >> > >> Me.Recordset.MoveNext
> >> > >>
> >> > >> 'If at EOF, move to the previous record
> >> > >> If Me.Recordset.EOF Then
> >> > >> Me.Recordset.MovePrevious
> >> > >> MsgBox "Already at Last Record!!"
> >> > >> End If
> >> > >>
> >> > >> 'Set the bookmark of the form to the bookmark
> >> > >> 'of the recordset underlying the form
> >> > >> Me.Bookmark = Me.Recordset.Bookmark
> >> > >> End Sub
> >> > >>
> >> > >> Thanks for anyones help in advance.
> >> > >> My email is
> >> > >> robert.f.smith@sbcglobal.net
> >> > >>
> >> > >
> >> > >
> >> >
> >> >
> >> >
>
>
>  |