Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2007
    Posts
    3

    Question Unanswered: Help w/cascading combo box, null field

    I am new to this forum and still learning access. I have a form that has a select statement pulling fields from related tables. I created a combo box for Firms with a dependent combo box for Attorneys (filtered by the law firm selection). However, there is not always a need to select a firm and attorney - but if you try to leave record, states that cannot find a matching record in the firms table for the key field firmid. I know it is because I am not writing anything to the atty table then which is also not matching up with the firms table - but I don't know how to fix it.


    The main table in form is tenants with attorneys related via fk and firms related to atty. I tried removing firmid (from atty table) from the select statement of form - but then cannot select any firms or attys if needed - fields show #name.

    Can anyone tell me where to look or what I need to change? I am driving myself crazy as first I needed to get the combo boxes working correctly (at first the attorney box was duplicating records in the attorney table even though it was filtered correctly for the firms) This is probably something really simple - or my design is terrible - either way, I will probably have a "DUH!" reaction to any solution.

    I would appreciate any assistance - and can send the db if needed. Thanks

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Hey kalock, welcome to the forums!
    Quote Originally Posted by kalock
    Can anyone tell me where to look or what I need to change?
    Would you mind posting the SELECT SQL statement so we can take a look at it? It'll help if we know what you've already got.
    Me.Geek = True

  3. #3
    Join Date
    Jun 2007
    Posts
    3
    Here it is:

    SELECT tblTenants.TenantID, tblTenants.BldgID, tblTenants.dbaID, tblTenants.CorpID, tblTenants.Suitid, tblTenants.ContactFirstName, tblTenants.ContactLastName, tblTenants.Address, tblTenants.City, tblTenants.State, tblTenants.Zip, tblTenants.Phone, tblTenants.Extension, tblTenants.FaxNumber, tblTenants.EMailAddress, tblTenants.AttyID, tblTenants.Note, tblAttorney.FirmID, tblAttorney.AttyFirst, tblAttorney.AttyLast, tblFirms.PhoneNumber, tblFirms.FaxNumber, tblFirms.Address, tblFirms.City, tblFirms.State, tblFirms.ZipCode, tblTenants.CopyTo, tblTenants.CopyFirstName, tblTenants.CopyLastName, tblTenants.CopyAddress, tblTenants.CopyCity, tblTenants.CopyState, tblTenants.CopyZip, tblTenants.DateModified, tblTenants.TimeModified, tblTenants.UserModified, tblTenants.AttyCopy FROM tblFirms INNER JOIN (tblTenants INNER JOIN tblAttorney ON tblTenants.AttyID=tblAttorney.AttyID) ON tblFirms.FirmID=tblAttorney.FirmID;

    Let me know if you need anything else to try to help with this mess.

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    If you can post the entire dB, I'll take a look at that. (Don't know how sensitive your info is, if you need to generic-ize it?). Else...
    Quote Originally Posted by kalock's 1st post
    I have a form that has a select statement pulling fields from related tables. I created a combo box for Firms with a dependent combo box for Attorneys (filtered by the law firm selection). However, there is not always a need to select a firm and attorney - but if you try to leave record, states that cannot find a matching record in the firms table for the key field firmid. I know it is because I am not writing anything to the atty table then which is also not matching up with the firms table - but I don't know how to fix it.
    I'm interested in how the combo boxes are set up on the form; how are the boxes filtering? Are you using vbcode? If so, what is it?
    Me.Geek = True

  5. #5
    Join Date
    Jun 2007
    Posts
    3
    I have attached the db. I stripped out a bunch of data - but now I also have a problem with the second combo box creating another record even if you select an existing attorney! I am SOOOO frustrated at this point, I keep going in circles and feel I am making the whole thing more complicated than needed. I appreciate any help in this matter.

    BTW, I am using this same 2 combo set up in another db, but I get a different error (can not find matching record for attyid) even if I don't pick a firm! Extremely confusing!!
    Attached Files Attached Files

  6. #6
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388

    Null Field

    In your tblFirms put a dummy record with a Firm Name of N/A meaning Not Applicable. INSERT A SPACE before N/A so that N/A will be displayed in your combo box as the first record e.g N/A then Kelly & Sons as the second record.

    Your Attorney will not be displayed as no attorneys will be linked to your firm N/A.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •