Solved

Set field sequence of a form in sheetview

Posted on 2016-09-16
9
37 Views
Last Modified: 2016-09-17
I added a field to a table which is part of an existing query and then included the field in the query design in the position that I want it to display.
When I view the query, the field is in the required position.
I also inserted a field in the corresponding form in the required position.
When I view the form in sheetview, the new field is right at the right hand end of the fields.
Is there a way of determining the field sequence without using VBA?
This is what I did. The field in question is [Confirm].
Query design: query design
Form  design: form designDatasheet view: Datasheet viewThanks
0
Comment
Question by:Fritz Paul
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41801571
Theoretically, in datasheet view, you should be able to drag the column to where you want it, and then click the save button in the QAT.  That should save the position.
0
 

Author Comment

by:Fritz Paul
ID: 41801580
You've got me. What is the "QAT"? Please.
0
 

Author Comment

by:Fritz Paul
ID: 41801584
Oh OK, I found it on Google.
No it does not work.
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 125 total points
ID: 41801623
You do that by setting (in code only) the property ColumnOrder of the textbox/combobox.

First control has no. 1, and if you set a control to, say, 4, those with values of 4 and higher will be shifted up by 1.

/gustav
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 125 total points
ID: 41801691
Then use the technique that Gustav mentions.

Use the Load event of the form, and code similar to the following,  I generally set them in reverse order, right to left, setting the columnorder to zero for each control.

Private Sub Form_Load

    me.controls("txtFarRight").columnOrder = 0
    me.controls("nextCol").columnorder = 0
    ...
    me.controls("farLeftControl").columnorder = 0

End Sub

Open in new window

 If you step through this, you should see each control moved into the first position and then shift to the right as the next control is repositioned.

I've also got an EE article on allowing users to customize their datasheet column orders and widths, and then save those settings for the next time they load a datasheet.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
ID: 41801879
MS has completely hosed the way datasheets work in newer versions of Access.  It is virtually impossible to control position and width of columns.  Someone on the team should look at how this worked in A2003 and go back to that feature set.

When you add a new control to a form it is always a good idea to press the TabOrder icon on the Design tab and then either press the Auto Order button or manually rearrange the sequence.  You can also do this by typing a number into the tab index property of an individual control.  I usually end up doing one or all of these depending on how many controls I am repositioning and how complex the form is.

Also, the picture you displayed does not show the form design of a DS form.  It looks like a continuous form.  When you display this form in DS view, you will have trouble changing the column names since there are no labels attached to the textboxes and that is what Access is going to use to create the column headings.  If you use a Continuous format form and display it as DS, Access uses the column names from the query as the column headers.  It was years before I worked this out.  Nothing in the documentation mentions this.  I used to avoid using DS subforms because I never want to use my internal column names as the column headers.
DSViewLayout.JPGNotice that the column headers match the control labels rather than the column names.
DSViewData.JPG
0
 

Author Comment

by:Fritz Paul
ID: 41802254
Dear PatHartman,

Thanks for your reply. It looks like what I was looking for and I will test it out and give feedback.

I know the VBA method, but specifically asked "Is there a way of determining the field sequence without using VBA?".

Thanks.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41802273
The problem is as I said in my opening statement, MS has completely hosed this process.  USUALLY, setting the tab order will get the columns in the desired order but not always.  SOMETIMES, if the tab order won't work, you can open the form in Layout view and drag the columns around.  It is getting your edits to stick that is so elusive.
0
 

Author Closing Comment

by:Fritz Paul
ID: 41803025
Thanks guys.
It is solved, but this is such an unfriendly process, just to get your fields in required sequence!
Regards
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now