473,836 Members | 1,510 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Runtime Error 3077 with a list box

SteHawk85
34 New Member
Hi I am having a problem with a listbox that selects a pupil from a recordset and shows the pupils information in a form (essentially a advanced find). I know it has something to do with some pupils names having an ' e.g. O'Brien but I can't figure out how to solve it.

the code I am using is as follows:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdShowSelectedPupil_Click()
  2. Dim rs As DAO.Recordset
  3.  
  4. Set rs = Forms!frmBasicPupilDetails.RecordsetClone
  5.  
  6. Call rs.FindFirst("FullName =" & Me.lstPupilSearch.Value)
  7. Forms!frmBasicPupilDetails.Recordset.Bookmark = rs.Bookmark
  8. Call DoCmd.Close(acForm, "frmFindPupilBPD")
  9.  
  10. End Sub
any help would be greatly appreciated as I am still quite new to the world of VBA

Thanks
Mar 27 '12 #1
5 2060
NeoPa
32,584 Recognized Expert Moderator MVP
The main problem is actually not with the O'Briens, but with the basic format of the string you have created. Once that is fixed, then you'll have a problem with the O'Briens.

Also, there are various other problems I suspect, that I'd like to help with but the information available is not there for me to know exactly how to format my response. What is the name of the form that the code is running from? Wherever this form is referred to in the code the object Me works as a preferred reference (No need for all the Forms(""). blah blah blah).

It's important to note too, that code should always be posted exactly as it is in your project (after compiling and testing - See Before Posting (VBA or SQL) Code) to avoid various problems which you don't need help from us with. Please read that link and memorise. It can prove very helpful and important to you.

The format for your line #6 should be something like :
Expand|Select|Wrap|Line Numbers
  1. Call rs.FindFirst("[FullName] = '" & Replace(Me.lstPupilSearch, "'", "''")')
This resolves both your current problem, as well as the O'Brien issue which surely would have raised its ugly head (The issue's not O'Brien's) once the first was cleared.
Mar 27 '12 #2
SteHawk85
34 New Member
I have created two forms one called 'frmBasicPupilD etails' and another called 'frmFindPupilBP D'. In the main form (frmBasicPupilD etails) I have added a command button which when clicked opens the second form (frmBasicPupilD etails) that contains the listbox, which is where the code above is running from. The purpose of the list box is to find a specific pupil and then filter the fromBasicPupilD etails by said pupil.

I have tried your line of code, but now I am getting an ‘Compile Error: Expected: List separator or )’ error and its highlighting the last ‘ in the code. I am also using Access 2003.

Thanks again
Mar 27 '12 #3
NeoPa
32,584 Recognized Expert Moderator MVP
SteHawk85:
I have tried your line of code, but now I am getting an ‘Compile Error: Expected: List separator or )’ error and its highlighting the last ‘ in the code. I am also using Access 2003.
That's because I'm a muppet :-(

That character should have been enclosed in a string and appended to the previous string. It should have read :
Expand|Select|Wrap|Line Numbers
  1. Call rs.FindFirst("[FullName] = '" & Replace(Me.lstPupilSearch, "'", "''") & "'")
BTW. Nice response. It helps when posters respond appropriately to points that are made (It's much rarer than I'd like).

Your code could look like :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdShowSelectedPupil_Click()
  2.     Dim strFilter As String
  3.  
  4.     With Forms!frmBasicPupilDetails
  5.         strFilter = "[FullName] = '" & Replace(Me.lstPupilSearch, "'", "''") & "'"
  6.         Call .RecordsetClone.FindFirst(strFilter)
  7.         .Recordset.Bookmark = .RecordsetClone.Bookmark
  8.         Call DoCmd.Close
  9.     End With
  10. End Sub
This code can work, but there's really no need to use a separate form to handle it. Another concept to consider is filtering. See Example Filtering on a Form for some ideas on that and also on how to use controls in the Header/Footer sections to control what happens in the Detail section.
Mar 27 '12 #4
SteHawk85
34 New Member
Hi Mate I will give your link to 'Example Filtering on a Form' a look and make changes to my other forms that will be using a similar 'Advanced Find' system. However I figured out a different way of doing it. Both forms have a PupilID ‘AutoNumbe r’ sitting behind them in the query so I recreated the list box to include this ID then used that as the 'FindFirst' and it now works swimmingly

The code is as follows:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdShowSelectedPupil_Click()
  5. On Error GoTo ErrorHandler
  6.  
  7. Dim rs As DAO.Recordset
  8. Set rs = Forms!frmBasicPupilDetails.RecordsetClone
  9.  
  10. Call rs.FindFirst("PupilID =" & Me.lstPupil.Value)
  11. Forms!frmBasicPupilDetails.Recordset.Bookmark = rs.Bookmark
  12. Call DoCmd.Close(acForm, "frmFindPupilBPD")
  13.  
  14. CleanUpAndExit:
  15. 'Close the recordset defensively
  16. On Error Resume Next
  17. rs.Close
  18. 'Destroy the object defensively
  19. On Error Resume Next
  20. Set rs = Nothing
  21. Exit Sub
  22.  
  23. ErrorHandler:
  24.     Call MsgBox("An error was encountered" & vbCrLf & vbCrLf & "Description: " & Err.descrpition _
  25.     & vbCrLf & "Error Number: " & Err.Number, , "Error")
  26.     Resume CleanUpAndExit
  27. End Sub
  28.  
  29.  
Thanks for your help and in future i will use your method above for data containing ' and let you know how i get on with the link you have supplied.

Ste
Mar 28 '12 #5
NeoPa
32,584 Recognized Expert Moderator MVP
Sounds good.

A couple of points :
  1. The On Error statement is effective until it is either superceded, or the current procedure is left. Thus line #19 is thoroughly redundant.
  2. I strongly recommend the habit of using the With statement wherever you can for objects that your code references multiple times in a section of code. It's both more efficient and easier to read/maintain. Both good ideas.
  3. As I mentioned in an earlier post, having the Combo/ListBox control on a separate form is unnecessary and leads to complication of the design and code. If you put it in the header section that will simplify the design.
Just some thoughts on the matter. Good luck with your project.
Mar 28 '12 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

10
9791
by: Seeker | last post by:
Hi! I have to do some developing and I'm trying to configure my server to mimic the operation of our production server. The issue I'm having is that I'm trying to use CDONTS to send an email under IIS on a Windows 2003 Server. The server does not have the SMTP service running, but I do have a third party SMTP/POP/IMAP server running. I have copied the CDONTS.DLL file and registered it. In the Web Serice Extensions I have enabled the...
3
15797
by: bill_hounslow | last post by:
I'm trying to transfer data from one Sql Server into a table on another, using a simple INSERT query in an Access database with links to tables on both servers (the reasons for this are complicated but it IS the simplest solution, believe me). The 'Select' clause of the query works fine when run alone, but, when I run the INSERT query I get a Runtime Error 3167 Record Deleted. I get the error even when I'm the only person accessing...
6
539
by: mark | last post by:
I have an asp.net ecommerce web application on a remote web server. I'm using an Access database on the back end. I've notice a few strange things. When I mimic an multiple user environment by surfin it in multiple browsers simultaneously the site generates a generic runtime error after awhile. I'm thinking this has something to do with my access database and multiple connections. I'm using forms authentication with a login page. Is...
4
5243
by: news.citenet.net | last post by:
I keep getting the following error message after my web site running 2 or 3 days I share one folder with about 200 domain names Any one can help? --------------------------------------------------------------------------------
3
12482
by: ebasshead | last post by:
Hi everybody, I'm getting a syntax error message with my code below (using a combo box to pull data from a table), the combo box will work a few times and then this message pops up. Runtime error :'3077' Syntax error (missing operator) in expression The code is below with... rs.FindFirst " = '" & Me! & "'" highlighted in yellow
0
1467
by: Justin Dutoit | last post by:
Hey! I'm sorry if this is a bit of a newbie question, but I am gettting unwanted 'Runtime error' messages, i.e. not the error proper, and customErrors mode="Off" doesnt' seem to work. My web.config file probably needs something- it is below. Please help soon... <?xml version="1.0" encoding="utf-8" ?> <configuration> <system.web> <!-- DYNAMIC DEBUG COMPILATION Set compilation debug="true" to insert debugging symbols (.pdb
4
2541
by: katzc | last post by:
Microsoft Visual C++ Runtime Library Runtime Error! Program:C:\PROGRA~1\Aveo\Attune\bin\Attune_DI.exe abnormal program termination I get this error message mostly when I'm trying to get onto the internet and sometimes after rebooting, but not always. I'm lost I know a little about computers and I'm not sure where to look for the answer to correct it, if I can. I see alot of "Fix It For Free" downloads which I did down load one, but it...
0
1744
nurikoAnna
by: nurikoAnna | last post by:
Dim rsFaculty As New ADODB.Recordset Dim rsDepartment As New ADODB.Recordset Dim oCm As New ADODB.Command Private Sub Disconnect() connect.Close End Sub
3
3719
by: ShyamKrishnegowda | last post by:
We are writing test scripts to our product. In one of the script we are facing an issue. This test script will create a COM component and executes the following function. O1 and O2 are out parameters. v2iAuto.ComputerPolicyLevel(False, o1, o2) We are facing issue while iterating through 02 object. O2 contains array of variants(Interface objects)
1
2629
by: ShyamKrishnegowda | last post by:
We are writing test scripts to our product. In one of the script we are facing an issue. This test script will create a COM component and executes the following function. O1 and O2 are out parameters. v2iAuto.ComputerPolicyLevel(False, o1, o2) We are facing issue while iterating through 02 object. O2 contains array of variants(Interface objects)
0
9814
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9666
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10585
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9369
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6977
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5645
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5821
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4010
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3111
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.