cascading combo nightmare... (1 Viewer)

wiklendt · May 18, 2008

hi,

i thought i was nearly finished this ddb, but then i was asked by my client to chenge a few things... so i'm back in the depths of the form design...

let's see if i can untangle this mess. i'll mention only what's applicable to my problem(s):

CASCADING COMBO/LIST

SETUP

i have a form with a combobox (cmbMuscleRegion), a listbox (lstMuscleByGroup) and a few textboxes (txtFunction, txtSymptoms, txtTreatment).

i also have a few queries, which i have been using as sources for the listbox based on values in the combobox (using SELECT CASE to individually hard code each option to source a different query for the listbox in each case). the combo lists groups, which fires each case, in turn displaying a different query (each query specifically filtering the data using the same groups as in the combo but not using the combo directly to filter, i did it this way b/c when i started i was just learning and i didn't know a better way to do it).

e.g., what was there before:

Code:

    Case "Head"
        lstMuscleByGroup.RowSource = "qryMusclesHead"

WHAT CHANGED

however, there has been an addition of 29 extra cases in the combo, and i don't want to have to create a query for each. i wanted to use ONE query that displays ALL the data, and then to use an SQL statement in the after-update event to filter the records that match the combo value.

LITTLE MORE ABOUT MY ASSETS

now a little about my query... i have three tables. tblMuscles tblGroups and tblMusclesInGroups. the third table associates each muscle with one or more groups. my query (qryMuscleDISTINCTgroups) returns the Muscle and Group names from the tblMusclesInGroups, as well a couple of useful fields from the tblMuscles.

THE PROBLEMATIC CODE

now, in the form_load event, i have stated to use the unfiltered query (qryMuscleDISTINCTgroups).

Code:

lstMuscleByGroup.RowSource = "qryMuscleDISTINCTgroup"

This works fine, displaying the columns i want, except that the query returns each combination of muscle and group... so there may be double-ups of a muscle name. however, when i try to make any attempt to filter the data, my queries always without fail come back null... this is my latest attempt. specifically, this code below does not fire an error dialog msg by access, but also does not select ANY data! however, other version of this have triggered a parameter dialog box asking for either MuscleName or some such.

Code:

       lstMuscleByGroup.RowSource = "SELECT " & _
                                        "qryMuscleDISTINCTgroup.MuscleName, " & _
                                        "qryMuscleDISTINCTgroup.MuscleDepth, " & _
                                        "qryMuscleDISTINCTgroup.GroupID, " & _
                                        "qryMuscleDISTINCTgroup.MuscleFunction, " & _
                                        "qryMuscleDISTINCTgroup.MuscleInnervation, " & _
                                        "qryMuscleDISTINCTgroup.MuscleNotes " & _
                                        "qryMuscleDISTINCTgroup.MuscleID " & _
                                    "FROM qryMuscleDISTINCTgroup" & _
                                    "GROUP BY qryMuscleDISTINCTgroup.MuscleName;"

i'm at my wits end with this one, i've tried so many different things, followed so many different tutorials on cascading combos, and have read extensively the posts on this and other forums.... nothing i code seems to work (just when i thought i could upgrade my status from beginner to amateur!)

UPDATING TEXT BOXES

there are the three text boxes i mentioned earlier: txtFunction, txtSymptoms and txtTreatments.

i have these fields in my unfiltered query (qryMuscleDISTINCTgroup) so that (when it works) when the user selects a group from the combo, the listbox populates with the appropriate filtered muscles. Now, to choose a muscle, the user clicks once on the listed name in the listbox, and the correct associated function, symptom and treatment for that selected record ought to pop into the text boxes on the form.

so far i have been able to get "#Name?" appear in the appropriate text box, but not the actual text! i tried changing the bound columns, checked column number (zero based) etc etc...

i would have thought this would be simple, and have been trying various code this is what i thought would be most logical:

Code:

Private Sub lstMuscleByGroup_Click()
On Error GoTo Err_lstMuscleByGroup_Click

    Me.txtFunction.ControlSource = Me.lstMuscleByGroup.ItemsSelected.Column(3)
    Me.txtSymptoms.ControlSource = Me.lstMuscleByGroup.ItemsSelected.Column(4)
    Me.txtTreatment.ControlSource = Me.lstMuscleByGroup.ItemsSelected.Column(5)

Exit_lstMuscleByGroup_Click:
    Exit Sub

Err_lstMuscleByGroup_Click:
    Msg = "Error # " & Str(Err.Number) & Chr(13) & Err.Description
    MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext

    Resume Exit_lstMuscleByGroup_Click

End Sub

instead of ItemsSelected i've also tried almost anything else that looked data related in the dropdown VB gives you after you put in the period, however, i keep getting "compile error: method or data member not found" or says invalid use of property or some such. often highlighting either the bit after "lstMuscleByGroup." or highlighting the ".Column" part.

e.g, with .value, i get "error #424: object required"

if anyone can help, i'd be MUCH appreciated. i've been working on the code all weekend with no luck (tho i got excited even to see "#Name?" in the textboxes!!!)

cheers,
agnieszka.

georgedwilkinson · May 18, 2008

Hi Agnieszka,

From my limited understanding of you problem, "group by" is not a very effective way of "filtering". It is more designed to aggregate data, which doesn't seem to be what you need to do. Why aren't you using a where clause instead?

Do you mind posting the code for qryMuscleDISTINCTgroup? I feel like I'm blind when troubleshooting this issue. Or, would you mind posting your db or a jpg of your relationships?

I fear (can't be certain) that you have a normalization problem...I vaguely remember another thread where we talked to you about M:M relationships and wonder how that went. I thought you had decided to skip having the M:M and only allow 1:M muscle to group, or vice versa.

I cannot comment on your list box problem since I never ever use them (not since 1994). It doesn't look right to me (again, look like a 1:M problem). However, if you post your DB, I'd be happy to take a look.

Have you single stepped through the various problem code areas and checked your variables at each step? I find that by setting my formulas, sql, strings, etc. equal to a local variable and by examining that local variable during single step operations, I can normally find a problem rather quickly.

Also, if you see something funny when doing this and want to get our opinion on it, you might try debug.print on the variable in question. For instance, I'd like to see the output of:

Debug.Print Me.lstMuscleByGroup.ItemsSelected.Column(3) '4 and 5.

If the Debug.Print errors out (which it might), that gives you a big clue.

wiklendt · May 18, 2008

hi george,

oh i DID try the WHERE clause, but no cigar. the only time i can get any data showing is to have the unfiltered query as the rowsource, as soon as i try to use SELECT or SELECT DISTINCT (which i would have thought was the better option) or anything else, then the query chokes and displays 0 records. i can't even get the query itself to work, let alone in the form!

i can't post the ddb for confidentiality reasons (i think it would also be slightly big!), but i can attach screen shots of anything you may need.

please find a gif each for the relationships of the muscles (there's superfluous fields in some of those tables which i'm too afraid to get rid of just yet incase they stuff up something along the way - esp since i'm in the middle of re-structuring the query combo thing)... and a screen shot of the form.

here's the ENTIRE code for my massage form, b/c i'm not sure what you need (there is no code for the qry?? i will post screen shot of the design view)

please note i'm in the middle of developing.trouble shooting, so i've commented out a lot in attempt to try other things....

Code:

Option Compare Database
' This database was compiled, written and designed by Agnieszka Wiklendt
' Where code has been sourced, appropriate credit has been given as comments.
' Copyright (C) 2008 Agnieszka Wiklendt
' This program is freely distributable intact

Private Sub cmbMuscleRegion_AfterUpdate()
On Error Resume Next

'Select Case cmbMuscleRegion
'
'    Case "ALL"
'        lstMuscleByGroup.RowSource = "qryMusclesALL"
'        txtMassageService.ControlSource = "MassageServiceALL"
'        txtAreaGuide.Value = "This text goes with whole horse image"
'
'    Case "Head"
'        lstMuscleByGroup.RowSource = "qryMusclesHead"
'        txtMassageService.ControlSource = "MassageServiceHead"
'        txtAreaGuide.Value = "This text goes with head image"
'
'    Case "Neck"
'        lstMuscleByGroup.RowSource = "qryMusclesNeck"
'        txtMassageService.ControlSource = "MassageServiceNeck"
'        txtAreaGuide.Value = "This text goes with neck image"
'
'    Case "Shoulder"
'        lstMuscleByGroup.RowSource = "qryMusclesShoulder"
'        txtMassageService.ControlSource = "MassageServiceShoulder"
'        txtAreaGuide.Value = "This text goes with shoulder image"
'
'    Case "Chest"
'        lstMuscleByGroup.RowSource = "qryMusclesChest"
'        txtMassageService.ControlSource = "MassageServiceChest"
'        txtAreaGuide.Value = "This text goes with chest image"
'
'    Case "Forelimbs"
'        lstMuscleByGroup.RowSource = "qryMusclesForelimbs"
'        txtMassageService.ControlSource = "MassageServiceForelimbs"
'        txtAreaGuide.Value = "This text goes with forelimb image"
'
'    Case "Barrel"
'        lstMuscleByGroup.RowSource = "qryMusclesBarrel"
'        txtMassageService.ControlSource = "MassageServiceBarrel"
'        txtAreaGuide.Value = "This text goes with barrel image"
'
'    Case "Back"
'        lstMuscleByGroup.RowSource = "qryMusclesBack"
'        txtMassageService.ControlSource = "MassageServiceBack"
'        txtAreaGuide.Value = "This text goes with back image"
'
'    Case "Hindquarters"
'        lstMuscleByGroup.RowSource = "qryMusclesHindquarters"
'        txtMassageService.ControlSource = "MassageServiceHindquarters"
'        txtAreaGuide.Value = "This text goes with hindquarters image"
'
'    Case "Hindlimbs"
'        lstMuscleByGroup.RowSource = "qryMusclesHindlimbs"
'        txtMassageService.ControlSource = "MassageServiceHindlimbs"
'        txtAreaGuide.Value = "This text goes with hindleg image"
'
'    Case "Unknown"
'        lstMuscleByGroup.RowSource = "qryMusclesUnk"
'        txtMassageService.ControlSource = "MassageServiceALL"
'        txtAreaGuide.Value = "This is an exact copy of the ALL text"
'
'    Case Else
        
        lstMuscleByGroup.RowSource = "SELECT DISTINCT tblMuscleInGroup.MuscleName" & _
                                     "FROM tblMuscleInGroup WHERE tblMuscleInGroup.GroupID = " & _
                                     cmbMuscleRegion.Column(2) & "';"

'End Select

        lstMuscleByGroup.Requery

End Sub

Private Sub cmbMuscleRegion_BeforeUpdate(Cancel As Integer)
'this is a little messy, but it seems to work for now on 2007.
'i ought not to need this sub due to the one below, but if i
'remove it, it don't get the desired effect.

       lstMuscleByGroup.RowSource = ""
End Sub

Private Sub cmdPreviewCurrentMassageReport_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    
    'Turns background colour of box Deep Aqua
    Me.cmdPreviewCurrentMassageReport.backcolor = rgb(173, 209, 255)

End Sub

Private Sub cmdSendFunction2Text_Click()
   
Dim strMuscleFunction As String

Me!txtFunction.SetFocus
strMuscleFunction = Me.txtFunction.Text

Me.txtMassageService = Me.txtMassageService & strMuscleFunction & vbCrLf
    
End Sub


Private Sub cmdSendSymptoms2Text_Click()

Dim strMuscleSymptoms As String

Me!txtSymptoms.SetFocus
strMuscleSymptoms = Me.txtSymptoms.Text

Me.txtMassageService = Me.txtMassageService & strMuscleSymptoms & vbCrLf

End Sub

Private Sub cmdSendTreatment2Text_Click()

Dim strMuscleTreatment As String

Me!txtTreatment.SetFocus
strMuscleTreatment = Me.txtTreatment.Text

Me.txtMassageService = Me.txtMassageService & strMuscleTreatment & vbCrLf

End Sub

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    
    'Returns to light aqua
    Me.cmdPreviewCurrentMassageReport.backcolor = rgb(237, 247, 249)

End Sub

Private Sub Form_Load()

'       lstMuscleByGroup.RowSource = "SELECT " & _
'                                        "qryMuscleDISTINCTgroup.MuscleName, " & _
'                                        "qryMuscleDISTINCTgroup.MuscleDepth, " & _
'                                        "qryMuscleDISTINCTgroup.GroupID, " & _
'                                        "qryMuscleDISTINCTgroup.MuscleFunction, " & _
'                                        "qryMuscleDISTINCTgroup.MuscleInnervation, " & _
'                                        "qryMuscleDISTINCTgroup.MuscleNotes " & _
'                                        "qryMuscleDISTINCTgroup.MuscleID " & _
'                                    "FROM qryMuscleDISTINCTgroup" & _
'                                    "GROUP BY qryMuscleDISTINCTgroup.MuscleName;"

   lstMuscleByGroup.RowSource = "qryMuscleDISTINCTgroup"
'   the above line works. trying now to generate a SELECT DISTINCT filter

'       lstMuscleByGroup.StatusBarText = "All muscles"
'       txtMassageService.ControlSource = "MassageServiceALL"
'       txtMassageService.StatusBarText = ""
'       txtAreaGuide.Value = "This text goes with whole horse image"
End Sub

Private Sub ListRecommend_DblClick(Cancel As Integer)

Dim varItem As Variant
Dim strMassageRecommend As String

For Each varItem In Me.ListRecommend.ItemsSelected
    strMassageRecommend = strMassageRecommend & Me.ListRecommend.ItemData(varItem) & vbCrLf

Next varItem
    Me.txtMassageRecommend = Me.txtMassageRecommend & strMassageRecommend & vbCrLf
    
End Sub

Private Sub lstMassageGlossary_DblClick(Cancel As Integer)

Dim varItem As Variant
Dim strMassageGlossary As String

For Each varItem In Me.lstMassageGlossary.ItemsSelected
    strMassageGlossary = strMassageGlossary & Me.lstMassageGlossary.ItemData(varItem) & vbCrLf

Next varItem
    Me.txtMassageGlossary = Me.txtMassageGlossary & strMassageGlossary & vbCrLf
    
End Sub
Private Sub cmdNewMassageSession_Click()
On Error GoTo Err_cmdNewMassageSession_Click

    DoCmd.GoToRecord , , acNewRec

Exit_cmdNewMassageSession_Click:
    Exit Sub

Err_cmdNewMassageSession_Click:
    MsgBox Err.Description
    Resume Exit_cmdNewMassageSession_Click
    
End Sub
Private Sub cmbSelectSession_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

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

Private Sub lstMuscleByGroup_Click()
On Error GoTo Err_lstMuscleByGroup_Click

    Me.txtFunction.ControlSource = "Me.lstMuscleByGroup.Column(3)"
    Me.txtSymptoms.ControlSource = "Me.lstMuscleByGroup.Column(4)"
    Me.txtTreatment.ControlSource = "Me.lstMuscleByGroup.Column(5)"

Exit_lstMuscleByGroup_Click:
    Exit Sub

Err_lstMuscleByGroup_Click:
    Msg = "Error # " & Str(Err.Number) & Chr(13) & Err.Description
    MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext

    Resume Exit_lstMuscleByGroup_Click

End Sub

Private Sub lstMuscleByGroup_DblClick(Cancel As Integer)

Dim varItem As Variant
Dim strMuscleName As String

For Each varItem In Me.lstMuscleByGroup.ItemsSelected
    strMuscleName = strMuscleName & Me.lstMuscleByGroup.ItemData(varItem) & vbCrLf

Next varItem
    Me.txtMassageService = Me.txtMassageService & strMuscleName & vbCrLf
    
End Sub

Private Sub lstTechniquePhrase_DblClick(Cancel As Integer)

Dim varItem As Variant
Dim strTechniquePhrase As String

For Each varItem In Me.lstTechniquePhrase.ItemsSelected
    strTechniquePhrase = strTechniquePhrase & Me.lstTechniquePhrase.ItemData(varItem) & vbCrLf

Next varItem
    Me.txtMassageService = Me.txtMassageService & strTechniquePhrase & vbCrLf
    
End Sub

Private Sub MassageDate_Exit(Cancel As Integer)

    cmbSelectSession.Requery
    
End Sub
Private Sub cmdPreviewCurrentMassageReport_Click()
On Error GoTo Err_cmdPreviewCurrentMassageReport_Click

    Dim stDocName As String
    Dim strCriteria As String

    stDocName = "rptHorseMassage"
    strCriteria = "[MassageID]= " & Me![MassageID]
    
    DoCmd.OpenReport stDocName, acPreview, , strCriteria

Exit_cmdPreviewCurrentMassageReport_Click:
    Exit Sub

Err_cmdPreviewCurrentMassageReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreviewCurrentMassageReport_Click
    
End Sub

i really appreciate your help. i'm afraid i have to log off for a little while now, but i'll see about stripping some of the (confidential) data so that i can post the ddb proper on here (possibly not until tomorrow, unfortunately)....

thanks again for taking the time to look at this, i know you do it on a voluntary basis.

ooopps... i just had a look at the screen shots, and noticed i forgot to take out the "group by" in the query design after my failed attempts at it... still, my SELECT statement wasn't working before i put it in there either... :-/

wiklendt · May 18, 2008

good morning,

here is a stripped down version of the database (i've had to get rid of data (for confidentiality), and also some extra forms and reports to get down to the attachment size limit)....

hope this makes things a little easier to trouble shoot...

thanks again george (and anyone else who may like to put in their 2c worth)

wiklendt · May 21, 2008

anyone???

georgedwilkinson · May 21, 2008

Where are you on this? Last time I looked at your DB, you had used non-normalized structures to solve your problem and had commented out your code to populate the lb.

georgedwilkinson · May 21, 2008

Why your text boxes show "#Error", part 1:

Code:

Private Sub lstMuscleByGroup_Click()
On Error GoTo Err_lstMuscleByGroup_Click

    Me.txtFunction.ControlSource = Me.lstMuscleByGroup.ItemsSelected.Column(3)
    Me.txtSymptoms.ControlSource = Me.lstMuscleByGroup.ItemsSelected.Column(4)
    Me.txtTreatment.ControlSource = Me.lstMuscleByGroup.ItemsSelected.Column(5)

Exit_lstMuscleByGroup_Click:
    Exit Sub

Err_lstMuscleByGroup_Click:
    Msg = "Error # " & Str(Err.Number) & Chr(13) & Err.Description
    MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext

    Resume Exit_lstMuscleByGroup_Click

End Sub

See my earlier post on 5/18.

As I suggested you do, I put the debug.print statements and, as I suggested, got an error. So, why is there an error?

In the Column Count property of lstMuscleByGroup you have a 2. Since the above code references column numbers 3, 4, and 5, this will cause an error. Since you didn't reference column numbers 3, 5, and 5 in VB code, just in the objects, you did not see a VBA error.

So the "temporary" fix was to make the number of columns in the lb 5. Please note that you have 7 columns in the query that populates the list box. You really need to sort this out.

Text box errors, Part 2:
So, fixing that didn't actually fix the "#Name?" issue. This is really caused because you put a string, "Me.lstMuscleByGroup.ItemsSelected.Column(X)" into its ControlSource. Access doesn't know what that means, as a control source. So you need to find a way to put the value into the text boxes that doesn't include putting an invalid value in their control source property. You could directly modify the .value or .text properties but you need to give the control focus first. There are probably multiple other ways to do it but there are people on these forums better qualified than me to advise you on that.

The short answer on why the list box shows duplicates:
You told it to. The query used as the source of the data for the list box includes a join on multiple tables. Since these are 1:M relationships, you should expect to get "many" results, which is what you're seeing.

Also, your filter for the list box doesn't work right. I really don't have much time for stepping through your code repeatedly trying to find stuff way down inside. But my guess is that you're filtering your list on a text value and the field that you compare it to is an ID value.

While I was digging through your code the other night, I had a couple of concerns you should address:
1. You have a table with a bunch of check boxes for the muscle groups...this is not normalized and can cause mucho problems.
2. In addition to 1, you seem to have a junction table containing the same information structures of those check boxes. If your intent was to replace the check boxes, good. If you think you're going to keep them coordinated, good luck...it ain't gonna happen.
3. You have lots of queries that are identical except they each have a different "where" parameter. Lose that and just call the query with a where clause.
4. Your form and query design makes it difficult for people like me to help. I would normally expect to spend 10 or fewer minutes looking over people's stuff. I have spent several hours on yours, both today and Saturday night. Keep your design simple. Don't use VB code if you don't have to (I use out of the box Access functionality over 90% of the time and I've been doing this long enough to have written multiple programs in straight VB--Access is there to save you time, not make work). Don't obfuscate your data via queries that don't add value. Remove superflous fields from your tables (all the check boxes). Name your tables real live plural english nouns so people can talk/think about them logically. Name junction tables after the 2 tables they join. Skip the "tbl" prefix...it is nonsense and makes it difficult to discuss/think about the foundation of your database. Use prefixes for your other objects, just not tables.

You have quite a bit of work to do to straighten out the immediate problems. Re-think your query. Re-think how you're going to get data into the text boxes. Get rid of those check boxes so you're not tempted/able to use them. Ask for help on each of those individually and it'll be a lot easier to get quick answers.

I hope this helps some. I don't want to sound like I'm getting on your case because you've done a pretty good job with table design and your forms are lovely (I wish I had your talent for designing forms). You just need to let Access have a more active part in making your project a success.

wiklendt · May 22, 2008

thank you, george

wow, thanks george! that's really valuable info you have given me. i'm REALLY sorry it took you so long to untangle my design :-( i didn't mean to put people out. but THANKYOU for persevering.

LOL, i TOLD you it was a nightmare!!! ;-)

the first time i really picked up Access/VB was about ooohhh.... say, 4 months ago? i undertook a big project with a purpose (as opposed to my previous attempts many years ago to 'just learn' access), so inevitably it became a big mess really quickly. as soon as i realised i made a mistake in my design regarding the queries etc, i tried to get the cascade to work on ONE query, instead of individual queries. hence my thread.

in a twist, i DID manage to work out my coding issues last night independantly. the code i ended up using to make the cascade work:

my coding solution

Code:

lstMuscleByGroup.RowSource = "SELECT * FROM qryMuscleDISTINCTgroup
WHERE GroupID='" & Me.cmbMuscleRegion.Column(0) & "';"

(the line break doesn't actually exist in my code, i just did it here so that the post wouldn't expand over the width of the page)

making my previous ~40 lines of code (before i even was to add the new 29 groups) into just one elegant line, which ancompasses ALL the groups, new and old :-D. it took me an entire week to get this one line, but now that i've got it i think it's the most beautiful line in the world!! ;-)

however, you did say that i ought to avoid VB where i can use Access' inbuilt functionality - do you mean in this instance also? how can i do that for my goal here - do you mean for me to put in a criteria in the query itself, to take its GroupID from the combo, rather than making the rowsource the query via VB and filtering it via VB? i tried doing that in the first instance, but was really unsuccessful. i'm not sure i was getting the reference correct for a subform, but i did follow tutorials on how to do this and still it refused to work. i could try again, now that i have this backup of VB to use if i continue to be unsuccessful, but considering it can take me several days to complete each step of my intention, i'm often not as motivated to find an alternative method if i stumble on one that works for me (properly, like this new code as opposed to my old checkbox/multi-query way).

my other issue you hit the nail on the head, george: column count. once i'd sorted out my cascade, i turned my attention 100% on this issue. here is the working code i am now using (similar to my earlier effort, though only came into effect once i'd sorted out my column count issues, just like you've stated here):

Code:

Me.txtFunction.Value = Me.lstMuscleByGroup.Column(3)
Me.txtSymptoms.Value = Me.lstMuscleByGroup.Column(4)
Me.txtTreatment.Value = Me.lstMuscleByGroup.Column(5)

i only had a column count of 2 for the list that i was trying to get info from the 4th, 5th and 6th columns; the query had 7 - i THINK i can safely remove this last column... i am DILIGENT with backups, so if it causes issues, i'm still safe ;-) . once changed appropriately, the code worked marvelously :-D i knew something was wrong along those lines because it was returning "#name?" in only those fields which actually had that data, it dawned on me that i was trying to pull columns which i didn't tell access were there, because i could see it was pulling the right records, just not displaying it 'quite right'.

again, i don't know how else to implement this via Access only (without VB).... this is perhaps one of those moments when gurus can step in to point in the right direction? i know this works for me, and i am happy with it for now, but just because i say this is what i want dosn't mean people can't suggest the 'proper' or more elegant/logical way of achieving the same outcome!! ;-)

now about your other points:

check boxes and queries for muscle groups

yes, my intention is to replace this system with the third 'joining' table. i had not removed the checkboxes yet b/c i didn't want to ruin any working things... i think i am at the stage where i can safely remove these fields. (i took out the extra queries last night). i think this is how i figured out the M:M relationship? i made a 'join' (i'm not sure i'm using the correct terminology there) table... i.e., had the muscleID and groupID (as well as the autonumber primary key as a third field) as the only fields in my MuscleInGroups table, as lookup dropdowns, then in a form i made this join table a subform of the muscles form, so each muscle could have multiple assocciations to groups. it works well so far, but i do welcome suggestions if it is obvious this is not the correct way to do it.

i had originally put the checkboxes in because i was a complete newbie with Access, and didn't know how to do it differently. this is also why i had the superfluous queries. once i did the M:M table, and the groups subform in the muscles main form, this rendered the checkbox/multi-query method obsolete. this is the whole reason i started on the query that i originally asked for help for on this thread. i wanted to get rid of the other queries and the checkboxes. i know NOW it was bad design, but i didn't know any better when i had started.

elegance of code

unlike you george, and many other gurus on the forum, i'm so new at this that i am not familiar at all with what access can do 'out of the box'... it IS my first real database, and with time i hope to really become expert at it (or should i just aim for "competent" for now? LOL) like e.g., you, bob larson, pbaldy, missingling, and quite a few more who have helped me along the way on the forums (with the weird screen names that are hard to remember/spell)... strategy has NEVER been my strong point! ;-)

also, the tbl, qry, rpt etc. prefixes are helping understand how things work, and b/c i'm so new to Acces/VB, they help me orientate myself (in my own database! LOL) so that i know that i'm writing my VB correctly. maybe in the future i'll leave them off, but i'm afraid that for my own learning purposes they are currently a must. (aren't there two schools of thought on this convention amongst gurus like yourself?) i have noticed Access help documents also lack the prefixes, though i find them hard to follow because i don't know Northiwnd intimately and cannot guess whether they are referring to tables or queries, and thus i run into my own difficulties of knowing that there may be better ways to use access e.g., than making a million queries...

i've had HEAPS of fun learning, though (and also HEAPS of frustration! LOL) my massage databse is a long term project, so it's something i really want to do right. i don't want to spit it out next week in a "that will do" kind of fashion, so i REALLY appreciate the efforts of the forum members, like you george, who take the time to explain the importance of certain aspects, rather than just pasting code in a reply to make reach my goal. you may have noticed i have in the past posted question about sourcing information on coding conventions, cangrow and canshrink methods, etc, etc, rather than just making a thread titled "HELP ME!! MY HOMEWORK IS DUE TOMORROW I NEED THIS CODE TO WORK!!" ...my point is that i'm very responsive to constructive criticism, like you have generously given provided for me, george.

guilt

and it didn't feel like you were getting on my case - you were commenting on my code and design, and making extremely good observations and suggestions. they were very useful, in fact. ;-)

form aesthetics

awwwww..... *shucks* ;-) i can't claim all the credit - my inspiration actually came from the default aesthetics that access 2007 now applies to its forms. i've expanded on it, though and added a few whistles (like rollover effects on certain command 'buttons' - which are actually labels b/c they have border/backcolour properties, and with an OnClick event ;-) ).

i'm sure this will slow my ddb down (at least i've used linked images!! ;-) ), but i want it to be pretty so that the user LIKES to use the database, and i've always HATED the awful backward looking access95 form look - that bland grey and OMG the awful aqua and green people used to as a form background colour, attempting some design improvement, only to leave the grey bounded etchings!!! *eeech!* i like things to be easy to use (LOL though you may not have thought so from my back-end!), especially for less computer-literate people than myself. if they like using it, they won't be so scared of computers and 'databases'!

finally

thank you so much for taking the time, and having the patience, to look into my database. i had asked help about design before i even put finger to keyboard, but received no return posts (fair enuf - people do this on their own watch and it's easier to trouble shoot a line or two of code that to structure a databse from stratch, i knew it was a long shot! LOL). it takes something like 29 new groups pending that wakes newbies like me wake up to the idea of doing things more expertly(?) to make things simpler ;-)

ajetrumpet · May 22, 2008

wiklendt said:
wow, thanks george! that's really valuable info you have given me. i'm REALLY sorry it took you so long to untangle my design :-( i didn't mean to put people out. but THANKYOU for persevering.

LOL, i TOLD you it was a nightmare!!! ;-)

the first time i really picked up Access/VB was about ooohhh.... say, 4 months ago? i undertook a big project with a purpose (as opposed to my previous attempts many years ago to 'just learn' access), so inevitably it became a big mess really quickly. as soon as i realised i made a mistake in my design regarding the queries etc, i tried to get the cascade to work on ONE query, instead of individual queries. hence my thread.

in a twist, i DID manage to work out my coding issues last night independantly. the code i ended up using to make the cascade work:

my coding solution

Code:

lstMuscleByGroup.RowSource = "SELECT * FROM qryMuscleDISTINCTgroup WHERE GroupID='" & Me.cmbMuscleRegion.Column(0) & "';"

(the line break doesn't actually exist in my code, i just did it here so that the post wouldn't expand over the width of the page)

making my previous ~40 lines of code (before i even was to add the new 29 groups) into just one elegant line, which ancompasses ALL the groups, new and old :-D. it took me an entire week to get this one line, but now that i've got it i think it's the most beautiful line in the world!! ;-)

however, you did say that i ought to avoid VB where i can use Access' inbuilt functionality - do you mean in this instance also? how can i do that for my goal here - do you mean for me to put in a criteria in the query itself, to take its GroupID from the combo, rather than making the rowsource the query via VB and filtering it via VB? i tried doing that in the first instance, but was really unsuccessful. i'm not sure i was getting the reference correct for a subform, but i did follow tutorials on how to do this and still it refused to work. i could try again, now that i have this backup of VB to use if i continue to be unsuccessful, but considering it can take me several days to complete each step of my intention, i'm often not as motivated to find an alternative method if i stumble on one that works for me (properly, like this new code as opposed to my old checkbox/multi-query way).

my other issue you hit the nail on the head, george: column count. once i'd sorted out my cascade, i turned my attention 100% on this issue. here is the working code i am now using (similar to my earlier effort, though only came into effect once i'd sorted out my column count issues, just like you've stated here):

Code:

Me.txtFunction.Value = Me.lstMuscleByGroup.Column(3) Me.txtSymptoms.Value = Me.lstMuscleByGroup.Column(4) Me.txtTreatment.Value = Me.lstMuscleByGroup.Column(5)

i only had a column count of 2 for the list that i was trying to get info from the 4th, 5th and 6th columns; the query had 7 - i THINK i can safely remove this last column... i am DILIGENT with backups, so if it causes issues, i'm still safe ;-) . once changed appropriately, the code worked marvelously :-D i knew something was wrong along those lines because it was returning "#name?" in only those fields which actually had that data, it dawned on me that i was trying to pull columns which i didn't tell access were there, because i could see it was pulling the right records, just not displaying it 'quite right'.

again, i don't know how else to implement this via Access only (without VB).... this is perhaps one of those moments when gurus can step in to point in the right direction? i know this works for me, and i am happy with it for now, but just because i say this is what i want dosn't mean people can't suggest the 'proper' or more elegant/logical way of achieving the same outcome!! ;-)

now about your other points:

check boxes and queries for muscle groups

yes, my intention is to replace this system with the third 'joining' table. i had not removed the checkboxes yet b/c i didn't want to ruin any working things... i think i am at the stage where i can safely remove these fields. (i took out the extra queries last night). i think this is how i figured out the M:M relationship? i made a 'join' (i'm not sure i'm using the correct terminology there) table... i.e., had the muscleID and groupID (as well as the autonumber primary key as a third field) as the only fields in my MuscleInGroups table, as lookup dropdowns, then in a form i made this join table a subform of the muscles form, so each muscle could have multiple assocciations to groups. it works well so far, but i do welcome suggestions if it is obvious this is not the correct way to do it.

i had originally put the checkboxes in because i was a complete newbie with Access, and didn't know how to do it differently. this is also why i had the superfluous queries. once i did the M:M table, and the groups subform in the muscles main form, this rendered the checkbox/multi-query method obsolete. this is the whole reason i started on the query that i originally asked for help for on this thread. i wanted to get rid of the other queries and the checkboxes. i know NOW it was bad design, but i didn't know any better when i had started.

elegance of code

unlike you george, and many other gurus on the forum, i'm so new at this that i am not familiar at all with what access can do 'out of the box'... it IS my first real database, and with time i hope to really become expert at it (or should i just aim for "competent" for now? LOL) like e.g., you, bob larson, pbaldy, missingling, and quite a few more who have helped me along the way on the forums (with the weird screen names that are hard to remember/spell)... strategy has NEVER been my strong point! ;-)

also, the tbl, qry, rpt etc. prefixes are helping understand how things work, and b/c i'm so new to Acces/VB, they help me orientate myself (in my own database! LOL) so that i know that i'm writing my VB correctly. maybe in the future i'll leave them off, but i'm afraid that for my own learning purposes they are currently a must. (aren't there two schools of thought on this convention amongst gurus like yourself?) i have noticed Access help documents also lack the prefixes, though i find them hard to follow because i don't know Northiwnd intimately and cannot guess whether they are referring to tables or queries, and thus i run into my own difficulties of knowing that there may be better ways to use access e.g., than making a million queries...

i've had HEAPS of fun learning, though (and also HEAPS of frustration! LOL) my massage databse is a long term project, so it's something i really want to do right. i don't want to spit it out next week in a "that will do" kind of fashion, so i REALLY appreciate the efforts of the forum members, like you george, who take the time to explain the importance of certain aspects, rather than just pasting code in a reply to make reach my goal. you may have noticed i have in the past posted question about sourcing information on coding conventions, cangrow and canshrink methods, etc, etc, rather than just making a thread titled "HELP ME!! MY HOMEWORK IS DUE TOMORROW I NEED THIS CODE TO WORK!!" ...my point is that i'm very responsive to constructive criticism, like you have generously given provided for me, george.

guilt

and it didn't feel like you were getting on my case - you were commenting on my code and design, and making extremely good observations and suggestions. they were very useful, in fact. ;-)

form aesthetics

awwwww..... *shucks* ;-) i can't claim all the credit - my inspiration actually came from the default aesthetics that access 2007 now applies to its forms. i've expanded on it, though and added a few whistles (like rollover effects on certain command 'buttons' - which are actually labels b/c they have border/backcolour properties, and with an OnClick event ;-) ).

i'm sure this will slow my ddb down (at least i've used linked images!! ;-) ), but i want it to be pretty so that the user LIKES to use the database, and i've always HATED the awful backward looking access95 form look - that bland grey and OMG the awful aqua and green people used to as a form background colour, attempting some design improvement, only to leave the grey bounded etchings!!! *eeech!* i like things to be easy to use (LOL though you may not have thought so from my back-end!), especially for less computer-literate people than myself. if they like using it, they won't be so scared of computers and 'databases'!

finally

thank you so much for taking the time, and having the patience, to look into my database. i had asked help about design before i even put finger to keyboard, but received no return posts (fair enuf - people do this on their own watch and it's easier to trouble shoot a line or two of code that to structure a databse from stratch, i knew it was a long shot! LOL). it takes something like 29 new groups pending that wakes newbies like me wake up to the idea of doing things more expertly(?) to make things simpler ;-)

if I had the time to read all of this, that would mean that I didn't have a job.

wiklendt · May 22, 2008

oh, and when i say my 'client', i mean my partner who's into equine massage. i'm doing this for her - it's just quicker to write 'client' than it is to explain in detail. i am not acutally running a business in databse development or anything... certainly not charging money for it!! LOL ;-)

wiklendt · May 22, 2008

if I had the time to read all of this, that would mean that I didn't have a job.

sooo.... don't read it...? ( LOL or are you saying you don't have a job ;-P )

georgedwilkinson · May 23, 2008

I read it and I have a job.

Wiklendt...there are all kinds of people on the forum. Some of them will hear your appeal but not somebody else's. Others will listen to those other people's appeals and totally ignore you. It is all because of criteria that people have for what questions they're going to answer.

In my case with regards to you, I noticed that you had been contributing in other areas and was happy to help, convinced that you weren't going to dis' me for not answering your exact question, realizing that your question could not be answered directly without writing a book, which there are plenty of already.

Regarding conventions and what is right to do in your design. Whatever works is the right thing to do. It really doesn't matter a lot to me how you design/name/use conventions until you ask for help fixing it or unless it has to do with table design, which is my passion. I do believe you should use prefixes for all objects except tables, which is what I do religiously.

The reason I don't use prefixes for tables is because they are the basis of the entire system...the foundation. And when you are discussing the system (and its foundation, the tables) with other systems folks or thinking about the system yourself, you can logically talk about the table design using nouns and constructed phrases that can actually define the entire system to people who know nothing about computers. For instance, within your system, you could state: "each horse can have therapy applied during one or more massage sessions" and "each massage session is conducted for the benefit of one and only one horse." Through those words, a data modeler can design that part of the system regardless of the DBMS that is used. Additionally, a non-IT person can "get it". But if you named your tables differently, a professional wouldn't have the ability to use natural english language to define your system.

I think you're doing fine and respect you as a colleague. With regards to using Access to build business systems, I have a little bit more knowledge than you in many areas, a lot more in one or two, and less in one or two others. So, maybe one day I'll ask for help and you'll be able to come to my aid. I'm guessing it's the same with many of the other full-time professionals and excellent casual experts we have here on the forum.

Oh, and I didn't want to make you feel bad by saying I spent a lot of time on your problem. I would not have done it if I didn't want to. The point was, I didn't think you were doing things the easiest way (I'm very lazy) and wanted to make sure you re-thought some of your design. It looks like you've done that so you're on your way.

Is there anything else that you need help with to get this sytem fully operational?

wiklendt · May 23, 2008

thanks for your reply, and for reading my previous reply even though you have a job ;-)

no more help at the moment, though i'm nearing my need to start designing the *reports* for the massage sessions (the whole point of creating the databse, actually!!), so rest assured you haven't heard the last of me... ;-)

i have started the report design, so half the work is done (making things pretty takes a long time!!), i just have to pull together the actual muscle group thing into it - each massage session report will be made up of text entered respective of each muscle group by the EMT (equine massage therapist). the groups will appear with a subheading, one or two images of the muscles associated with the group selected, and the text describing what the EMT did to which muscles. i think it's going to be a very comprehensive way of presenting the wholistic approach my partner is taking to her massage (it's also the design she requested to have, LOL)

i just have to figure out how to easily filter out the unused (in that massage session) muscle groups.

at the moment, i have just one MassageData table, which has everything in it like start time, duration, cost, purpose of visist... etc etc... and also fields for each group (i haven't added the new extra 29 yet)...

now, however, i'm thinking this is probably going to cause trouble because the muscle groups involved have nothing to do with, say, how much the session costs.

perhaps i'm better off making the memo fields for each group as a separate, related table (have a ServiceID foreign key in the MassageData table, with a 1:M to the services...?) which can be filtered to remove empty fields easily... and possibly make this a subreport.... hmm.... i should have thought about this before!! LOL

and i said i didn't need any more help right now.... **pffffttt** i'm SUCH a liar! ;-)

and yes, i believe in contributing back to the fantastic forum that helped me so much. i see people having trouble with things i originally did can can't help but to help ;-) my only fear is that i point them in the wrong direction!! i try to stay away from (answering) the more advanced topics if i'm not confident i can make valuable contribution... LOL

SORRY ABOUT ANOTHER THESIS!!

ajetrumpet · May 23, 2008

georgedwilkinson said:
I read it and I have a job.

You're too fast for me then, George.

wiklendt · Oct 17, 2008

fixed one issue only to have another jump up!

wow, it's been a while since i started this thread. only now have i managed to get this working! that's several months of scratching my head. LOL... it's only because of a few other database projects i've got going that i managed to find the solution. here's what i managed to do:

i have now normalised the data going into the massage table. there are several, but the two that concern this thread are:

tblMassageData: holds the primary info of the massage session, like time start, time finish, amount due, etc

tblMassageData_Services: holds information on the services peformed on each muscle group for that particular massage.

now, it was easy making sure the the services data were linked to the massage session (master and child fields linked the MassageID PK in tblMassageData, and the MassageID FK in tblMassageData_Services) BUT the challenge that faced me and i battled over the last several months was how to also get the services table to accept the muscle group chosen

the way i achieved this (eventually!) was by linking a second set of master/child fields!! (the subform has two master fields)

so, when a person chooses a muscle group in the drop-down, it automatically either finds the record in the subform, or prepares the form for a new record - brilliant! (and it still relates to the correct massage session)

so my master child fields for the subform look like (see subform linking JPG attachment):

and the reports are also now done. they work fantastically (see report page 1 JPG attachment)

however, i have encountered a problem in my database which appears to be a VBA problem for some listboxes in the massage form i've created (see Observations JPG attachment).

This is one of a set of listboxes i am including in my database. it works like this:

there is a listbox for "available" items, and once selected, can be moved to the listbox for "selected" items. the available items are sourced from a table, filtered by a "NOT IN" statement.

the VBA i have associated with this transfer suggests that all the correct data is inserted into the table correctly, BUT if you compare the items selected in the (observations tab.jpg attachment), against the items selected appearing in the report (report page 2.jpg attachment) these do NOT match. upon opening the tblMassage_AssObservations table (where the assigned items are stored for each massage session), it appears that the VBA is inserting the item, but under arbitrary MassageIDs.... which should NOT be happening.

i have attached my db to anyone to peruse through and see if they see anything out of the ordinary...

the other two sets of listboxes (one for "glossary" and another for "recommendations") APPEAR to be working, but i haven't tested heavily... i've been trying to trouble-shoot the Observations one...

this is basically now the only hurdle in passing the database on to my partner for real data entry and 'real-world' use. there is one other small niggly issue i have, but i'll tackle that later. this listbox one is much more important to me.

anyone have any ideas?

thanks,
wiklendt

wiklendt · Oct 18, 2008

i'm afraid i could have organised myself better and prepared some code pre-trimmed...(see pdf attachment)... this code is the code relating to my problem stated above.

i've been looking closer at my code and i THINK i have located a problem, but i can't find any reference to whether i'm doing to right thing or not. my problem, i think, lies in my WHERE statements.... i have a

Code:

... WHERE blah=blah AND blah=blah

now, i don't know that that's entirely proper? should there be parentheses there for multiple WHERE arguments? or something? like:

Code:

... WHERE (blah=blah AND blah=blah)

access help only gives examples with one WHERE variable, but here i have two, so maybe there's a convention there i'm not familiar with...

edit: sorry, the WHERE statements are in the 'remove from list' part of the database.... hmmm, which leaves me back at rumpled square one! ...will continue to scratch my head and dabble in some code...

georgedwilkinson · Oct 18, 2008

Can you be extremely specific about what your problem is and the name of the forms/reports that it manifests itself on? Please leave out any information about anything else or pictures of things or even how it works. Just the specific details about what is broken, please.

By the way, your database has tons of references to things on your c: drive. It makes troubleshooting cumbersome, especially not knowing exactly where you think the problem appears.

Banana · Oct 18, 2008

wiklendt-

With regards to your observation form, you want a double listbox that transfer items, basically?

I do lot of them for my design. After tinkering with so many different way, I've decide the far most simplest solution is to use recordsets in a subform setting. In my case, it's usually a many-many relationship, using the parent form's record to tell me what selections were made for this particular record (e.g. visit/event/observation whatever)

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

I then set the listbox that display the current selection rowsource to a query from the junction table for given parent record.

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.

If this is relevant and you're interested, I can provide some more details on how to implement this.

wiklendt · Oct 18, 2008

ah crap, sorry george, completely forgot about the linked images... gimme some time and i'll remove the OLE images and references in the VBA... and then post back.

banana, i'd be very interested in knowing more about the recordset avenue -if you say it's easier, that'd definitely be a good thing to try.

thanks guys,
wiklendt.

Banana · Oct 18, 2008

As I don't have Access right now, this is right off the top of my head.

In the subform's module I have the following events used:

OnLoad event: Initalize the variable:

Code:

Private Sub Form_Load()

Set qdf=CurrentDb.QueryDefs("NameOfQuery") 'This is a parameter query

End Sub

Click event for Add button: Call a custom sub (let's call it AddRecord
Double-click event for selection listbox: Ditto

Code:

Private Sub AddRecord

Dim var as Variant

With rst
  For each var in Me.SelectionListbox.Itemsdata() 'Or was it Selected()? Check the help file
    .Addnew
    rst.Fields("NameOfField") = var 
    .Update
Next var

Set Me.SelectedChoices.Recordset = rst

End Sub

Click event for Remove button: Call another sub (DelRecord)
Double-click event for selected listbox: Ditto

Code:

Private Sub DelRecord

Dim var as Variant

With rst
  For each var in Me.SelectedChoices.ItemsData() 'Or Selected()?
    .FindFirst("PrimaryKey = " & Me.Parent.PrimaryKey & " AND NameOfField= " & var
    .Delete
  Next var
End with

Set Me.SelectedChoices = rst

End Sub

Close event: Clear the variables.

Code:

Private Sub Form_Close()

Set qdf=Nothing
Set rst=Nothing

End Sub

Then one more sub, but make it public:

Code:

Public Sub SyncList()

qdf.Parameters("NameOfParameter")=Me.Parent.PrimaryKey
Set rst = qdf.Openrecordset
Set Me.SelectedChoice.Recordset= rst

End Sub

Then in the parent form, you need this code in OnCurrent:

Code:

Private Sub Form_Current()

Me.SubformName.Form.SyncList

End Sub

As indicated in earlier post, both listbox are unbound, the selection listbox using the rowsource to display possible selections, while the other listbox listing selected choices binds directly with the recordset object, reflecting the change made to it.

The only reason why I like this approach is because this is now fully modular and black-box; just drop in the queries I need for the selection listbox and selected choices and I'm done. Time saving when I needed several instances of those.

And to be sure, this is total aircode so don't count on smooth sailing from get-go; you may have to do some trial and error to get it working, but the basic gist is all there. Of course if you have any question, feel to fire away.

HTH.

cascading combo nightmare... (1 Viewer)

i recommend chocolate

AWF VIP

i recommend chocolate

Attachments

i recommend chocolate

Attachments

i recommend chocolate

AWF VIP

AWF VIP

i recommend chocolate

Banned

i recommend chocolate

i recommend chocolate

AWF VIP

i recommend chocolate

Banned

i recommend chocolate

Attachments

i recommend chocolate

Attachments

AWF VIP

split with a cherry atop.

i recommend chocolate

split with a cherry atop.

Similar threads

Users who are viewing this thread