Double click record go to form problem

[sniki]

Hi,

I have a form called "Overview per Course", displaying several employees for
every course. I want to be able to click on an employee and then get up the
form "Course Overview per Employee", displaying all the courses that employee
has taken.

I have the following event procedure under "On Dbl Click"

Private Sub Employee_DblClick(Cancel As Integer)
Dim stDocNAme As String

stDocNAme = "Course Overview per Employee"
DoCmd.OpenForm stDocNAme, acNormal, , "[Employee]=" & [Employee]
End Sub

However, when doubleclicking on the Employee field I get the error message:
Run-time error '3075':
Syntax error (missing operator) in query expression '"[Employee]=A'

(under A the name of the employee is mentioned)

Could anyone help me, I don't know what I am doing wrong...

 

[Danny J. Lesandrini]

The problem is that your argument isn't a number, [Employee], so
it needs to be delimited with quotes, either single or double, but
if the value of [Employee] includes a single quote (like O'Malley),
you need to handle that, so try this instead ...


Private Sub Employee_DblClick(Cancel As Integer)
Dim stDocNAme As String
Dim stCriteria As STring
Const q As String = """" ' 4 double quotes

stCriteria = q & Nz([Employee], "") & q
stDocNAme = "Course Overview per Employee"
DoCmd.OpenForm stDocNAme, acNormal, , "[Employee]=" & stCriteria
End Sub

 

[Stefan Hoffmann]

hi Danny,

The problem is that your argument isn't a number, [Employee], so
it needs to be delimited with quotes, either single or double, but
if the value of [Employee] includes a single quote (like O'Malley),
you need to handle that, so try this instead ...

For readability reasons, you should use single quotes. Also don't forget
to escape quotes, if they are content:

Private Sub Employee_DblClick(Cancel As Integer)

Const QUOTE As String = "'"

Dim stCriteria As STring
Dim stDocNAme As String

stCriteria = "[Employee]=" & _
QUOTE & _
Replace(Nz(Me![Employee], ""), QUOTE, QUOTE & QUOTE) & _
QUOTE
stDocName = "Course Overview per Employee"

DoCmd.OpenForm stDocNAme, acNormal, , stCriteria

End Sub







mfG
--> stefan <--

 

[Danny J. Lesandrini]

Stefan, you apparently missed my point and you failed at your own
primary goal, readability. I don't mean that unkindly, but remember
that anyone who would ask the question posed by this poster would
be somewhat overwhelmed by the more concise syntax ...

stCriteria = "[Employee]='" & Replace(Me!Employee,"'","''") & "'"
DoCmd.OpenForm stDocNAme, acNormal, , stCriteria

The above is what I customarily do, but it's such a difficult thing to
read in a newsgroup post. Is that "thing" on the end 5 single quotes
or some combination of single-double quotes? You know what it is and
I know what it is and anyone who copies/pastes it into Notepad knows
what it is, but I guarantee you that most newbies cannot read that line.
It's like an eye-exam chart. Everything below the 4th line just looks fuzzy.

Thus, I gave the double-quote-obfuscated-in-a-constant solution. It
works and doesn't require paragraphs of explanation, which I ostensibly
had to give anyhow.