Wrong record retrieved after move to SQL Svr

[J]

I've moved the database tables from the .mdb file to Sql Svr and now I have
an *intermittent* problem. When I select a record from a combo box, it will
intermittently pull up the wrong record. The code that does the search is
below. I have a column named 'ProsId' that is a unique number (identity
column). The error -- when it happens -- seems to only happen on higher
ProsId numbers. At first it appeared to me as some sort of data conversion
thing, but I'm not so sure now. Here's some examples:

Record selected Record actually displayed
---------------- -------------------------
100,000 41,859
59,073 15,794
57,000 11,273
57,001 11,274
45,000 45,000 <--- appears to return correct
value every time. Numbers lower than this also seem to return the correct
record every time.
Private Sub cboFindProsName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

If Len(Trim(Me.cboFindProsName.Text)) > 0 Then
Set rs = Me.Recordset.Clone
rs.FindFirst "[ProsId] = " & (Me![cboFindProsName])
Me.Bookmark = rs.Bookmark
End If

End Sub
Many Thanks!

[Salad]

J wrote:

I've moved the database tables from the .mdb file to Sql Svr and now I have
an *intermittent* problem. When I select a record from a combo box, it will
intermittently pull up the wrong record. The code that does the search is
below. I have a column named 'ProsId' that is a unique number (identity
column). The error -- when it happens -- seems to only happen on higher
ProsId numbers. At first it appeared to me as some sort of data conversion
thing, but I'm not so sure now. Here's some examples:

Record selected Record actually displayed
---------------- -------------------------
100,000 41,859
59,073 15,794
57,000 11,273
57,001 11,274
45,000 45,000 <--- appears to return correct
value every time. Numbers lower than this also seem to return the correct
record every time.
Private Sub cboFindProsName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

If Len(Trim(Me.cboFindProsName.Text)) > 0 Then
Set rs = Me.Recordset.Clone
rs.FindFirst "[ProsId] = " & (Me![cboFindProsName])
Me.Bookmark = rs.Bookmark
End If

End Sub
Many Thanks!

Is it really finding the record? If you know when you are searching for
an incorrect record, you may also want to step through your code. Maybe
try it without the () around the (ME![cboFindProsName]) Maybe the table
is corrupt. Do you have a filter so the records can't be found? Check
your recordsource too. Create a query and open it up and filter it on
high-end numbers, see what happens.

rs.FindFirst "[ProsId] = " & (Me![cboFindProsName])
If not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
Else
msgbox "Record not found"
Endif

[Salad]

J wrote:

I've moved the database tables from the .mdb file to Sql Svr and now I have
an *intermittent* problem. When I select a record from a combo box, it will
intermittently pull up the wrong record. The code that does the search is
below. I have a column named 'ProsId' that is a unique number (identity
column). The error -- when it happens -- seems to only happen on higher
ProsId numbers. At first it appeared to me as some sort of data conversion
thing, but I'm not so sure now. Here's some examples:

Record selected Record actually displayed
---------------- -------------------------
100,000 41,859
59,073 15,794
57,000 11,273
57,001 11,274
45,000 45,000 <--- appears to return correct
value every time. Numbers lower than this also seem to return the correct
record every time.
Private Sub cboFindProsName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

If Len(Trim(Me.cboFindProsName.Text)) > 0 Then
Set rs = Me.Recordset.Clone
rs.FindFirst "[ProsId] = " & (Me![cboFindProsName])
Me.Bookmark = rs.Bookmark
End If

End Sub
Many Thanks!

One other thing. Do you have a combo populated with 100K rows? Perhaps
you have exceeded some sort of combo box limit....I have no idea if
there is a limit but to create a combo with that many rows may cause
Access to choke. I would find that number of rows in a combo ridiculous.

[Rich P]

I am going to guess that you are basing your combobox on an ODBC table.
If this is the case, just know, that ODBC (for Sql Server to Access) is
mostly legacy technology. ODBC has a constantly open connection with
little bandwidth compared to ADO. ODBC works OK with small tables of a
few thousand records or less, more than 20-30k you will have problems
with ODBC (I would really say more than 10k of records). This is why ADO
was developed. What I do is to create the same table in Access as the
one in Sql Server. Pull in a subset of data from the sql table to the
Access table (which acts like a temp/buffer table), using ADO, then use
VBA to manipulate the data in your Access buffer table.

As for seaching for a specific record, pick a value from your combobox
which is based on an Access Buffer table (with data you pulled in from
Sql Server) then use an ADO command object to fetch the desired record
from Sql Server:

Dim cmd As New ADODB.Command, RS As New ADODB.RecordSet

...
cmd.CommandType = adCmdText
cmd.CommandText = "Select * From tblX Where fldx = '" & cbo1 & "'"
Set RS = cmd.Execute
...

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!