MS Accss DAO Issue

The following code crashes MS Access every time.  The database used to reside on a laptop with Access 2007.  Issues have occurred since I’ve installed the database on a PC running Access 2013.

The code is triggered by double clicking a list box, it’s the same code as other forms that bizarrely continues to work without issue.

I’ve commented out all the code and reinstated each line until Access crashes – it occurs at the line    Set rs = Forms!MyForm.Recordset.Clone.  

Previously the code had the following declaration:  Dim rs as DAO.Recordset so I changed it to fall in line with the other forms code that clearly does work  ie Dim rs As Object.

Suspect it may be a reference issue but why does it work on other forms?  I've imported all objects into a new database but it continues to crash.....



Dim rs As Object

    DoCmd.OpenForm "MyForm"

    Set rs = Forms!MyForm.Recordset.Clone

    rs.FindFirst "[signinID] = " & Str(Nz(Me![List122], 2))

    If Not rs.EOF Then Forms!MyForm.Bookmark = rs.Bookmark

    DoCmd.Close acForm, Me.Name

Open in new window

padrepio2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Have you tried:

Set rs = Forms!MyForm.RecordsetClone

Personally, I prefer syntax similar to:

    Dim rs as DAO.Recordset    '<= delete this row
    DoCmd.OpenForm "MyForm"

    Set rs = Forms!MyForm.Recordset.Clone ' <= delete this row
    With Forms!MyForm.RecordsetClone

        .FindFirst "[signinID] = " & Str(Nz(Me![List122], 2))

        if .NoMatch then
            msgbox "Not Found!"
        Else 
            Forms!MyForm.Bookmark = .Bookmark
        End If

    End With

    DoCmd.Close acForm, Me.Name

Open in new window

this technique eliminates to instantiate a recordset object.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Can you give a little more detail in regards to the crash?   Does it fault?  If so is the address and module always the same?   If so, then you've most likely tripped over an Access bug or have a corrupt form, or VBA project.

Jim.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Is "signinID" a Numeric or Text value? I ask because you're using Str, which returns a STRING representation of a Number. If signinID is a number, then you don't need to use STR to convert it to a string. If it's a string, then you need to enclose it in quotes.

And while I know this was working in previous versions, we've seen quite a few instances where that same code will fail in newer versions of Access/VBA. Not sure why, but it does happen more than you'd think.

Only other piece of advice I have is make sure your Office and Windows installations are fully up to date.
padrepio2Author Commented:
'Set rs = Forms!MyForm.RecordsetClone' done the trick.  Strange one but it works, many thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.