Datasheet problem: no datasheet showing… but the row selectors are

[funominal]

Datasheet problem:
Just moved to Access2000. I created a new database with a new datasheet form.

My datasheet in my form shows I have 9 records (from a table) by showing the navigation bar, but the datasheet itself is blank!! Well almost blank, the datasheet area is dark gray, with no datasheet showing… but the row selectors are still there, indicating there should be some rows...but nothing there! And the Format/Unhide Columns menu item (Access2002) don’t show any columns to Unhide.

This is probably something simple I’m just not aware of with Access02 since it is my 1st exposure to it. Please help.

 

[funominal]

I was told that apparently you have to place controls on the form and bind them for a datasheet to work... but this seems like a form to me, not a datasheet.

I ended up using the ObjectSource property to assign a table or query to the datasheet rather than using an underlying SQL statement for the recordsource since it would not allow it. I did however, set the parent forms recordsource to an SQL statement, put the datasheet form as a subform on the parent, and synchronized it with code, but not a simple solution. I will need to layout some forms with controls to really get what I want for certain data sets.

Does anyone know how to hide the navigation bar on a datasheet? I tried setting it at the form level for the datasheet, but it still shows...

 

[markphsd]

Do you not want to use continous forms?

Mark P.

Bleh

 

[funominal]

No because I want to use a dynamic record source that could be from any of 100 different tables or queries... I don't want to have to place controls on the continuous form specific to the fields of each table/query.

like I said, I'll probably have to form some main queries like Company, Orders, etc.

 

[markphsd]

Oh yeah, I had the same sort of thing I wanted to do a couple of months ago. You can't create a data sheet that way. A datasheet is really a form, it just looks like a table. You can't create controls in runtime on a form, only in design view. Therefore you can't add or remove fields.

However, one option might be to create a bunch of controls. Name them generic names like txt1, txt2. etc..Set their visiblity to false.

Then when you want to set the record source have a main form that you select the record source from, then have it update the sub form: loop through the fields on the table and set the txtboxes to the control source of the fields, set the visibility property to true. You'll hit a maximum of fields you can do when you don't have anymore textboxes.

This seems like a limited solution, because you can't create textboxes in a form in runtime. You would just have to guess how many controls you might ever use; however 255 would be a good guess, because you can't have more then 255 fields in a table. So it's a perfect solution. Wow, I think I'll do this.

Let me know what you think.


Mark P.

Bleh

 

[funominal]

Thanks. That sounds like a good idea for the long term, and for maximum flexibility. I would like to do it, but not sure if the time coding would be worth it in the short term, so will probably make some forms for the main screens (maybe 10 or so). I may reapproach this later when I have more time, and try it. If you end up doing it, I would love to see your code.

Caution: I remember reading somewhere that Access keeps a running total of all the controls you use during the lifetime of a form, so I believe there is a physical limit to what Access would allow...you would probably reach the limit when reusing a dynamic form in this way. If I come accross the article I'll post it for you.

Thanks!

 

[markphsd]

Thanks. That would be helpful.

There's also the question of what would this be used for, currently i have a form that exports any selected table, it exports into any of the avalible formats and you can choose them.

It's kind of worrisome to give someone access to tables and all of their data. So, although that would be a fun excersise, I can't really imagine implementing it full scale.

Mark P.

Bleh

 

[funominal]

I'm using it for data entry and viewing summary data via the datasheet subform (better for some cases than form view as far as viewing is concerned).

I'm also using datasheet view because I have so many tables & queries... otherwise it would be too many forms. I have built in some security that disables usage of viewing/editing table data. I will build forms for the main data where a lot of data entry is required, or related subforms come into play.

 

[markphsd]

Hey,

If you are going to just use it for data entry like that, what you can do is set up a list box that has all of the tables.
Then open a table based on the selection of the list box. You can set up another list box for queries.



Mark P.

Bleh

 

[funominal]

I'll think about that as another option... it's a slight variation from what I've done, which is use a combo box with a table where I've just put the names of queries and tables in it, and used the Left(3, name) function against the selection to determine if the name selected was a tbltablename (tbl) or qryqueryname (qry), then set the ObjectSource of the datasheet on a subform to that name. It works. Your idea would also be good, except that I'm using the main form to show common fields that are on all tables, like Status, Class, CreatedBy, etc. (using Dlookup and some other stuff to get the values in the form fields dynamically from the datasheet).

Thanks for all the input, I really do appreciate it.

Jeff