Solved Listbox (1 Viewer)

[goldeliz]

I'm not sure if this is the correct place to ask this question and if it should go somewhere else I do apologize. What I am doing is adding text from some tables to a listbox. All that is working fine except where the text contains a comma, the text in the textbox cuts off at the comma. Obviously I need the whole text to display not just the text up to the comma. Any suggestions on how to get it to function correctly?

 

[Galaxiom]

Are you are adding to a List RowSourceType? It may be that your Regional Settings use a comma in the RowSource to separate the columns and the characters after the comma are appearing in the next column.

Generally better to use a Table/Query RowSourceType if the data is coming from a table.

 

[goldeliz]

Table query will not work tried it. It only works, albeit incorrectly, with value list. It is just text, not numbers, that should be displayed.

Table query gives run-time error 6014

 

[Galaxiom]

Surely it is just a matter of constructing the right query if the values are in a table? (I assume you were not trying to use code to add individual items directly to a table/query RowSource.)

Another alternative is to create a disconnected ADODB.Recordset, add the records to it, then set the Recordset Property of the Listbox to the recordset. But this should not be necessary.

 

[goldeliz]

Private Sub searchresponses_Click()

If ifTableExists("password") Then
Stop

strsql = "select * from terminate where ID=1;"
Set rst = CurrentDb.OpenRecordset(strsql, dbOpenDynaset)
For i = 1 To rst.Fields.Count - 1
Data2 = DLookup(rst.Fields(i).Name, "terminate")

If InStr(Data2, "terminate") <> 0 Then
MySearchLisBox.AddItem Item:=Data2
End If

Next i
End If

End Sub


This is an example of the code. I can see how to do it if I want the whole table, but I only want the items containing certain words.

 

[Galaxiom]

You have the data spread across multiple fields. This suggests you have a normalization error in your design.

All those terms should be able to come from the same field in the table.
Whatever reason you have them in separate fields should be designated by a value in another field.

This job (and many others you are yet to crash into), will be much simpler if you get the structure fixed.

 

[goldeliz]

No, the data is not across multiple tables, but there are multiple possible fields that can have a keyword I am searching to add to listbox

 

[Galaxiom]

I didn't suggest the data was across multiple tables. Having the same kind of data across multiple fields can also be a normalization error.

It is hard to tell without seeing the structure but I have never been wrong before when suspecting a normalization problem.

Why are the terms you are searching through in different fields?

 

[arnelgp]

...
...
MySearchLisBox.AddItem Item:="""" & Data2 & """"
...
...

 

[goldeliz]

I didn't suggest the data was across multiple tables. Having the same kind of data across multiple fields can also be a normalization error.

It is hard to tell without seeing the structure but I have never been wrong before when suspecting a normalization problem.

Why are the terms you are searching through in different fields?

Because their are multiple options to choose from that may contain a keyword. I have no choice to have a flat structure.

 

[goldeliz]

...
...
MySearchLisBox.AddItem Item:="""" & Data2 & """"
...
...

Thank you. That worked perfectly.

 

[arnelgp]

you're welcome

 

[Galaxiom]

The code you are using is dreadfully inefficient.
You could use a UNION query with for a Query RowSource.

SELECT field1 FROM Terminate WHERE field1 Like "*terminate*" AND ID = 1
UNION 
SELECT field2 FROM Terminate WHERE field2 Like "*terminate*" AND ID = 1
UNION
SELECT field3 .......
etc
ORDER BY field1

You would use a parameter for ID rather than the literal value.

If you believe you need to do it your way because you are adding new fields during runtime, that would confirm that you are definitely making a huge design mistake. Either way I still believe you are building a denormalized nightmare and should stop right now and get advice about the deign.