How can I set focus to each detail row of a subform in a way that will cause a data-refresh?

I have a MS-Access 2007 form with a subform within it.  This subform has header, detail, and footer sections.  Within the detail section I've placed a couple of data-bound controls (ListBoxes).  These work great, but only when a detail row gets focus.  There are usually about 3 details showing at one time and only the first one shows the correct data when the form opens.  The user currently has to click into each detail row to get the current data to show for those rows.  In the code if I just programmatically set the focus to one of the problem rows it doesn't help.  So far only the mouse clicking by the user causes the data to refresh.  Anybody dealt with this?
LVL 15
David L. HansenProgrammer AnalystAsked:
Who is Participating?
 
David L. HansenConnect With a Mentor Programmer AnalystAuthor Commented:
As an add-on to my last comment.  I believe I've found most of the solution.  "Recalc" does the trick (so glad to have found that)!  I thought I was done when I successfully implemented it; however, it only works for the detail-rows that are showing (even though the code is applied to all the rows).  And even with those rows that show, as soon as the user scrolls past them, they get their listbox blanked out again (ie. scroll past then back and it's blank).  Here is the code:
'code in the parent form
Private Sub Form_Activate()
        With Form_CallLogSubform
        .Recalc
        .Recordset.MoveNext
        .Recalc
    End With
End Sub

Open in new window

If you make this a EOF loop then you'll just see blank listboxes for the reasons I've described.

Any ideas?
0
 
jcrozier21Commented:
Here's a few things to try

me.child.form.requery
me.child.form.refresh
me.child.form.recordset.movelast
me.child.control.setfocus
me.child.form.dirty = false

Access tends to load on demand, which is why you often don't see the record count on the record navigation control until you have browsed to the last record. I think Snapshot recordset type may solve that, though it's never been a problem for me so I haven't had to work around it.

EDIT: You could also try attaching this to a button for debug purposes
me.child.form.recordset.movelast
debug.print me.child!control

Open in new window

It may throw an error that could yield a clue.
0
 
IrogSintaCommented:
Do you mean to say thay you a listbox in a continuous form? I'm not certain that would work. What do you have as the rowsource for each listbox?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
David L. HansenProgrammer AnalystAuthor Commented:
Yes, that's what I'm doing.  It works great (except for the fact that the user needs to click in the detail for it to refresh the listbox in that detail).  The datasource, or rather "Source Object" for the subform is just the name of the subform itself.  I inherited this project and have just added a few things.
0
 
David L. HansenProgrammer AnalystAuthor Commented:
But the parent form's "record source" is a saved query (a simple select statement).
0
 
IrogSintaCommented:
Can you post the rowsource for the listbox?
0
 
David L. HansenProgrammer AnalystAuthor Commented:
It's just a simple Select query...like:

SELECT myID, myDescription FROM myTable

Note: the "Bound Column" property is set to 1 (so it can be used as a value member, ie. myID)
0
 
IrogSintaCommented:
I don't follow your design. Can you post a screenshot or upload a stripped down copy of your database?  If you upload a db, be sure to remove any private info first.
0
 
David L. HansenProgrammer AnalystAuthor Commented:
Note, in addition to the above comment I should point out that I've written two subroutines to accommodate the listbox.  One just receives a recordset in as a parameter which tells the subroutine which items to highlight (ie. select).  The other manages the table which holds the data about which selections apply to which detail...when a selection is made this routine adds to the table and when a selection is deselected it deletes the entry from that table.
0
 
IrogSintaCommented:
which tells the subroutine which items to highlight (ie. select)
I don't follow the need for this routine.  Isn't the listbox bound to a field in your table?  It would really be helpful if you can upload a copy of your database.
0
 
David L. HansenProgrammer AnalystAuthor Commented:
You see, one SELECT statement just fills the listbox with all of the choices (that's the listbox's datasource).  The subroutines, on the other hand, manage the highlighting of items in that listbox (both when the detail row containing the listbox is first shown and also when new choices are selected/deselected by the user).

It's not that I don't want you to see the code structure for the listbox behavior, but really all I need is to programmatically cause each detail-row of the subform to refresh (like it does when the user clicks in a detail-row).  NOTE: This refreshing currently happens no matter where in the detail-row the user clicks (even if it is nowhere near the listbox...that is what I want to imitate).

Isn't there some sort of "IsDisplyed" event for the detail-row object that I can use?  I'd place some sort of row.refresh command in it.  That's the kind of behavior that I'm attempting.
0
 
IrogSintaCommented:
what I'm getting at is that if the list box is bound to your table, you wouldn't need the procedure to select the item in the listbox, it would already be selected at the onset.  Maybe I just don't understand what you're trying to do. Can you at least post a screenshot?
0
 
David L. HansenProgrammer AnalystAuthor Commented:
Here are the screen shots:

This is how the screen looks when it is freshly loaded (I've done nothing).  You'll notice in this one that only the first row has selections in the listbox showing (there actually is one in the second detail-row too...it just isn't shown).
On Loading
If I click (see my arrow drawn freehand) in the second detail-row then the selection in the listbox is refreshed and shown.
On Detail-Row Click
By the way, the listbox must be fed by two tables (thus the need for the subroutines).  One table gives all the choices and the other table gives the selections assigned to that particular detail.
0
 
mbizupCommented:
Did you try all of the suggestions in the first post (http:#a38804026 )?

What happened when you moved to the last record in the subform's recordset?
0
 
IrogSintaCommented:
I understand now.  I didn't realize you were working with multiselect listboxes, hence my confusion.  Try this:
    With Me.RecordsetClone
        .MoveFirst
        Do While Not .EOF
            Me.Bookmark = .Bookmark
            '...Call routine to highlight listboxes here.
            
            .MoveNext
        Loop
        .MoveFirst
        Me.Bookmark = .Bookmark
    End With

Open in new window

0
 
David L. HansenProgrammer AnalystAuthor Commented:
I have tried the suggestions...I think I must be implementing a good solution the wrong way though.  I have a cleaned up database that I would be happy to post.  However, since it is a client's project, mbizup could you just see to it that the attachment gets deleted from this thread after the thread is closed out?
0
 
mbizupCommented:
What about scrapping the multi-select listbox and going with a different approach?

Option Groups?
sub-subforms ?


I'm almost certain that we can find an approach that works more naturally than this.
0
 
IrogSintaConnect With a Mentor Commented:
The main problem here is that there is no event that fires when you use the scrollbar.  If you still want to proceed in this manner, I would recommend hiding the scrollbar and adding 2 buttons for scrolling up or down 3 records at a time. This way you could call the routine to fill your listboxes.  Other than that, a different approach may be better as Miriam suggested.
0
 
David L. HansenProgrammer AnalystAuthor Commented:
I thought about doing that IrogSinta (just replacing the scrollbar).  I wasn't sure how to find the current top-most displayed row so I can key off of that and move three more up or down (provided that many rows above/below the current rows exist).  I love that approach though...it would be a quick solution.

Thanks to both of you BTW :)
0
 
mbizupCommented:
Another option if you can live with the look/feel is to change that subform to "Single Form View", which is a solution I have used in various databases with complex subforms.
0
 
David L. HansenProgrammer AnalystAuthor Commented:
I'd like to make the attempt of keying off of the the current row to drive a custom "scroll" function.  How would I do that?  I don't expect you to just go do all of the work, but which commands would I need to use the grab this sort of information?
0
 
jcrozier21Commented:
Just navigate me.form.recordset

IE
forward button:
me.form.recordset.movenext

back button:
me.form.recordset.moveprevious
0
 
David L. HansenProgrammer AnalystAuthor Commented:
What about scrollbar hiding??
0
 
mbizupCommented:
That should be in the form's design -- scrollbar properties Horizontal/vertical/both or none.
0
 
mbizupCommented:
Also for recordset navigation, try Ron's method at http:#a38820468 as well - using the recordsetclone.

The idea there is that navigating the recordsetclone has no visible effect, so you can iterate through three (or however many) records without any effect that the user can see.   Once done, you  can bookmark the forms recordset to match the recordset clone position, and that is when the user sees the record change.

Me.recordset.bookmark = me.recordsetclone.bookmark

The net effect of both methods is the same, but using the recordsetclone method in some cases can appear less choppy.
0
 
David L. HansenProgrammer AnalystAuthor Commented:
So grateful!  I will implement these this evening and let you know how it goes.
0
 
David L. HansenProgrammer AnalystAuthor Commented:
Works great, thank you everybody...really!
0
All Courses

From novice to tech pro — start learning today.