Frustrated with Datasheet View, I need help (1 Viewer)

David8 · Nov 8, 2010

I would like to use datasheet view for what is probably the most important form in my database. I like/need its unfussy ability to display large amounts of data at once, such that moving the eye from one record to another and back is very easy.

I also really need cascade lookups. But my attempts at cascades lookups in datasheet view are resulting in problems. To use the simplest example of Buildings and Rooms, and if Building is the first 'master' lookup, while Rooms is the second (dependent) lookup, then if Building A is the active cell in the first lookup, then only those rooms that belong in Building A will show up in the Rooms column/field within the datasheet view form.

This is obviously totally unacceptable for the review of data. I DO NOT want to choose between datasheet view and cascade lookups. I want both. Is there a way, please? I'm getting a bit frustrated with Access over this. Maybe there is some code I should be using somewhere?

Galaxiom · Nov 8, 2010

Every record in a datasheet shares the same controls and consequently cannot have different RowSources on each record.

You can get around this by showing the combos on the datasheet inside popup forms.

David8 · Nov 8, 2010

Galaxiom said:
Every record in a datasheet shares the same controls

Oh for goodness sake! I'm rapidly losing patience with Access. This is RUBBISH. Surely that does not have to be that way?

David8 · Nov 8, 2010

I think what Glaxiom is saying is that Datasheet view is not compatible with cascade combo boxes/lookups if I also want the Datasheet to display itself correctly at all times?

I hope not.

But popup forms.... I will look at them next. I do hope they aren't another aspect of Access which seems to offer the chance to achieve what I want, leads me on a steep and winding learning curve, but ultimately ends in disappointment and frustration. But they sound like a way forward, the idea sounds like a reasonable way of melding the restrictions of forms with datasheet view's data review freedom. I'll have to see what the Access implementation of pop -up forms is. I didn't know the feature existed.

Galaxiom · Nov 9, 2010

David8 said:
Oh for goodness sake! I'm rapidly losing patience with Access. This is RUBBISH. Surely that does not have to be that way?

Think about how the form is structured. There is only one RowSource property for a combo. The datasheet and continuous form are just repititions of one set of controls.

Maybe someone else has a better idea but the popup form is the only way I can think of to deal with it (other than a whole page of unbound combos and a behemoth of a loop to change them every time you scrolled

).

Developing on any platform is all about finding a way to achieve your goals with what is provided. Patience and tenacity are essential in programming. There are many frustrations.

John Big Booty · Nov 9, 2010

The work around for this is to populate your datasheet with a query that collects all the information you wish to display.

To add or edit records you then use a pop up form that uses your cascading combo set, and then requires and refreshes the datasheet when it is closed.

David8 · Nov 9, 2010

Galaxiom said:
Think about how the form is structured.

Look with an ordinary form, even if it has cascade lookup elements, every record is displayed correctly as you move from one record to the next (I think, anyway, I haven't done a thorough check, but I'd darn well hope so).

But with datasheet view, with a cascade lookup the master combo box focus in the cascade is allowed to affect the rest of the cascade fields FOR ALL RECORDS. Why? This does not happen in ordinary form view.

John Big Booty · Nov 9, 2010

Galaxiom said:
... but the popup form is the only way I can think of to deal with it (other than a whole page of unbound combos and a behemoth of a loop to change them every time you scrolled ).

.......

SNAP

I've got this feeling of Déjà vu

David8 · Nov 9, 2010

Did you say that (pop-up form suggestion)? I'm sorry I didn't pursue that. I'll go back and review our other conversation to see where you mentioned it.

John Big Booty · Nov 9, 2010

David8 said:
Look with an ordinary form, even if it has cascade lookup elements, every record is displayed correctly as you move from one record to the next (I think, anyway, I haven't done a thorough check, but I'd darn well hope so).

But with datasheet view, with a cascade lookup the master combo box focus in the cascade is allowed to affect the rest of the cascade FOR ALL RECORDS. Why? This does not happen in ordinary form view.

That is because with a standard form, you only see one record from the Record Set at a time, whilst with a continuous or datasheet view you are seeing all the available records at once, so the record that currently holds focus determines what is shown in any derived controls on that form.

David8 · Nov 9, 2010

John Big Booty said:
I've got this feeling of Déjà vu

Fair play, I've checked and you did indeed. I even said I'd look at it.

I got a little side-tracked by taking offence at something, and forget to go back and reconnect with the substance. Well, I was up really late at the time, but I owe you an apology.

boblarson · Nov 9, 2010

David8 said:
Look with an ordinary form, even if it has cascade lookup elements, every record is displayed correctly as you move from one record to the next (I think, anyway, I haven't done a thorough check, but I'd darn well hope so).

Yes, on a single form it works that way. And that is because a single record is being shown at a time so the control IS what is being shown.

But with datasheet view, with a cascade lookup the master combo box focus in the cascade is allowed to affect the rest of the cascade fields FOR ALL RECORDS. Why? This does not happen in ordinary form view.

You have already been told why. In datasheet view and in continuous form view, if you look in design view you only see one set of controls, correct? When you go to look at the form in datasheet or continous form view then Access is using a COPY of the control for each record displayed, with the current record selected being the live one. The "behind-the-scenes" stuff works its magic to show the records on either side of the current one. If you have a cascading combo - you are setting the control to view based on the current value of the other combo. So, if you change the current combo, the one on the current record will show the right stuff, but everything else will go wonky. This is a limitation and source of irritation for many of us (not only you). So you have to think in a different manner than what you expect. You may not be able to have it act exactly as you would like and may have to go a different route than originally planned. That is just the way it goes sometimes. Access is good but it does have its limitations.

David8 · Nov 9, 2010

John Big Booty said:
so the record that currently holds focus determines what is shown in any derived controls on that form.

Couldn't Access let datasheet view records each have their own individual focus simultaneously? Or is that going to be a massive hardware resource hog?

boblarson · Nov 9, 2010

David8 said:
Couldn't Access let datasheet view records each have their own individual focus simultaneously? Or is that going to be a massive hardware resource hog?

Sorry, but accept the fact that is not how Access is built. Send the Access Team some feedback about this, but it is what it is and it can't be changed at this point.

David8 · Nov 9, 2010

boblarson said:
The "behind-the-scenes" stuff works its magic to show the records on either side of the current one.

Edit: sorry comment removed. A bit too facetious. (I suggested the magic (code) could do with improving.)

David8 · Nov 9, 2010

boblarson said:
Sorry, but accept the fact that is not how Access is built.

Right. A bit of a shame, but I'll have to. Its good to get confirmation on when I am stuck due to lack of knowledge of the program on the one hand, and when I am stuck due to what the program can do on the other.

But, although I have to go now, I must find some time to look at pop-up forms which John first suggested to me. They may offer a way forward for me.

Simon_MT · Nov 9, 2010

Can I get this straight - you have a Building and you want to see the rooms in that Building (A) so what happens after looking at Building A and you want to look at the rooms in Building B. Requery the subform to get the Rooms in Building B?

I was surprised how easy Cascading combiboxes are in Continuous forms but I don't think that is really the answer. It seems to me that you select a building and want to see the rooms in that building in the subform. Correct me if I'm wrong, if I haven't understood the question correctly.

Simon

David8 · Nov 9, 2010

Simon_MT said:
Correct me if I'm wrong, if I haven't understood the question correctly.

Simon

Simon, I have IT equipment with a number of cascade elements eg Manufacturer and Model. Location (Building and Room) is another example.

The records are actually the items of IT equipment. There are no subforms. I do not want to see the rooms from each building. I want to see all the IT equipment, and in a datasheet so I can sort it whichever way I like.

I'd also like the cascade lookups for certain elements such as selecting the Building>Room (location) of equipment or ItemType>Manufacturer>Model is another example of a cascade and there are probably others. The trouble is that cascades and datasheet view do not mix very well.

Pop-up forms used in conjunction with datasheet view has been suggested as a possible solution. But I really MUST go now, I'm sorry, and I won't be able to post again for a day or so.

John Big Booty · Nov 9, 2010

Here's an example of how it might be done.

smig · Nov 9, 2010

there is a solution for this I used:
create 3 combo:
1. is your main one - the first selected
2. is the second one - the one that you select after you select the first one. make only the drop down sign of it visible. use the Form_OnCurrent and Combo1_AfterUpdate events to requery combo2
3. can have all possible values, but can't be selected nor changed.
use combo2_AfterUpdate event to send combo2 value to combo3.

allign combo2 and combo3 so they will look like one (combo3 on top of combo2)
the only problem I found is you can't write a text in combo2 to select. you must drop down and scroll the list.

Frustrated with Datasheet View, I need help (1 Viewer)

Registered User.

Super Moderator

Registered User.

Registered User.

Super Moderator

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Smeghead

Registered User.

Smeghead

Registered User.

Registered User.

Registered User.

Registered User.

AWF VIP

Attachments

Registered User.

Similar threads

Users who are viewing this thread