Show multiple attributes and records in one textbox

[wiklendt]

first off, sorry for the delay in my reply - there were mardi gras festivities to attend

ok, now, yes, if you want to add a combo box or more to filter your listbox you can. for example, you CAN filter by country or by steet, or by minimum age or by anything...

you first need to decide what it is you want to do. i think i'm starting to understand now. you want to see a list, say, of all the clients you have, then, but several combo boxes you want to gradually limit what you're looking at: by, say, country, street name etc... let me know if i'm close.

the first attachment is a specimen database i built for work. it has a listbox and a search feature. you can see in the snapshot that i'm filtering for all records with a "P" in them - i have set up my query to filter on both the name of the specimen and the study that specimen is involved with. however, you can set up the listbox in any way you want.

the second attachment shows you how the query looks that runs the listbox.

finally, the third attachment is a screenshot of another work database i have, which has a listbox but instead of filtering by a textbox, i have filtered using the combobox you see in red as "select study" - this combobox also tell the two buttons to the right of it which data to display/export... also, all the information you see on the right of the listbox, about the chosen specimen, are in subforms. only the chosen specimen's details are displayed on the right, while you still have a list of all those available on the left.

just like the textbox, you need to refer to the combobox in your query to filter the data on the appropriate field. the screenshot i've posted has only the one combobox, but i've filtered on many combos in other databases.

HTH, wiklendt

edit: and like datAdrenaline implied, you can make these combo's work for you in filtering the data you send to a report i have done that many a time also. you can even make several buttons refer to the same physical report, each with their own SQL filters, so i can for example send to the one report via three different buttons either all my negative results, or positive results, or all the results together.

 

[wiklendt]

ha! here's another screenshot (from yet another work database of my design). this form has a listbox of all the samples with *several* filters, both combo and textbox... selecting one sample in the listbox on the left brings up all associated data/records for it (and only it) in the fields/subforms on the right, while still seeing all the other samples in the listbox.

 

[M3lvn]

Thank you very much for the screenshots wiklendt. They make things much clearer.

I like that listbox (on the left) design, but more importantly, I think the user/customer will like it also, because it gives an overview.
At the moment, the selection is implemented as a dropdownbox which is more compact and the items are not directly shown.

Ive already made a test program (as I type).
The listbox has been built, when I click on an item the information is shown in a textbox:

The problem now is, that it would be better if the info of all the textboxes (name, addres, tel and discription) can be displayed in 1 textbox (in the discription one).
Now Im using Dlookup for every textbox.

 

[wiklendt]

you don't need DLookup for this kind of functionality... you can bind the form to a query with all the fields you want (remembering that some data is better to have as a subform) and then set the listbox to find the appropriate record for you automatically.

Private Sub lstBoxNumber_Click()
    
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Box_Position] = " & Str(Nz(Me![lstBoxNumber], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

the above code does not use dlookups - the text boxes just update automatically. this means that you can add/remove fields in your form from your source and you won't have to individually add code to each of them.

the code goes into the "on click" event of the listbox. (this will trigger even if you use the arrow keys to go up and down the list)

 

[wiklendt]

sorry - the only hitch is that you want multiselect - that will need different code.... my bad. do a search on the forum for "multiselect listbox" and there ought to be heaps fo examples.

i THINK what you may need to display multiple data is a subform with either continuous view or datasheet view. here's an example code for a 'match form' that i've got in one of my databases - it finds which sample comes from which patient IF you only know the sample name... (i have another form for finding patient and display all samples taken form them)

Private Sub cmdMatch_Click()

    ' credit for this code goes to nanishasorn from a post on the
    ' access world forums (link accurate as at 2008-09-08)
    ' http://www.access-programmers.co.uk/forums/showthread.php?t=55452&page=2

    Dim varItem As String
    Dim i As Variant

    ' Build criteria string from selected items in list box.
    varItem = ""

    For Each i In Me![lstIsolates].ItemsSelected
    If varItem <> "" Then
        varItem = varItem & " , "
    End If
        varItem = varItem & Me![lstIsolates].ItemData(i) & ""
    Next i

    ' Filter the form using selected items in the list box.
    txtSQLstring = varItem

    If varItem = "" Then
        Me.lstMatch.RowSource = "SELECT * FROM qryMatchSpecimenToPatient"
        MsgBox "You have not selected any isolates", vbInformation, "ICU cycling study"
    Else
        Me.lstMatch.RowSource = "SELECT * FROM qryMatchSpecimenToPatient WHERE [Box_Position] In(" & varItem & ");"
    End If

End Sub

the code is in the "on click" event of my "match >" button, which ensures that the user first selects all the samples they want before the search is performed.

both sides are listboxes ("the find a sample" and the "match to patient"), and the sample side is multiselect. however, you should be able to make that second listbox a "continuous form" subform, if you need extra space to display data (rather than single-line per record).

here's screen shot in design view (i can't show you form view b/c, obviously, it displays confidential patient data).

 

[M3lvn]

I got feedback from the customer and they say its ok:
the listbox is very handy and the info in the separate textfields can be just like that.
So I guess I dont need to put it in 1 textbox.

What would be very nice now, is that I'd like to replace subform tabs with a dropdownbox (Ill post a pic soon). The subform with loads of tabs will be replaced with a dropdownbox. The selection wont be per tab anymore, but via the dropdownbox.
The listbox will then be populated according to the selection from the dropdownbox. Can I use the above code for this?


On a side note, Id like to build a search function that goes through the entire database, like the ones on websites. But Ill make another thread for this.

 

[wiklendt]

What would be very nice now, is that I'd like to replace subform tabs with a dropdownbox (Ill post a pic soon). The subform with loads of tabs will be replaced with a dropdownbox. The selection wont be per tab anymore, but via the dropdownbox.
The listbox will then be populated according to the selection from the dropdownbox. Can I use the above code for this?

you'd need to give us more info on what you have/want. a screenshot or two might also be useful (of both what you ahve and what you envisage).

i don't recall you ever mentioning multiple tabs in a subform before (except when you said "Well, the program is already full of subforms, because I needed forms with tabs and subtabs. And therefor used subforms", which was really not saying much at all about your structure...), or shown any screenshots of your project showing this, so it's very hard to say that what you want will work the way you think it will.

On a side note, Id like to build a search function that goes through the entire database, like the ones on websites. But Ill make another thread for this.

depending on the size and structure of your database, this could either be easy or the bane of your existence... but yes, post a new thread on this matter - but before you do, please do a thorough search of these forums and the internet as there's a plethora of solutions and discussion on this topic.

i remember you once said that the senior is in charge because he is your client. that may be true, but he can only tell you what he wants - it's up to you as the designer of the database to tell him how it can be done or what might work better for him. looks like he's open to suggestion, and congratulations are in order for the good reception of the listbox

yo uhave also said "At this moment I dont feel 100% comfortable exposing my project publicly.", which i understand, but if we are to truly be able to help you, you've got to give us something to go on. any scrutiny would be in good faith for the progress and SUCCess of your project. we're all here to help, not hinder

 

[M3lvn]

Here is a screenshot as example:

There are 27 chapters in this example. The importance is that the user uses the chaptername to find what he wants. Every chapter has a bunch of articles.
The problem is that at the moment there is no overview: he must click on every tabbutton to see what the chaptername is. Thats a lot of clicking for him; he wants a helicopterview of all the chapters so he can estimate which articles are worth reading. Some chapters have like 100-300 articles or even more.

I'd like to put all the chapternames in a dropdownbox to create an overviewing list that will make the search more efficient (see pic below).

After the chapter selection is made the listbox gets populated with items/articles of that chapter.

Hope this helps a bit to explain what Im trying to build for the customer.

 

[wiklendt]

ok, so i presume you know how to make the dropdown you've got in your image. now what you want is to place a listbox control on the form also (which it looks like you have, but as yet unpopulated).

make the rowsource of that listbox a query, where you have the fields for your ChapterID, and for the details of the articles that you want to show. in the criteria for the ChapterID field, you will need to tell it to get its data from the dropdown combobox on your form. it looks like your combobox is in a subform, so you'll need to be careful to add the correct syntax for it. it would be something like this:

forms!frmMain.frmSub.form.cboChapters

when you hit enter after typing this is, access usually like to put all the field and control names in brackets - don't panic this is normal. what you'll end up with is something like this:

[forms]![frmMain].[frmSub].[form].[cboChapters]

let us know if this is helpful, or if you get stuck...

edit: make sure the 'bound column' for your combobox is the ChapterID field... that is, whatever you have named your unique identifier for each chapter... (edit3: so, if your unique identifier is the first field in your query source for your combo, type in "1" in the 'bound column' area in the properties pane.)

edit2: oh, one last thing. you have to tell access to rerun the query (of the listbox) with the new criteria (the chapter) and display. you do this by adding the following code to the "after update" event of the combobox:

Me.lstArticles.Requery

where "lstArticles" is the name of your control (i.e., listbox)

-- this is known as a "cascading combo" - even though you are dealing with a listbox, the principle and code is exactly the same. if you need more info on this or in the future become more in need of this kind of setup, there is a MASSIVE amount of information and posts on cascading combos on this forum and on the internet.

 

[M3lvn]

Ok! I got it working! Thanks wiklendt!

I have indeed noticed that there was some kind of refreshing needed for the controls. When I changed from design view to normal view things suddenly worked.

I did a 'DoCmd.Requery' on the OnChange event of the dropdownbox and it seams to work fine.
I hope there is not a big difference with 'Me.lstArticles.Requery'?


But... there is still one problem. It doesnt work in Access2003!
I think I got it narrowed down to the criteria part of the listbox:

[forms]![frmMain].[frmSub].[form].[cboChapters]

I think I need the older Access VBA version or so.
If I put the criteria manually, e.g "Chapter I" then it works, otherwise it doesnt: the listbox stays blank with no error. The rest of the septup is probably good because it works under Access2007.

 

[wiklendt]

have you tried Me.lstArticles.Requery in the AC2003 version?

if this doesn't help, maybe post a fresh thread to ask people about a query that works in AC2007 but not AC2003.... as i only have AC2007, i can't troubleshoot on my end (save to do google/forum searches, which you can do yourself)

 

[M3lvn]

have you tried Me.lstArticles.Requery in the AC2003 version?

That did it!
I put 'lstArt.Requery' in the OnChange event of the dropdownbox and voila!

It works for 2003 and 2007 as well.
wiklendt, you're awesome

 

[wiklendt]

aw shucks *blush*

 

[M3lvn]

It seems I need help once again.

Im trying to figure out the syntax of the criteria for a subform that has a subform.

The code below is for the subform directly connected to the mainform.

[forms]![frmMain].[frmSub].[form].[cboChapters]

I have 3 forms setup as:
mainform(A) has a subform(B) and subform(B) has subform(C).

I tried
[forms]![frmMain].[frmSub].[frmSub2].[form].[cboChapters]
but it didnt work.

 

[RuralGuy]

Forms are displayed on other forms by means of a SubFormControl. This SubFormControl has its own name which defaults to the form it is displaying but can be changed. It is the SubFormControl's name that is referenced in the syntax for referencing controls. Therefore to get to your nested SubForm the syntax would be:
[FORMS]![MainForm].[SubFormControl1].[FORM]![SubFormControl2].[FORM].[cboChapters]

 

[M3lvn]

Thank you very much for the explanation and the answer RG.

It would have taken me forever to figure out the correct syntax.

Especially where the first part is [formS]! and at the second one is [form]! (without the 's').

Thanks again!

 

[RuralGuy]

The first element of that syntax is the name of the collection where all of the loaded forms are enumerated; the FORMS collection. There is also an ALLFORMS collections where you can find enumerated *all* of the forms in the project whether they are loaded or not.

 

[M3lvn]

The first element of that syntax is the name of the collection where all of the loaded forms are enumerated; the FORMS collection. There is also an ALLFORMS collections where you can find enumerated *all* of the forms in the project whether they are loaded or not.

Alright, good to know.


Ps.
You must spread some Reputation around before giving it to RuralGuy again.

 

[wiklendt]

Ps.
You must spread some Reputation around before giving it to RuralGuy again.

yeah, i get that all the time too... usually for RuralGuy/gemma-the-husky/DCrake/boblarson/.... a few others i'm sure...

it's a feature i run into more often than not. which is sad b/c someone might genuinely be a hell of a lot more helpful than anyone else... anyway, i understand it's to prevent abuse of the system and 'buddying-up'...

 

[RuralGuy]

Ps.
You must spread some Reputation around before giving it to RuralGuy again.

Thanks for trying anyway. It's the thought that counts.