Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


displace input row in Access 2003 and SQL Server 2000


displace input row in Access 2003 and SQL Server 2000

Author
Message
omid.shokri
omid.shokri
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 107
Hi,
Output in Access 2003:

ID | Description | Quantity | Title | Obj
--- ---------------- --------------------------------------------------

22| 6 | 253000.00 | |
23| 7 | 330000.00 | |
17| 1 | 340000.00 | 8414 | 69327
18| 2 | 120000.00 | 8414 | 69344
19| 3 | 615000.00 | 8414 | 69327
20| 4 | 320000.00 | 8414 | 69327
21| 5 | 809500.00 | 8414 | 69327

Query :

SELECT      TVFundBillDetail.ID ,
TVFundBillDetail.HID ,
TVFundBillDetail.Description ,
TVFundBillDetail.Quantity ,
TVFundBillDetail.Title ,
TVTitle.Name TitleName ,
Obj ,
TVAllObjects.Name ObjName
FROM TVFundBillDetail
LEFT OUTER JOIN TVTitle ON TVFundBillDetail.Title = TVTitle.Code
LEFT OUTER JOIN TVAllObjects ON TVFundBillDetail.Obj = TVAllObjects.Code


Problem: sequence of input row show in description column. when title and obj is null then displace sequence of input row. application is access 2003 and DBMS is SQL Server 2000
WendellB
WendellB
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 1633
Adding an "ORDER BY HID" clause to your SQL statement should correct the sort order.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
omid.shokri
omid.shokri
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 107
WendellB (11/11/2012)
Adding an "ORDER BY HID" clause to your SQL statement should correct the sort order.

Thank you for reply.
Because I used this query in microsoft access 2003, with order by can not data entry in form.
WendellB
WendellB
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 1633
We need some additional information if this query is being used as the data source for a data entry form. First of all, is your database a .adp file or a .mdb file? Second, if this is a .mdb file, are your tables linked using ODBC? Third, does your table have a primary key, and if so what field is it - ID or HID, and are either or both autonumber fields? Finally, are you displaying this on a continuous form or one record at a time? In theory if your table has a primary key, the ORDER BY clause won't prevent you from adding new records.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
omid.shokri
omid.shokri
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 107
WendellB (11/11/2012)
We need some additional information if this query is being used as the data source for a data entry form. First of all, is your database a .adp file or a .mdb file? Second, if this is a .mdb file, are your tables linked using ODBC? Third, does your table have a primary key, and if so what field is it - ID or HID, and are either or both autonumber fields? Finally, are you displaying this on a continuous form or one record at a time? In theory if your table has a primary key, the ORDER BY clause won't prevent you from adding new records.


1- I used SQL Server 2000 as DBMS.
2- Refer 1
3- ID and Identity column
4- Continuous form

the ORDER BY clause prevent me from adding a row.
WendellB
WendellB
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 1633
omid.shokri (11/11/2012)


1- I used SQL Server 2000 as DBMS.

That wasn't my question. What is the file extension of the Access database that contains your data entry form - .ADP or .MDB?

2- Refer 1
3- ID and Identity column
4- Continuous form

the ORDER BY clause prevent me from adding a row.

It is difficult to debug problems where you are using Access as a front-end to SQL Server as you can't really upload the SQL Server database like you can with the Access front-end. Can you script the table and copy the results to a post?

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
omid.shokri
omid.shokri
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 107
WendellB (11/12/2012)
omid.shokri (11/11/2012)


1- I used SQL Server 2000 as DBMS.

That wasn't my question. What is the file extension of the Access database that contains your data entry form - .ADP or .MDB?

2- Refer 1
3- ID and Identity column
4- Continuous form

the ORDER BY clause prevent me from adding a row.

It is difficult to debug problems where you are using Access as a front-end to SQL Server as you can't really upload the SQL Server database like you can with the Access front-end. Can you script the table and copy the results to a post?


I used .ADP file.


WendellB
WendellB
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 1633
That helps consisderably. It appears that you really want to sort the display by the Description column then. (It also appears that either Access or SQL Server thinks that "Description" is a reserved word - you might want to edit the column name to something like "txtDescr" to prevent possible problems.) Since that is a varchar(200) you are going to get an alpha sort rather than a numeric sort - is that field always going to be numeric? You could try adding an "ORDER BY Description" and see if that works. How is the value of the Description field set? By the user, or by some logic on the form? Note that Access forms always add a new record at the bottom of the form, so unless you add logic to resort the recordset after each new record, the display will no longer be sorted the way you want.

Finally, one note of caution about using the .ADP format. Beginning with Access 2013, that format is no longer supported, and in both Access 2007 and Access 2010 there were no enhancements beyond what was in Access 2003.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
omid.shokri
omid.shokri
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 107
WendellB (11/12/2012)
That helps consisderably. It appears that you really want to sort the display by the Description column then. (It also appears that either Access or SQL Server thinks that "Description" is a reserved word - you might want to edit the column name to something like "txtDescr" to prevent possible problems.) Since that is a varchar(200) you are going to get an alpha sort rather than a numeric sort - is that field always going to be numeric? You could try adding an "ORDER BY Description" and see if that works. How is the value of the Description field set? By the user, or by some logic on the form? Note that Access forms always add a new record at the bottom of the form, so unless you add logic to resort the recordset after each new record, the display will no longer be sorted the way you want.

Finally, one note of caution about using the .ADP format. Beginning with Access 2013, that format is no longer supported, and in both Access 2007 and Access 2010 there were no enhancements beyond what was in Access 2003.


I used description column to show order of rows. When use ORDER BY Clause Access prevent the adding row and fire exception on "Me.AllowAddition = true". Otherwise sequence of record is "FundBillD.ID" (shown in figure in previous post).
WendellB
WendellB
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 1633
It's not clear to me why sorting the data would prevent you from adding a new record, but I seldom work with .ADP Access applications. Perhaps one of the other forum members can shed some light on your situtation. What I would suggest as an alternative is creating an indexed view data source that does the sort in SQL Server before the data is displayed on the form. The support of those in SQL Server 2000 isn't quite as robust as in later versions, but we have used those with success in applications based on SQL Server 2000.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search