Problem w. recordesets from sql server

[limpan]

I´m getting desperate, I need help with this issue. I have an
access2000 front end with SQLServer 7.0 back end. My problem concerns
the retrieval of recordsets from the sqlserver. I have a procedure
which is supposed to check whether a certain record exists in a table.
If the record exists that record should be updated with the new info,
otherwise a new record should be added. The problem is that the
returned recordset only seems to contain the first record in the table
which results in the updating of that record, and not the correct one.
Here´s the code:


Public Sub nyBest(Modell As String)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset, rst2 As DAO.Recordset
Dim fanns As Boolean
Dim styckepris As Currency
Dim bestID As Integer, orderrad As Integer
Dim sqlStr As String

Set dbs = CurrentDb
bestID = Me.txtBestId.Value
sqlStr = "SELECT DISTINCTROW * FROM TempBeställning WHERE " & _
"bestId = " & bestID & " AND modell = '" & Modell & "' " & _
"AND Färg = '" & Me.cboFärg.Value & "' " & _
"AND Visas = 0 order by rad;"

Set rst = dbs.OpenRecordset(sqlStr, dbOpenDynaset, dbSeeChanges)
fanns = False

If Not rst.BOF Then
rst.MoveFirst
End If

If Not rst.EOF Then
fanns = True
orderrad = rst("rad&quot

Dim pris As Currency
Dim Antal As Integer

Antal = (rst.Fields("Antal&quot + CInt(txtBestAntal.Value))
pris = (rst.Fields("Pris&quot + CCur(txtTotpris.Value))
styckepris = pris / CCur(Antal)

With rst
.Edit
![Antal] = Antal
![pris] = pris
![styckepris] = styckepris
.Update
End With

End If

If Not fanns Then
rst.Close
sqlStr = "SELECT rad FROM TempBeställning;"
Set rst = dbs.OpenRecordset(sqlStr, dbOpenDynaset,
dbSeeChanges)
If Not rst.EOF Then
sqlStr = "SELECT MAX(rad) as orderrad FROM TempBeställning;"
Set rst2 = dbs.OpenRecordset(sqlStr, dbOpenDynaset,
dbSeeChanges)
orderrad = rst2("orderrad&quot + 1
rst2.Close
Set rst2 = Nothing
Else
orderrad = 1
End If
styckepris = txtTotpris.Value / CCur(txtBestAntal.Value)
sqlStr = "INSERT INTO TempBeställning (Modell, Antal, pris, " & _
"Färg, blockrabatt, kvantitetsrabatt, " & _
"styckepris, Visas, bestID, rad) VALUES " & _
"('" & Modell & "', " & CInt(Me.txtBestAntal.Value) & _
", " & Me.txtTotpris.Value & ", '" & Me.cboFärg.Value & _
"', " & CInt(Me.txtBlockrabatt.Value) & ", " & _
CInt(Me.txtKvantitetsrabatt.Value) & _
", " & styckepris & ", 0, " & bestID & ", " & orderrad
& ""
dbs.Execute sqlStr

End If

Me.lstBeställningar.RowSource = "SELECT * FROM TempBeställning " & _
"WHERE bestId = " & bestID & " AND
visas=0;"

rst.Close
Set rst = Nothing
dbs.Close

End Sub

Does anyone have a clue about what could point me in the right
direction to solve this problem? My tables in sqlserver are indexed
with primary keys and I´ve connected the access application to
sqlserver through ODBC.

Any help would be greatly appreciated.
Best regards,
Linus

 

[limpan]

I solved by dropping the odbc connection and using connectionstrings instead.
/Linus