Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 17th, 2007, 04:00 AM
Registered User
 
Join Date: Apr 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Another Runtime 2001 Error

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
Reply With Quote
  #2 (permalink)  
Old April 18th, 2007, 08:50 AM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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.

Reply With Quote
  #3 (permalink)  
Old April 20th, 2007, 07:31 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
Reply With Quote
  #4 (permalink)  
Old April 20th, 2007, 05:57 PM
Registered User
 
Join Date: Apr 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!!

Reply With Quote
  #5 (permalink)  
Old April 23rd, 2007, 04:32 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

Reply With Quote
  #6 (permalink)  
Old May 25th, 2007, 05:22 PM
Registered User
 
Join Date: Apr 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Why does it show Link error 2001? Jams Visual C++ 2 July 10th, 2007 05:15 AM
Please Help! getting Runtime error 2001 Waseem Raja Access VBA 2 April 13th, 2007 07:50 AM
Runtime Error 2001 ketAccess Access VBA 2 April 11th, 2007 03:44 AM
Run-time error 2001 kcolli20 Access VBA 1 October 3rd, 2006 07:00 AM
Run-time error 2001 ebburks Access 1 June 8th, 2006 06:56 AM



All times are GMT -4. The time now is 12:42 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.