Invalid Character

[S Jackson]

I have created a multi-select list box. After the user makes his
selections, he presses okay. I am trying to open up a form collecting only
the data as specified by the user per his selections. Someone was kind to
reply as follows:

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


Unfortunately, when I try to type in this code in VB, I get an Invalid
Character error when I attempt to type the last line:

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

VB doesn't like the _

Sorry, I know you shouldn't use code unless you understand it, but I'm
floundering here. I have no books, no nothing. Only MS help files which
are useless and a deadline to get this done. I tend to learn better by
"doing" anyway rather than by "reading."

Any help is very much appreciated!

 

[Cheryl Fischer]

Hello Shelly,

The _ is a string continuation character. However, you do not have to use
the line continuation in your code and can replace:

stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) _
& ")"

with this:

stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) &
")"

Also, I think there is a problem in this one snippet of code in that there
needs to be a ) after the -1 and the variable name "strLinkCriteria" appears
in this snippet, when the original variable is "stLinkCriteria".

Try one of the above in your code and see if it works.
hth,

 

[S Jackson]

Hi Cheryl:

You have helped me in the past and I sure do APPRECIATE IT! Thank you so
much for taking the time to do so.

I did wonder about the strLinkCriteria as opposed to stLinkCriteria (doh!
I'm catchin on!)

I also see where I need to put the extra )

The line now reads as follows:

stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

NOW, I'm getting a new error:

Compile error: Can't find object or library

The word "Left" is highlighted in the debug window.

Do I need a reference of some sort? What does "Left" mean?

Thank so much for your help.

S. Jackson

Hello Shelly,

The _ is a string continuation character. However, you do not have to use
the line continuation in your code and can replace:

stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) _
& ")"

with this:

stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) &
")"

Also, I think there is a problem in this one snippet of code in that there
needs to be a ) after the -1 and the variable name "strLinkCriteria" appears
in this snippet, when the original variable is "stLinkCriteria".

Try one of the above in your code and see if it works.
hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I have created a multi-select list box. After the user makes his
selections, he presses okay. I am trying to open up a form collecting only
the data as specified by the user per his selections. Someone was kind to
reply as follows:

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


Unfortunately, when I try to type in this code in VB, I get an Invalid
Character error when I attempt to type the last line:

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

VB doesn't like the _

Sorry, I know you shouldn't use code unless you understand it, but I'm
floundering here. I have no books, no nothing. Only MS help files which
are useless and a deadline to get this done. I tend to learn better by
"doing" anyway rather than by "reading."

Any help is very much appreciated!

 

[Cheryl Fischer]

Hi Shelly,

I suspect that you need to check whether or not there is a reference to the
Microsoft DAO xx.x Object Library that is appropriate to your version of
Access; 3.51 for Access 97, 3.6 for Access 2000/2002. To check and set
references, open any module and from the VBA menu, click Tools|References.
Scroll through the list of available references until you find Microsoft DAO
3.51 [or 3.6] Object Library.

What the code you were given is doing is building a string of values which
will appear in the In() function. Here is the code with comments:

' First, let's say that your user has selected the following Regions in the
List Box:

New England
South Central
South Florida

' The following line is the start of the criteria string.
stLinkCriteria = "[Region] IN ("
For Each varItem In Me.RegionList.ItemsSelected
stLinkCriteria = stLinkCriteria & Me.Regionlist.ItemData(varItem) & ","
Next varItem

' After the first pass through the For/Next loop, stLinkCriteria will look
like the following, as the selected item has been concatenated or added to
the string:

"[Region] IN ("New England,"

' After the second pass, it will look like:

"[Region] IN ("New England,South Central,"

' After the third and last pass, it will look like:

"[Region] IN ("New England,South Central,South Florida,"

Note, that at this point the string still cannot be used because there is a
last comma that must be removed and a closing parentheses must be added.
So, the purpose of the following line of code:

stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

is to clean up the string so that it can be used. The Left() function is
saying:

Starting at the left-most position in the string, "[Region] IN ("New
England,South Central,South Florida,", return a certain number of
characters. The number of characters in this case is determined by the
Len()function. In this expression, the Len() function will evaluate the
length of stLinkCritera and subtract 1. Now your stLinkCriteria will look
like:

"[Region] IN ("New England,South Central,South Florida"

Notice that the comma is gone.

Now, a closing parenthesis must be added to stLinkCriteria using: & ")".

Post back and let us know how this worked.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Hi Cheryl:

You have helped me in the past and I sure do APPRECIATE IT! Thank you so
much for taking the time to do so.

I did wonder about the strLinkCriteria as opposed to stLinkCriteria (doh!
I'm catchin on!)

I also see where I need to put the extra )

The line now reads as follows:

stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

NOW, I'm getting a new error:

Compile error: Can't find object or library

The word "Left" is highlighted in the debug window.

Do I need a reference of some sort? What does "Left" mean?

Thank so much for your help.

S. Jackson

Hello Shelly,

The _ is a string continuation character. However, you do not have to use
the line continuation in your code and can replace:

stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) _
& ")"

with this:

stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) &
")"

Also, I think there is a problem in this one snippet of code in that there
needs to be a ) after the -1 and the variable name "strLinkCriteria" appears
in this snippet, when the original variable is "stLinkCriteria".

Try one of the above in your code and see if it works.
hth,

kind

to

reply as follows:

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


Unfortunately, when I try to type in this code in VB, I get an Invalid
Character error when I attempt to type the last line:

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

VB doesn't like the _

Sorry, I know you shouldn't use code unless you understand it, but I'm
floundering here. I have no books, no nothing. Only MS help files which
are useless and a deadline to get this done. I tend to learn better by
"doing" anyway rather than by "reading."

Any help is very much appreciated!

 

[S Jackson]

Hi Cheryl:

I checked to see what references I had. Here is what is referenced:

- Visual Basic for Applications
- Microsoft Access 9.0 Object Library
- Microsoft DAO 3.6 Object Library
- OLE Automation
- Microsoft Visual Basic for Applications Extensibility 5.3
- MISSING: Microsoft Word 11.9 Object Library

(I don't know what the last one means when it says MISSING and still has a
"checked" checkbox beside it - its not there?)

Also, FYI, I am using MS Access 2000 (9.0.3821 SR-1)

Things are still not working. Can you tell what's wrong from the
information above?

And, Cheryl, thank you so much for writing an explanation of the code for
me.

BTW, if you don't mind me asking, what do you do? I see by your posts that
you work for Law Sys/Assoc. I am a legal assistant. I would prefer to be
in the Information Technology/Data Management field (of course, I need some
education!) - have you somehow managed to combine these two areas (legal
and information technology)?

Also, just as an fyi, my employer has tasked me with coming up with a
case-management system for our legal division - hence all my posts on here.
I had developed one for just our regional office and it works great, but
taking this to a larger scale has really caused me problems since I have no
training in this sort of thing and I'm being stubborn about having to shell
out my own money to buy manuals when they should be buying them for me
(shooting myself in the foot, I know).

S. Jackson

Hi Shelly,

I suspect that you need to check whether or not there is a reference to the
Microsoft DAO xx.x Object Library that is appropriate to your version of
Access; 3.51 for Access 97, 3.6 for Access 2000/2002. To check and set
references, open any module and from the VBA menu, click Tools|References.
Scroll through the list of available references until you find Microsoft DAO
3.51 [or 3.6] Object Library.

What the code you were given is doing is building a string of values which
will appear in the In() function. Here is the code with comments:

' First, let's say that your user has selected the following Regions in the
List Box:

New England
South Central
South Florida

' The following line is the start of the criteria string.
stLinkCriteria = "[Region] IN ("
For Each varItem In Me.RegionList.ItemsSelected
stLinkCriteria = stLinkCriteria & Me.Regionlist.ItemData(varItem) & ","
Next varItem

' After the first pass through the For/Next loop, stLinkCriteria will look
like the following, as the selected item has been concatenated or added to
the string:

"[Region] IN ("New England,"

' After the second pass, it will look like:

"[Region] IN ("New England,South Central,"

' After the third and last pass, it will look like:

"[Region] IN ("New England,South Central,South Florida,"

Note, that at this point the string still cannot be used because there is a
last comma that must be removed and a closing parentheses must be added.
So, the purpose of the following line of code:

stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

is to clean up the string so that it can be used. The Left() function is
saying:

Starting at the left-most position in the string, "[Region] IN ("New
England,South Central,South Florida,", return a certain number of
characters. The number of characters in this case is determined by the
Len()function. In this expression, the Len() function will evaluate the
length of stLinkCritera and subtract 1. Now your stLinkCriteria will look
like:

"[Region] IN ("New England,South Central,South Florida"

Notice that the comma is gone.

Now, a closing parenthesis must be added to stLinkCriteria using: & ")".

Post back and let us know how this worked.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Hi Cheryl:

You have helped me in the past and I sure do APPRECIATE IT! Thank you so
much for taking the time to do so.

I did wonder about the strLinkCriteria as opposed to stLinkCriteria (doh!
I'm catchin on!)

I also see where I need to put the extra )

The line now reads as follows:

stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

NOW, I'm getting a new error:

Compile error: Can't find object or library

The word "Left" is highlighted in the debug window.

Do I need a reference of some sort? What does "Left" mean?

Thank so much for your help.

S. Jackson

to
use

1)

_

1)

&

")"

Also, I think there is a problem in this one snippet of code in that there
needs to be a ) after the -1 and the variable name "strLinkCriteria" appears
in this snippet, when the original variable is "stLinkCriteria".

Try one of the above in your code and see if it works.
hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I have created a multi-select list box. After the user makes his
selections, he presses okay. I am trying to open up a form collecting
only
the data as specified by the user per his selections. Someone was

kind

to
reply as follows:

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


Unfortunately, when I try to type in this code in VB, I get an Invalid
Character error when I attempt to type the last line:

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

VB doesn't like the _

Sorry, I know you shouldn't use code unless you understand it, but I'm
floundering here. I have no books, no nothing. Only MS help files which
are useless and a deadline to get this done. I tend to learn better by
"doing" anyway rather than by "reading."

Any help is very much appreciated!

 

[Cheryl Fischer]

Hi Shelly,

Are you doing any Automation of MS Word in your application? I mean, are
you using Access to open Word Templates and fill in information and things
like that? If so, you would need the OLE Automation and Microsoft Word
Object Library. (I think we have done posts on Word Automation, but I
wanted to be sure.) However, I am curious about the version of the Word
Object Library. What version of Word are you using? Word 2000? If so,
then your reference should be for Microsoft Word 9.0 Object Library. Try
unchecking the Word 11.9 reference and then scrolling through the other
references to find the 9.0 library. If your Access application is not
interfacing with Word, just uncheck that reference. Also, Access MVP Doug
Steele has excellent tips and advice for making references work correctly
at: http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html



As to what I do, I'm a Programmer/Systems Analyst/Developer specializing in
tools for the legal industry such as case management systems, cost recovery
and financial systems interfaces, although I work for other industries as
well. I have absolutely no credentials as a legal assistant, although one
cannot help but pick up some knowledge here and there, and I have been
fortunate to have worked with some really excellent Legal Assistants! At
present Law/Sys Associates is a one-person company (me), but one has to
start somewhere!

With the current economic situation being what it is, it can be very
difficult to "play hardball" with an employer about providing training and
education. It distresses me, however, that often employees are expected to
perform to certain standards in fields for which they have no formal
training with no help from the employer in getting that training. Does your
employer have a formal Tuition Refund program? Perhaps that would be the
way to go - by taking courses which are directly related to your job and
then getting the tuition reimbursed. Many of the computer and software
stores (MicroCenter, for one) offer reasonably-priced classes. Also, I
believe much of these expenses would be deductible from your US Income Taxes
as Training/Education not reimbursed by your employer (if required to do a
job). You might want to look into that. At least as far as books are
concerned, I'd recommend that at least you get a copy of the "Access 2000
Developer's Handbook" by Ken Getz et al - I think you are ready for it.



--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Hi Cheryl:

I checked to see what references I had. Here is what is referenced:

- Visual Basic for Applications
- Microsoft Access 9.0 Object Library
- Microsoft DAO 3.6 Object Library
- OLE Automation
- Microsoft Visual Basic for Applications Extensibility 5.3
- MISSING: Microsoft Word 11.9 Object Library

(I don't know what the last one means when it says MISSING and still has a
"checked" checkbox beside it - its not there?)

Also, FYI, I am using MS Access 2000 (9.0.3821 SR-1)

Things are still not working. Can you tell what's wrong from the
information above?

And, Cheryl, thank you so much for writing an explanation of the code for
me.

BTW, if you don't mind me asking, what do you do? I see by your posts that
you work for Law Sys/Assoc. I am a legal assistant. I would prefer to be
in the Information Technology/Data Management field (of course, I need some
education!) - have you somehow managed to combine these two areas (legal
and information technology)?

Also, just as an fyi, my employer has tasked me with coming up with a
case-management system for our legal division - hence all my posts on here.
I had developed one for just our regional office and it works great, but
taking this to a larger scale has really caused me problems since I have no
training in this sort of thing and I'm being stubborn about having to shell
out my own money to buy manuals when they should be buying them for me
(shooting myself in the foot, I know).

S. Jackson

Hi Shelly,

I suspect that you need to check whether or not there is a reference to the
Microsoft DAO xx.x Object Library that is appropriate to your version of
Access; 3.51 for Access 97, 3.6 for Access 2000/2002. To check and set
references, open any module and from the VBA menu, click Tools|References.
Scroll through the list of available references until you find Microsoft DAO
3.51 [or 3.6] Object Library.

What the code you were given is doing is building a string of values which
will appear in the In() function. Here is the code with comments:

' First, let's say that your user has selected the following Regions in the
List Box:

New England
South Central
South Florida

' The following line is the start of the criteria string.
stLinkCriteria = "[Region] IN ("
For Each varItem In Me.RegionList.ItemsSelected
stLinkCriteria = stLinkCriteria & Me.Regionlist.ItemData(varItem) & ","
Next varItem

' After the first pass through the For/Next loop, stLinkCriteria will look
like the following, as the selected item has been concatenated or added to
the string:

"[Region] IN ("New England,"

' After the second pass, it will look like:

"[Region] IN ("New England,South Central,"

' After the third and last pass, it will look like:

"[Region] IN ("New England,South Central,South Florida,"

Note, that at this point the string still cannot be used because there

is

a
last comma that must be removed and a closing parentheses must be added.
So, the purpose of the following line of code:

stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

is to clean up the string so that it can be used. The Left() function is
saying:

Starting at the left-most position in the string, "[Region] IN ("New
England,South Central,South Florida,", return a certain number of
characters. The number of characters in this case is determined by the
Len()function. In this expression, the Len() function will evaluate the
length of stLinkCritera and subtract 1. Now your stLinkCriteria will look
like:

"[Region] IN ("New England,South Central,South Florida"

Notice that the comma is gone.

Now, a closing parenthesis must be added to stLinkCriteria using: & ")".

Post back and let us know how this worked.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Hi Cheryl:

You have helped me in the past and I sure do APPRECIATE IT! Thank you so
much for taking the time to do so.

I did wonder about the strLinkCriteria as opposed to stLinkCriteria (doh!
I'm catchin on!)

I also see where I need to put the extra )

The line now reads as follows:

stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

NOW, I'm getting a new error:

Compile error: Can't find object or library

The word "Left" is highlighted in the debug window.

Do I need a reference of some sort? What does "Left" mean?

Thank so much for your help.

S. Jackson

Hello Shelly,

The _ is a string continuation character. However, you do not

have

to

use
the line continuation in your code and can replace:

stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) -

1)

_
& ")"

with this:

stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) -

1)

&
")"

Also, I think there is a problem in this one snippet of code in that there
needs to be a ) after the -1 and the variable name "strLinkCriteria"
appears
in this snippet, when the original variable is "stLinkCriteria".

Try one of the above in your code and see if it works.
hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I have created a multi-select list box. After the user makes his
selections, he presses okay. I am trying to open up a form collecting
only
the data as specified by the user per his selections. Someone was kind
to
reply as follows:

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

_ better
by

 

[S Jackson]

Well, that solved the problem although I'm not sure how! LOL! I unchecked
the MS Word 11 Object Library and checked the MS Word 9.0 Object Library -
works beautifully now! How exciting when you get something to work! I
think what happened there was that I have the some parts of the betta
version of Office 2003 installed on my computer (not Word 2003).

Cheryl, I feel to guilty picking your brains here when you do this for a
living and should be getting paid for it. But, unfortunately, I work for
the State of Texas. Need I say more?

I find it fascinating and a great coincidence that you actually develop case
management systems for law offices! That is exactly what I am attempting to
do for the state. It differs greatly it is for Admin law and it also needs
to address the special considerations as we work for the state and are not a
private law firm.

How long has you been in business? I'd love to do what you do!

If you would like, email me at (e-mail address removed)

Shelly

Hi Shelly,

Are you doing any Automation of MS Word in your application? I mean, are
you using Access to open Word Templates and fill in information and things
like that? If so, you would need the OLE Automation and Microsoft Word
Object Library. (I think we have done posts on Word Automation, but I
wanted to be sure.) However, I am curious about the version of the Word
Object Library. What version of Word are you using? Word 2000? If so,
then your reference should be for Microsoft Word 9.0 Object Library. Try
unchecking the Word 11.9 reference and then scrolling through the other
references to find the 9.0 library. If your Access application is not
interfacing with Word, just uncheck that reference. Also, Access MVP Doug
Steele has excellent tips and advice for making references work correctly
at: http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html



As to what I do, I'm a Programmer/Systems Analyst/Developer specializing in
tools for the legal industry such as case management systems, cost recovery
and financial systems interfaces, although I work for other industries as
well. I have absolutely no credentials as a legal assistant, although one
cannot help but pick up some knowledge here and there, and I have been
fortunate to have worked with some really excellent Legal Assistants! At
present Law/Sys Associates is a one-person company (me), but one has to
start somewhere!

With the current economic situation being what it is, it can be very
difficult to "play hardball" with an employer about providing training and
education. It distresses me, however, that often employees are expected to
perform to certain standards in fields for which they have no formal
training with no help from the employer in getting that training. Does your
employer have a formal Tuition Refund program? Perhaps that would be the
way to go - by taking courses which are directly related to your job and
then getting the tuition reimbursed. Many of the computer and software
stores (MicroCenter, for one) offer reasonably-priced classes. Also, I
believe much of these expenses would be deductible from your US Income Taxes
as Training/Education not reimbursed by your employer (if required to do a
job). You might want to look into that. At least as far as books are
concerned, I'd recommend that at least you get a copy of the "Access 2000
Developer's Handbook" by Ken Getz et al - I think you are ready for it.



--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Hi Cheryl:

I checked to see what references I had. Here is what is referenced:

- Visual Basic for Applications
- Microsoft Access 9.0 Object Library
- Microsoft DAO 3.6 Object Library
- OLE Automation
- Microsoft Visual Basic for Applications Extensibility 5.3
- MISSING: Microsoft Word 11.9 Object Library

(I don't know what the last one means when it says MISSING and still has a
"checked" checkbox beside it - its not there?)

Also, FYI, I am using MS Access 2000 (9.0.3821 SR-1)

Things are still not working. Can you tell what's wrong from the
information above?

And, Cheryl, thank you so much for writing an explanation of the code for
me.

BTW, if you don't mind me asking, what do you do? I see by your posts that
you work for Law Sys/Assoc. I am a legal assistant. I would prefer to be
in the Information Technology/Data Management field (of course, I need some
education!) - have you somehow managed to combine these two areas (legal
and information technology)?

Also, just as an fyi, my employer has tasked me with coming up with a
case-management system for our legal division - hence all my posts on here.
I had developed one for just our regional office and it works great, but
taking this to a larger scale has really caused me problems since I have no
training in this sort of thing and I'm being stubborn about having to shell
out my own money to buy manuals when they should be buying them for me
(shooting myself in the foot, I know).

S. Jackson

Hi Shelly,

I suspect that you need to check whether or not there is a reference

to
the

Microsoft DAO xx.x Object Library that is appropriate to your version of
Access; 3.51 for Access 97, 3.6 for Access 2000/2002. To check and set
references, open any module and from the VBA menu, click Tools|References.
Scroll through the list of available references until you find

Microsoft
DAO

3.51 [or 3.6] Object Library.

What the code you were given is doing is building a string of values which
will appear in the In() function. Here is the code with comments:

' First, let's say that your user has selected the following Regions

in
the

List Box:

New England
South Central
South Florida

' The following line is the start of the criteria string.
stLinkCriteria = "[Region] IN ("
For Each varItem In Me.RegionList.ItemsSelected
stLinkCriteria = stLinkCriteria & Me.Regionlist.ItemData(varItem)

&
","

Next varItem

' After the first pass through the For/Next loop, stLinkCriteria will look
like the following, as the selected item has been concatenated or

added

to

the string:

"[Region] IN ("New England,"

' After the second pass, it will look like:

"[Region] IN ("New England,South Central,"

' After the third and last pass, it will look like:

"[Region] IN ("New England,South Central,South Florida,"

Note, that at this point the string still cannot be used because there

is

a
last comma that must be removed and a closing parentheses must be added.
So, the purpose of the following line of code:

stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

is to clean up the string so that it can be used. The Left() function is
saying:

Starting at the left-most position in the string, "[Region] IN ("New
England,South Central,South Florida,", return a certain number of
characters. The number of characters in this case is determined by the
Len()function. In this expression, the Len() function will evaluate the
length of stLinkCritera and subtract 1. Now your stLinkCriteria will look
like:

"[Region] IN ("New England,South Central,South Florida"

Notice that the comma is gone.

Now, a closing parenthesis must be added to stLinkCriteria using: & ")".

Post back and let us know how this worked.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Hi Cheryl:

You have helped me in the past and I sure do APPRECIATE IT! Thank

you
so

much for taking the time to do so.

I did wonder about the strLinkCriteria as opposed to stLinkCriteria (doh!
I'm catchin on!)

I also see where I need to put the extra )

The line now reads as follows:

stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

NOW, I'm getting a new error:

Compile error: Can't find object or library

The word "Left" is highlighted in the debug window.

Do I need a reference of some sort? What does "Left" mean?

Thank so much for your help.

S. Jackson

Hello Shelly,

The _ is a string continuation character. However, you do not

have

to
use
the line continuation in your code and can replace:

stLinkCriteria = Left(stLinkCriteria,

Len(stLinkCriteria) -
1)

_
& ")"

with this:

stLinkCriteria = Left(stLinkCriteria,

Len(stLinkCriteria) -
1)

&
")"

Also, I think there is a problem in this one snippet of code in that
there
needs to be a ) after the -1 and the variable name "strLinkCriteria"
appears
in this snippet, when the original variable is "stLinkCriteria".

Try one of the above in your code and see if it works.
hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I have created a multi-select list box. After the user makes his
selections, he presses okay. I am trying to open up a form collecting
only
the data as specified by the user per his selections. Someone was
kind
to
reply as follows:

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

_ but
I'm