Frustrated with Datasheet View, I need help

D

David8

Registered User.
Local time
Today, 04:10
Joined
Sep 27, 2010
Messages
74
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?
 
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.
 
Galaxiom said:
Every record in a datasheet shares the same controls
Click to expand...

Oh for goodness sake! I'm rapidly losing patience with Access. This is RUBBISH. Surely that does not have to be that way?
 
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.
 
Last edited:
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?
Click to expand...

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 :rolleyes:).

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.
 
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.
 
Galaxiom said:
Think about how the form is structured.
Click to expand...

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.
 
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 :rolleyes:).

.......
Click to expand...
SNAP

I've got this feeling of Déjà vu :rolleyes:
 
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.
 
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.
Click to expand...

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.
 
John Big Booty said:
I've got this feeling of Déjà vu :rolleyes:
Click to expand...

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.
 
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).
Click to expand...
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.
Click to expand...
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.
 
John Big Booty said:
so the record that currently holds focus determines what is shown in any derived controls on that form.
Click to expand...
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 said:
The "behind-the-scenes" stuff works its magic to show the records on either side of the current one.
Click to expand...

Edit: sorry comment removed. A bit too facetious. (I suggested the magic (code) could do with improving.)
 
boblarson said:
Sorry, but accept the fact that is not how Access is built.
Click to expand...
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.
 
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
 
Simon_MT said:
Correct me if I'm wrong, if I haven't understood the question correctly.

Simon
Click to expand...
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.
 
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.
 
You must log in or register to reply here.

Similar threads

S
Solved Datasheet: Different back colors for columns and alternate row colors
Replies
17
Views
377
AHeyne
A
B
Edit a row on continuous form while the other rows are locked.
Replies
7
Views
587
MajP
MajP
klsblues
Solved Combobox to return the values from a table that haven't already been used and exclude the values that have been used
Replies
5
Views
212
klsblues
klsblues
R
Error Filtering on Lookup Field from a Query
Replies
14
Views
287
Pat Hartman
P
J
Solved Conditional formatting on a form using the expression builder.
Replies
17
Views
301
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom