Show multiple attributes and records in one textbox

[M3lvn]

Hi,

Here's an example:

tblClients with the following columns:
- name
- address
- tel

I want the information shown in one textbox via a formbutton, e.g:

I want the following shown in one textbox:

John
Thirdstreet 3
97978789
 
Pete
Sunstreet 345
231211
 
Etc
etc

I need the code to make the formbutton do this for me.

Thanks in advance.

 

[RuralGuy]

How many records are you planning to put in this one control and why?

 

[M3lvn]

The amount of records can vary from 3 or 4 to 400.

Its what the customer wants. He wants to be able to select a record individually, as it is now with a combobox, but he also wishes to see the whole list if necessary.

 

[wiklendt]

tell him it will cost him less (i.e., your fees for time spent developing the db) if you just show all that data in their separate controls. why can't they just be separate in the form? or perhaps you can place text controls for these data but make them LOOK like one box - i.e., no borders or background colours...

 

[M3lvn]

The form already has separate textboxes for the record attributes individually. At the moment the selection is done via a (dropdown) combobox. OnChange the textboxes display the information according to the choice made.

But only 1 record is shown each time. The lowest textbox on the form is a bit larger. So I need a function (either via the same combobox or a separate button) to select all the records and display them in the bigger textbox.

Is it possible?

 

[wiklendt]

why not just use i don't really understand.... when you select a record from a dropdown there will always only be one record displayed at one time. if you want more records displayed, use a multi-select listbox.

i have no idea what a "lowest" textbox is and why it needs to be bigger - can't you just expand the text box so that it can display all the information?

alternatively, you can use a subform and display as a datasheet - this would be sueful if you say, select a customer and want to see all their purchases (which is the only way i can think of that you may want to display more than one record when asking a combo box to select only one record....?

what are you actually trying to do. what's the data involved?

edit: what is your combo box actually selecting? how is it possible you want to select one record but display more than one? how is access supposed to know you wanted more than one when you asked it to display only one?

 

[M3lvn]

Thats the whole point. When a selection is made with the dropdown only 1 record is displayed. I want to make it possible that there is a button (or the same dropdownbox) that can select and display all the records of the same table.
But the output must be displayed in 1 textbox. This textbox is on the same form and is vertically larger. The user can scroll and look at all the records.

A subform with a datasheet will be using more space than necessary.

Again, the output Im looking for is like this:

person1
address1
tel1
discription (a lot of text here)

person2
address2
tel2
discription (a lot of text here)

person3
and so on

This should be displayed in 1 textbox.

edit: what is your combo box actually selecting? how is it possible you want to select one record but display more than one? how is access supposed to know you wanted more than one when you asked it to display only one?

Thats my question!
How can I select and display all the records?

 

[DCrake]

Ok here is how to get it how you want it. But I like the rest am convinced your manager has the wrong idea.

On the form Load event of your form

Dim Rs As DAO.Recordset
Dim strItems As String

Set Rs = CurrentDb.OpenRecordset("YourTableName")

If Not Rs.EOF And Not Rs.BOF  Then
    Do Until Rs.EOF
      StrItems = StrItems & Trim(Rs("Field1") & " ") & vbnewline
      StrItems = StrItems & Trim(Rs("Field2") & " ") & vbnewline
      StrItems = StrItems & Trim(Rs("Field3") & " ") & vbnewline
      etc
      Rs.MoveNext
      StrItems = Replace(StrItems,vbnewline & vbnewline,vbnewline)
      StrItems = StrItems & vbnewline
   Loop
   Rs.Close
   Set Rs = Nothing
End If

Me.LongTextBox = StrItems

This is aircode and untested

 

[M3lvn]

It works! Thank you very much DCrake!

For those who are interested Ive changed the following code to get a blank line between records, so the output is more neatly:

strItems = Replace(strItems, vbNewLine & vbNewLine, vbNewLine & vbNewLine)

But I do have 1 other request:
How do I put in a criteria?

I prefer it in the code, otherwise I guess I can use a query.

 

[wiklendt]

put in criteria for what? where? and with what result? please try to be as specific and detailed as possible.

 

[wiklendt]

the bit i don't understand: you have a table of clients. you select to view the details of client 1, say. but you expect to see the details of all clients????

if you ask access to show you client 1, it will show you client one. what are you actually trying to view? b/c i don't believe you when you say you want to say you want to view the details of one client, but to see the details of all the clients: which do you want??? one client or all?? ...are you selecting one client and wanting to view their address? or their purchase details? or what? you've lost me on the "one but all but only one" bit....

 

[RuralGuy]

It works! Thank you very much DCrake!

For those who are interested Ive changed the following code to get a blank line between records, so the output is more neatly:

Are you sure a SubForm in Continuous Form mode will not work for you? I think you will find it much neater and easier to use. Or a ListBox even.

 

[M3lvn]

put in criteria for what? where? and with what result? please try to be as specific and detailed as possible.

A criteria for the selection. At the moment the output is like this:

Person1
canada
street1

person2
canada
street2

person3
mexico
street4

I wanted to filter out only the people living in Canada.
I can probably achieve this with a querry, but it would be better if it can be done within de vba code that DCrake posted.

 

[M3lvn]

the bit i don't understand: you have a table of clients. you select to view the details of client 1, say. but you expect to see the details of all clients????

if you ask access to show you client 1, it will show you client one. what are you actually trying to view? b/c i don't believe you when you say you want to say you want to view the details of one client, but to see the details of all the clients: which do you want??? one client or all?? ...are you selecting one client and wanting to view their address? or their purchase details? or what? you've lost me on the "one but all but only one" bit....

Actually I want both: to view per client and the whole list.
The per client selection is done by the dropdownbox, which was already working. I only needed to add a feature to the program where all records can be displayed.
But ofcourse, where should the 'all records' selection be displayed?
For every output there is a textbox. One of these textboxes is larger, so it can hold the 'all records' selection.

 

[DCrake]

I would be interesting in viewing an image of your screen if only out of curiosity as the layout seems to be dictated by your seniors.

David

 

[M3lvn]

Are you sure a SubForm in Continuous Form mode will not work for you? I think you will find it much neater and easier to use. Or a ListBox even.

Well, the program is already full of subforms, because I needed forms with tabs and subtabs. And therefor used subforms.

I found info about continuous forms:
http://www.codeproject.com/KB/grid/ContiuousForms.aspx

At the moment I cant yet say that it would be better or not, but I'll think about it.

 

[M3lvn]

I would be interesting in viewing an image of your screen if only out of curiosity as the layout seems to be dictated by your seniors.

David

I can send you screenshots via email.
At this moment I dont feel 100% comfortable exposing my project publicly.
The seniors actually do dictate the layout for a certain amount; the senior is the customer.

 

[wiklendt]

I wanted to filter out only the people living in Canada.
I can probably achieve this with a querry, but it would be better if it can be done within de vba code that DCrake posted.

the VBA code that DCrake posted only concatenates the fields and displays them in a text box.

if you want to filter, you will need to use a query, whether you do that via design view or via SQL in VBA, a query is a query. in the past, i have filtered things by using controls that feed criteria to the query... so, if i start typing "cana".... then it will bring up anyone with "canada" in their address. this requires both queries and VBA.

if you want to display all the records, and then also more details on one particular record, you can make a listbox showing all the records and whatever columns you want to see at once, then use that listbox as a control to find a specific record to display in your bound form...

i have this setup quite frequently in many of my DBs b/c it's very handy. this is my setup in a nutshell:

a listbox which is run by a query who's criteria are fed by a serach box and/or dropdown boxes, that filter the listbox depending on what i type/select.

from that filtered (or unfiltered, if i don't chose something from the dropdown) listbox i can then click on one record (be it a client or whatever) and their details are displayed in the form, whilst the listbox retains the records it was displaying earlier.

does that sound closer to what you need/want? - i can post a screenshot of a setup like this to give you a visual clue about what i'm talking about later today if you're interested: i'm currently not on my own computer, so don't have access to them...

 

[M3lvn]

the VBA code that DCrake posted only concatenates the fields and displays them in a text box.

if you want to filter, you will need to use a query, whether you do that via design view or via SQL in VBA, a query is a query. in the past, i have filtered things by using controls that feed criteria to the query... so, if i start typing "cana".... then it will bring up anyone with "canada" in their address. this requires both queries and VBA.

if you want to display all the records, and then also more details on one particular record, you can make a listbox showing all the records and whatever columns you want to see at once, then use that listbox as a control to find a specific record to display in your bound form...

i have this setup quite frequently in many of my DBs b/c it's very handy. this is my setup in a nutshell:

a listbox which is run by a query who's criteria are fed by a serach box and/or dropdown boxes, that filter the listbox depending on what i type/select.

from that filtered (or unfiltered, if i don't chose something from the dropdown) listbox i can then click on one record (be it a client or whatever) and their details are displayed in the form, whilst the listbox retains the records it was displaying earlier.

does that sound closer to what you need/want? - i can post a screenshot of a setup like this to give you a visual clue about what i'm talking about later today if you're interested: i'm currently not on my own computer, so don't have access to them...

Is the listbox setup like several dropdownboxes, eg. the first i select the country, the second will give the region, third the streetname, etc?
Like a step1-2-3-x selection?
If thats not the case, please post a pic.

The difficulty of this project is that there is a lot of overhead from the information. There are a lot of catergories and subcategories within subcategories.
In fact the customer wants less clicking and to see as much as possible at once.

 

[datAdrenaline]

You are definately headed the wrong direction with what you are doing ... the RuralGuy has the right idea with a subform with a Source Object form in datasheet view mode (or a ListBox). If you customer wishes, you could easily create a report to view "all" the information.

As another alternative, I am assuming that your combo shows all the records, so ... just add some more column to the combo box, then the seniors can view "All the Records" ...

>> The difficulty of this project is that there is a lot of overhead from the information. <<

Then design your tables well, and present your information efficiently with quick methods to navigate to different pieces of information.... which means you'll probably have more forms and table.

>> There are a lot of catergories and subcategories within subcategories. In fact the customer wants less clicking and to see as much as possible at once. <<

Then create a Report Object to do that ... not a form.

....

As a side note. Text boxes are limited to 65000 characters.