After a few days of frustration, I have decided to present this issue here for resolution. Searches through countless forums and help sites have not presented any similar issue. The following details the problem with a FindFirst method in MS Access 2003:
Tables and Forms: Customer Info Table > Service Details Table as a one-to-many relationship with referential integrity and cascading updates enabled (there may be more than one service detail for each customer). A search form allows user to select ‘customer’ and ‘service provider’ (a field in table Service Details) sending that information to open a main form/subform to enter service data related to ‘customer’ using ‘service provider’ (text string) as criteria. There may be more than one ‘service provider’ per ‘customer’ (i.e. each Service Detail entry may have a different ‘service provider’ for the same ‘customer’).
Objective: The main form (with subform, both based on select queries) should open to the specified record/subrecord.
Current Method: A combo box on the main form (not the search form) allows user to select appropriate ‘service provider’ using the FindFirst method (combo box created by wizard). This works but not what is intended. The ‘service provider’ criteria is initially selected in the search form and passed into the main form (as a global variable for now), so the combo box selection on the main form becomes redundant and could cause confusion for the user (they could accidentally enter data for the wrong service details).
Problem: I assumed copying the code from the AfterUpdate() event of the combo box to the Load() event of the main form would be adequate, but it does nothing, no errors, just loads the first available Service Details record instead of the selected one using ‘service provider’ as criteria.
Tests: I have inserted message boxes to check the criteria value for the ‘service provider’ selected, and everything seems ok, both in and out of the FindFirst method inserted in the Load() event of the main form. I have tried a DLookup(), DoCmd.Findrecord and setting the default of the combo box to the criteria passed from the search form, but still only the first ‘service provider’ record is displayed, instead of the one selected in the search form.
Code:
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Service Provider] = '" & [g_serviceProvider] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
This produces no errors, and hovering over the g_serviceProvider variable with the mouse in debug mode shows the correct string value. This code works perfectly in the AfterUpdate() event of the combo box, but does nothing when inserted into the Load() event of the form, even though the values are indeed passed (checked using message boxes to display data at each stage of the execution).
I am at a loss of why this only works for the combo box but not in the Load() event of the form. I suspect the problem is in the recordsource of the form (select query), as the recordsource of the combo box uses a SELECT expression based on the same query as the form. I have had no success with coding the recordsource to use with the FindFirst method within the Load() event, as that produces errors (cannot find any relevant examples to work from). Any ideas? Thanks in advance.
Ok, I finally figured it out. The actual problem was the form loading the Current() event multiple times when opening the form to a specific record. This was happening on pretty much every form where an existing record is being displayed. It seems to be an artifact of the form opening and record selection of MS Access 2003 (don't know about newer versions, but I have read several other posts concerning this issue).
There is a dirty work around, by using a count of the number of times the Current() event runs when a form is opened. I have found that for a "virgin" form (blank form ready to accept input with blank record source (table)) the Current() only runs one time. If there is any existing records, but the form is blank ready to input a new record, the Current() will run twice. When displaying a record that already exists that must populate a form, the Current() will run three times before all of the data loads enough to make the selection through coding rather than through a combo box (which loads with the control). Subform Current() seems to run one more time than the parent form.
To check to see if and how many times any event occurs, insert a simple message box in the suspected block of the form's VBA code.
Creating a simple counter, coded in the Current() event block of the main form, solved the problem.