Scroll a subform

[Secret Squirrel]

I have a subform that is in continuous view. There are a lot of fields on the
form and I need to scroll left and right to view all the fields. I also
scroll up and down since I'm only showing 25 records on the form at a time
due to the size of the form. The first column in the form is the employee
name. When I scroll to the right I can't view that column anymore. Is there a
way to have that column show on my main form to the left of my subform but
still scroll through the employees names when I scroll up and down? It's sort
of like freezing a column like in a spreadsheet. I know this can be done in
datasheet view but I need to use a continuous form for this application.

 

[Peter Hibbs]

A Flex Grid Control will do what you want but you will need to write a
bit of VBA code, have a look at my Flex Grid Demo program for some
examples. You can fix the first (or any number) column so that it does
not scroll.

Go to http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=180

However it is a bit overkill for a simple form, why do you have to use
a Continuous form rather than a Datasheet type form?

HTH

Peter Hibbs.

 

[Secret Squirrel]

The reason I want to use a continuous form is because the first column is
going list my employees and then the next 20 columns will be where I enter
dates for that task completed. I want to be able to double click on the field
and have it pop up my calendar so I can choose a date for that field. I
looked at your flex grid demo but I'm not sure how that will work for me. Can
you maybe explain it a little more please?

 

[John W. Vinson]

The reason I want to use a continuous form is because the first column is
going list my employees and then the next 20 columns will be where I enter
dates for that task completed. I want to be able to double click on the field
and have it pop up my calendar so I can choose a date for that field. I
looked at your flex grid demo but I'm not sure how that will work for me. Can
you maybe explain it a little more please?

If you're storing 20 dates in 20 fields in your table... you're "committing
spreadsheet", and you should reconsider your table design!!

What are your tables? What are the relevant fields? How are the tables
related? If each employee has multiple scheduled tasks, and each task may
involve multiple employees, you should really model it as a many to many
relationship with a table of Employees and a table of Tasks, both related one
to many to a table of Assignments; rather than 20 *fields* for tasks, you
would enter 20 *records* - or 24 or 38 or whatever.

 

[Secret Squirrel]

I agree with you John. But the powers that be want to be able to see a "grid"
with the employees listed on the left and the tasks on the top. Then inside
the grid they want to see a date where that task for a specific employee has
been completed. They want to be able to enter a date right into a field on
the grid when a task has been completed. They have it set up right now in a
spreadsheet and basically want to duplicate that in access.

I don't know another way to do it and that's why I'm just going by what they
want.

I have a table for my employees and then another table for my tasks. They
are linked together by the tblemployees ID - PK.

I just don't know another way to set this up to look like that. Maybe you
have another idea that might work?

 

[John W. Vinson]

I agree with you John. But the powers that be want to be able to see a "grid"
with the employees listed on the left and the tasks on the top. Then inside
the grid they want to see a date where that task for a specific employee has
been completed. They want to be able to enter a date right into a field on
the grid when a task has been completed. They have it set up right now in a
spreadsheet and basically want to duplicate that in access.

I don't know another way to do it and that's why I'm just going by what they
want.

I have a table for my employees and then another table for my tasks. They
are linked together by the tblemployees ID - PK.

I just don't know another way to set this up to look like that. Maybe you
have another idea that might work?

Don't confuse data STORAGE with data PRESENTATION. They are separate tasks
with separate requirements!

You may want to use a DataGrid or a (non-normalized, wide-flat) temporary
table to display and enter data, but you would really be better off storing
the data in a normalized form. Sure, it's more work setting up the form - you
must have VBA code to move the data from the form into the table and vice
versa; but it's going to be an utter nightmare if you try to store a variable
and unpredictable list of tasks in fieldnames.

 

[Peter Hibbs]

Secret Squirrel,

As John says, you need to have the data properly normalised but it
sounds as if you have already done that.

As far as using a Flex Grid control is concerned there are two methods
you could use. You could create a query which returns the data you
need and then write some code to copy this into the Flex Grid control
or you create a Crosstab query and copy the output from that into the
control. This option is easier as it requires considerably less code.
If you are interested I have made up a small database with a Flex Grid
control which does basically what you want. You can find it at :-

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=429

If you decide to go for the Flex Grid option and you need any more
information about it, just post back.

HTH

Peter Hibbs.