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

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.
Who is Participating?
Prisoner362670Connect With a Mentor Author Commented:
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

Prisoner362670Author Commented:
I used a sql insert into...values statement.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.