Results 1 to 12 of 12
  1. #1
    dan_man is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Location
    Bucharest, Romania
    Posts
    5

    Cascade combo with invisible/visible fields


    Hi all,

    I am working to a database with a sort of inventory. I have to select the data according to 3 criteria. One of the criteria is "Region", the second one is "Subregion" and the third one is "Location". I want to put each of these criteria in comboboxes. After selecting the last criteria i want to have some fields (related to Location) becoming visible. Can anyone help me with this? Please!

    Thank you in advance
    Dan

  2. #2
    Rod is offline Expert
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    How far have you got?

  3. #3
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    This is possible if your data is normalized properly with one to many relationship where all the three tables are connected with their primary and foreign keys (PK and FK)

    You need to have Region ID connected to sub-region and sub-region to Location. This structure will give you the desire result. You can then create a combo box and use the Region table its data source, second you can create a sub-region based on the sub-region table and use the Region ID (FK) as it criteria and lastly the Location combo box with sub-region (FK) id as its criteria.
    This will filter the Location based on the sub-region and Region selected from the above two combos.

    Hope this make sense.

  4. #4
    dan_man is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Location
    Bucharest, Romania
    Posts
    5
    I have attached a short form of my database. So here I am.

  5. #5
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    Can you write tables names/Fields names in English? need to know which table belong to what? Region/sub-region and location.

  6. #6
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    Here is your sample db attached, I have Access-2010 installed on my laptop, the db is converted to 2010 format, but it should not be the problem.

    Anyhow, the queries for your combos are as follows:

    cboDR:
    SELECT tblDR.ID_DR, tblDR.DR
    FROM tblDR
    ORDER BY tblDR.DR;

    Bound Column:1
    Count Column:2
    Column width: 0";1"
    =======================

    cboCIZ:
    SELECT tblCIZ.ID_CIZ, tblCIZ.ID_DR, tblCIZ.CIZ
    FROM tblDR INNER JOIN tblCIZ ON tblDR.ID_DR = tblCIZ.ID_DR
    WHERE (((tblCIZ.ID_DR)=[Forms]![Form1]![cboDR]));

    Bound Column:1
    Count Column:3
    Column width: 0";0";1"
    =======================

    cboLocatii
    SELECT tblLocatii.ID_Locatii, tblLocatii.ID_CIZ, tblLocatii.Locatie
    FROM tblCIZ INNER JOIN tblLocatii ON tblCIZ.ID_CIZ = tblLocatii.ID_CIZ
    WHERE (((tblLocatii.ID_CIZ)=[Forms]![Form1]![cboCIZ]))
    ORDER BY tblLocatii.ID_CIZ;

    Bound Column:1
    Count Column:3
    Column width: 0";0";1"
    =======================


    Event procedures:

    Private Sub cboCIZ_AfterUpdate()
    Me.cboLocatii.Requery
    End Sub

    Private Sub cboDR_AfterUpdate()
    Me.cboCIZ.Requery
    End Sub

    Private Sub cboLocatii_AfterUpdate()
    Me.cboLocatii.Requery
    End Sub

    hope you got it solved.

  7. #7
    dan_man is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Location
    Bucharest, Romania
    Posts
    5
    Thank you Khalid,

    Cascade combo works fine but on second query it looks like the Sub Region combo (cboCIZ) is hooked on its previous selection. I have tried to put a command button to refresh the whole form but obviously something is missing and the button did not work.

  8. #8
    dan_man is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Location
    Bucharest, Romania
    Posts
    5
    Finally I succeeded to have the refresh button working. here it is the code I have used:

    Private Sub cmdRefresh_Click()
    On Error GoTo err_cmdRefresh_Click
    cboDR = Null
    cboCIZ = Null
    cboLocatii = Null
    Me.RecordSource = tblCIZ
    Me.RecordSource = tblLocatii
    Me.Refresh

    exit_cmdRefresh_Click:
    Exit Sub
    err_cmdRefresh_Click:
    MsgBox Err.Description
    Resume exit_cmdRefresh_Click
    End Sub

    Without this button the cascade combos remain hooked on last selection. I wonder if there is a simple way to do the same thing.

  9. #9
    Rod is offline Expert
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Yes, this is the way Access behaves. I don't remember experiencing it in v2003 and earlier - maybe someone can comment on that. I also don't know whether to classify it as a bug or just accept that, 'This is the way it works.'

    Why the refresh button? Why not add an extra statement in the AfterUpdate procedures immediately following the requery? For example:


    ...
    Me.cboCIZ.Requery
    Me.cboCIZ = Null
    ...

  10. #10
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    Rod, if you will do Me.cboCIZ = Null of After update envent of the combo,
    then you will get nothing to select from your combo....

  11. #11
    Rod is offline Expert
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Hi Khalid,





    The situation as I understand it and experience it is (using dan_man's db as an example):
    • User selects a new value from cboDR;
    • This triggers an AfterUpdate event for which the code is:
      Code:
      Private Sub cboDR_AfterUpdate()
      Me.cboCIZ.Requery
      End Sub
    • User sees that cboCIZ goes blank, but ... if you interrogate cboCIZ it retains the value it had before cboDR was updated.
    • There is now an inconsistency: cboCIZ appears blank or null but it has a non-null value! That value may not even correspond to a row on the updated list. Any programming using this value will be wrong! This is the 'bug.'
    • My suggestion is to force cboCIZ to be null so that its value corresponds to its user display. User is now free to select from cboCIZ list.
    I spent over an hour chasing this one down some time ago. I suspect I'm not alone. As I commented I do not rememeber having this problem with v2003 or earlier.




    If you want to see a consequence of this try:
    1. Select a value from cboDR;
    2. Select a value from cboCIZ;
    3. Select a different value from cboDR - cboCIZ now goes blank;
    4. Select the original value (1) in cboDR. Low and behold the selection made in (2) above magically reappears on cboCIZ even though it has been requeried twice.
    I would be interested to hear of anyone else's experience with this.


    PS I said the retained value may not be in the requeried list. The reason the combo box goes blank is because the retained value IS NOT in the requeried list.
    Last edited by Rod; 06-29-2011 at 04:18 AM. Reason: More accurate reporting

  12. #12
    dan_man is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Location
    Bucharest, Romania
    Posts
    5
    Issue solved Thank you all!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Making subform field visible/invisible
    By Snufflz in forum Forms
    Replies: 3
    Last Post: 01-17-2011, 05:30 AM
  2. Replies: 2
    Last Post: 01-06-2011, 04:38 AM
  3. Invisible/visible box
    By teirrah1995 in forum Forms
    Replies: 4
    Last Post: 10-03-2010, 02:45 AM
  4. Setting fields visible/invisible
    By col5473 in forum Forms
    Replies: 1
    Last Post: 09-17-2010, 09:14 AM
  5. Combo Boxes Visible/Invisible
    By Rick West in forum Forms
    Replies: 2
    Last Post: 07-06-2010, 09:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums