Syntax error missing operator

[juniorz]

Hello,

I have a Listbox and when i double click on a selected record (Student Name), I receive a syntax error. I am trying to open a form containing the student information.

VBA:

Private Sub List1_DblClick(Cancel As Integer)
DoCmd.OpenForm "NEP", , , "[Student Name] = Forms!NEP!Student Name"
End Sub


any suggestions?

Thanks

 

[plog]

I think you have a few syntax errors.

1. When you use non-alphanumeric characters in names it makes things harder for you. Because you have a space in the name you need to enclose it in brackets.

2. When you want to use the value of a something you need to escape out of your string you are building, use the variable/control then go back into the string.

This should be your criteria argument:


"[Student Name] = '" & Forms!NEP![Student Name] & "'"

 

[juniorz]

Thanks Plog,

I now receive a different error when clicking on the student,

"Microsoft Access cannot fined the referenced form "NEP""

However I can clearly see my form is still there.

 

[JHB]

..
However I can clearly see my form is still there.

And what is the name of it?
Check for any spaces in the name.

 

[juniorz]

The form that the listbox should open is "NEP" the form that contains the listbox is called "Nepchoice"

would that mean this should be changed?


DoCmd.OpenForm "NEP", , , "[Student Name] = '" & Forms!NEP![Student Name] & "'"

 

[JHB]

The form that the listbox should open is "NEP" the form that contains the listbox is called "Nepchoice"

would that mean this should be changed?


DoCmd.OpenForm "NEP", , , "[Student Name] = '" & Forms!NEP![Student Name] & "'"

DoCmd.OpenForm "NEP", , , "[Student Name] = '" & Forms![B][COLOR=Red]Nepchoice[/COLOR][/B]![Student Name] & "'"

 

[juniorz]

Sorry for my incompetence, will get there shortly.

DoCmd.OpenForm "NEP", , , "[Student Name] = '" & Forms!Nepchoice![Student Name] & "'"

I corrected the code only to receive an error stating that access cannot find the referenced [Student Name].

Therefore as i am talking about the listbox in Nepchoice and the correct name for the listbox is List1 then i should change the code to:

DoCmd.OpenForm "NEP", , , "[Student Name] = '" & Forms!Nepchoice![COLOR="Red"]List1[/COLOR] & "'"

This resulted in the form NEP opening correctly once I double click on the student however the form is blank, scrolling down further it says the form is in filtered mode. (if that means anything)

 

[AccessBlaster]

Hello,

I have a Listbox and when i double click on a selected record (Student Name), I receive a syntax error. I am trying to open a form containing the student information.

VBA:

Private Sub List1_DblClick(Cancel As Integer)
DoCmd.OpenForm "NEP", , , "[Student Name] = Forms!NEP!Student Name"
End Sub


any suggestions?



Thanks

See attached.

 

[namliam]

DoCmd.OpenForm "NEP", , , "[Student Name] = '" & Forms!Nepchoice![COLOR="Red"]List1[/COLOR] & "'"

This resulted in the form NEP opening correctly once I double click on the student however the form is blank, scrolling down further it says the form is in filtered mode. (if that means anything)

Well you are applying the filter using the statement as you are so that is right.

I am going to gamble that the listbox actually holds 2 values, the PK of the student and the student name (possibly more).

If it doesnt then you should, was communicating by PK rather than only student name is much better.
Assuming that is true (or you correct it) you should be able to fix it I hope ?

P.S. Using spaces in column names can cause you problems down the line, I advice you dont do that (anymore)

 

[juniorz]

See attached.

 

[AccessBlaster]

Glad it worked out.