Runtime Error 3077 with a list box

[SteHawk85]

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:

Private Sub cmdShowSelectedPupil_Click()
Dim rs As DAO.Recordset
 
Set rs = Forms!frmBasicPupilDetails.RecordsetClone
 
Call rs.FindFirst("FullName =" & Me.lstPupilSearch.Value)
Forms!frmBasicPupilDetails.Recordset.Bookmark = rs.Bookmark
Call DoCmd.Close(acForm, "frmFindPupilBPD")
 
End Sub

any help would be greatly appreciated as I am still quite new to the world of VBA

Thanks

[NeoPa]

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 :

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.

[SteHawk85]

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

[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 :

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 :

Private Sub cmdShowSelectedPupil_Click()
    Dim strFilter As String
 
    With Forms!frmBasicPupilDetails
        strFilter = "[FullName] = '" & Replace(Me.lstPupilSearch, "'", "''") & "'"
        Call .RecordsetClone.FindFirst(strFilter)
        .Recordset.Bookmark = .RecordsetClone.Bookmark
        Call DoCmd.Close
    End With
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.

[SteHawk85]

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:

Option Compare Database
Option Explicit
 
Private Sub cmdShowSelectedPupil_Click()
On Error GoTo ErrorHandler
 
Dim rs As DAO.Recordset
Set rs = Forms!frmBasicPupilDetails.RecordsetClone
 
Call rs.FindFirst("PupilID =" & Me.lstPupil.Value)
Forms!frmBasicPupilDetails.Recordset.Bookmark = rs.Bookmark
Call DoCmd.Close(acForm, "frmFindPupilBPD")
 
CleanUpAndExit:
'Close the recordset defensively
On Error Resume Next
rs.Close
'Destroy the object defensively
On Error Resume Next
Set rs = Nothing
Exit Sub
 
ErrorHandler:
    Call MsgBox("An error was encountered" & vbCrLf & vbCrLf & "Description: " & Err.descrpition _
    & vbCrLf & "Error Number: " & Err.Number, , "Error")
    Resume CleanUpAndExit
End Sub
 
 

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

[NeoPa]

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.