Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

displace input row in Access 2003 and SQL Server 2000 Expand / Collapse
Author
Message
Posted Saturday, November 10, 2012 11:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 12:40 AM
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
Post #1383422
Posted Sunday, November 11, 2012 8:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:41 AM
Points: 139, Visits: 509
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!
Post #1383438
Posted Sunday, November 11, 2012 9:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 12:40 AM
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.
Post #1383448
Posted Sunday, November 11, 2012 3:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:41 AM
Points: 139, Visits: 509
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!
Post #1383471
Posted Sunday, November 11, 2012 9:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 12:40 AM
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.
Post #1383493
Posted Monday, November 12, 2012 5:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:41 AM
Points: 139, Visits: 509
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!
Post #1383635
Posted Monday, November 12, 2012 5:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 12:40 AM
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.

Post #1383645
Posted Monday, November 12, 2012 6:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:41 AM
Points: 139, Visits: 509
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!
Post #1383668
Posted Monday, November 12, 2012 9:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 12:40 AM
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).
Post #1383939
Posted Tuesday, November 13, 2012 6:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:41 AM
Points: 139, Visits: 509
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!
Post #1384054
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse