Hi folks,

This routine had been working fine until, one day, it would return a runtime 2001 error. I've added the On Error line so that the program wouldn't get interrupted, but I would like to be able to VBA to manipulate the RecordSource. What am I doing wrong? Thanks for any help you can give.

Private Sub ABttn_Click()

    On Error Resume Next

    Dim SearchStr As String
    SearchStr = "SELECT MedID, MedName, MedSig, MedQuant, MedClass"
    SearchStr = SearchStr + " FROM MedList WHERE (((Left$([MedName], 1)) = 'A'))"
    SearchStr = SearchStr + " ORDER BY MedName"


    Forms![Formulary].RecordSource = SearchStr

End Sub

Is a different form opening Formulary and then setting it's RecordSource?

If this is the case, I'd try moving the setting of the RecordSource to the Formulary form then make a public sub or function that you pass the criteria to and set it from that form. That should probably clear that up for you. The code itself looks solid. I'd probably also use Forms("Formulary").RecordSource myself.

Welcome to P2P!

Try this:

SearchStr = "SELECT MedID, MedName, MedSig, MedQuant, MedClass"
SearchStr = SearchStr & " FROM MedList WHERE [MedName] Like 'A*'"
SearchStr = SearchStr & " ORDER BY MedName"

Note the use of & not +

Note I used the Like comparison. Using Like '*A*' means anything with an "A" anywhere in it. Using Like 'A*' is anything that STARTS with an "A".



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks for your replies.

Allen - the routine is already in the "Formulary" form, but still gets the runtime error. I also tried making the variable public without success.

Greg - I tried your codes and they seem to be working great! Hopefully, they'll continue to run without errors. As I mentioned in my original post, my original lines of codes were working fine until it suddenly encountered the error. I still don't know why. Thanks for your help!!

jladder,

Have you changed your version of office at all?

I had similar problems to this, I had an application developed in 2000, when the office upgraded to 2005, of course the program still worked, but the reference to Excel/Access 10 caused errors to pop up with "Function not found" and other weird things.

I re-referenced the code on a 2005 machine and all worked fine after that..

Just thought I would add this as normally, when your code falls over for no reason, theres a reason outside of the code.

Regards,
Rob

Just an update folks. So far, everything is still running smoothly. And Rob, in response to your question, I developed the program in 2000, and am still using that version. Thanks again for your replies.

Jake