Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    GoToRecord - Why does it FindNext? (Access97/SR2)

    I wrote this code many months ago. It works on a "FindNext" command button (code attached), after I have used my "Find Record" button.

    The code works, but I'm not sure why.

    If I were re-creating the code, as I tried today, the Wizard generates the same procedure, but without the line "DoCmd.GoToRecord acDataForm, Me.Name". It seems to me that I must have found that the basic "DoCmd.FindNext" wasn't working without it, so put it in.

    The Help screens give me no clue as to why it should work, although I suspect that after my Find has positioned me at the first 'found" recrod, this extra line, issued before the Findnext, advances me out of the current record, thus preparing me for the next Find, or rather, the FindNext.


    <pre>Private Sub cmdFindNext_Click()
    Static lngOldPosition As Long
    On Error GoTo Err_cmdFindNext_Click
    DoCmd.GoToRecord acDataForm, Me.Name ' what is this, why does it work?
    DoCmd.FindNext
    Call SetAge

    Exit_cmdFindNext_Click:
    Exit Sub
    Err_cmdFindNext_Click:
    MsgBox Err.Description
    Resume Exit_cmdFindNext_Click
    End Sub
    </pre>

    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: GoToRecord - Why does it FindNext? (Access97/SR2)

    All that line does is make sure there is a current record selected, Chris. If you had done something like click a button on the form to trigger find or made a selection from a custom menu bar, you would need to get the cursor into the current record so a find could take place. Otherwise, you get an error because there is nothing to search.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: GoToRecord - Why does it FindNext? (Access97/SR2)

    I should add that this process is not the recommended way of moving between records with a given characteristic - the much quicker and more satisfactory way is to use a filter. Then the visible records are only the one meeting a given criteria, and the standard navigation tools can be used to move back and forth. Not only that, it can be done from the user interface, and requires no VBA to make it work - though you can also do quite clever things with controls to reduce or expand the visible records.
    Wendell

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GoToRecord - Why does it FindNext? (Access97/S

    > more satisfactory way is to use a filter

    I'm sure, and that is the way I first "did" my contacts database; a crude table, six filters, a form and six buttons.

    Today I am approaching it from the view of generating all-VBA code to take a simple flat-file database table of records (e.g, an Excel Spreadsheet) and migrate it to an Access database equipped with all-you-need controls. To this end I was trying to generate a VBA module that had all that was needed as - well, as an ascii *.BAS file.

    I was trying to avoid "cluttering up" the migrated database with filters and what not.


    I am, of course, trying to do quite clever things with controls to reduce or expand the visible records. That's what (my) life is all about, after all (grin!)

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: GoToRecord - Why does it FindNext? (Access97/S

    The real issue with "Find" in Access is that it isn't like Excel where everything is resident in memory - to do a Find it has to read every record off the disk and check to see if it contains the data requested. A filter however will use the index and only read the record or records you want - much faster. If you only got 100 records, no big deal. If you got 1000, the difference is very noticeable. If you've got 10,000 you will be amazed at how much faster the filter is. If you've 100,000 you might want to consider a lunch break if you are using Find.

    One of the best controls for getting to a particular record in Access is the unbound combo that applies a filter - we use them regularly and make that the only record navigation tool. It does involve a bit of VBA, but not much.
    Wendell

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: GoToRecord - Why does it FindNext? (Access97/S

    Chris,

    Unless you have created some really clever code that also normalizes the "crude table", you're still going to wind up with something not much different from an Excel spreadsheet even though it lives in an MDB. Once you start working in Access, you're no longer in Kansas, Toto! <img src=/S/alien.gif border=0 alt=alien width=14 height=15>
    Charlotte

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GoToRecord - Why does it FindNext? (Access97/S

    > to do a Find it has to read every record off the disk

    Wendell, thanks.

    Just two days after I posted the initial question I got lumped with a 270MB database with some 2,000,000 records in the primary table - quite different from my client/prospect list.

    OK. Now I have Indexed some fields (on the mega-) and created some Queries, just as I had done initially for my client list.


    It was in re-writing the client list that I wondered why I'd created so many queries - on 1,500 records it doesn't seem to make a lot of sense, hence my original question that started this thread.


    > the unbound combo that applies a filter

    I gather that this would be used with a filter that gets specified immediately before use, rather than by having a specifcally-named Filter, created days in advance, hooked up to the button? Presumably I'd type in a text box that i wanted all counties that had a "church" within one mile of a "stream", generate the approriate SQL SELECT statement to identify those records, and then apply the filter?

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: GoToRecord - Why does it FindNext? (Access97/S

    <<> the unbound combo that applies a filter

    I gather that this would be used with a filter that gets specified immediately before use, rather than by having a specifcally-named Filter, created days in advance, hooked up to the button? Presumably I'd type in a text box that i wanted all counties that had a "church" within one mile of a "stream", generate the approriate SQL SELECT statement to identify those records, and then apply the filter?>>


    It doesn't have to be that sophisticated, or it can be much more depending on the problem you are trying to solve. The Filter property of a form determines what records will be displayed as a subset of the basic record source for the form. You apply/remove it by turning it on/off. You can also use wildcards to increase the scope of results, e.g. you could do something like <font face="Georgia"><font color=blue>[myTable].[LastName] Like "sm?th*"</font color=blue></font face=georgia> to find all records where the Last Name field contained "Smith", "Smythe", "Smathers", etc. A very powerful tool.
    Wendell

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GoToRecord - Why does it FindNext? (Access97/S

    > depending on the problem you are trying to solve.

    In general, the problems tend towards the dynamic, run-time type.

    If I'm developing an application to extract records from a large (280MB) database, I want to write the code just once and then have the extraction be specified at run-time.

    I understand that filters are a good thing. I have to learn at what time they start being good.

    At one extreme, a fixed filter "Population" applied to the basic table 'USA" would obtain only those records of USA which had Population>0. That's a bit of a no-brainer if I know that many enquiries will be asking for ranking by population. With such a common and anticipated requirement it makes sense to have a hard-wired filter in place, and for Access to spend time doing index maintenance in advance of my use of the filter.

    A more general filter (like the stream&church I outlined) will come to the surface only at run-time, in response to an end-user enquiry, and so that filter, or the SQL Select statement, must be built on-the-fly.


    I think that I don't really have much of an option here. i have to be able to build filters at run-time, and that suggests that Acecss is going to be trundling its way through the 280MB of records for these enquiries.

Posting Permissions

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