Access 2007 Open a datasheet subform in either acFormAdd or acFormEdit depending

Posted on 2011-10-28
Last Modified: 2012-05-12
I have a PO detail subform in a one-to-one relationship with a PO table.  They are both displayed as form datasheet -- the detail is revealed by clicking on the '+'.  

If the primary key of the PO already exists in the detail table I need the detail subdatasheet to open in edit mode; if the primary key of the PO doesn't exist in the detail table, I need the subdatasheet to open in addition mode (able to add a new record).  

I don't know how to check the detail table for the PO key of the parent; I can open the form in acFormAdd but not in the subdatasheet, etc.  Guidance please dear gurus.
Question by:Prisoner362670
    LVL 84
    You have no control over how the native (and Access controlled) SubDatasheet presents itself. You can mimic much of the bevhavior of the datasheet + subdatasheet by building two forms (both in datasheet view) and embedding the child datasheet form in the parent datasheet form and perhaps control that functionality in the Load or Open event of the subdatasheet, but that's probably about as far as you could get.


    Accepted Solution

    There were actually two solutions both fairly simple:
    1) Base the datasheet form and its subdatasheet on the same table just use a different query to pase up the data presentation for the user; or
    2) whenever a new record is created in the table used as a datasource for the datasheet form, also create a new record in the detail table on which the subdata sheet is based.  All subdatasheets display in edit only mode and are in a one-to-one relationship with the datasheet form table.

    I do not award myself points; nor do I award them to LSMC Consulting.

    Author Closing Comment

    I used a sql insert into...values statement.
    LVL 84
    Good suggestion on using Queries for your forms.

    However, adding a new record to a child table whenever a parent record is created can cause troubles when querying and reporting, so be careful with that. In most cases, it's not a good idea to "auto add" blank records.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Suggested Solutions

    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    Outlook Free & Paid Tools
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now