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

[Galaxiom]

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.

It affects all records because all records share the exact same control which has be reproduced multiple times.

In an ordinary form (Single Form View) the cascading combo Row Source is configured for that single visible record. Each time you move to another record the On Current Event reconfigures the Row Source.

Any other record with a value that is not part of that particular Row Source would be showing an Error just as it does in the datasheet or Continuous Forms View. It just doesn't matter because those other records are not being displayed.

 

[Simon_MT]

You should be able to get Cascading combiboxes to work on a Continuous Form if:

The Rowsource is set in VBA and triggers On Entry and then removed On Exit. This way everytime ou go into the combibox the information has to be provided afresh.

Simon

 

[David8]

You should be able to get Cascading combiboxes to work on a Continuous Form if:

The Rowsource is set in VBA and triggers On Entry and then removed On Exit. This way everytime ou go into the combibox the information has to be provided afresh.

Simon

Simon, this is not an idea that's been put to me before, and sounds interesting. As a beginner I'm not confident setting up the Rowsource in code like this - would you be able to do me a small example?

 

[smig]

@Simon - sorry to say, but you'r bringing him back to the original issue.
if you cahnge the Rowsource for a combo in a continous form it will change for all combo on this form.
this will cause some to show empty data.

@David - did you try what I suggested ?
I do change the rowsource of the combo (combo3) in VBA but it will not effect the combo you see (combo2)

 

[JANR]

As a beginner I'm not confident setting up the Rowsource in code like this - would you be able to do me a small example?

See if attached db might help.

JR

 

[gemma-the-husky]

david8

this has probably been pointed out.

to do what you want, you probably have the design wrong. what oyu need is a main form, and a subform. select the building you want in the main form, and then access will auto-filter the rooms to just how the rooms for the selected building.

acces is a very advanced application though, and is more intractable than excel. Its far more powerful, and faster for managing data, but the data has to be designed correctly - otherwise it becomes next to impossible to use effectively.

It isn't just access. This is relational database (set) theory. It's no good complaining it doesn't work. The problem is generally that the designer isn't getting it right.

 

[Simon_MT]

There is another way put your Search combis on the Parent and rather than linking the parent and subform use a query in the subform referencing the combi and then use a wildcard. Push the user into the first combi and requery the subform On Exit of each combi. This way there is a little flexibility in the Search process that not all the combis have to completed yet information will still be returned.

Simon

 

[David8]

See if attached db might help.

JR

Really interesting how you've got datasheet view to behave almost exactly how I wanted it to.

However, unfortunately in your example datasheet view only works for data entry (it seems not to show pre-existing records). I want datasheet view for:
Priority 1: Data review
Priority 2: Data editing or ammendment

Data review seems to be a possibility. But, data editing with cascade combos in datasheet view seems impossible.

I shall look into using continuous view, however its array of continuously shown drop down arrows is unfortunate. Datasheet view is probably smarter.

I am currently trying to work out how to get a datasheet view data review form containing a pop-up form to edit or add individual records. Ideally it would pop-up with the record that had been in-focus in datasheet view.

 

[David8]

See if attached db might help.

JR

To quote a note you placed on the example you very helpfully produced:

"Cascading comboboxes on datasheet and continious forms works when DataEntry property is set to True. If you use it to edit then you'll lose the display value from previous entries, only new records will retain its display value. (don't worry it will be in the table)."

Unfortunately if I'm using it to edit, then I really MUST see the display values from previous entries, otherwise I'll have difficulty selecting what I want to edit.

I do really appreciate your example though it is interesting. Thanks.

 

[David8]

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

I'm really sorry, but I'm struggling with your example. As I think we established, I want to see my data in datasheet view by default.

I thought you had suggested to me that I should be using pop-up forms? I had thought you were saying that although cascade combos would not work in datasheet view, I could have a pop-up form coming out of datasheet view for individual entries that would handle the cascade combos.

Your pop-ups in your example do not come from out of datasheet view. Secondly, I am getting some debugging prompts when I try to use some of the buttons.

 

[David8]

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.

I'm just checking we haven't misunderstood each other. Now I've done a bit of research into pop-up forms I am still a bit hazy about them (I think they need a deal of code to set up well - not ideal for me but I'll go there if I have to), but I think they generally require a button to pop them up with.

Given that I want to view my data primarily in datasheet view (I hope I was clear on that), where is the button going to go?

I am viewing a whole load of IT equipment. I want to see lots of records at once - datasheet view is ideal for this, whereas a continuous form is tolerable but very much less ideal.
If I am in datasheet view looking at the equipment records on a laptop and the real physical equipment with my eyes and I spot a record that needs changing - then I'll need the cascade combo capability. That could be in a pop-up, but it would have to pop-up FROM a datasheet starting point. Is that possible?

 

[John Big Booty]

Try this then.

 

[John Big Booty]

There is not a great deal of difference between the function of a datasheet and a continuous form. The main difference is that a datasheet has the look (but not the functionality) of a spreadsheet whilst a continuous form can be made to have a more user friendly feel to it.

 

[David8]

There is not a great deal of difference between the function of a datasheet and a continuous form. The main difference is that a datasheet has the look (but not the functionality) of a spreadsheet whilst a continuous form can be made to have a more user friendly feel to it.

Multiple records (or field values for multiple records) can be pasted into datasheet view. Is that possible with continuous view?

Coming to the latest example you posted (thank you) it shows a datasheet view form embedded within a more ordinary looking form. That doesn't tie in with my thinking so far, perhaps it should -you'll have to correct me.

Let's say I am auditing IT equipment. Some of it may have been moved or upgraded, without that having been recorded properly. I have to make sure the records are up to date. I open a datasheet view form that summarises all the equipment. I sort it first by building then by room, then by equipment type, and I begin to walk through the rooms checking, scrolling down the datasheet as I go.

I find something that has been moved to a new location. I use the find function and locate the item in the database by its inventory number. I want to edit its location characteristics. Maybe at this stage I pop-up another form to edit the item. I'd like to choose its new building leading to a cascade lookup for its new room within that building. (This is not the only intended use of cascade, but I'll stick to this simple example for purpose of this discussion if I may). I've now edit the data to put the item in its new location. Maybe at this stage I resort the datasheet, then I continue to walk through the rooms, checking, checking.

I don't quite see how your example fits in with this. Its records are still shown individually, with a sub-datasheet for details of their addresses. I do not want to use datasheets to summarise details, instead I want to use datasheets to summarise the vast bulk of the data.

 

[John Big Booty]

Have you double clicked on the record selector in the data sheet?

 

[David8]

Thanks John

Your first posted example did say something about double clicking the record-selector, but it wasn't in datasheet view and I wasn't sure what the record-selector was.

But (thanks for your patience) I have just seen what you mean.

Suddenly I am optimistic again - this does look really promising. So THAT'S how you pop-up a form from a datasheet view. I found no mention of this technique by googling.

 

[David8]

Have you double clicked on the record selector in the data sheet?

Well I have now! So this is based on the "On Dbl Click" event. Is it possible to describe the event procedure with the help of a wizard or UI, or do you have to go to the VB editor?

 

[David8]

So basically, did you write this yourself, or did you get the program to write it for you?

Option Compare Database
Private Sub Form_DblClick(Cancel As Integer)
 
    Dim stDocName As String
 
 
    stDocName = "FRM_AddAdd"
    DoCmd.OpenForm stDocName, , , , , , "Edit" & Me.Address1 & "*" & Me.Address2 & "?" & "*" & Me.ClientID & "*" & Me.ClientAddID & "*" & Me.StateID & "*" & Me.PCodeID & "*" & Me.TypeID
 
 
End Sub

If you wrote it yourself, would you mind stepping me through it?

 

[John Big Booty]

I wrote that my self, the last section highlighted in purple;

DoCmd.OpenForm stDocName, , , , , , [COLOR="DarkOrchid"]"Edit" & Me.Address1 & "*" & Me.Address2 & "?" & "*" & Me.ClientID & "*" & Me.ClientAddID & "*" & Me.StateID & "*" & Me.PCodeID & "*" & Me.TypeID[/COLOR]

Is the OpenArgs to understand what that is doing you need to On Load event of the Pop Up form.

Essentially it is all the data that is required to populate that pop up form.

 

[David8]

John,

I also note that FRM_AddAdd contains no bound fields. Instead of being bound they rely on the buttons and the code behind them. The button is linked to a click, and that to an event procedure - which is command 14. Clicking on the 3 small dots leads me through to the VB editor and a scary (for me) amount of code for FRM_AddAdd.

I cannot master all that tonight, but maybe I am mistaking how you actually set up these buttons? Did you say on click then event/command (in the UI) and then for this event go straight to the code to describe how the command would work? Or is there an easier way? Because if this method relies on code as much as it looks, then I've got a lot of learning to do.

I'm happy to do it, I just want to check I'm not wasting time by first understanding how much of that code was generated by the program.