Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Record Find Problem

Discussion in 'Information Technology' started by robert.f.smith@sbcglobal.net, Jul 28, 2006.

  1. robert.f.smith@sbcglobal.net

    Guest

    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
     
  2. Loading...


  3. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    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"
    <robert.f.smith@sbcglobal.net@discussions.microsoft.com> 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
    >
     
  4. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    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.

    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" <Graham.Mandeno@nomail.please> 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"
    > <robert.f.smith@sbcglobal.net@discussions.microsoft.com> 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
    >>

    >
    >
     
  5. robert.f.smith@sbcglobal.net

    Guest

    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.
    >
    > 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" <Graham.Mandeno@nomail.please> 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"
    > > <robert.f.smith@sbcglobal.net@discussions.microsoft.com> 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
    > >>

    > >
    > >

    >
    >
    >
     
  6. robert.f.smith@sbcglobal.net

    Guest

    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.
    > >
    > > 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" <Graham.Mandeno@nomail.please> 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"
    > > > <robert.f.smith@sbcglobal.net@discussions.microsoft.com> 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
    > > >>
    > > >
    > > >

    > >
    > >
    > >
     
  7. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    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"
    <robertfsmithsbcglobalnet@discussions.microsoft.com> 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.
    >> >
    >> > 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" <Graham.Mandeno@nomail.please> 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"
    >> > > <robert.f.smith@sbcglobal.net@discussions.microsoft.com> 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
    >> > >>
    >> > >
    >> > >
    >> >
    >> >
    >> >
     
  8. robert.f.smith@sbcglobal.net

    Guest

    Graham,
    You are a genius. Thank you for your help. I was really stumped. I now have
    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"
    > <robertfsmithsbcglobalnet@discussions.microsoft.com> 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.
    > >> >
    > >> > 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" <Graham.Mandeno@nomail.please> 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"
    > >> > > <robert.f.smith@sbcglobal.net@discussions.microsoft.com> 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
    > >> > >>
    > >> > >
    > >> > >
    > >> >
    > >> >
    > >> >

    >
    >
    >
     
  9. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    Hi Robert

    Probably the most elegant way around this is to create your own InputBox.
    (I always avoid InputBox anyway, except for something quick and dirty,
    because it's limited and ugly.)

    Create a small unbound form (frmSearchPhone) with a textbox for the phone
    number (txtSearchPhone) and use the same input mask as you have for the
    actual field. Add two buttons - cmdFind and cmdCancel - and set the Default
    property of the first and the Cancel property of the second.

    Turn off all the default stuff like record selectors and navigation buttons
    and control menu.

    For cmdCancel, simply close the current form:

    DoCmd.Close acForm, Me.Name

    Use the same code as below for cmdFind, with the following changes:

    Private Sub cmdFind_Click()
    Dim frm as Form
    Dim rsc As ADODB.Recordset
    Set frm = Forms("name of your main form")
    Set rsc = frm.RecordsetClone
    With rsc
    .MoveFirst
    .Find "[Home_Phone] = '" & txtSearchPhone & "'"
    If .EOF Then
    MsgBox "Home Phone Number " & txtSearchPhone & " Not Found!!"
    txtSearchPhone.SetFocus
    Else
    frm.Bookmark = .Bookmark
    DoCmd.Close acForm, Me.Name
    End If
    End With
    Set rsc = Nothing
    Set frm = Nothing
    End Sub

    On your main form, all the command button needs to do is open the new form
    modally:

    DoCmd.OpenForm "frmSearchPhone", WindowMode:=acDialog
    --
    Good Luck!

    Graham Mandeno [Access MVP]
    Auckland, New Zealand


    "robert.f.smith@sbcglobal.net"
    <robertfsmithsbcglobalnet@discussions.microsoft.com> wrote in message
    news:F2392E2D-A1B9-4595-8FBC-67410ED44375@microsoft.com...
    > Graham,
    > You are a genius. Thank you for your help. I was really stumped. I now
    > have
    > 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
     
  10. robert.f.smith@sbcglobal.net

    Guest

    Gramham,
    Thanks you very much for your help. It works perfectly. I may call on you
    again. i am try to do so additional things with it, but I may be fine on my
    own.
    I want to nest teh if to check all three phone numbers home, work, and cell
    before saying teh record is not there. I have some ideas, and it may work,
    but if not I will post a help message.

    Again thanks you very much
    you are truly a MVP

    "Graham Mandeno" wrote:

    > Hi Robert
    >
    > Probably the most elegant way around this is to create your own InputBox.
    > (I always avoid InputBox anyway, except for something quick and dirty,
    > because it's limited and ugly.)
    >
    > Create a small unbound form (frmSearchPhone) with a textbox for the phone
    > number (txtSearchPhone) and use the same input mask as you have for the
    > actual field. Add two buttons - cmdFind and cmdCancel - and set the Default
    > property of the first and the Cancel property of the second.
    >
    > Turn off all the default stuff like record selectors and navigation buttons
    > and control menu.
    >
    > For cmdCancel, simply close the current form:
    >
    > DoCmd.Close acForm, Me.Name
    >
    > Use the same code as below for cmdFind, with the following changes:
    >
    > Private Sub cmdFind_Click()
    > Dim frm as Form
    > Dim rsc As ADODB.Recordset
    > Set frm = Forms("name of your main form")
    > Set rsc = frm.RecordsetClone
    > With rsc
    > .MoveFirst
    > .Find "[Home_Phone] = '" & txtSearchPhone & "'"
    > If .EOF Then
    > MsgBox "Home Phone Number " & txtSearchPhone & " Not Found!!"
    > txtSearchPhone.SetFocus
    > Else
    > frm.Bookmark = .Bookmark
    > DoCmd.Close acForm, Me.Name
    > End If
    > End With
    > Set rsc = Nothing
    > Set frm = Nothing
    > End Sub
    >
    > On your main form, all the command button needs to do is open the new form
    > modally:
    >
    > DoCmd.OpenForm "frmSearchPhone", WindowMode:=acDialog
    > --
    > Good Luck!
    >
    > Graham Mandeno [Access MVP]
    > Auckland, New Zealand
    >
    >
    > "robert.f.smith@sbcglobal.net"
    > <robertfsmithsbcglobalnet@discussions.microsoft.com> wrote in message
    > news:F2392E2D-A1B9-4595-8FBC-67410ED44375@microsoft.com...
    > > Graham,
    > > You are a genius. Thank you for your help. I was really stumped. I now
    > > have
    > > 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

    >
    >
    >
     

Share This Page