Using list box to select records causes form to jump temporarily to first record

[timesscript]

I have a list box that I set up by choosing to add a list box, and selecting "find a record on my form based on the value I selected in my list box." This will then populate the other controls in my form based on the record selected,



It works to choose the record, but when I choose a record on the list box, it will jump to the first record and display its info in my controls for a split-second, before it settles on the correct record. The status bar says "Calculating..." while this takes place.

If I select a record just using the back and forward options on the form, this does not happen (though it still says "calculating...")

Any thoughts on how to solve this?

[June7]

The listbox is UNBOUND?

This is why I avoid the wizards - they do stuff I don't like. Did it generate a macro or VBA? Need to review code.

[timesscript]

It is unbound, though I am a beginner and am not sure what that implies, but here is the macro it created:

[June7]

UNBOUND means the ControlSource is not set to a field. It is blank or has an expression.

I don't see anything wrong with that code but I don't use macros, only VBA. It always baffles me why the wizard puts the = sign at beginning of the expression. Seems unnecessary.

[Missinglinq]

To search for a Record, you have to tell Access where to start the search. When you select the action SearchForRecord, the default for the third argument ('where to start') is the First Record, hence the

Record First

in the third line under SearchForRecord in the Macro definition. My guess, and it is just a guess (almost no one here uses Embedded Macros, including myself) is that when doing this through a Macro, it runs relatively slow, allowing you to actually see the First Record briefly.

I would delete the Macro from the AfterUpdate Property, in the Properties Pane, and use this code, instead:

Private Sub List269_AfterUpdate()
  
 Dim rs As Object

 Set rs = Me.Recordset.Clone
   rs.FindFirst "[Full Name] = '" & Me![List269] & "'"
 If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

I suspect that this will solve your problem.

Linq ;0)>

[timesscript]

Missinglinq,

That did indeed solve the problem. I wonder why Microsoft didn't have it work that way in the first place!

Thanks!

[Missinglinq]

...I wonder why Microsoft didn't have it work that way in the first place...

They did...until Access 2007! Then the Boys of Redmond, for some obscure reason, started trying to coerce everyone into using Embedded Macros, instead of VBA code, by making Embedded Macros the default for events. The problem is that EMs

1. Use an arcane language their own
2. Don't offer the flexibility that VBA does
3. Offer no real advantage over VBA

In fact, they actually present some disadvantages, as you've seen, by executing Macros slower than comparable code! In this case, because the Macro executes slower than the code, you actually get a glimpse of the first Record when Access moves to it!

Because of this, very few experienced developers routinely use Embedded Macros.

If your copy of Access had been set up with the option 'Always Use Event Procedures' selected, when you used the Combobox Wizard to create your 'search' function, Access would have generated VBA code just like I gave you, instead of a Macro!

If you're interested in using/learning VBA code, you can set your Access to make that the default by going (in 2007, at least) to Options - Object Designers - Forms/Reports and ticking the box for the Always Use Event Procedures option.

Aside from offering more flexibility in what you can do, and how you can do it, VBA has one more big advantage...everyone here uses it, and will happily help you with advice, and almost no one here uses Macros...and hence can't help you with them!

Linq ;0)>

[June7]

I find the 'Always Use Event Procedures' is inconsistent. I just tried it with a button. Wizard still generated embedded macro. However, when I clicked the ellipsis in the event property, it shows [Event Procedure] and opens the VBA editor at the procedure - instead of popping up Choose Builder dialog box.

[Missinglinq]

Been a while since I diddled with this, but it seems like you can't change the option and have it go into effect immediately. In other words, the mode it is in when then file is originally created remains the mode, for that file, regardless of what you do with the option later.

Linq ;0)>

[timesscript]

Hmmm... so I have come upon a new and strange problem with this listbox. The error below is popping up when I select anything. However, if I then go into the form and bring up the vba code for that event procedure, do not edit it, but leave the window open, it works when I switch it back to form view. It will then continue to work if I close the vba code, until I close the database and reopen it, when I begin getting the error again when I click on the list box.

[timesscript]

A decompile and recompile fixed it.

[Missinglinq]

Glad you got it fixed. For future reference, you really need to start a new thread, with an appropriate title, when you have a new problem. A title of "Using list box to select records causes form to jump temporarily to first record" is not apt to give you help for your current problem!

Linq ;0)>