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!
-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.
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!