comma in a list box

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
Click to expand...

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]
 
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
 
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...
 
Damon, you're a gem! Thanks. I tried something similar, but used """
instead of "'". Thanks again.
 
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.)
Click to expand...

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.
Click to expand...

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:
Click to expand...

Yes... always easier to debug code if you can see it... said:
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...
Click to expand...


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]
 
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.
Click to expand...

What happens if you surround it with double quotes?

Const strQuote = """"

...

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