Subform Comboboxes in Datasheet View

[gray]

Hi All

WinXPPro (SP2)
Access 2002 (SP3) and Access 2007

I have a mainform with a number of tabs on it. On the 1st tab there is a subform.

On subseqent tabs there are controls some of which are bound to fields in the table 'Tracks'and the mainform has many buttons (including my own navigation buttons). I wanted to display a datasheet-view of the table 'Tracks' but wanted the main form to remain in form-view such that all these features can be used; this is the purpose of the subform on the 1st tab.

The main and sub forms both have a recordsource of :-

SELECT * FROM Tracks;

I syncronise bookmarks in the current events of main and subforms to ensure they both are 'looking' at the same record in the 'Tracks' table.

I have various other tables that record the properties of each main 'Tracks' record; 'Media Type' and 'Owner' for example. The 'master' Tracks table has fields in which are stored the autonumbers of the other property tables.. so 'Media_Type_ID' and 'Owner_ID' in my examples.

Unbound comboboxes on my subform are populated from these other tables via SQL in their rowsources (controlsources are left blank). In the current event of the mainform, I scroll to the appropriate row in the subform comboboxes so relevant data is displayed for the selected 'Tracks' record. Edits of Tracks' properties data are recorded via SQL updates in Vb.

All works marvellously well until I switch the subform to Datasheet view.

As the datasheet is scolled through, the columns contaning the comboboxes all display the same data for every record. This is quite difficult to put in text so I have attached some jpgs.

subform1.jpg - Shows Track75 selected - Track75 has no media defined for it yet and so the Media field is empty.

subform2.jpg - Shows Track75 selected and the possible options from the media combobox.

subform3.jpg - Shows Track79 selected. This track has a media property of 3.5" Floppy ... but this property is displayed for all records??

subform4.jpg - Shows Track89 selected. This track has a media property of CDR ... but, again, this property is displayed for all records??

I have the default view for the subform set to continuous forms?

Any idea why this is happening please?

Thanks in advance...

 

[lcook1974]

I'm not sure this can be done...Could you post a sample database?

 

[gray]

Hi

I think I can chop something down, yes. I'll post as soon as poss.

Essentially, what I'm trying to achieve is a datasheet view of, say, tableA.. but... where fields in tableA hold the IDs of items in other 'foreign' tables, they are populated with the textual 'name' fields from those foreign tables....

At the moment I have to rely on current events to requery the subform's controls and when in datasheet view, all 'unselected' rows appear to contain the 'selected' rows data.

Thanks for your assistance...

 

[gray]

Hi

I attach a very much slimmed down version of my Db but I think the features needed to look at my issue are working OK.

If you open 'Tracks_Form' you will see the subform in question on the 1st tab.

The 'View' button switches the subform between form and datasheet view.

If you want to change any of the fields, use the 'Edit' button to put the form into 'edit - mode'. Select your changes then press the temporary Save button located on the subform and then click the Cancel button which will put the form back into read-only mode.

Hopefully, my navigation buttons are self-evident.

Thanks very much for your time... very much appreciated...

 

[lcook1974]

Haven't forgot about you...I have some things to get done at work, then I'll jump all on it...like white on rice....to see if I can help out.

Larry

 

[gray]

Thanks LCook1974! I'm dabbling myself too... seems to me that there would be a strong requirement for this feature in a relational Db?

Look fwd to any ideas you have.... rgds

 

[lcook1974]

I can't see any of the table in the DB...

What I think you will have to do is this though...

I think you'll have to create another query and form. Then when you click the "view" button, have it open the "new" form in datasheet view with the list.

I really don't think you can do what you want with a continous form. I added a tab and a form. Added some queries too.

I hope this get you pointed in the right direction...I'll continue to see what I can come up with as far as a better solution goes.

OH BTW...Nice DB...you put a lot of work in that thing.

Larry

 

[gray]

Hi Larry

Thanks for that ! .. I think you are right...maybe it can't be done? I've tried all sorts of ways to make it work properly...

It seems to be the drop comboboxes confusing the form... when one is clicked on in Datasheet view , the data in it gets presented in all rows, not just the clicked one. But when I played with 'Lookups' this didn't happen.. could quite happily select drop down data in a row without it affecting all the others.... but this works at the table level I believe.

If I use a query to populate my subform, I don't think I could have all the options in the comboboxes.. although maybe I could use a field for the options as well as a the field for the actual selection itself.... that might crack it...

Thanks for looking at this... I know only too well how much time it takes up!

Rgds

P.S. "I can't see any of the table in the DB" ... sorry I think i left them as hidden objects... so (from memory) go to tools, options, show hidden and system objects...cheers

 

[lcook1974]

Sorry I couldn't be of more help...I'll continue to see about a work around when I get some time this weekend.

I looked at the tools hidden objects (tables) but must have missed the system objects. no worries though.

have a good day! if I come up with a better solution I'll let you know.