Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Filter Continuous form using combo box

Posted on 2002-07-01
2
Medium Priority
?
780 Views
Last Modified: 2008-03-06
My simple database in Access 2002 contains a list of books, many of which have the same title but different Book_ID. I have a continuous form with all the records. In the form header I have a combo box which filters the records according to the book name. ie, If there are several books with the selected title I want them all on the form. I copied & amended the code from an example in Access Help

Private Sub cboTitleSearch_AfterUpdate()
    Dim strNewRecord As String
    strNewRecord = "SELECT * FROM Library " _
        & " WHERE Title = '" _
        & Me!cboTitleSearch.Value & "'"
    Me.RecordSource = strNewRecord
End Sub

Everything works fine until I select a book which has an apostrophe in the title, eg Samantha's Story
Then I get the following error:

Run-time error '3075';
Syntax error (missing operator) in query expression 'Title = 'Samantha's Story".

Can someone please help me fix it?
Thanks

0
Comment
Question by:Doozy
2 Comments
 
LVL 28

Accepted Solution

by:
TextReport earned 400 total points
ID: 7123818
The use of the Apostrope in the code can be replace by using double quotes, so on the assumption there are no double quotes in your titles replace the code with

Private Sub cboTitleSearch_AfterUpdate()
   Dim strNewRecord As String
   strNewRecord = "SELECT * FROM Library " _
       & " WHERE Title = """ & _
       & Me!cboTitleSearch.Value & """"
   Me.RecordSource = strNewRecord
End Sub

Personally though I prefer the following using Chr(34) which is the " character as it is easier to read that all those double quotes.

Private Sub cboTitleSearch_AfterUpdate()
   Dim strNewRecord As String
   strNewRecord = "SELECT * FROM Library " _
       & " WHERE Title = " & Chr(34) _
       & Me!cboTitleSearch.Value & Chr(34)
   Me.RecordSource = strNewRecord
End Sub


Chr(34) is the " character but is easier to read than the alternative of entering


0
 

Author Comment

by:Doozy
ID: 7123835
Works a dream - Thanks so much
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question