Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FindFirst Me.Bookmark = rst.Bookmark (Win XP Excel 2003)

    This is sample code from DAO Object Model, Ch 8. The original code works fine. When I add the following:

    Me.Bookmark = rst.Bookmark
    MsgBox ([LastName])

    then the MsgBox shows the record following the FindFirst. Why is that? How do I address the original found record? The original code finds Davolio, and the MsgBox returns Fuller.

    Private Sub cmdFindFirst_Click()

    Dim dbs As Database
    Dim rst As Recordset
    Dim strDBName As String
    Dim strTable As String
    Dim strSearch As String
    Dim strName As String

    strTable = "Employees"
    strDBName = cDBNAME
    Set dbs = CurrentDb() 'OpenDatabase(strDBName)
    Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)
    strName = Chr(39) & "Davolio" & Chr(39)
    strSearch = "[LastName] = " & strName

    With rst
    .FindFirst strSearch
    Me.Bookmark = rst.Bookmark
    MsgBox ([LastName])
    Debug.Print strName & " found? " & Not .NoMatch
    .Close
    End With

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FindFirst Me.Bookmark = rst.Bookmark (Win XP Excel 2003)

    1) Is cDBNAME defined anywhere? If not, you apparently don't require explicit variable declaration. That is very dangerous. You should turn on "Require variable declaration" in Tools | Options (in the Visual Basic Editor) and make sure that every module has a line Option Explicit at the top.

    2) You should always indicate explicitly whether a recordset is a DAO or an ADODB recordset. In this case:

    Dim rst As DAO.Recordset

    3) You should check whether the FindFirst action succeeded:

    .FindFirst strSearch
    If .NoMatch = True Then
    MsgBox "Not found"
    Else
    Me.Bookmark = .Bookmark
    MsgBox Me.LastName
    End If

    4) The code, when corrected as indicated above, returns the correct last name when I try it.

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FindFirst Me.Bookmark = rst.Bookmark (Win XP Excel 2003)

    cDBNAME is defined in the Standard module basGeneral as follows:

    Option Compare Database
    Option Explicit

    Public Const cDBNAME = "C:Program FilesMicrosoft OfficeOffice11SamplesNorthwind.mdb"

    The Option Explicit is also defined there.

    I have the Require Variable Declaration turned on.

    I have placed the following in the General section of the class module where my FindFirst sub is found.

    Option Compare Database
    Option Explicit
    Private Const cDBNAME = "C:Program FilesMicrosoft OfficeOffice11SamplesNorthwind.mdb"
    Dim rst As DAO.Recordset

    The DAO 3.6 Object Library is selected in references.

    I run this code and the intermediate window confirms Davolio, but the message box in your check routine gives Fuller, the following record.

    Here is what the routine looks like:

    Private Sub cmdFindFirst_Click()

    Dim dbs As Database
    Dim rst As DAO.Recordset
    Dim strDBName As String
    Dim strTable As String
    Dim strSearch As String
    Dim strName As String

    strTable = "Employees"
    strDBName = cDBNAME
    Set dbs = CurrentDb() 'OpenDatabase(strDBName)
    Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)
    strName = Chr(39) & "Davolio" & Chr(39)
    strSearch = "[LastName] = " & strName

    With rst
    .FindFirst strSearch
    ' Your routine
    If .NoMatch = True Then
    MsgBox "Not found"
    Else
    Me.Bookmark = .Bookmark
    MsgBox Me.LastName & " Hans test"
    End If
    End With

    End Sub

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: FindFirst Me.Bookmark = rst.Bookmark (Win XP Excel 2003)

    You can't use the bookmark property in this manner. The only time you can set "Me.Bookmark=rst.Bookmark " is when rst is based on the recordsetclone property of the from. You actually don't even need to use rst, you can use the recordsetclone property directly. So instead of the "With rst", you merely use "With Me.RecordsetClone".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FindFirst Me.Bookmark = rst.Bookmark (Win XP Excel 2003)

    Good catch, Mark! I completely overlooked that.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FindFirst Me.Bookmark = rst.Bookmark (Win XP Excel 2003)

    Thanks for the explanation. cDBName and strDBName are not used in cmdFindFirst_Click, however.

    See Mark's reply!

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: FindFirst Me.Bookmark = rst.Bookmark (Win XP Excel 2003)

    Thanks. I know how it is. You read so many messages you tend to skim them, often jumping to a solution (because the message is similar to others) and focusing in on just that area of the problem. I'm guilty of this way too often!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FindFirst Me.Bookmark = rst.Bookmark (Win XP Excel 2003)

    I can't get the zipped version of the db to less than 168, which is rejected. Mark, where does the "With Me.RecordsetClone" statement go?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FindFirst Me.Bookmark = rst.Bookmark (Win XP Excel 2003)

    I deleted my request for a zipped database when I saw Mark's reply. You can change the line

    Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)

    to

    Set rst = Me.RecordsetClone

    and leave the rest of the code unchanged.

  10. #10
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FindFirst Me.Bookmark = rst.Bookmark (Win XP Excel 2003)

    When I make that change, I get a "The db has been placed in a state by user......." message. Here is the code with the change. If I remove that line, where do I tell the sub to open the table "Employees"?

    Private Sub cmdFindFirst_Click()

    Dim dbs As Database
    Dim rst As DAO.Recordset
    Dim strDBName As String
    Dim strTable As String
    Dim strSearch As String
    Dim strName As String

    strTable = "Employees"
    strDBName = cDBNAME
    Set dbs = OpenDatabase(strDBName) 'CurrentDb()
    Set rst = Me.RecordsetClone
    ' Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)
    strName = Chr(39) & "Davolio" & Chr(39)
    strSearch = "[LastName] = " & strName

    With rst
    .FindFirst strSearch
    Debug.Print strName & " found? " & Not .NoMatch
    .Close
    End With

    End Sub

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FindFirst Me.Bookmark = rst.Bookmark (Win XP Excel 2003)

    Try saving the form, then closing and reopening the database.

  12. #12
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FindFirst Me.Bookmark = rst.Bookmark (Win XP Excel 2003)

    I get runtime error 3045, file already in use, with the error occuring on the Me.RecordsetClone statement. Strange stuff. I removed the Me.RecordsetClone statement, put a MovePrevious statement after the FindFirst, but I still show the following record. For example, I look for Davolio and get Fuller. Then, with the MovePrevious, I still get Fuller. The Not
    .NoMatch is true, so supposedly the Davolio record was found. I am confused over what should be fairly straightforward.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FindFirst Me.Bookmark = rst.Bookmark (Win XP Excel 2003)

    I'm afraid you'll have to try to attach a stripped down and zipped copy of your database after all. If you follow the instructions from <post#=401925>post 401925</post#>, it should be possible.

  14. #14
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FindFirst Me.Bookmark = rst.Bookmark (Win XP Excel 2003)

    It is down to 136 KB, but is still rejected. What is the maximum size allowed?

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FindFirst Me.Bookmark = rst.Bookmark (Win XP Excel 2003)

    The maximum is 100 KB. Have you tried converting to Access 97 format? I have never yet seen a problem where it was impossible to get the zip file below 100 KB.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •