Page 2 of 2 FirstFirst 12
Results 16 to 18 of 18
  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    When adding new company record, don't you want to populate Business, Category, Subcategory, etc? Why would this be optional? I suspect this is contributing to issue because combobox RowSource has filter criteria dependent on Bill_ID but your code does not populate this field.

    If contact cannot be added without associated company record, why use DLookup to find company name - isn't it already available in Company combobox? Might want code that prevents selecting/entering contact without company first selected/entered.

    Saving category in tblJournal is duplication of data because associated Category can be retrieved via tblSubcategories. Appears to be circular relationship. Similar for Bill_ID field - this can be retrieved through tblCompanies.

    I NEVER use multi-value field and avoid attachment field (a type of multi-value field). Embedding files in db uses up Access 2GB size limit.

    Also advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention: better would be IsNew, IsArchived, FollowUpDate. Also advise not to use reserved words such as Date as field names. Better would be DateEnterJnl.

    ArchiveDate is more informative then just a yes/no field. What significance is New? field - how long is something 'new'?

    I find lookups in table more annoying than useful, especially when an alias is involved.

    Save yourself some typing. Value is default property for fields and data controls so don't need to explicitly reference.
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  2. #17
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Thanks for you great advice, June7.

    I have read quite a bit about the pitfalls of multi-valued fields. For the purpose of this little 3-user database, it was the best approach, based on the main user's habits. I assume the best approach would have been for the Billing table to be on the other side of Companies table, with one-to-many relationship set. At this point, since the main user is more accustomed to using his database, I could switch it around, but I fear the downstream problems in making that change.

    I try not to use too many spaces, dashes, etc. Thanks for that check; The Journal table was easy to fix. I also took out the last few fields (including New?) that are no longer needed. Those were used for data import from spreadsheet during initial design stage. I also removed the attachment field from the table. It's never been used. My user(s) would not take the time to use them anyway.

    The DLookup function to get the CompanyName is just for the MsgBox asking the user if he wants to add the Contact to the Company list.

    Don't really need an archive date. Checkbox serves the purpose for running a query to save them off to another table for history.

    I think I need to turn the order around and have the cboCompany fall before, and not filter for, Bill_ID. I think you're right that it may be causing the problem. Will let you know if it works.

    Again, thank you so much for all the advice. It's always appreciated, as I will never learn enough.


  3. #18
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    June7, you were right in your suspicion that the code wasn't working because of the Bill_ID. I've switched the field order, and taken the criteria off Bill_ID off of the combo box for the Company, and it works fine now.

    Thanks again so much, everyone, for the help!


Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. ClsModule and a NotInList Event
    By d9pierce1 in forum Programming
    Replies: 4
    Last Post: 08-04-2019, 05:16 AM
  2. Key Press Event and/or Key Up Event not firing as expected
    By GraeagleBill in forum Programming
    Replies: 7
    Last Post: 01-16-2018, 04:11 AM
  3. NotInList event
    By jgelpi16 in forum Programming
    Replies: 2
    Last Post: 04-13-2011, 09:10 AM
  4. NotInList event issue
    By elinde in forum Forms
    Replies: 1
    Last Post: 04-01-2011, 08:43 PM
  5. Cancelling the NotInList event
    By Remster in forum Programming
    Replies: 12
    Last Post: 11-21-2010, 10:12 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 - Senior Forums