The values after , stored in table doesn't appear in listbox

[aman]

I am getting a very strange problem. My table stores some values which I try to populate in a listbox. Any record in the table that stores "," (Comma) sign doesn't get stored in the listbox. The record before comma sign will get stored in the listbox but not the record after comma sign.

I am writing the following code:

 Set rs = CurrentDb.OpenRecordset("select Action,Type from tblTC_Actions where Type=" & Me.cboMDForms.Column(0))
End If
 rs.MoveFirst
 Do While Not rs.EOF
lstItems.AddItem rs.Fields(0).Value
rs.MoveNext
Loop

The data in the table suppose is:
tblTC_Actions
1.Action 3 pass graded live calls (A mix of NB/EB) 3 pass graded Documentation (can be linked to the call audits)
2.1 competent live EO call (where such a call is taken) Where no EO call is taken through NE stage a pass graded role play must be completed before nomination to ‘Competent’ status
3.If, after 8 weeks, a lack of opportunity prevents acquiring evidence in NB, EB or EO activity, supervisor agrees with QAT most appropriate action, e.g. role play, and/or assessment of first event.

The last record only stores "If" in the listbox

I hope I made it clear. Any help will be much appreciated.

Thanks

 

[The_Doc_Man]

First, just a couple of quick comments ... you can omit ".Value" because that is the default for any VBA control or recordset field that actually HAS a value. You should avoid using TYPE as a variable name because I believe TYPE is a reserved word. Using reserved words as variables can badly confuse Access and VBA.

Second, for testing purposes, make item 3 read "If after 8 weeks a lack.... in NB, EB, or EO" - and then tell us if it truncates after the "NB," position.

A comma isn't a special character like carriage-return or line-feed etc. so should not be interpreted as a field break. However, I'm not going to swear that it isn't because in some standard SQL for ORACLE, that comma IS used as a field break when you are looking at this as a raw record stream (ODBC-type connections). If you look at Excel's "CSV" format, that looks VERY MUCH like the internal data stream output by an SQL query from an ODBC source. Which means that you might need quoting to include commas in a natural sentence.

However, you might also consider this another way... Do you erase lstItems before you start adding to it? AND... do you have any other fields in the list box? If everything you wanted to have in the list box was the action value (string), you might just change the rowsource of the the list box.

lstItems.RowSource = "SELECT Action FROM tblTC_Actions WHERE Type = " & Me.cboMDForms.Column(0));"
lstItems.Requery

(or something like this).

I infer from your code that you want the text explanations in the combo box to be the thing that you insert into your data later, not the code for it. That is the apparent effect of the code. My suggestion is, in essence, "don't copy the source to another control; re-point the other control to the same source and let the control pick up the same values for itself."

 

[aman]

The_Doc_Man, Access won't let me have a comma within a string to assign to a row of a list box. It just ignores everything after the comma. I tried your suggestion as below but it still truncates anything after ,.

Second, for testing purposes, make item 3 read "If after 8 weeks a lack.... in NB, EB, or EO" - and then tell us if it truncates after the "NB," position.

 

[The_Doc_Man]

I'm afraid that confirms what I suspected... internally, something is being passed as though it were a comma-separated set of fields. In a purely graphic environment, this little detail is hidden from view because the GUI extracts what it wants and we never see it. BUT the comma is there.

The other system on which I worked was a command-line environment (more like a mainframe) and didn't have that GUI in the way, which is how I came to suspect your real problem.

Can you try to change the .RowSource for the combo box to see if that changes the way it looks at things?

 

[JHB]

I don't know why you've that problem, but comma should not be a problem in a listbox.

 

[Minty]

I don't know why you've that problem, but comma should not be a problem in a listbox.

I think it's because the OP is using the list add method, looping through a recordset, not a just simple rowsource query.

 

[JHB]

I think it's because the OP is using the list add method, looping through a recordset, not a just simple rowsource query.

Please read post #3.

 

[Minty]

Please read post #3.

Damn my rubbish reading skills...

 

[JHB]

Damn my rubbish reading skills...

Yeah, but your post got me thinking, and I tried what the OP did, and I got the same error until I until I enclosed it with a single quotation mark.

lstItems.AddItem ([COLOR=Red][B]"'" &[/B][/COLOR] rs.Fields(0) [B][COLOR=Red]& "'"[/COLOR][/B])

 

[aman]

That's great JHB. You are a star. One more question is when I add items in the listbox then it truncates the long lines in the records .It only displays the records to the width of listbox. I have found no way to WRAP TEXT IN A LISTBOX, and I do not want to use a horizontal scrollbar.

ANy help will be much appreciated.

 

[JHB]

.. I have found no way to WRAP TEXT IN A LISTBOX, and I do not want to use a horizontal scrollbar.

Sorry, but you can't wrap the text.