Solved

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

Posted on 2013-01-21
27
955 Views
Last Modified: 2013-02-06
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?
0
Comment
Question by:David L. Hansen
  • 13
  • 7
  • 5
  • +1
27 Comments
 
LVL 3

Expert Comment

by:jcrozier21
Comment Utility
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
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
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
 
LVL 15

Author Comment

by:David L. Hansen
Comment Utility
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
 
LVL 15

Author Comment

by:David L. Hansen
Comment Utility
But the parent form's "record source" is a saved query (a simple select statement).
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
Can you post the rowsource for the listbox?
0
 
LVL 15

Author Comment

by:David L. Hansen
Comment Utility
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
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
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
 
LVL 15

Author Comment

by:David L. Hansen
Comment Utility
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
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
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
 
LVL 15

Author Comment

by:David L. Hansen
Comment Utility
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
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
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
 
LVL 15

Author Comment

by:David L. Hansen
Comment Utility
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
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
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
 
LVL 15

Author Comment

by:David L. Hansen
Comment Utility
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
 
LVL 15

Accepted Solution

by:
David L. Hansen earned 0 total points
Comment Utility
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
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 500 total points
Comment Utility
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
 
LVL 15

Author Comment

by:David L. Hansen
Comment Utility
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
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
 
LVL 15

Author Comment

by:David L. Hansen
Comment Utility
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
 
LVL 3

Expert Comment

by:jcrozier21
Comment Utility
Just navigate me.form.recordset

IE
forward button:
me.form.recordset.movenext

back button:
me.form.recordset.moveprevious
0
 
LVL 15

Author Comment

by:David L. Hansen
Comment Utility
What about scrollbar hiding??
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
That should be in the form's design -- scrollbar properties Horizontal/vertical/both or none.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
 
LVL 15

Author Comment

by:David L. Hansen
Comment Utility
So grateful!  I will implement these this evening and let you know how it goes.
0
 
LVL 15

Author Closing Comment

by:David L. Hansen
Comment Utility
Works great, thank you everybody...really!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now