Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DAO find first (XP)

    I'm trying to use FindFirst to identify and then move to a record on a (bound) form.

    I just can't get it to work, this is what I'm trying.

    Sub mySearchAttempt()

    Dim rsTemp As DAO.Recordset
    Dim varBookmark As Variant

    On Error GoTo ErrorHandlerPoint

    Set rsTemp = Me.RecordsetClone
    varBookmark = Me.Bookmark

    'Begin search at current record, search towards end of recordset,
    'looking for the first record without 'Examiner' filled in
    With rsTemp

    .FindNext IsNull(rsTemp.Fields("Examiner"))

    If Not .NoMatch Then ' there is a matching record, so move there
    Me.Bookmark = .Bookmark
    'do some other things like setting the value property of 'Examiner' control
    End If

    End With

    TidyUpAndExit:
    Set rsTemp = Nothing
    Set varBookmark = Nothing
    Exit Sub

    ErrorHandlerPoint:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdNewOne_Click of VBA Document Form_frmWalk"
    GoTo TidyUpAndExit

    End Sub


    I have cut this from the actual code and trimmed away excess comments so it's possible there could be typos. I swear this has worked (after a fashion) but with no consistency.


    In case you are wondering, the point of it it this:
    A database contains a few thousand records which have some basic information completed.
    The remaining information needs to be filled in by a series of users 'Examiners').
    The code above needs to allow them to move to the next one that needs to be completed*

    *The completeness of the records wont be fully validated, once the Examiner goes to the 'new' record and their name has been filled in (automatically) we will assume they will complete the rest of the require info.

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

    Re: DAO find first (XP)

    The line

    .FindNext IsNull(rsTemp.Fields("Examiner"))

    calls FindNext but you haven't called FindFirst before. Moreover, the criteria argument should be the WHERE part of an SQL string, without the word WHERE itself. Try this instead:

    .FindFirst "Examiner Is Null"

    Note: you never use varBookmark in the code as posted.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO find first (XP)

    I did think the criteria could have been a bit off but couldn't think straight.

    When the code is called, the forms recordsource could have any number of gaps (ie where 'Examiner is null'). I want to look for the first instance after the CURRENT record, not the beginning of the recordset. Once I use the correct criteria should FindNext work or does it have to be used after a FindFirst?

    Cheers

    ps. was planning on using varBookmark but realised after I posted that it is not used.

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

    Re: DAO find first (XP)

    You must set the position in the recrodset before issuing a FindNext. If you don't, the effect of FindNext is the same as that of FindFirst.

    Dim rsTemp As DAO.Recordset
    Set rsTemp = Me.RecordsetClone
    With rsTemp
    ' Set position in recordset to that in form
    .Bookmark = Me.Bookmark
    ' Find next record
    .FindNext "Examiner Is Null"
    ' Did we find it?
    If Not .NoMatch Then
    ' Set position in form to that in recordset
    Me.Bookmark = .Bookmark
    End If
    End With
    Set rsTemp = Nothing

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO find first (XP)

    Thanks Hans

    I kind of got it working but was forgetting to set the bookmark for rsTemp before the search.

    Thanks also for the help with my previous topic (Tablet PCs).

    Is there any way to requery the form without loosing the bookmark (and the form returning to the first record). I'm guessing there is no way around this but though i'd ask anyway. The main form is just used to 'hold' a tab control with 2 tabs, each with a subform. These are filtered to show either the 'left' and 'right' instances of a position along a line. My recordsource for the main form is a distinct query and hence read only.

    Once the user moves to the appropriate record, the 'Examiner' control is filled on the both subforms, but the main 'Examiner' control remains empty unless I requery the form.

    I know this sounds horrible <img src=/S/confused.gif border=0 alt=confused width=15 height=20> but its what someone asked for.

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

    Re: DAO find first (XP)

    Since you can't edit bound controls in the main form anyway, you might use a text box on the main form with control source

    =[Subformname]![Examiner]

    where Subformname is the name of one of the subforms as a control on the main form. When the user enters an examiner in the subform, the text box on the main form will be updated. Note: the text box should NOT be named Examiner; that would confuse Access.

Posting Permissions

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