SQL Server view - not refreshed

[sharonc]

I have a MS access Form that access a View on SQL Server. The view on SQL Server shows 20 records and
looks great. When my program access the view through the MS Access application, the view only shows
one record, but it shows the record 20 times.

This is how I'm access the record:

Set db = CurrentDb()
strSQL = "SELECT * from dbo_v_DMRLabResults;"
Set rs = db.OpenRecordset(strSQL)

I have tried using sendkeys, refresh and requery and nothing seems to load the correct data into the
view. If I open the view from the query tab and do F9, it shows the good data but it doesn't save it.

Can someone tell me how I can fix this problem?

 

[Databaseguy]

Why not just bind the form and the fields to the view (Query to us Access guys) directly ? Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[sharonc]

Because I'm doing major calculations on the numbers before they go into the form.

 

[Databaseguy]

Sounds like it's your calculated fields that are giving you greif.

Could you post some more code ??? Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[sharonc]

Here's the form code:

Set db = CurrentDb()
strSQL = "SELECT * from dbo_v_DMRLabResults;"
Set rs = db.OpenRecordset(strSQL)
SendKeys "{F9}", True

rs.MoveLast
rs.MoveFirst

For i = 1 To rs.RecordCount
Debug.Print rs.Fields("Parameter&quot
rs.MoveNext
Next i

Here is the query/View:

SELECT dbo.DMRLabResults.ID, CAST(dbo.DMRWkData.[Month] AS varchar(12)) + '/' + CAST(dbo.DMRWkData.[Year] AS varchar(12)) AS SelectDate,
dbo.DMRLabResults.[Month], dbo.DMRLabResults.[Year], dbo.DMRLabResults.Original_Results, dbo.DMRLabResults.Duplicate_Results,
dbo.DMRLabResults.PQL,
EXPR2 = CASE WHEN dbo.DMRLabResults.Original_Results = 'ND' THEN 0 WHEN dbo.DMRLabResults.Duplicate_Results = 'ND' THEN 0 ELSE (CAST(dbo.DMRLabResults.Original_Results
AS Decimal(10, 2)) + CAST(dbo.DMRLabResults.Duplicate_Results AS Decimal(10, 2))) / 2 END,
FROM dbo.tblMonth INNER JOIN
dbo.DMRLabResults ON dbo.tblMonth.Mth_Num = dbo.DMRLabResults.[Month] INNER JOIN
dbo.DMRWkData ON dbo.DMRLabResults.[Month] = dbo.DMRWkData.[Month] AND dbo.DMRLabResults.[Year] = dbo.DMRWkData.[Year] INNER JOIN
dbo.DMRLoadRpt ON dbo.DMRWkData.[Month] = dbo.DMRLoadRpt.Entry_Month AND dbo.DMRWkData.[Year] = dbo.DMRLoadRpt.Entry_Year

I've removed a lot of the code from the query/view to fit it in here.

 

[Databaseguy]

I just ran your code (substituting my tables) in the debug window and it ran fine.

Exactly how are you getting the recordset to the form ?? Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[sharonc]

In MS Access I'm linking the tables using ole DB to SQL Server. Then In the form I use:

Set db = CurrentDb()
strSQL = "SELECT * from dbo_v_DMRLabResults;"
Set rs = db.OpenRecordset(strSQL)

 

[Databaseguy]

On Open?? On Current ??? Record Source ??? Where is the code ?? Is the form continuous or single form view ?? Your text boxes, what is their data source ???

Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[sharonc]

I figured it out. I needed to place a unique index on the view.