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
988 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 7
  • 5
  • +1
27 Comments
 
LVL 3

Expert Comment

by:jcrozier21
ID: 38804026
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
ID: 38804221
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
ID: 38815617
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Author Comment

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

Expert Comment

by:IrogSinta
ID: 38816276
Can you post the rowsource for the listbox?
0
 
LVL 15

Author Comment

by:David L. Hansen
ID: 38816791
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
ID: 38816835
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
ID: 38816848
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
ID: 38816865
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
ID: 38819007
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
ID: 38819076
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
ID: 38819461
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
ID: 38820299
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38820468
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
ID: 38828376
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
ID: 38833211
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
ID: 38833544
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
ID: 38833879
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
ID: 38835608
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
ID: 38835762
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
ID: 38836976
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
ID: 38837280
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
ID: 38837378
What about scrollbar hiding??
0
 
LVL 61

Expert Comment

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

Expert Comment

by:mbizup
ID: 38837406
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
ID: 38837460
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
ID: 38858679
Works great, thank you everybody...really!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 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