displace input row in Access 2003 and SQL Server 2000
-
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
-
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! -
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.
-
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! -
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.
-
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! -
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.
-
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! -
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).
-
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!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply