By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,287 Members | 1,668 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,287 IT Pros & Developers. It's quick & easy.

Recordset And Then Looping Through, Display Results TextBox Each Line

gcoaster
100+
P: 117
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'
Mar 27 '14 #1
Share this Question
Share on Google+
9 Replies


Expert 100+
P: 1,240
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:

Expand|Select|Wrap|Line Numbers
  1. me.Results_list.rowsource="Select [name of column you want to show] from tblKEYWORDS where colKeyword='" & me.cboCategory & "'"
  2.  
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
Mar 27 '14 #2

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim strRst As String
  3. Dim rst As DAO.Recordset
  4. Dim strKeywords As String
  5.  
  6. 'Setup the recordset
  7. Set db = CurrentDb
  8. strRst = "SELECT colKeyword FROM tblKEYWORDS WHERE cboCategory = '" & txtcategories & "'"
  9. Set rst = db.OpenRecordset(strRst, dbOpenDynaset)
  10.  
  11. 'Loop through all the records
  12. Do While Not rst.EOF
  13.     strKeywords = rst!colKeyword & vbCrLf
  14. Loop
  15.  
  16. 'Set the textbox value
  17. 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.
Mar 27 '14 #3

zmbd
Expert Mod 5K+
P: 5,397
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.
Mar 27 '14 #4

gcoaster
100+
P: 117
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
Apr 12 '14 #5

gcoaster
100+
P: 117
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!
Apr 12 '14 #6

gcoaster
100+
P: 117
Got this to work in Access 2010!

after much drama here is the code that works!

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboCategory_AfterUpdate()
  2.   Dim dbs As DAO.Database, rst As DAO.Recordset, StrKeywords As String
  3.  
  4.   Set dbs = CurrentDb
  5.  
  6.   Set rst = dbs.OpenRecordset("SELECT colKeyword " _
  7.   & "FROM tblKeywords " _
  8.   & "WHERE colCategory='" & Me.cboCategory & "'")
  9.   If Not rst.EOF Then
  10.     Do
  11.       StrKeywords = StrKeywords & rst![colKeyword] & "<br>"
  12.       rst.MoveNext
  13.     Loop Until rst.EOF
  14.     Me.txtKeywords = Left(StrKeywords, Len(StrKeywords) - 4)
  15.   End If
  16. End Sub
Thank you everyone! Happy Easter... JESUS HAS RISEN!
Apr 12 '14 #7

zmbd
Expert Mod 5K+
P: 5,397
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!"
Apr 13 '14 #8

NeoPa
Expert Mod 15k+
P: 31,768
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.
Apr 13 '14 #9

Expert 100+
P: 1,240
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
Apr 14 '14 #10

Post your reply

Sign in to post your reply or Sign up for a free account.