DLoolup Show Results Values in Texbox on its own line multiple columns (1 Viewer)

[gcoaster]

Access 2010 - Question

Hello Group
what i am trying to occomplish something i cannot find no matter how hard i search! maybe i am not wording it right.

I would like to use DLookup to show results values from a table and display in a unbound textbox on a form. the results from each column in the table need to be on seperate lines, a break if you may. Here is the code I have so far.

txtKeywords = DLookup("colKeyword", "tblKEYWORDS", "cboCategory = '" & txtcategories & "'")

In colKeyword Column In tblKEYWORDS table match what i select in cboCategory Combo drop down box and populate txtcategories textbox on form

What I would like to do is show All colKeyword results in textbox [txtcategories] and display each result on its own line!

example

entry1
entry2
entry3
entry4


Thank you for your time

 

[JHB]

How does the data looks like returned from the Dlookup?

Set the text control property "Text format" to "Rich Text"
You also need to insert the HTLM code for new line = <br> between each entry.
Ex:"entry1<br>entry2<br>entry3<br>entry4"

 

[gcoaster]

Hello JHB,

I apologize for not being more clear. I have a great deal of trouble putting all of this into the correct syntax.

I am so excited! THIS is working but it only shows one result

txtKeywords = DLookup("colKeyword", "tblKeywords", "colCategory = '" & Me.cboCategory & "'")

I would love txtKeywords to display all of the results from table showing each result on its own line. somehow putting <br> in the code or something.

entry one
entry two
entry three
entry etc

Thank you for your time

 

[JHB]

...
I am so excited! THIS is working but it only shows one result
...

Yes - and it will always be so, (the first found value for the criteria is returned), it is the way DLookUp works, it only return one entry.
Therefore I asked how the data looks like returned from the DLookUp, because if it was in the same field and record like "Entry1 Entry2 Entry3 .." you could have replaced the " " between the entries with "<br>".
Now to get what you wanted, you need to use some VBA code and a Recordset.
You can use the below code in the form's "On Current" event or the cboCategory "After Update" event.
But as mention before set the text control property "Text format" to "Rich Text" and another thing, the textcontrol must be as high as the amount of lines you want to see. The property "Can Grow" is only affected when the form is printed.
Maybe you should use a List control instead of a text control, it is easier to deal with, and you could use a query with the criteria as control source.

  Dim dbs As Database, rst As Recordset, StrKeywords As String
  
  Set dbs = CurrentDb
  
  Set rst = dbs.OpenRecordset("SELECT colKeyword " _
  & "FROM tblKeywords " _
  & "WHERE colCategory='" & Me.cboCategory & "'")
  If Not rst.EOF Then
    Do
      StrKeywords = StrKeywords & rst![colKeyword] & "<br>"
      rst.MoveNext
    Loop Until rst.EOF
    Me.txtKeywords = Left(StrKeywords, Len(StrKeywords) - 4)
  End If

 

[gcoaster]

awesome JHB, Thank you for your time..

Trying it now, getting a compile error at

dbs As Database

 

[JHB]

Have you checked the References, the Microsoft DAO object libary need to be marked.
I'll attached a small sample database.

 

[gcoaster]

JHB YOU ROCK!

I really appreciate it. you went out of your way to help

Here is the solution
Developers who want to use the Data Access Object (DAO) library instead of, or in addition to, the new Microsoft ActiveX Data Object (ADO) model, must manually set the reference in each new database.

1. Open a new Access database.
2. Click Modules under Objects, and then click New.
3. In the Visual Basic Editor, click References on the Tools menu.

4. Scroll through the list until you find the Microsoft DAO 3.6 Object Library, and click to select the reference

5. Click OK.
6. On the File menu, click Close and Return to Microsoft Access.

And now.. I get this error... Error in loading DLL.

I guess im not worthy of using access! anyways i am on the right trail
i will do a office repair and see if there are any updates for buggy access


thank you JHB




thank you thank you thank you!

 

[gcoaster]

Got It To Work With Access 2010!!!

Here is the modified Code

Private Sub cboCategory_AfterUpdate()
  Dim dbs As DAO.Database, rst As DAO.Recordset, StrKeywords As String
  
  Set dbs = CurrentDb
  
  Set rst = dbs.OpenRecordset("SELECT colKeyword " _
  & "FROM tblQldKeywords " _
  & "WHERE colCategory='" & Me.cboCategory & "'")
  If Not rst.EOF Then
    Do
      StrKeywords = StrKeywords & rst![colKeyword] & "<br>"
      rst.MoveNext
    Loop Until rst.EOF
    Me.txtKeywords = Left(StrKeywords, Len(StrKeywords) - 4)
  End If
End Sub

Also.. had to copy DLL to C:\Program Files\Common Files\microsoft shared\DAO in windows 8

Has Microsoft abandoned Access? Anyways... after jumping threw many hoops and help from the experts on www.access-programmers.co.uk this is a success! never give up!

thank you