comma in a list box

[Guest]

I am using the AddItem method to fill a listbox from string variables, but
when there is a comma in the string, it continues on a new line and does not
include the comma. I am quite new to this so it is probably something
simple, but it has me baffled.

 

[John W. Vinson]

I am using the AddItem method to fill a listbox from string variables, but
when there is a comma in the string, it continues on a new line and does not
include the comma. I am quite new to this so it is probably something
simple, but it has me baffled.

You may want to consider using a small Table (or a query sorting the records
in the query in alphabetical order); it's easy to just add or delete a record
from the table. Value List listboxes are useful mainly if you have a very
short and static list (for instance "Mr.","Ms.","Miss","Mrs." or the like).

You can use a Value List with values containing commas if the entire value is
enclosed in quotemarks: e.g.

"ABC","DEF","A,C","XYZ"

gives A,C as one of the choices.

John W. Vinson [MVP]

 

[Damon Heron]

Here ya go:

Dim mystr As String
mystr = "'" & Me.Text3 & "'"
AddItemToEnd List0, mystr

this assumes you are using the additemtoend function in Access help and
Textbox is source of string.

Damon

 

[Guest]

Thanks John, but I want to avoid using another table or a query if I can.
The listbox is on a form on its own which is loaded through code from another
form. According to the user's choices, many fields are combined into rows in
the list ready for pasting into a separate document. This text needs to be
in one control. (I originally used a text box, but the text soon got too big
for it.)
The text goes into the list box from string variables. I can't see how to
include quotemarks in a string. Can this be done? When I tried, it added
the variable name rather than the content.
The program stores questions, answers, subjects, etc for quizzes then pastes
chosen data into a separate document. Perhaps it would explain more if I
included some of the code:
Dim c As Control, fn As Boolean, inclSubj As Integer, s As String
Static qNum As Long
fn = IsLoaded("PasteToDocument")
If fn = False Then DoCmd.OpenForm "PasteToDocument"
If Forms!PasteToDocument!Text0.ListCount = 0 Then qNum = 1
inclSubj = MsgBox("Include the subjects?", vbYesNo, "Paste To Document")

For Each c In Forms!buildQuiz.Controls
If InStr(1, c.Name, "check", vbTextCompare) > 0 Then
If IsNumeric(Right$(c.Name, 1)) = True Then
If c.Value = -1 Then isChecked = 1
End If
End If
Next c
If isChecked = 0 Then
MsgBox "None of the check boxes are ticked"
Exit Sub
End If

If Check1.Value = -1 Then
s = CStr(qNum) & " "
If inclSubj = 6 Then
subject1.SetFocus
s = s & subject1.Text
Forms!PasteToDocument!Text0.AddItem s
question1.SetFocus
Forms!PasteToDocument!Text0.AddItem question1.Text
Else
question1.SetFocus
s = s & question1.Text
Forms!PasteToDocument!Text0.AddItem s
End If
answer1.SetFocus
Forms!PasteToDocument!Text0.AddItem answer1.Text
qNum = qNum + 1
End If

If Check2.Value = -1 Then
s = CStr(qNum) & " "
If inclSubj = 6 Then
subject2.SetFocus
s = s & subject2.Text
Forms!PasteToDocument!Text0.AddItem s
question2.SetFocus
Forms!PasteToDocument!Text0.AddItem question2.Text
Else
question2.SetFocus
s = s & question2.Text
Forms!PasteToDocument!Text0.AddItem s
End If
answer2.SetFocus
Forms!PasteToDocument!Text0.AddItem answer2.Text
qNum = qNum + 1
End If

If Check3.Value = -1 Then
etc...

 

[Guest]

Damon, you're a gem! Thanks. I tried something similar, but used """
instead of "'". Thanks again.

 

[John W. Vinson]

Thanks John, but I want to avoid using another table or a query if I can.
The listbox is on a form on its own which is loaded through code from another
form. According to the user's choices, many fields are combined into rows in
the list ready for pasting into a separate document. This text needs to be
in one control. (I originally used a text box, but the text soon got too big
for it.)

Whoa. If a Textbox is bound to a memo field, it can hold two billion bytes.
How big do you want!!!?

The text goes into the list box from string variables. I can't see how to
include quotemarks in a string. Can this be done? When I tried, it added
the variable name rather than the content.

You need to represent a doublequote character by two consecutive doublequote
characters in a string delimited by doublequotes - or, if your text won't ever
contain apostrophes, you can use ' as a delimiter instead of ".

The program stores questions, answers, subjects, etc for quizzes then pastes
chosen data into a separate document. Perhaps it would explain more if I
included some of the code:

Dim c As Control, fn As Boolean, inclSubj As Integer, s As String
Static qNum As Long
fn = IsLoaded("PasteToDocument")
If fn = False Then DoCmd.OpenForm "PasteToDocument"
If Forms!PasteToDocument!Text0.ListCount = 0 Then qNum = 1
inclSubj = MsgBox("Include the subjects?", vbYesNo, "Paste To Document")

For Each c In Forms!buildQuiz.Controls
If InStr(1, c.Name, "check", vbTextCompare) > 0 Then
If IsNumeric(Right$(c.Name, 1)) = True Then
If c.Value = -1 Then isChecked = 1
End If
End If
Next c
If isChecked = 0 Then
MsgBox "None of the check boxes are ticked"
Exit Sub
End If

If Check1.Value = -1 Then
s = CStr(qNum) & " "
If inclSubj = 6 Then
subject1.SetFocus
s = s & subject1.Text
Forms!PasteToDocument!Text0.AddItem s
question1.SetFocus
Forms!PasteToDocument!Text0.AddItem question1.Text
Else
question1.SetFocus
s = s & question1.Text
Forms!PasteToDocument!Text0.AddItem s
End If
answer1.SetFocus
Forms!PasteToDocument!Text0.AddItem answer1.Text
qNum = qNum + 1
End If

If Check2.Value = -1 Then
s = CStr(qNum) & " "
If inclSubj = 6 Then
subject2.SetFocus
s = s & subject2.Text
Forms!PasteToDocument!Text0.AddItem s
question2.SetFocus
Forms!PasteToDocument!Text0.AddItem question2.Text
Else
question2.SetFocus
s = s & question2.Text
Forms!PasteToDocument!Text0.AddItem s
End If
answer2.SetFocus
Forms!PasteToDocument!Text0.AddItem answer2.Text
qNum = qNum + 1
End If

If Check3.Value = -1 Then
etc...

Well... this is CERTAINLY not how I would go about this. Just for starters,
you are repeating big blocks of code for Check1 and Question1 and Answer1;
this would seem to be using THE FORM ITSELF as a data source. Access works a
lot better if you use tables as data sources. If you had a table with one
record per question, then it would become a very simple recordset loop to fill
in any number of questions.

If it's working for you, fine... but I don't see what benefit you are getting
from the use of Access as a relational database; I may be way off, but it
appears you're just using it as a forms manager.

John W. Vinson [MVP]

 

[David W. Fenton]

I am using the AddItem method to fill a listbox from string
variables, but when there is a comma in the string, it continues
on a new line and does not include the comma. I am quite new to
this so it is probably something simple, but it has me baffled.

What happens if you surround it with double quotes?

Const strQuote = """"

...

Me!MyListbox.AddItem strQuote & "New Item" & strQuote