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

 

[Pat Hartman]

The form's Current event is executed when the form is positioned to a new record. This happens after the form's Load event or when you scroll to a new record using the navigation buttons or your own code. It does NOT run when you click into list boxes. Apparently, you are not scrolling to a new record because the record is already the current record.

I design all my forms to work well in a client/server environment so I never use this technique. I load all forms "empty" by having the form's RecordSource query reference the search fields on the form. So, when the form first opens, the search fields are empty and so no matching record is found. If I use only a single search field, I requery the form in the AfterUpdate event of the search combo. If I have multiples, I use a button so the query doesn't run multiple times.

This method eliminates the need for any form changes should I need to upsize to SQL Server.

 

[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

 

[Pat Hartman]

It most certainly does not. Your code is repositioning the form to a new record and THAT is what causes the current event to run. So instead of arguing with me, try to understand why the code doesn't run when you select the first item. Put messageboxes into the Open, Load, and Current events so you can watch them execute. You will see that the current event runs after the form loads. It then runs again when you scroll to a new record with the combo.

 

[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

 

[Pat Hartman]

You were saying that the click event in the combo was causing the Current event to run but I was trying to explain that it wasn't the click event but the fact that code in the click event moved the record pointer was what was causing the current event to run. Since the first item in the combo is already the current record, the record pointer doesn't move because the current record doesn't change when you click on the same record.

What exactly are you trying to do that requires running the current event?

 

[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?

 

[Pat Hartman]

Did you put the message boxes in the current event so you can see when it runs? You should also put a message box in the AfterUpdate combobox event so you can see the repositioning code execute.

 

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

 

[Pat Hartman]

Please post all the code in the click event of the listbox.

 

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

 

[Pat Hartman]

If there is no code, then why do you think that changing the value in the combo should make the form move to a new record?

 

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

 

[Pat Hartman]

Why don't you just try it and see that it's the embedded macro generated by the wizard

How would I know you were using macros? If it's a macro, we need to know what the macro is doing. Open the form in design view and use the button on the ribbon to convert macros to code (do this in a copy of your database if you don't want to change your own copy). Then copy the code so we can see it. You could also post your database but be sure to remove any sensitive data first.

 

[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