List/combo refresh (Access 2000)

Topic

New Reply

Help!
I have a simple form, tied to a 3-field table, built by the form wizard. I added an unbound listbox, and using the wizard, told it to look up a record based on which list item I choose. Instead of using a rowsource for the listbox, I used a function to fill the list, and put its name in the Row Source Type property. The function contains SQL code to look up the values in the list box using ADO. So far so good… everything works properly. When I click on a list item, that record comes up on the form. I also have a Save button on the form, with wizard-generated code to save the record (DoCmd.DoMenuItem…..).

I put code in the save button to requery the list, and re-select the item in the list, so if I change a field on the form, that column of the list will also be changed. Of course, if I add a record, I want it inserted in the list.

Here’s the problem(s):
1: If I change a description field, it doesn’t update the list after I hit the Save button. But… if I click the save button several times, it sometimes does update the list. If I exit the form, then come back, everything is ok, the list reflects the new field value. I’ve tried putting in DoEvents after the save, before the requery. I’ve tried putting the requery in the Form_AfterUpdate event instead of the Save button click event. Nothing works.

2: The undo button on the toolbar is active after I save the record, and it shouldn’t be. If I hover over it, the tool tip says “Undo saved record”. This is strange.

I believe these 2 problems are related. Why would Access offer to undo a saved record once it’s saved. Is there any way to turn off this state of affairs so Access doesn’t think there’s something to undo after I’ve saved the record?

Thanks,

Reply | Quote

Replies

I’m suspicious that the Undo button is actually there because it thinks the design of the control has changed – Access 2000 introduced the concept of being able to make changes in designs when you aren’t in design mode and it can cause a heap of trouble if things go astray. To be frank that a SWAG, but it probably deserves looking into.

As to the first problem, I don’t think your Requery of the list/combo is working because it doesn’t see a SQL string to requery. Is there some compelling reason to use a function to populate the rowsource? Functions in general only return a single value, although it could be a text string representing the rowsource you would put in if the rowsource were done as a text string. In that case, Access treats that as though you are making a design change because you are changing the rowsource, leading to the problem with the Undo. At least that’s one theory. Hope this generates some ideas – I would simply make the table the rowsource, or at least make the row source a SQL String or a parameter query where you supply the parameter in code and I think your problems will disappear.

Reply | Quote

Thanks for taking the time to answer my questions. Unfortunately, as often happens, the answers generate more questions.

1: I don’t quite understand your comment about the Undo button. The button (and, for that matter, the “Undo Saved Record” choice on the Edit menu) becomes available when a record is added or saved. This has nothing to do with changing design. I was just wondering if you could programatically disable that “feature”. Not in the sense you disable a control on a form, but in the sense of making Access think there’s nothing to undo…flip a switch, tweak a flag, something like that.

2: Obviously, you’re right, the requery wasn’t working, but when I exit the form and return, it works fine. This must be a peculiarity of using a function. When I was using a query, it was causing a strange problem: Access wasn’t exitting properly, and I had to kill it from the task list. This problem stopped when I used a function instead of a query. I guess the query was leaving a recordset object alive somewhere, and not allowing a full closeout of Access. I seemed to have solved the problem by using the table itself instead of a function as the Record Source, and the listbox properly refreshes when I tell it to. BTW, there are times when a simple query or table RowSource won’t work (for example, when you need data from a different database, and are constrained against attaching to it by company policy or technical reasons, but are allowed to get an ADO or DAO recordset from it using code). From your answer, it doesn’t seem like you understood how I was using the function. I use the function name as the Row Source TYPE, not the Row Source. See the Access 2000 help topic “RowSourceType, RowSource Properties”. The “See Also” link takes you to a topic called “RowSourceType Property (User-Defined Function)….”. Check out the example. It’s simplistic, but you can put in ADO or DAO code to get a recordset from a SQL query. The recordset internally gets loaded into an array, which actually fills the List or Combo. This technique lets you do things in code with your records if necessary. It’s actually been around since Access 2.0 and works great.

Anyway, thanks again for the feedback.

Reply | Quote

One of the “virtues” of following this forum is you learn new things every day. The “Undo Saved Record” was a feature added in A2k, and I quickly forgot about it. In fact it so seldom works, because as soon as you move to a different record and start editing or close the form or half a jillion other things, it goes away. So I don’t even tell users about it – I want them to be more than a little uneasy about changing things so we say once it’s saved it can’t be changed without re-editing. Of course that’s hard on a delete.

I also missed the issue of you using the combo as a navigation tool. In general, thats hard to do when you allow users to add records on the same form you use to navigate through the records. The Wizard that does this uses a Clone Recordset approach which worked fairly reliably in A97, but is problematic in 2000 and beyond. What we typically do these days if we use a combo for navigation is to apply a filter based on the combo event, and only show one record with no navigation controls. Another problem with the Clone Recordset approach is that at least one user figured out that you could go to another record with the PageUp and PageDn keys and then the combo is no longer in synch. The filter approach solves that problem too.

To solve the Add problem, we typically switch to a data entry mode (or open a new form), and take the navigation combo away and replace it with the description field. When the new record is saved, we either close the new data entry form, or revert to the data edit mode and requery the combo control to update to the latest recordset. You were also right that I didn’t understand that the funtion was in the RowSource Type – I have never used that approach. I work on the premise that no code written by me is the most reliable way to get things done if I can do so with reasonable performance. It is also nearly always faster to deployment because I don’t have to do debuggin at the code level.

Now I am curious why you would have a company policy or technical issues where you could not link to a table or view, but still use ADO to attach to it. Until yo get to Access 2002, ADO is a work in progress – there are many things that horribly difficult or impossible to do in ADO but are quite straightforward in DAO. Yes, there is more overhead in DAO, but at the workstation end, so performance doesn’t suffer. And DAO appears to me to be just as secure as ADO. There are times when linked tables are vastly superior to disconnected recordsets – the inverse is also obviously true. In any event, in this case it doesn’t appear to work so great, mostly because the requery apparently can be performed reliably. It may make sense in some situations where the recordset is static, in fact there is a mention of that in the help, but it doesn’t seem to make sense in a dynamic recordset.

All in all an interesting learning experience – now will I remember it next week???

Reply | Quote

Wow…that’s a lot to digest.

Re Undo Saved Record: I agree completely, and would be very interested in how to disable it easily in code without fooling with command bars, etc.

Re combo/list as navigation tool: Wow great idea! I never thought of filtering the form for the one record chosen in the combo/list. What I do to keep things in sync is A) remove record navigation buttons, put a requery of combo/list in the Form_AfterUpdate event, C) put Me.ComboOrListName = Me.txtWhatever in the Form_Current event (assuming txtWhatever contains key field and bound column of combo/list is same field). These steps usually handle the issues you mentioned, and allow adding a record on the same form. One gotcha: I’ve found you have to have a form-level boolean (bClickedList for example) and set it to True in the combo/list after update event, just before setting the Me.Bookmark item in the Wizard-generated code. In the Form current event, say If bClickedList = False Then (and do item C above). Then, set the boolean value to False. This prevents the list/combo trying to reset itself infinitely. I’m curious about problems you mentioned with RecordsetClone in A2k. I’ve not noticed any problems.

Re last paragraph: That was just an example. I have a project in a database which needs to get valid employees from the Personnel database. I Want a combo listing those employees, but don’t want to take a chance of anybody in my db changing that sensitive information. I couldn’t figure out how to link a table but make it read-only. With a recordset, that’s easy. Also, sometimes you need more flexibility in filling your listbox. What if you need data from more than one table? What if you want an item labelled or at the top of your list? Those things are doable with a UNION query. Finally, I said ADO but could just as easily have said DAO. Both work to obtain a recordset with a SQL query. I try and use ADO because it’s more consistent and works with more data sources, and besides, it’s M$’s technology du Jour.
Thanks for the comments.

Reply | Quote

[indent]

---

I’m curious about problems you mentioned with RecordsetClone in A2k. I’ve not noticed any problems.

---

[/indent]I’m rather curious about that myself, unless Wendell is talking about Clone Recordsets rather than Recordsetclones. The former is ADO and the latter DAO. The DAO approach works fine in A2k, the other is problematic because a Recordset.Clone is an independent recordset, NOT the same thing as a Recordsetclone.

Reply | Quote

Actually, I was talking about Clone Recordsets, which is what the code for the combo wizard generates when you create a navigation combo using Access XP. It really doesn’t work very well either – some sort of race condition I think that causes it to sometimes end up on the wrong record.

Reply | Quote

I suspected that might be the case. I think they shot themselves in the foot with clone recordsets as opposed to recordsetclones. When I use ADO, I prefer to just do the find on the real recordset for that very reason. Not as tidy, but you don’t wind up on the wrong record either.

Reply | Quote