Access & SQL server

[smitan]

Hi,
I have problems in getting the information in my subform either sorted or added without an error.
Using SQL server 2000, there is NO problem at all, using SQL server 7 it simply doesn't work. Changing from 7 to 2000 is no option for my client.
The point is that the records of the subformtable have to be presented in DESC-order (date, newest record first).
I tried with a table (in the recordsource of my subform), query, table plus sortfield (in the orderby-prop.), query with/without orderby

The result was either no sort at all, despite my orderby property, an error saying that the data was added but couldn't be displayed (refresh showed the record), no possibilty to add/delete records.

So, now it's time to ask the experts

Thanks,
Hans

 

[BobJacksonNCI]

Hans,

You didn't state the front end being used - assuming it's Access. Would probably be better to post under Access Project.

Which is what I'm developing and ran into the same issue. My solution is the subform uses a stored procedure for its data source

STORED PROCEDURE:
Alter Procedure MgrTB_spr_Time_Off_Subform
@FormID int
AS
SELECT *
FROM
"MgrTB_tbl_Time_Off_Detail" WHERE FormId_Many = @FormID
ORDER BY DateRequested

SUBFORM RECORD SOURCE:
MgrTB_spr_Time_Off_Subform

SUBFORM INPUT PARAMETERS:
@FormID Int=Forms!frm_Time_Off.txtFormID_One

I don't qualify as an expert, but this works for me.



HTH,
Bob

 

[smitan]

Hi Bob,
Thanks for the tip. It works like ashine.
Funy thing is, I tried a query with inputparameter, without luck.

Which group is the AccessProjectGroup?
Bye
Hans

 

[BobJacksonNCI]

Hans,

Glad to help!

The forum is located at:

Programmers \ DBMS Packages \ Microsoft: Access Project (ADP)

HTH,
Bob