Syntax Erro

[S Jackson]

I have created a form that has a multi-select List Box .

The OnClick Event for the OK button is as follows:

Private Sub OkBtn_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMasterall"

stLinkCriteria = "[Region]=" & Me![RegionList]
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

I keep getting an error:
Run-time error '3075'
Syntax error (missing operator) in query expression
'[Region]='

Can anyone help?
TIA
S. Jackson

 

[John Vinson]

I have created a form that has a multi-select List Box .

A Multiselect listbox doesn't have *one* value. It has an array of all
the values which have been selected.

You'll need to loop through the listbox's ItemsSelected collection,
building up an IN clause in a SQL string: e.g.

Private Sub OkBtn_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim varItem As Variant

stDocName = "frmMasterall"

stLinkCriteria = "[Region] IN ("
For Each varItem In Me.RegionList.ItemsSelected
stLinkCriteria = stLinkCriteria & _
Me.Regionlist.ItemData(varItem) & ","
Next varItem
strLinkCriteria = Left(strLinkCriteria, Len(strLinkCriteria) - 1 _
& ")"

DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

Or else change the listbox to Single rather than multiselect.

 

[S Jackson]

Thank you for the code! (I wished I understood it) Unfortunately, because
of my ignorance I can't get it to work. I keep getting an error after the
2nd last line:

strLinkCriteria=Left(strLinkCriteria,Len(strLinkCriteria)-1_

The error is: Invalid Character

What is happening here?

(I have been told that there are several books that could help me with VB
code. I am waiting on my employer to supply them.)

TIA S. Jackson

I have created a form that has a multi-select List Box .

A Multiselect listbox doesn't have *one* value. It has an array of all
the values which have been selected.

You'll need to loop through the listbox's ItemsSelected collection,
building up an IN clause in a SQL string: e.g.

Private Sub OkBtn_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim varItem As Variant

stDocName = "frmMasterall"

stLinkCriteria = "[Region] IN ("
For Each varItem In Me.RegionList.ItemsSelected
stLinkCriteria = stLinkCriteria & _
Me.Regionlist.ItemData(varItem) & ","
Next varItem
strLinkCriteria = Left(strLinkCriteria, Len(strLinkCriteria) - 1 _
& ")"

DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

Or else change the listbox to Single rather than multiselect.

 

[John Vinson]

Thank you for the code! (I wished I understood it) Unfortunately, because
of my ignorance I can't get it to work. I keep getting an error after the
2nd last line:

strLinkCriteria=Left(strLinkCriteria,Len(strLinkCriteria)-1_

The error is: Invalid Character

What is happening here?

Sorry! That's word wrap causing problems. The underscore character is
a line continuation (meaning that the next line should be considered a
continuation of this line); but it must be preceded by a blank to be
recognized. Change this to

.... Len(strLinkCriteria) - 1 _

(with the blanks!), or simply remove the underscore and the carriage
return and make it all one line.