Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003

    Angry Unanswered: Searching for and moving to an existing record

    The icon says it all. I've been staring at this problem far too long for it not to be solved. Here's what I want to do:

    I am creating an employee information database in Access 2002.

    I have a form that is bound to a query. It displays information from the query by person. However, I know that as the list of people grows, clicking Next Record will become tedious, so I want to include a Search box on the form.

    Once the user enters a person's name and clicks "Go", I want Access to find the first record with that occurrence of the person's last name, and MOVE the recordset to that instance. I've looked at Move, Find, Bookmark, and all that other good stuff in Help, but I'm still new enough to VBA that it just doesn't make sense.

    The current record is for a person with the last name of "Alfredson". However, I want to show the record for someone with the last name of "Chandler", so I type "chandler" into the Search box. When the procedure successfully runs, all information now showing on the form will be for "Chandler". If there were a "Chandler, George" and a "Chandler, Rolf", the record displayed would be that of George.

    The problem I foresee is updating. From the looks of the Help files, in order to do this I have to create a RecordsetClone, but if I make changes to the data shown on the form, won't that only change information in the cloned recordset? I want to be able to make changes to my underlying tables directly from the form.

    Now that "Chandler" is the current record, I want to update his Annual Review date. When I modify the date to sometime next month, I want that change to reflect in the table that supplied the original date.

    I get the feeling that I'm very close to an answer, but I feel like all my approaches are unnecessarily complex. Please help!

  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    how about going in a different direction?

    -save an access query that returns the fields you need to stuff your form

    now is a great time to run the query and try an edit - if the recordset returned by the query is updateable (i.e. your table structure is not too insane), you should be able to edit...

    If Not EditWorkedOK Then Exit ThisPost

    -use this query as recordsource for your form
    -add an unbound combo "myCombo" to the form header (wizard on) values from the table having the names
    -open the .rowsource of the combo and sort-ascending the name
    -back to your query: click in the criteria box under the name column and then click the magic wand in the main manu... navigate in the left pane forms/all forms/your form and in the middle pane, double-click myCombo
    -add a me.requery to the _afterupdate of the combo

    what should be happening now is that the combo allows you to select a name and then displays the data

    problem is with multiple "Smith" entries. yes you can scroll through all the Smiths, but m,aybe better is to rework the combo so that it only gets one hit:

    probably you have a unique ID (? employeeNumber ?) in your names table.

    -open the rowsource of the combo and
    -add the [employeeNumber] column if it is not already there
    -add another "calculated" column along the lines of:
    [lastName] & ", " & [firstName] & " - " & [employeeNumber]
    -adjust .columncount to allow for the new column(s)
    -adjust .columnwidths to make your calculated column the displayed column (i.e. a 0 for everything except your calculated column).
    - set .boundcolumn to employeeNumber
    -finally, re-jig the query so the criteria is employeeNumber rather than name

    now what should be happening is that the user selects a unique last/first/emp# from the combo and a single record is returned by the query. as usual with combos, typing the first few chars of the name speeds selection

    the query will panic when you open the form and the combo is null. easiest is to set a default for the combo: ??? use your employeeNumber as default so that the form misbehaves if you get "workforce managed"? otherwise DLAST("employeeNumber", "nameOfNameTable") to victimise the newest employee.

    Last edited by izyrider; 09-11-03 at 04:42.

  3. #3
    Join Date
    Aug 2003
    I've thought of the query method but it's not the way I want it to work. When the user goes to the first Chandler record, for instance, I want it to work so that when he clicks on the next record button, the next record in the base recordset will appear.

    If my recordset has consecutive records for Callick, Chandler (George), Chandler (Rolf), and Chesterton, when the user searches for Chandler he will see records for Chandler (George). Once he clicks on Next Record, Chandler (Rolf) will show, then Chesterton, usw.

    You see, I have a feeling I need to use the Move or Find methods, but all the examples I have seen are from a previous version of VBA and use the FindFirst method, which doesn't exist in Access 2000 (or I don't have access to it). When I try this:

    Dim rst As Recordset
    Set rst = New Recordset
    all I get for Find related methods is Find. Do you have or know of a place to find current examples of how Find works? I'm ready to get this problem solved and move on!



  4. #4
    Join Date
    Aug 2003
    Montreal, Québec


    I have the same problem as you. If somebody knows how to apply find or findfirst(or maybe seek) method in Access 2000.


Posting Permissions

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