none
Comobox question

    Question

  • I'm using Access 2007, novice

    I have 3 tables.  2 of the 3 tables are used as lookup (tblSites & tblDistricts) in my third table (tblJobdetails).

    I based the form on the "tbljobdetails".  When making my form I synchronized comboboxes for districts/sites.  A user selects a "cboDistrict" and then only the "cboSites" for that district display.  I have synchronized the comboboxes by using the "tblDistricts" as the RowSource fo "cboDistrict" then inserted an "after_update" event to populate the "cboSites".  All other fields come from the "tblJobdetails".  It works.  The problem I have is, I can save the "cboDistricts" selection to my "tblJobdetails", but for some reason the site is not saving.  I do not have a control source or RowSource assigned for the "cboSites" because then the comoboxes won't work (the method I used was from microsoft's 'synchronizing comoboxes; northwind').  I'm pulling my hair out.  I'm guessing I have made a mistake in a relationship somewhere?  Any advice would be appreciated.  Thank you.

    Tuesday, February 26, 2013 7:46 PM

Answers

  • You might like to take a look at the file ComboDemo.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little demo file illustrates the use of correlated combo boxes to do what you are attempting.  Its main purpose, however, is to do so in the context of a set of correctly normalized tables.  In the demo the Locations table has only a ParishID column, not a DistrictID or CountyID as the latter two are functionally determined by ParishID, so to include them in Locations would introduce redundancy and the consequent risk of update anomalies.  This would be analogous to your having a redundant column for the district as well as for the site in your jobs table.

    Ken Sheridan, Stafford, England

    Wednesday, February 27, 2013 10:54 PM

All replies

  • If you used the wizard to create the sites cbo, did you see the choice of "Remember the value for later use" and "Store that value in this field" You need to choose store that value in this field.

    Chris Ward

    Tuesday, February 26, 2013 7:54 PM
  • Hi Jstnw,

    You should be able to save the site and still have everything synchronize as per the example you followed.  Set a Control Source property for cboSites and you should be fine.

    -Bruce

    Tuesday, February 26, 2013 8:24 PM
  • The comobo boxes will not work when I enter a 'control source' for 'cboSites'. 

    There are 3 tables. 

    The 'District' table: DistrictID (PK), DistrictName

    'Site' table: SiteID (PK), SiteName, DistrictID(FK: one-to-many)

    'Job' table: EmployerID (PK), District (FK: one-to-many), Site (FK: one-to-many), LastName, FirstName, Address

    I make a Form based on the 'Job' table.  I delete the District and Site comboboxes and manually enter my own and cancel the wizard. 

    Name:   cboDistricts
     

    Control Source: Districts (from 'job' table);

    RowSourceType: Table/Query    RowSource:     Districts

    ColumnCount:   2

    ColumnWidths:  0";2"

    BoundColumn:   1

    AfterUpdate:   [Event Procedure]

    Name:   cboSites
         RowSourceType: Table/Query
         ColumnWidths:  3"

         Width:         3"

    Each time I try to add a 'control source' to 'cbosites' so I can save the data to 'job' table the combo boxes don't work.  I get a message after selecting 'cboDistrict' saying 'The value you enetered isn't valid for this field'.  When I leave it unbound they work great.  Not sure what I'm missing.

    District 

    Wednesday, February 27, 2013 10:19 PM
  • You might like to take a look at the file ComboDemo.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little demo file illustrates the use of correlated combo boxes to do what you are attempting.  Its main purpose, however, is to do so in the context of a set of correctly normalized tables.  In the demo the Locations table has only a ParishID column, not a DistrictID or CountyID as the latter two are functionally determined by ParishID, so to include them in Locations would introduce redundancy and the consequent risk of update anomalies.  This would be analogous to your having a redundant column for the district as well as for the site in your jobs table.

    Ken Sheridan, Stafford, England

    Wednesday, February 27, 2013 10:54 PM