By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 994 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Runtime Error 3077 with a list box

SteHawk85
P: 34
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

✓ answered by NeoPa

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.

Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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
P: 34
I have created two forms one called 'frmBasicPupilDetails' and another called 'frmFindPupilBPD'. In the main form (frmBasicPupilDetails) I have added a command button which when clicked opens the second form (frmBasicPupilDetails) 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 fromBasicPupilDetails 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
Expert Mod 15k+
P: 31,186
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
P: 34
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 ‘AutoNumber’ 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
Expert Mod 15k+
P: 31,186
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

Post your reply

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