473,320 Members | 2,110 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Datasheet Freezing Columns And Tabing

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
14 4560
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
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
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
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

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
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
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
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
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
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
"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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: MacDermott | last post by:
I have an Access 2000 form with a subform in Datasheet view. When I click on an item in the header of the subform (the title at the top of one of the columns), I'd like to be able to capture the...
13
by: Aladdin | last post by:
I have an MS Access form on which I have a listbox listing tables in that database. I want to be able to click on any of those tables and view its contents on the same form using subforms or any...
2
by: Arnie | last post by:
I have searched this NG for datasheet column width posts and have read many of them. None of them seem to address what I would like to do. I'm beginning to think what I want can't be done. Here...
1
by: sasan3 | last post by:
when I set visible=false, then entry is not visible in form view, but still shows up in datasheet view. How do I make a field not to show based on some condition in datasheet view? Tx.
2
by: Job Lot | last post by:
Is there any way of freezing columns in Windows Forms Data Grid control? Thanks
1
by: S. van Beek | last post by:
Dear reader, I try to switch between to columns in a form showing as datasheet view. The idée is to show one of the two columns in a datasheet. The problem is that the property visible...
1
by: Simon | last post by:
Dear reader, In a form with the property "Default view" as DataSheet I like to hide some fields in the datasheet view. It is possible to manage this with the command Format>>Unhide...
5
by: Newbie | last post by:
I would like to display certain columns from a table on a form. One of my columns that I want to show is a Yes/No field so that my users can pick their players for a fantasy football week. If...
5
by: deekay | last post by:
I want to allow users to resize and reposition columns of a datasheet but for a prompt to be brought up and only the layout only to be saved if they select "save changes". This is the way it works...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.