0

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.

user3716557
  • 41
  • 5
  • Just a shot in the dark, but maybe the form isn't bound yet? Your code doesn't do anything if rs is at EOF. Maybe handle the true case for debugging with Debug.Print or a messagebox. By the time AfterUpdate() is happening, maybe it is bound by then, so works properly. – VBlades Jun 07 '14 at 02:13
  • Maybe OnCurrent? That should be late enough in the event chain that the form must be bound. Just a thought. – VBlades Jun 07 '14 at 02:20
  • The form's recordsource is a query. Message boxes placed throughout each condition have confirmed no EOF condition and the bookmark seems to be working correctly as well. Interestingly enough, pasting the code into the 'Current' event of the main form actually produces an error "cannot go to specified record." Thanks for the suggestion, though! – user3716557 Jun 08 '14 at 17:32
  • Hmmm, breaks OnCurrent? You can get that message when the recordset is at EOF - which could possibly indicate you are the desired record already, and FindFirst is not finding an additional match, thus, EOF (Current can run many times). Have you tried handling rs.NoMatch on the OnCurrent event (I know you tried it before, but maybe on Load)? I think this is something I would need to be in front of to debug. I guess another alternative is to change the query to only bring up that one record you want, with a param explicitly to the form or global variable. May not be fit your design, though. – VBlades Jun 08 '14 at 22:07
  • Also, there are subtle differences between Me.Recordset.Clone vs Me.RecordsetClone, btw. Not sure if relevant here, but could be. I remember having an issue with one that the other resolved, but can't remember the situation. – VBlades Jun 08 '14 at 22:09
  • I'm pretty sure the error came from the fact that the code was looking at the parent record and not the child record, while the findfirst referenced the child record, thus could not find the referenced record. I will try the EOF handler in the Current event and see what happens. Perhaps some alternate method would be more appropriate. If this suggestion works, I'll post as solved, if not, then I'll be back. Thanks for the ideas. – user3716557 Jun 08 '14 at 22:41

1 Answers1

0

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.

user3716557
  • 41
  • 5