By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,377 Members | 3,052 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,377 IT Pros & Developers. It's quick & easy.

Datasheet Freezing Columns And Tabing

P: n/a
Hi

I have a datasheet with 32 columns, the first 2 columns are frozen
and disabled. What I would like to do is as a user tabs thru a row
on the sheet is to shift the sheet to the left so it becomes the first
column next to the frozen columns ( column 3 ), also if the user
back tabs the sheet would shift to the right. I am doing it now by
setting the columnwidth of the column being tabbed out of to a
width of 10 and when it gets focus resetting the columnwidth back
to its normal size. Is there a way to do this without setting and
resetting column widths? I now I can also change the order of
the columns but I was trying to avoid that. Thank you in advance
for your help.
Dec 24 '06 #1
Share this Question
Share on Google+
14 Replies


P: n/a
Just a style comment. Assuming that this a datasheet/continuous view of
your form (and data entry should only be through forms) then 32 bound
controls is a lot. Why make the user tab/pan/scroll through all those? Why
not pop-up a single record style form that can display all the fields that
you want, in the order that you want?
-Ed

<fh***@aol.comwrote in message news:eU****************@newsfe12.lga...
Hi

I have a datasheet with 32 columns, the first 2 columns are frozen
and disabled. What I would like to do is as a user tabs thru a row
on the sheet is to shift the sheet to the left so it becomes the first
column next to the frozen columns ( column 3 ), also if the user
back tabs the sheet would shift to the right. I am doing it now by
setting the columnwidth of the column being tabbed out of to a
width of 10 and when it gets focus resetting the columnwidth back
to its normal size. Is there a way to do this without setting and
resetting column widths? I now I can also change the order of
the columns but I was trying to avoid that. Thank you in advance
for your help.

Dec 24 '06 #2

P: n/a
Hi Ed,

The data is entered 1 column at a time, each column represents a
single job, the data is the time worked on that job, so the data is
being entered by column not by row. As the operator completes
a 'Job/Time' sheet they horizontally scroll to the next job column (it
could be the next column in order or not ) making it the first column
next to the frozen columns, I would like to do that for them by using
the tab, I would also like to setup a hot key that would tab to the next
column and return them to the first row of the sheet. Thank you.

Bob
Dec 24 '06 #3

P: n/a
Exactly how, in Access, do you enter records one column at a time rather than
one row at a time?

fh***@aol.com wrote:
>Hi Ed,

The data is entered 1 column at a time, each column represents a
single job, the data is the time worked on that job, so the data is
being entered by column not by row. As the operator completes
a 'Job/Time' sheet they horizontally scroll to the next job column (it
could be the next column in order or not ) making it the first column
next to the frozen columns, I would like to do that for them by using
the tab, I would also like to setup a hot key that would tab to the next
column and return them to the first row of the sheet. Thank you.

Bob
--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1

Dec 24 '06 #4

P: n/a
Not the whole column, the operator enters the data in
a field and arrows down to the next field. Do you know
how to do what I'm asking?
Dec 25 '06 #5

P: n/a

fh***@aol.com wrote:
Not the whole column, the operator enters the data in
a field and arrows down to the next field. Do you know
how to do what I'm asking?
When we design a continuous form and (later) view the form in datasheet
view the properties and event procedures we introduced for the
continuous form hold for the datasheet.

I'm not sure what you are asking, but this achieves my interpretation:

Dim r As DAO.Recordset
Private Sub CompanyName_KeyDown(KeyCode As Integer, Shift As Integer)
Dim c As Control
Dim z As Long
If KeyCode = vbKeyTab Then
DoCmd.RunCommand acCmdRecordsGoToNext
If Me.NewRecord Then
r.MoveFirst
Me.Bookmark = r.Bookmark
For z = 0 To Me.Controls.Count - 1
Set c = Me.Controls(z)
If c Is Application.Screen.ActiveControl Then
DoCmd.GoToControl Me.Controls(z + 1).Name
Exit For
End If
Next z
End If
End If
End Sub

Private Sub Form_Load()
Set r = Me.RecordsetClone
End Sub

I expect there is an easier way to proceed to the next control than:
For z = 0 To Me.Controls.Count - 1
Set c = Me.Controls(z)
If c Is Application.Screen.ActiveControl Then
DoCmd.GoToControl Me.Controls(z + 1).Name
Exit For
End If
Next z
but it does not pop to my mind right this minute.
In any case, something should be included to handle the errors that
will occur when the next control is disabled and cannot accpet the
focus, and when there is not next control.

In a wizard produced form in Northwind based on Quarterly Orders, this
code results in the cursor progrssing down the CustomerName column on
the promptings of Tabs, and, at the bottom, jumping up to the top and
moving over to the City Column.

Dec 25 '06 #6

P: n/a
Hi Ed,

What I'm trying to do is move the data sheet left automatically.

The datasheet looks like this:

Employee Number | Employee Name | Job 1 | Job 2 | Job 3 | ect
123456 AAAAAAAAA 2.5 3.0
987654 BBBBBBBBB 1.2 4.1
ect

The Employee Number and Employee Name columns are frozen.
When the user opens the datasheet Job 1 row one has focus, the
user either tabs to the job column the want to enter data in ( the
job columns represent the time an employee worked on the job
in fractional hours ) or horizontally scrolls to the column. The data
entry sheets they work off of looks like this:

Job Number: 1

Employee Number | Employee Name | Time
123456 AAAAAAAAA 2.5
987654 BBBBBBBBB 1.2
ect

The data entry sheet is preprinted with the Employee Number and
Employee Name and match the order on the datasheet, the Job
Number on the sheet is filled in by the job supervisor and corresponds
to a column on the datasheet.

So what I'm trying to do is when the operator tabs to the Job
column they will be entering time for ( or if they horizontally
scroll and click on it ) it shifts the sheet to the left making
it the first column next to the Employee Name ( column 3 ).
As they enter the data they arrow down to the next employee.

I played around with the column order but thats not really a
workable solution because I would like to keep the original
order of the columns at all times. I've also played around
with hiding the columns but then you can't scroll the sheet
to the right and reveal a column. ( I didn't actually hide the
column just set the width very narrow so you could backtab
and when it got focus expand it to normal size ).

Sorry I wasn't clearer before and thank you for your help.
Dec 25 '06 #7

P: n/a
Well, the repeating "Job" numbers is a flag to me that you may want to
rethink your data structure, and normalize the tables. What you have now is
more of a flat file spreadsheet. What Access ( and other relational
databases) work best with is a combination of relatively small related
tables. I.e.

tblEmployees
EmpID
EmpLastName
EmpFirstName
DOB
SSN
etc.

tblJobs
JobID
JobDate
Supervisor - linked to EmpID in tblEmployees
Details
etc.

tblWorkRecords
WorkID
EmpID -linked to EmpID in tblEmployees
JobID -linked to JobID in tblJobs
TimeWorked
etc.

Once you have this set of related tables, then you can design forms for data
entry. Done properly, you would entering the least amount of info to make a
complete record; i.e, the Employee name would only need to be entered once,
all other related records would use the employeeID. I know that you've
invested time in the present setup and may not be able to change the paper
input sheets, but getting a good foundation will save you lots of problems
in the future. Some examples: with your current structure, you'll have to
add a field to your table each time that you add a job. And there's now
easy way of getting the total man/hours on Job#x ( you'll need a domain
function), nor an easy way of getting the total hours for employee-X, etc.

-Ed

<fh***@aol.comwrote in message news:Ov************@newsfe10.lga...
Hi Ed,

What I'm trying to do is move the data sheet left automatically.

The datasheet looks like this:

Employee Number | Employee Name | Job 1 | Job 2 | Job 3 | ect
123456 AAAAAAAAA 2.5 3.0
987654 BBBBBBBBB 1.2 4.1
ect

The Employee Number and Employee Name columns are frozen.
When the user opens the datasheet Job 1 row one has focus, the
user either tabs to the job column the want to enter data in ( the
job columns represent the time an employee worked on the job
in fractional hours ) or horizontally scrolls to the column. The data
entry sheets they work off of looks like this:

Job Number: 1

Employee Number | Employee Name | Time
123456 AAAAAAAAA 2.5
987654 BBBBBBBBB 1.2
ect

The data entry sheet is preprinted with the Employee Number and
Employee Name and match the order on the datasheet, the Job
Number on the sheet is filled in by the job supervisor and corresponds
to a column on the datasheet.

So what I'm trying to do is when the operator tabs to the Job
column they will be entering time for ( or if they horizontally
scroll and click on it ) it shifts the sheet to the left making
it the first column next to the Employee Name ( column 3 ).
As they enter the data they arrow down to the next employee.

I played around with the column order but thats not really a
workable solution because I would like to keep the original
order of the columns at all times. I've also played around
with hiding the columns but then you can't scroll the sheet
to the right and reveal a column. ( I didn't actually hide the
column just set the width very narrow so you could backtab
and when it got focus expand it to normal size ).

Sorry I wasn't clearer before and thank you for your help.

Dec 26 '06 #8

P: n/a
On Mon, 25 Dec 2006 18:11:59 GMT, fh***@aol.com wrote:

Ed is right on the db design advice.

Additionally, scrolling horizontally with a few columns locked (like
you can do in Excel with Freeze Panes) is not possible in Access.

One more difference between the two environments, underscoring that
Access is not Excel, and each requires its own design approach.

-Tom.

>Hi Ed,

What I'm trying to do is move the data sheet left automatically.

The datasheet looks like this:

Employee Number | Employee Name | Job 1 | Job 2 | Job 3 | ect
123456 AAAAAAAAA 2.5 3.0
987654 BBBBBBBBB 1.2 4.1
ect

The Employee Number and Employee Name columns are frozen.
When the user opens the datasheet Job 1 row one has focus, the
user either tabs to the job column the want to enter data in ( the
job columns represent the time an employee worked on the job
in fractional hours ) or horizontally scrolls to the column. The data
entry sheets they work off of looks like this:

Job Number: 1

Employee Number | Employee Name | Time
123456 AAAAAAAAA 2.5
987654 BBBBBBBBB 1.2
ect

The data entry sheet is preprinted with the Employee Number and
Employee Name and match the order on the datasheet, the Job
Number on the sheet is filled in by the job supervisor and corresponds
to a column on the datasheet.

So what I'm trying to do is when the operator tabs to the Job
column they will be entering time for ( or if they horizontally
scroll and click on it ) it shifts the sheet to the left making
it the first column next to the Employee Name ( column 3 ).
As they enter the data they arrow down to the next employee.

I played around with the column order but thats not really a
workable solution because I would like to keep the original
order of the columns at all times. I've also played around
with hiding the columns but then you can't scroll the sheet
to the right and reveal a column. ( I didn't actually hide the
column just set the width very narrow so you could backtab
and when it got focus expand it to normal size ).

Sorry I wasn't clearer before and thank you for your help.
Dec 26 '06 #9

P: n/a
I don't know what version of Access you're using, Tom, but "scrolling
horizontally with a few columns locked (likeyou can do in Excel with Freeze
Panes)" certainly IS possible in Access 2000!

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1

Dec 26 '06 #10

P: n/a
Hi Ed,

This application is a replacement for a mainframe
data entry screen and it's only function is to accept
the input and it's transfered back to the mainframe.

The process works like this:

On a daily basis a text file containing the Employee
Number and Employee Name is generated on the
mainframe. The previous days input in the Access
application is deleted ( all records in the table are
deleted and the new file imported ). The job times
are inputed and at the end of the day the file is
exported back to the mainframe for processing.

Any new employees are added on the mainframe
so the Access application needs no add new
functions.

The table in the Access application is the same
basic layout as the file input on the mainframe.
When the table data is exported it is formated
for the mainframe application.

As far as more jobs being needed I set it up for
30 and there is really never more than 10 or 12
needed at any given time.

The job numbers are recycled, once job 1 is
complete a new job is assigned the number 1
or 2 ect.

While this would probably not be my first design
choice, it closely mimics the input screen on the
mainframe (which is what the customer wanted,
the company is in the process of removing it's
old mainframe terminals wherever possible),
and by keeping the table in the same format for
the mainframe it minimized what had to be done
on both the PC and mainframe sides of the process.

While I guess I could have setup an Excell spread
sheet for this I'm a little more familiar with Access.

Anyway thats the backround on this and I appreciate
your help.

Thank you

Bob
Dec 26 '06 #11

P: n/a
"fh***@aol.com" <fh***@aol.comwrote in message
<Ov************@newsfe10.lga>:
Hi Ed,

What I'm trying to do is move the data sheet left automatically.

The datasheet looks like this:

Employee Number | Employee Name | Job 1 | Job 2 | Job 3 | ect
123456 AAAAAAAAA 2.5 3.0
987654 BBBBBBBBB 1.2 4.1
ect

The Employee Number and Employee Name columns are frozen.
When the user opens the datasheet Job 1 row one has focus, the
user either tabs to the job column the want to enter data in ( the
job columns represent the time an employee worked on the job
in fractional hours ) or horizontally scrolls to the column. The
data entry sheets they work off of looks like this:

Job Number: 1

Employee Number | Employee Name | Time
123456 AAAAAAAAA 2.5
987654 BBBBBBBBB 1.2
ect

The data entry sheet is preprinted with the Employee Number and
Employee Name and match the order on the datasheet, the Job
Number on the sheet is filled in by the job supervisor and
corresponds to a column on the datasheet.

So what I'm trying to do is when the operator tabs to the Job
column they will be entering time for ( or if they horizontally
scroll and click on it ) it shifts the sheet to the left making
it the first column next to the Employee Name ( column 3 ).
As they enter the data they arrow down to the next employee.

I played around with the column order but thats not really a
workable solution because I would like to keep the original
order of the columns at all times. I've also played around
with hiding the columns but then you can't scroll the sheet
to the right and reveal a column. ( I didn't actually hide the
column just set the width very narrow so you could backtab
and when it got focus expand it to normal size ).

Sorry I wasn't clearer before and thank you for your help.
I think Lyles suggestion looks promising. If you can't make that work,
then here's another property to play with. The .SelLeft property of
the form.

Say, in the forms keydown event, with keypreview enabled, all columns
are equally wide - note - this is only air code, using litterasl, no
errochecks, no testing... you'll have to adapt/see if it fits your
challenge yourself.

if keycode = vbkeytab then
select case me.selleft
case is < 30
me.selleft = me.selleft + 2 ' one less than number of
' visible columns
me.selleft = me.selleft - 3
case else
me.selleft = 30
end select
end if

--
Roy-Vidar
Dec 26 '06 #12

P: n/a
Hi Lyle,

Thank you for responding, I will play around with
this and see if I can make it work. Again thank
you and I appreciate your help.

Bob
Dec 26 '06 #13

P: n/a
Hi Roy,

Thank you for responding, Looking thru the
Access help I thought selleft is what I needed
but had no idea how to implement it, I will play
around with this and see if I can make it work.
Again thank you and I appreciate your help.

Bob
Dec 26 '06 #14

P: n/a
On Tue, 26 Dec 2006 14:53:31 GMT, "missinglinq via AccessMonster.com"
<u28780@uwewrote:

Oops, you are correct. In datasheet view, right-click a column
header.
-Tom.

>I don't know what version of Access you're using, Tom, but "scrolling
horizontally with a few columns locked (likeyou can do in Excel with Freeze
Panes)" certainly IS possible in Access 2000!
Dec 27 '06 #15

This discussion thread is closed

Replies have been disabled for this discussion.