VBA query (Win XP Access 2007)

Topic

New Reply

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

Reply | Quote

Replies

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 = .

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

I will get there. This works fine. Thanks.

Reply | Quote