April 21, 2005 at 12:29 pm
I'm currently having an odd issue and was going to see if anybody here could help as I've been stumped on this problem for about 2 weeks now.
I have an application that's been running fine now for about 2 years but all of a sudden is having odd behaviour.
My setup is MS Access connected to SQL Server 2000 via ODBC.
I have a table named party with a primary key on the identity field ptyrfnbr.
I have a form that has a "select * from party" in it. And I have a combo box that when changed moves the form to a record based on the ptyrfnbr.
The basic code to do this (don't have it in front of me at this time.. I apologize) is
dim rstObj as recordset
set rstObj = Me.Recordset.CLone
rstObj.FindFirst("[ptyrfnbr] = " & cmbParty.value)
me.bookmark = rstObj.bookmark
This worked fine until about 2 weeks ago. There are a series of parties that always return a different record.
e.g.
57651 Smith, John always returnes 9876 Public, James T;
13389 Test, Mary always returnes 19008 Hammer, MC
What happens is that the rstObj.Fields("ptyRfnbr") shows the appropriate number, but as soon as I assign the bookmark Me!ptyRfnbr shows the other and the form moves to the wrong record.
I've removed MDAC 2.8 and DAO 3.6 as the culprits as it still happens leading me to believe it's server side.
In addition, if I remove the primary key on the party table it works fine.
This makes me believe that the issue is really a corrupt index problem but I have no consistency issues being reported nor does a DBREINDEX seem to fix the problem.
Also, If I drop the index and recreate it I still have the same problem.
My next concern is that it might be a disk issue but I've made a backup of the DB and restored it on two different machines and the same behaviour occurs (including the exact erroneous rows being returned).
Any suggestions on anything else to try?
April 21, 2005 at 2:08 pm
Does this helps:
http://www.mvps.org/access/forms/frm0005.htm
http://www.mvps.org/access/bugs/bugs0012.htm
* Noel
April 21, 2005 at 7:53 pm
Thank's so much for the reply. Unfortunately I tried that (but forgot to mention it).
This form is just newly opened and no deletes have occured in the form or the table even at this point (e.g. system rebooted, application started and party form opened).
Thanks again for the response.
April 21, 2005 at 10:47 pm
OK. It might be the bookmark bug as well. A little more info.
If i take my form and have a rowsource of "select * from party" and then do the bookmark lookup it works fine.
However, if I do a requery right before doing the bookmark set then it acts up.
If I take my form and on form open do a me.recordsource="select * from party" then it fails.
It seems that setting the rowsource or doing a requery (e.g. anything that causes a requery) causes the issue.
Figure this though. If I do a requery after the findfirst and then do another findfirst it works. Or, if I make no modifications to my prior code and then just rerun it it works.
Is this perhaps a timing issue between SQLServer and Access or just another manifestation of the Bookmark bug?
Thanks again for all of the help.
April 22, 2005 at 11:04 am
Just curious, how many records is your form loading?
April 22, 2005 at 11:05 am
Initially none.
e.g. select * from party where ptyrfnbr = 0
Then I have a public function call GetRecords that's called by another form.
This then does a "select * from party" and then does a findfirst on a given party.
April 22, 2005 at 7:02 pm
Shawn,
The following comes from VBA's Help file in the Bookmark Property Topic. I think it partly explains some of your problems:
Requerying a form invalidates any bookmarks set on records in the form. However, clicking Refresh on the Records menu doesn't affect bookmarks.
Since Microsoft Access creates a unique bookmark for each record in a form's recordset when a form is opened, a form's bookmark will not work on another recordset, even when the two recordsets are based on the same table, query, or SQL statement. For example, suppose you open a form bound to the Customers table. If you then open the Customers table by using Visual Basic and use the ADO Seek or DAO Seek method to locate a specific record in the table, you can't set the form's Bookmark property to the current table record. To perform this kind of operation you can use the ADO Find method or DAO Find methods with the form's RecordsetClone property.
An error occurs if you set the Bookmark property to a string variable and then try to return to that record after the record has been deleted.
The value of the Bookmark property isn't the same as a record number.
April 22, 2005 at 7:28 pm
Thanks for the info. However, to make things more fun I took the bookmark out of it.
I'm using this in the combo box with the string explicitly set
Me.RecordSource = "select * from Party"
Call Me.Recordset.FindFirst("[ptyrfnbr] = 51756")
51756 is for the pary Smith, Joe.
I put a stop breakpoint right after the
Call Me.Recordset.FindFirst("[ptyrfnbr] = 51756")
line and added a watch on Me.Recordset.
The fields(0).ptyrfnbr shows 51756, lastname shows smith and so on. I step through the code and the form_current fires. And the ptyrfnbr now equals 9852, lastname is different etc.
This only happens with a linked table to SQL server.
If I create a local table with the same definitions of the linked table and copy the data I don't have this problem.
I even used the upsize wizard to copy this new table (party2) to a new database and did a link to the new database and table. Once again, same problem.
I've also installed SP3 for Office and SQL Server 2000 so I believe I'm up to date on all patches, etc.
April 22, 2005 at 9:01 pm
I've found the solution (workaround).
I explicitly defined a DAO recordset and then set it to the form's recordset
Dim rs As DAO.Recordset
Set rs = Me.Recordset
rs.Requery
Call rs.FindFirst("[ptyrfnbr] = " & Str("51756"))
then I did a requery and the findfirst and it works.
Thanks so much for all of the help
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply