Access 2013 stops working with Recordset.FindFirst method

[TimF]

Hi all! I wonder if anyone could kindly offer suggestions for this problem.



I have a database with a table and 2 forms.

One form (frmList) is a list of all records in the database, and the other form (frmInput) displays fields for a single record.

There is a button in frmList which runs the following script. The user clicks a record in frmList, clicks the button, and the selected record opens in frmInput. ID is a text field containing digits and an alpha that is in both frmList and frmInput.

Private Sub Command9_Click()
    Dim ID As String
    strCriteria = "ID = '" & Me.ID.Value & "'"
    DoCmd.OpenForm "frmInput", acNormal
   Form_frmInput.Recordset.FindFirst strCriteria
End Sub

This script works as expected in Access 2010. However we recently upgraded to Access 2013 and since then, this script causes Access to crash ("Access has stopped working..." dialog appears).

I have stepped through the script and the line causing the crash is

Form_frmInput.Recordset.FindFirst strCriteria

I can't find any problems with syntax or anything about Recordset.Findfirst being deprecated in 2013 so I suspected some kind of file corruption. To resolve this I have tried:

• Compacting and repairing the database
• Decompiling and recompiling the VBA modules
• Creating a fresh database and copying all the objects into it

However none of these have made any difference.

So my questions are - could this be due to a missing library or something? Are there other methods of resolving a corrupt database if that is the problem? Or as a last resort is there an alternative method to Recordset.FindFirst that I can use to open a form to a particular record while retaining the ability to navigate through other records?

[June7]

I use Access 2010 so don't know about fix for 2013.

First I open form and pass ID value in OpenArgs:
DoCmd.OpenForm "SampleInfo", , , , , , strLabNum

Then code in the opened form:
Private Sub Form_Open(Cancel As Integer)
Me.RecordsetClone.FindFirst "Submit.LabNum='" & Me.OpenArgs & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

[TimF]

Thanks very much, I'll give that a try. When you use OpenArgs to open to one record can you still use the form navigation buttons to move to other records, or does it filter the recordset to just the record you open to?

[John_G]

Have you used debug.print or msgbox to see what the string strCriteria actually looks like?

You could also try using
Me.Recordset.FindFirst strCriteria

[TimF]

Thanks for the suggestion, I will give that a go too. If I use Me.Recordset will it treat the opened form as 'Me' or the form the script is called from?

I did check the string and it appears to give me the expected result, eg

ID = '1234A'

[June7]

My code does not open form to one record. It uses the value passed by OpenArgs to move to that record. All records are available for navigation.

[John_G]

Tim -

When you use Me. , it refers to the current form, i.e. the form the code is in. To refer to another open form, you do have to use the Forms! ... notation.

[TimF]

Thanks all for the suggestions. After playing around with a few of your ideas, I found the following to work without causing Access to crash. I can't say whether the original script was faulty or if this is a problem specific to my Access installation or file corruption but it works so I'm happy!

Private Sub Command9_Click()
    Dim ID As String
    DoCmd.Close acForm, "frmInput", acSaveYes
    strCriteria = "ID = '" & Me.ID.Value & "'"
    DoCmd.OpenForm "frmInput", acNormal
    Form_frmInput.RecordsetClone.FindFirst strCriteria
    Form_frmInput.Bookmark = Form_frmInput.RecordsetClone.Bookmark
End Sub

[John_G]

Glad you got it working. I think perhaps the problem was that you had ID in a Dim statement as well, as Access was confused as to what to do. When you used the .Value property, the context was clear to it.

[June7]

The .Value was used in the original code that failed.

The ID variable is not used and conflicts with field name - remove the declaration.