Recordset And Then Looping Through, Display Results TextBox Each Line

[gcoaster]

Hello Group

I need help,

I need to use an [Event Procedure] 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.

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


Earlier thread 'Access 2010 - DLoolup Show Results Values in Texbox on its own line multiple columns'

[jimatqsi]

gcoaster,
Your question is not at all clear. At times you seem to be confusing "column" with "row." I doubt that "each column in the table need to be on separate lines", it would be a very odd way to collect the data. Assuming you mean each row in the table would need to be on separate lines.

Further, you say "What I would like to do is show All colKeyword results " after you also said "In colKeyword Column In tblKEYWORDS table match what i select in cboCategory " ... in that case, colKeyword will be only one value, the value=to what is in cboCategory, so all colKeyword results will be the same.

Read your post carefully and rewrite your request to reflect what you actually want so that someone who has no idea what you're doing will get the idea. The table name is tblKEYWORDs; the column to search is colKeyword; what is the name of the column you want to show for all rows that have colKeyword=cboCategory?

I'm going to take a shot in the dark here and suggest what you really want to populate is a list box and not a textbox. If you make a list box and set the data source with the SQL code as shown below I think that may give you what you want. Make a listbox called Results_list and in the AfterUpdate event for the combo box cboCategory put this code:

me.Results_list.rowsource="Select [name of column you want to show] from tblKEYWORDS where colKeyword='" & me.cboCategory & "'"
 

That will change the content of the list box every time you change the combo box. You might want to add a sort order.

Jim

[Seth Schrock]

This question is an offshoot from Access 2010 - DLookup Show Results Values in Textbox on its own line multiple columns. So your code would be something like this.

Dim db As DAO.Database
Dim strRst As String
Dim rst As DAO.Recordset
Dim strKeywords As String
 
'Setup the recordset
Set db = CurrentDb
strRst = "SELECT colKeyword FROM tblKEYWORDS WHERE cboCategory = '" & txtcategories & "'"
Set rst = db.OpenRecordset(strRst, dbOpenDynaset)
 
'Loop through all the records
Do While Not rst.EOF
    strKeywords = rst!colKeyword & vbCrLf
Loop
 
'Set the textbox value
Me.txtKeywords = strKeywords

Without further information as to where this is going, I can't help you as to which event this code should go in, but this should get you started.

[zmbd]

Jim and Seth have been very gracious with their time; however, gcoaster – we’re not a code writing service.

Therefor, at this point I am going to have to ask that you provide a clear, specific question and provide any code that you’ve already attempted to solve that issue, then we’re certainly here to help.

[gcoaster]

Hello Zmbd,
I understand you are not a coding service.
And i am grateful for the time from everyone.

its extremely hard for me to put all of this in words properly.
I apologize

[gcoaster]

Ok, I will do my best!!

I have a TABLE
table is named | tblKeywords
there are 3 columns in the table
[1] ID
[2] colKeyword
[3] colCategory

I have a FORM
The Form is named | frmMain

On frmMain i have a dropdown combo box and its named | cboCategory

on frmMain i also have a unbound text field set to rich format
and it is named |txtKeywords


Would i am trying to do is select a category from cbocategory and then have display all of the entries in tblKeywords where the cboCategory selection is matched

For instance i select Mens Shoes
and then in the box it shows all of the results from table
one on each line.

tblKeywords - colCategory, colKeyword
frmMain - txtKeywords


Example of data in table - colCategory=mens shoes colKeyword=business shoes

I would love txtKeywords to display

business shoes
best shoes for men
casual mens shoes
designer mens shoes


Thank you ALL!

[gcoaster]

Got this to work in Access 2010!

after much drama here is the code that works!

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 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
End Sub

Thank you everyone! Happy Easter... JESUS HAS RISEN!

[zmbd]

Hello,
Now that makes better sense (^_^)

and I'm glad to see that you worked it out... if you had started out with your post #6 here we would have sent you the following insite articles

Sorry, I'm babysitting at a friends house today; thus, I don't have my normal set of tools and the laptop has a realy small keyboard (and a missing key or two ROTFL); however, I think that if you will read thru the following Multiple Combobox Filter with Clear Option

and

How to select Records in an Access Subform from a Combo Box

you will certainly find other code in our insights articles that you can use/modify so I highly advise a read thru of the titles.

Our Pleasure, Happy Easter to you Too: "Christ Is Risen! Hallelujah!"

[NeoPa]

I was amused and very pleased to see that, once again, when someone goes to the trouble (and it is trouble. It can be really hard to express problems sometimes.) of expressing the situation clearly and in precise detail for the experts so that they can help, they naturally sort things out a lot more clearly in their own heads. Often, as in this case clearly, it leads very shortly to the OP coming up with their own answer without further help.

This isn't about criticism of not doing that to start with. It's much harder than it seems. It's just that much more benefit comes from it than most people realise. Congratulations on getting your answer GCoaster.

[jimatqsi]

gcoaster,
Congratulations on working out your own solution. Way to go.

Now I'd like to encourage to try to do it again but use a list box for your results instead of a text box. I say that as one very late to understand the virtues of list boxes. I avoided using them for years.

You can do the same thing with less code and get some follow-on processing available through the event handling of the list box. For instance, your user could click on "casual mens shoes" and make something happen.

Just a thought to extend your understanding of the tools available.

Jim