cascading combo nightmare...

[wiklendt]

wow! all that from the top of your head!??? i'm impressed. i'll play with it and let you know.

haven't had time to re-organise the C: drive image links, sorry. might try banana's solution first before i do the image thing. that's just a simple matter of removing the few references from the appropriate tables (b/c i have all the appropriate null check in the codes.... ...i think! LOL)

 

[wiklendt]

ok, after looking at banana's code, i decided the easiest thing to do would be to re-post my database minus all the image references for george (or whomever else wants to look at it). LOL

hopefully my coding is robust enough to handle this sudden loss of image references! i did a quick test by placing the db in a different folder and it all seems to be coping fine. the images were referenced absolutely - i haven't yet been able to figure out how to make them relative with my setup. i was going to zip up the images with the db (main db sits in its own folder, then ANY other related files are a subfolder in this one), but the images folder is about 30 Mb zipped, so it's best i just got rid of references to images all together. otherwise, the kit-n-kaboodle works on any pc so long as the "program folder" is directly on the C drive. i'm very happy vista still allows user-created folders to live in the root directory, otherwise i'd be stuffed! LOL

george, thanks for asking for clarification, i often forget that no one else in the 'verse knows this db as intimately as i do! there are a couple of avenues you can take to get to my problem listboxes:

when the db opens, and you click ok on splash, my switch form will open. here, you can select a horse (phoenix is the one i've been using to test things), click "selected" in the data entry section (wait a few seconds while access thinks about it) and then go to the last tab, which is "Massage". in here, you have a subform, frmMassageData, with its own tabs. the ones with the problematic listboxes are on tabs "Observations", "Recommendations" and "Glossary".

of course, the horse form opens as a dialog (via VBA attached to the button, not the forms themselves if you open them separately), so it may be easier for you to close the forms that open at startup, and just go into design view of frmHorse or frmMassageData... if you open just frmMassageData, i think the first record access opens is for a horse called "medea"... i can't remember for sure, but anyway, feel free to muck around with anything in there, i've got backups galore

the massage subform also conveniently has a "preview" button that opens a report and fills the fields based on the current horse/massage selection. be aware that i've not yet gotten to polish-up stage, so i don't think i've properly allowed for accepting new changes to be reflected in the report this way.

also the order that muscle groups are listed are not the same order they appear in the report... i only just noticed that myself, but will fix later.

sorry about the clunkyness of the db, like i said, i'm yet to spit-n-polish it (been doing a little bit of that while i was stuck with the "services" logistics mentioned earlier in this thread - like streamlining the image addition/replacing/removing in the horse form - feel free to test this out yourselves i'm quite proud of it, though i did have help with the Nz stuff, i've yet to properly reference that help in the code)...

anyway, i think it's time for a snack and maybe early lunch then after that brain food i'll tackle banana's suggestion... i think this solution will become useful for other areas of the database - e.g., my muscle lists in the services tab of the massage subform - where the user can click a button to insert pre-filled text to the memo field of the "services" text field. someone suggested WAY back that i use recordsets for this part (i was having issues with access truncating the memo text to 255 due to placing the data in a listbox), but i wasn't sure how to go about it - this may help.

thanks guys, you're great

edit: oh, one last thing, i ahve broken my own naming convention for my forms - i give my forms easy names, such as frmMainForm. usually, if a form a subform of a main form, i usually give it this name, for example: frmMainForm_subForm. however, i broke this rule naming my massage forms. there is a main form, frmMassageData, with all its subs, e.g., frmMassageData_Services. however, frmMassageData is istelf a sub within frmHorse (on the "Massage" tab) - i broke my convention for the massage forms because i felt the names would be WAY too long (e.g., frmHorse_MassageData_Services), i wanted to keep it a little more simple.

hopefully it won't be such a nightmare like before, george! certainly not as much now that i've normalised a hell of a lot more... and you may notice i renamed all my tables and forms so they arrange nicer in the assets lists

 

[georgedwilkinson]

But, you didn't say what was wrong. That's really all I need to know. I don't need apologies or an explanation of what the screens do or how to fill out the data: just the facts of what is wrong. It should be one or two sentences, easy to read and interpret, no superflous information. What is wrong and where do you see the wrongness (symptoms, form and report names only please).

 

[wiklendt]

thanks for your patience, george.

PROBLEM - seemingly correct data but attached to the wrong massage
------------------------
when an item is transfered from a list of 'available' items, to a list of 'selected' items using the listboxes on the form frmMassageData (in the three last tabs), the wrong item/description appears in both the table (which accepts the transfer data) tblMassageData_AssObservations and report rptHorseMassage (i.e., the subform rptMassageData_Observations).

Also, the fourth column of the tblMassageData_AssObservations (field: ObservationText) is supposed to be editable, but sometimes access 'remembers' a previously edited text even if the record was deleted via the button cmdRemoveObservation on frmMassageData. i think this is related to my first paragraph in this post.

EXAMPLE
------------------------
i am entering two massages:

massage A) i select ALL the 'available' observations listed, and transfer them to my 'selected' list, and edit the text (in frmMassageData_Observations) to be "1", "2", "3" ... for the items

massage B) i also select ALL the 'available' observations listed, and edit the text as "one", "two", "three".

-> view table or report, neither report will have all the items, and probably worse, one report will show "1", "two" (and nothing for 3) and the other will show "one", "2", "three".... the table reflects what i see in the report.

-> sometimes when entering fresh data for massage B, if i select the second item and transfer it, even though it's (supposed to be) a new record it sometimes shows text in the text edit area (e.g., "two") even though i hadn't put any text there yet for that massage observation. if i change that text to "2", it changes it for massage A, not massage B even though i changed it when the current massage is massage B.
------------------------

i have since checked my other listboxes (for "recommendations" and "glossary") and they are wrong the same way too. like i said, seemingly correct data but attached to the wrong massage.

 

[wiklendt]

the far most simplest solution is to use recordsets in a subform setting.

With listbox where I list the choices, I use rowsource to select the relevant selections. For both listboxes, they are unbound.

just so that i know i understand, before i become too eager iwth creating assets left, right and centre... for this to work i am to have:

-query1 with my list of items for selection
-query2 with list of selected items (this has the parameter?)
-subform with both listboxes, one whose rowsource is query1, the other query2.
-NOT link master/child fields, but allow the code to generate correct lists (triggered by user pressing comand buttons with the provided code, etc).

Whenever I need to add or delete the record, I deal with the recordset rather than rowsoource, then set the listbox to that recordset. It's easier to add/delete a record from the recordset than it is via bound listbox.

the code certainly look a lot simpler, once i figure out the assets involved it really does look like the way to go.

one question though: in my current setup, i have a M-M table that has records added or deleted, your solution uses queries, but i don't see any 'make table' commands... where is the data stored and retrieved again when i want to revisit this parent record? or is this the whole idea of recordsets? and if so, how do i add this recordset data to a report?

i'm happy to tackle the report stuff once i actually get the forms working, but i just want to make sure this is possible, as the main purpose of my database is to output pretty reports (but also to keep the data for reference).

thanks, banana - can't wait to see this working on my db!

 

[Banana]

just so that i know i understand, before i become too eager iwth creating assets left, right and centre... for this to work i am to have:

-query1 with my list of items for selection
-query2 with list of selected items (this has the parameter?)
-subform with both listboxes, one whose rowsource is query1, the other query2.
-NOT link master/child fields, but allow the code to generate correct lists (triggered by user pressing comand buttons with the provided code, etc).

Sounds about right. I can't remember if the subform did had links and the code referenced the code, so you will need to experiment whether it can work with link or not.

The query2 should have a parameter that refers to the parent form's primary key... Something like this:

PARAMETERS lngPrimaryKey LONG
SELECT ForeignKeyToParentForm, ForeignKeyToSelection FROM JunctionTableBetweenManyManyRelationship WHERE ForeignKeyToParentForm = [lngPrimaryKey];

one question though: in my current setup, i have a M-M table that has records added or deleted, your solution uses queries, but i don't see any 'make table' commands... where is the data stored and retrieved again when i want to revisit this parent record? or is this the whole idea of recordsets? and if so, how do i add this recordset data to a report?

There is no need to make table or anything because the information you need to return all selected choices for a given should come from the junction table that joins together the M-M relationship.

In other words, if your M-M relationship is something like this:

tblMuscles -> jctMusclesInGroups <- tblGroups

And your form deals with muscles first, then the subform would have those queries:

For the selection listbox: Query tblGroups... Assuming that duplicates is unwanted and you want to eliminate choices when it has been selected:

SELECT Group FROM tblGroups WHERE NOT IN(SELECT Group FROM jctMuslcesInGroups WHERE MuscleID = [lngMuscleID]);

(I can't remember if I used the IN or EXISTS; you need to experiment. Furthermore, you would need to add some code to update the listbox everytime a choice has been made; in my case I allowed for duplicates so that wasn't necessary, but my case isn't your case, is it? )

Then for the listbox showing the selected choices, use the similar query that I wrote in beginning of the post.

thanks, banana - can't wait to see this working on my db!

Quite welcome. Best of luck with it!

 

[wiklendt]

thanks for clarifying, Banana. it sounds very much like what i have already for my listboxes, including the "NOT IN" parts, except that you're using recordsets and subforms to actually add and delete the records, while i was using... ? not sure what i was using, plain SQL? i can see the benefit of the subforms, they can be plugged into any form whatsoever without much fuss. the supporting code is obviously different to reflect either using SQL or recordsets.

i'll let you know how i go.

 

[Banana]

Indeed. And so you know- you can make sure that you only need to refer to top lines of the module when you plug it somewhere and it'll just work™.

something like this:

Const PrimaryKey As String = "Name of the primary key we want to use for this particular application"

or

Property Get qdf() As QueryDef

qdf=CurrentDb.QueryDefs("Name Of Query You want to use for this")

End Property

This way when you copy the subform to other forms, you just need to change three things (the primary key to search upon, the selection list, and the query referencing the junction table). The rest of code will not need to be altered because they still are the same, just operating on different key, junction table and lookup table.

HTH.

 

[wiklendt]

clever! thanks for the tip...

 

[Banana]

Glad to help out!

 

[wiklendt]

just as a cross reference - this listbox issue started in this thread and is continued from current thread at post #15