Solved

First visible row in form property?

Posted on 2004-04-26
19
1,073 Views
Last Modified: 2008-03-03
Is there a way to ask a form what the first visible row is?  I have controls on my form which cause some rows to disappear and reappear and I would like the row to end up in the same place after the user chooses to hide and then unhide some of the rows.

The best I can do is remember the current row before buttons are pressed, and then make that the first visible row after all the buttons are pressed, but that often causes the view of the data to jump around.

Basically I have a view of objects some of which are lots and some of which are wafers in lots.  The user can hide the wafers and then unhide the wafers.  If the user is currently pointing to the row that is fifth from the visible top, then after hiding and unhiding the wafers, I want the cursor on the same row and I want it to be fifth from the visible top.

Any ideas?  I am using Access97, but may soon be upgrading my app to Access2000.  So a solution that works in both or either would be acceptable.
0
Comment
Question by:dastrw
  • 7
  • 5
  • 3
  • +2
19 Comments
 
LVL 11

Expert Comment

by:Mighty_Silly
Comment Utility
Hi dastrw,

I don't quite understand your need.

So you have a control that user selects, then click on a button to Hide/Unhide wafers?

What kind of control is this?  I have multiple Listboxes, and no matter where I go, when I return, the HighLight remains where I have made my selection prior to my clicking on various buttons.

-Silly-
0
 
LVL 7

Expert Comment

by:IT-Schubertz
Comment Utility
dastrw,

have a look at the bookmark property for the underlying datasource.
With this, your solution should be easy.

If you need further assistance, simply ask!

Greetings

Andy
0
 
LVL 7

Expert Comment

by:IT-Schubertz
Comment Utility
sorry, its the bookmark method of course, not a property.

Greetings

Andy
0
 
LVL 2

Author Comment

by:dastrw
Comment Utility
Silly,

The way I hide/unhide rows is to change the WHERE clause for the main table upon which the form is based, and then requery.  So normally the WHERE clause is blank, but I can also make it:

where object_type = "Lot"

and then the wafers don't show up.
0
 
LVL 11

Expert Comment

by:Mighty_Silly
Comment Utility
Hi D,

And you change your source via a command button, which changes your Form's RecordSource?  It then requeries the form to display the new criteria?

This is what I have:  when I make my Main List Box selection, it displays and drives a second (or third) List box.  This is where it might help you to decide where to go...  

Now, if I make a selection in my second (or third) list box, I can hide them and when I unhide them again, the selection stays where they are.... HOWEVER, if I change the Main List Box selection, such that it removes the selected items in listbox 2 or 3, then the focus of those two listboxes are removed.

Am I making sense?

I guess we need to know more of How your form works: like Make selection in LOT list box, click button to show Waffers... something like that.  This way, there's no guess work on the EEs.

-Silly-
0
 
LVL 2

Author Comment

by:dastrw
Comment Utility
Andy,

Thanks for the tip on bookmarks (I didn't know about them) but it doesn't seem like they'll work for my situation since I do a Requery to hide/unhide some records and bookmarks are invalidated by Requery's.  Also, I am already doing something like that to get the behavior I do have:

'Remember current object name
currObjName = Me.txt_object_name

'Requery base table (Check boxes are read and specify whether to show/hide certain object types)
Call requeryLotInfo

'Find object  (this puts object at top of visible rows on form, NOT at location it was before requery)
Me.txt_object_name.SetFocus
DoCmd.FindRecord currObjName, acAnywhere, True, acSearchAll, False, acCurrent, True

What I am asking for is a fine-tuning on what I already have.  Instead of finding the last object, what I want to do is:

1) Find out what the current record is
2) Navigate above it to the topmost record which is still visible
3) Remember how many rows I moved up to get to the top
4) Requery my form based on the status of checkboxes to hide/unhide objects
5) Navigate to the correct record so that the the current record is in the same location as it was before the requery, i.e. same object and same no. of rows below top of visible region.

0
 
LVL 2

Author Comment

by:dastrw
Comment Utility
Silly,

I'm not trying to hide/unhide elements in a list box.  I am trying to hide/unhide rows on the main display area of the form.  I am using a tabular form where multiple records are shown at the same time.  I can query the form to get the current record, but I can't query the form to ask it what the topmost visible record is or where the current record is in respect to the topmost visible record:

Records in table:

.....
Lot 11
 Wafer 11-1
 Wafer 11-2
-----------------------------------
Lot 12    (<--- Topmost visible record)
 Wafer 12-1
 Wafer 12-2
 Wafer 12-3
Lot 13   [CURRENT RECORD pointed to by cursur and 5th from top of visible region]
 Wafer 13-1
 Wafer 13-2  (<-- Bottommost visible record)
-----------------------------------------------
Lot 14
  Wafer 14-1
  Wafer 14-2
....

When I choose to hide the wafers, I want the Lot 13 record to be the 5th row from the top.
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
You can not selectively hide items in a list box, and you can not hide individual detail rows in a detail section of a form.  Its an all or nothing deal.

You MUST query those items out of the objects rowsource at runtime to make this happen.

If the records were selected you would have something to hook to (SelTop, SelHeight)  But just being listed in the window of the form doesn't qualify for this property...  

What you are asking for is not do-able without a significant effort in code and experimentation to my knowledge.  

WHY EXACTLY is it this functionality you need?  What purpose would this serve that simply refreshing the list does not?





 
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Hmm, perhaps a bit odd, but it looks to me that the mechanism you're looking for it that of a "Treeview" with opening and closing nodes and optionally an "expand all" or "collapse all"

Right?

Nic;o)
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 2

Author Comment

by:dastrw
Comment Utility
jadedata,

You can "hide" rows in the form detail section if you change the filter/where clause.  When a user checks or unchecks the "Show Wafer Data?" box on my form, then this causes the where clause to be changed and the form to be requeried.

If there are hundreds of records in the base table, and I don't have the form "remember" where the cursor is currently pointing, then the requery causes the cursor to move back to the first viewable record.  If the user was looking at records near the bottom of the table, then showing/hiding wafers is very disorienting.

I have minimized the disorientation by remembering the current record, but the best I can do is FIND that record.  This causes this record to be the topmost viewable record.  This is not always the case, since the current record is often in the middle of the viewable range.  So showing/hiding records is now just mildly disorienting.

Somewhere deep in the bowels of Access it must know what the topmost visible record is in order to display things properly.  Whether this property is also available to programmers is my basic question.

0
 
LVL 2

Author Comment

by:dastrw
Comment Utility
nico5038,

Your tree idea is what I wanted to implement, but the users around here are Excel fanatics and want everything to look like spreadsheets.

I may be able to give them something like that eventually, but this application's release has already been delayed too long so I'm looking for a fine-tune rather than an overhaul.
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
Your use of "hide" is in fact not hiding anything,... it is in fact NOT bringing those record into the recordset.  It's not like modifying the .visible property of an object.

As a matter of fact, Access does not know this record by any useable identifier.
You can have the first, last, min, max, darkest, lightest, first selected, how many selected,.....
   but not something as arbitrary as which record shows first.

There are several factors that could affect whether a record is showing at the top of a visible form list.

0
 
LVL 2

Author Comment

by:dastrw
Comment Utility
jadedata,

Whether or not a particular row has its corresponding pixels being displayed through the graphics card onto the screen is probably one of the least arbitrary attributes of that particular record.

Whether or not Bill lets us have access to this attribute directly is my question.
0
 
LVL 32

Assisted Solution

by:jadedata
jadedata earned 250 total points
Comment Utility
My point being that Access doesn't know it's showing unless/until some action like selection takes place, but it's action is not based on whether the record is showing or not.  This is exampled by the currently selected record being able to scroll out of view, but remain as the selected record.

Bill is not giving you access to the Access attribute to my knowledge.  There may be some obscure API code that could be hammered into a tool for this but nothing in plain vanilla Access/VBA
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 250 total points
Comment Utility
Hmm, another option is to have datasheets "nested" with a + to expand and - collapse the subform rows.
When you have A2000 it's only a matter of defining the related key in the relationship window and you're able to see the effect when opening the tables.
A27 doesn't have this so that might be a problem for you.

Idea ?

Nic;o)
0
 
LVL 2

Author Comment

by:dastrw
Comment Utility
nico,

Or I could have subforms that I can show or not show.  When I choose to not show the subform, then that would free up more real estate to show more info on the form.

When I storyboarded this project to the users, all of my form-subform ideas were trashed because not enough info was displayable given all of the overhead to show subforms within forms.  But I hadn't thought about hiding the subform in cases where this info was not wanted.

I'll think about this for the next iteration.

0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Hard thing to educate users, I know out of first hand :-)
Making some working samples will often work best.
Personally I have created an instruction document how to filter a datasheet with the right-click and most users love it as they are now in "full control" when sorting and filtering. Besides that it saves me a lot of time coding all sorts of filterings.
Drop me a line when interested and I'll mail the document.

Success !

Nic;o)
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
About the time you get all the information on the form your users want, is when you will hear that the forms are now too crowded.
 :)
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
thanx dastrw!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

763 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

8 Experts available now in Live!

Get 1:1 Help Now