Problem with Form_Current

[jch278]

In a form in Access 2010 when I click on the first entry in a List Box, Form_Current is not executed. When I click on any other entry it is executed.

A simple example will illustrate this.

Table, Table1 with 2 fields, Id and Field1.
Form with List Box created using "Find a record on my form based on the value I selected in my list box". Row Source is "SELECT Table1.ID, Table1.Field1
FROM Table1;"

4 entries in Table1 viz. Name1, Name2, Name3 and Name4.
These are displayed in the List Box.

When I click on any of these, the corresponding record is loaded.i.e. the relevant Field1 is displayed in the bound field on the form. However clicking on Name1 does not cause Form_Current to run whereas clicking on the other 3 does.(Message Box in Form_Current shows whether it runs).


This occurs when I go to Name1 first or when I go to Name1 after I’ve been to other records.

Am I missing something or is this a bug?

 

[ypma]

For some reason it does not like the embedded macro, the only way i could get to work apart for using the record selector was to delete the embedded macro and replace it with a [Event Procedure]

DoCmd.OpenForm "FORM1", wherecondition:="[id] = " & Me.List4.Column(0)

If you find the correct solution i hope you share it .

Regards

 

[jch278]

Hi Pat,

Thanks for your reply.

You say " It does NOT run when you click into list boxes", however it most certainly does. It's only for the first record that it doesn't and this is after scrolling to and displaying other records.

Chris

 

[jch278]

Hi Pat,

Thanks for your reply.

I'm confused.

In your first response you say that " It (the current event) does NOT run when you click into list boxes". In your second you say "Your code is repositioning the form to a new record and THAT is what causes the current event to run" i.e. it does run.

I've added the Open and Load events and they just run when the routine starts but not when moving round the records. The current event always runs when moving round the records either scrolling or clicking on the list box, except of course when clicking on the first entry.

Are you saying that even though the current event has run for other records, the form still thinks it's at the first record, in which case why does the current event run when I scroll back to the first record?

Thanks for your help.

Chris

 

[jch278]

Pat,

Thanks again for replying.

Apologies for my continued inability to see your point, but I'm still puzzled.

My form loads at the first record. If I now use the Navigation buttons to move to the 3rd record, say, doesn't this become the current record? i.e. The current record is no longer the first record. So why now, when I click on the first line of the list box and display the first record the current event doesn't run?

 

[jch278]

I've added Afterupdate with a messagebox to the example described in my original . This runs when I change Name3 to Name3x, for example, and navigate from the 3rd to the 2nd record, however now clicking on the first record of the list box (not combo box) still doesn't run the Form_Current event.

 

[jch278]

There is no vba code for the form apart from the message boxes that I've added in the afterupdate, current, load and open subs as described previously.

It would have taken you 2 minutes to replicate the example I described in my first posting and you would have seen that an embedded macro is generated by the list box wizard and that this is probably the culprit, as suggested by ypma.

I've had enough of my time wasted on this problem and I won't be making any more postings on this thread.

 

[jch278]

Why don't you just try it and see that it's the embedded macro generated by the wizard that does the work for the list box (not combo box). Absolutely no more from me after this!

 

[dpgttr]

Not sure if you found an answer to your problem already but I ran into this same problem and found this thread in my search for an answer. When I would click on the first item in my list box my form would navigate from whichever record it was on to the first record but several unbound textboxes that were supposed to update on the form_current event did not update. My problem was solved by using VBA code from Allen Browne's website instead of the Macro. It's on the page titled "Using a Combo Box to Find Records" (I'm not allowed to post a link). I adapted his code to use my list box and field names. Here is the code adapted for my use.

Dim rs As DAO.Recordset

If Not IsNull(Me.lstCountries) Then
    'Search in the clone set.
    Set rs = Me.RecordsetClone
    rs.FindFirst "[Country] = """ & Me.lstCountries & """"
    If rs.NoMatch Then
        MsgBox "Not found: filtered?"
    Else
        'Display the found record in the form.
        Me.Bookmark = rs.Bookmark
    End If
    Set rs = Nothing
End If

I put this in the After Update event of my list box. After this my Form_Current event fired exactly like it was supposed to, even on the first record. Hopefully this helps you if you haven't found an answer yet.

 

[jch278]

Dear dpgttr,

Thank you for your reply.

Unfortunately it's so long ago now that I can't remember how I resolved it. I must have used some sort of workaround. You can see from my messages that I was finding it pretty frustrating.

Anyway I really appreciate that you took time out to get back to me.

Chris