Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA query (Win XP Access 2007)

    More of the same. I appreciate your putting up with me. I will get the hang of it eventually.

    This runs from a form in Northwind named Customer List, which is based on the table Customers. There is a field on the form called ID and a field in the table called ID. I want to dbl-click on the ID field on the form and have the qry open that particular record.

    I get an error on Customers after From.

    Private Sub Command273_Click()

    Dim db As Database
    Set db = CurrentDb
    Dim rec As DAO.Recordset

    Set rec = db.OpenRecordset(“Select * From Customers where ID = " & Me!ID)

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA query (Win XP Access 2007)

    If you want to do this when the user double clicks, you can use the On Dbl Click event of the text box bound to the ID field.

    The opening quote after OpenRecordset( appears to be a curly quote. VBA expects a straight quote like the one after ID = .

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA query (Win XP Access 2007)

    It works without the where expression. With the where expression, I get a runtime error 3075, Syntax error (missing operator) in query expression '[Job Title] = Purchasing Representative'.

    Purchasing Representative is the correct job title for the field where I double click.

    Private Sub Job_Title_DblClick(Cancel As Integer)

    Dim db As Database
    Set db = CurrentDb
    Dim rec As DAO.Recordset
    Set rec = db.OpenRecordset("Select * From Customers where [Job Title] = " & Me![Job Title])

    With rec
    Debug.Print ![Job Title]
    End With

    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA query (Win XP Access 2007)

    Apparently Job Title is a text field. You must put quotes around text values. Change

    Set rec = db.OpenRecordset("Select * From Customers where [Job Title] = " & Me![Job Title])

    to

    Set rec = db.OpenRecordset("Select * From Customers where [Job Title] = " & Chr(34) & Me![Job Title] & Chr(34))

    Chr(34) is the double quote " character.

  5. #5
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA query (Win XP Access 2007)

    I will get there. This works fine. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •