SubForm Issue

[Colby]

I have a tabular form with subforms built in. One of the subforms is not
showing "New (blank) Record" at the bottom of the screen for new data entry.
I have looked on the form itself as well and cannot find the problem. Any
ideas as to where i can look for a solution?

 

[John W. Vinson]

I have a tabular form with subforms built in. One of the subforms is not
showing "New (blank) Record" at the bottom of the screen for new data entry.
I have looked on the form itself as well and cannot find the problem. Any
ideas as to where i can look for a solution?

Most likely the problem is that the Query that the subform is using as its
recordsource is not updateable. What's the Query? If you open it directly, is
there a new record? You could open the query in SQL view and post the SQL text
here if you can't figure out why it's not accepting new data.

 

[Colby]

John,
When I open the form there is not a new record. The form is for entering
data on physician "core measures" quarerly. It is pullind data that is
directly entered into the table, just not allowing a new (blank) record.
Here is the SQL text, though it looks pretty dang confusing.

SELECT Core_Measures_tbl.Medical_ID, Core_Measures_tbl.Quarter,
Core_Measures_tbl.AMI_Aspirin_at_Arrival_Numerator,
Core_Measures_tbl.AMI_Aspirin_at_Arrival_Denominator,
Core_Measures_tbl.AMI_Aspirin_at_Discharge_Numerator,
Core_Measures_tbl.AMI_Aspirin_at_Discharge_Denominator,
Core_Measures_tbl.[AMI_ACE/ARB_Numerator],
Core_Measures_tbl.[AMI_ACE/ARB_Denominator],
Core_Measures_tbl.AMI_BB_Discharge_Numerator,
Core_Measures_tbl.AMI_BB_Discharge_Denominator,
Core_Measures_tbl.AMI_PCI_90min_Numerator,
Core_Measures_tbl.AMI_PCI_90min_Denominator,
Core_Measures_tbl.AMI_Fibrinolytic_30_Numerator,
Core_Measures_tbl.AMI_Fibrinolytic_30_Denominator,
Core_Measures_tbl.AMI_Mortality_Numerator,
Core_Measures_tbl.AMI_Mortality_Denominator,
CMS_90th_percentile.AMI_CMS_90_percentile,
Core_Measures_tbl.PN_Blood_prior_ABX_Numerator,
Core_Measures_tbl.PN_Blood_prior_ABX_Denominator,
Core_Measures_tbl.PN_Blood_24hr_ICU_Numerator,
Core_Measures_tbl.PN_Blood_24hr_ICU_Denominator,
Core_Measures_tbl.PN_AbxSelection_ICU_Numerator,
Core_Measures_tbl.PN_AbxSelection_ICU_Denominator,
Core_Measures_tbl.PN_AbxSelection_NON_ICU,
Core_Measures_tbl.PN_AbxSelection_NON_ICU_Numerator,
Core_Measures_tbl.PN_AbxSelection_NON_ICU_Denominator,
CMS_90th_percentile.PN_CMS_90_percentile,
Core_Measures_tbl.CHF_EvalLVS_Numerator,
Core_Measures_tbl.CHF_EvalLVS_Denominator,
Core_Measures_tbl.[CHF_ACE/ARB_Numerator],
Core_Measures_tbl.[CHF_ACE/ARB_Denominator],
CMS_90th_percentile.CHF_CMS_90_percentile,
Core_Measures_tbl.[SCIP_Prohph_Abx_w/in_1_hr_to_incision],
Core_Measures_tbl.[SCIP_Prohph_Abx_w/in_1_hr_to_incision_Numerator],
Core_Measures_tbl.[SCIP_Prohph_Abx_w/in_1_hr_to_incision_Denominator],
Core_Measures_tbl.SCIP_Proph_Abx_Selection_Numerator,
Core_Measures_tbl.SCIP_Proph_Abx_Selection_Denominator,
Core_Measures_tbl.SCIP_SurgPts_hair_removal_Numerator,
Core_Measures_tbl.SCIP_SurgPts_hair_removal_Denominator,
Core_Measures_tbl.SCIP_SurgPts_VTEProph_Numerator,
Core_Measures_tbl.SCIP_SurgPts_VTEProph_Denominator,
Core_Measures_tbl.SCIP_SurgPts_BetaBlockers_Numerator,
Core_Measures_tbl.SCIP_SurgPts_BetaBlockers_Denominator,
CMS_90th_percentile.SCIP_CMS_90_Percentile,
Core_Measures_tbl.OUTAMI_Fibrinolytic_30ED_Numerator,
Core_Measures_tbl.OUTAMI_Fibrinolytic_30ED_Denominator,
Core_Measures_tbl.OUTAMI_Aspirin_Arrival_Numerator,
Core_Measures_tbl.OUTAMI_Aspirin_Arrival_Denominator,
Core_Measures_tbl.OUTSCIP_Proph_1hr_incision_Numerator,
Core_Measures_tbl.OUTSCIP_Proph_1hr_incision_Denominator,
Core_Measures_tbl.OUTSCIP_Proph_AbxSelection_Numerator,
Core_Measures_tbl.OUTSCIP_Proph_AbxSelection_Denominator
FROM Core_Measures_tbl LEFT JOIN CMS_90th_percentile ON
Core_Measures_tbl.Quarter = CMS_90th_percentile.Quarter;

 

[Colby]

John,
I actually figured it out... but thanks

John,
When I open the form there is not a new record. The form is for entering
data on physician "core measures" quarerly. It is pullind data that is
directly entered into the table, just not allowing a new (blank) record.
Here is the SQL text, though it looks pretty dang confusing.

SELECT Core_Measures_tbl.Medical_ID, Core_Measures_tbl.Quarter,
Core_Measures_tbl.AMI_Aspirin_at_Arrival_Numerator,
Core_Measures_tbl.AMI_Aspirin_at_Arrival_Denominator,
Core_Measures_tbl.AMI_Aspirin_at_Discharge_Numerator,
Core_Measures_tbl.AMI_Aspirin_at_Discharge_Denominator,
Core_Measures_tbl.[AMI_ACE/ARB_Numerator],
Core_Measures_tbl.[AMI_ACE/ARB_Denominator],
Core_Measures_tbl.AMI_BB_Discharge_Numerator,
Core_Measures_tbl.AMI_BB_Discharge_Denominator,
Core_Measures_tbl.AMI_PCI_90min_Numerator,
Core_Measures_tbl.AMI_PCI_90min_Denominator,
Core_Measures_tbl.AMI_Fibrinolytic_30_Numerator,
Core_Measures_tbl.AMI_Fibrinolytic_30_Denominator,
Core_Measures_tbl.AMI_Mortality_Numerator,
Core_Measures_tbl.AMI_Mortality_Denominator,
CMS_90th_percentile.AMI_CMS_90_percentile,
Core_Measures_tbl.PN_Blood_prior_ABX_Numerator,
Core_Measures_tbl.PN_Blood_prior_ABX_Denominator,
Core_Measures_tbl.PN_Blood_24hr_ICU_Numerator,
Core_Measures_tbl.PN_Blood_24hr_ICU_Denominator,
Core_Measures_tbl.PN_AbxSelection_ICU_Numerator,
Core_Measures_tbl.PN_AbxSelection_ICU_Denominator,
Core_Measures_tbl.PN_AbxSelection_NON_ICU,
Core_Measures_tbl.PN_AbxSelection_NON_ICU_Numerator,
Core_Measures_tbl.PN_AbxSelection_NON_ICU_Denominator,
CMS_90th_percentile.PN_CMS_90_percentile,
Core_Measures_tbl.CHF_EvalLVS_Numerator,
Core_Measures_tbl.CHF_EvalLVS_Denominator,
Core_Measures_tbl.[CHF_ACE/ARB_Numerator],
Core_Measures_tbl.[CHF_ACE/ARB_Denominator],
CMS_90th_percentile.CHF_CMS_90_percentile,
Core_Measures_tbl.[SCIP_Prohph_Abx_w/in_1_hr_to_incision],
Core_Measures_tbl.[SCIP_Prohph_Abx_w/in_1_hr_to_incision_Numerator],
Core_Measures_tbl.[SCIP_Prohph_Abx_w/in_1_hr_to_incision_Denominator],
Core_Measures_tbl.SCIP_Proph_Abx_Selection_Numerator,
Core_Measures_tbl.SCIP_Proph_Abx_Selection_Denominator,
Core_Measures_tbl.SCIP_SurgPts_hair_removal_Numerator,
Core_Measures_tbl.SCIP_SurgPts_hair_removal_Denominator,
Core_Measures_tbl.SCIP_SurgPts_VTEProph_Numerator,
Core_Measures_tbl.SCIP_SurgPts_VTEProph_Denominator,
Core_Measures_tbl.SCIP_SurgPts_BetaBlockers_Numerator,
Core_Measures_tbl.SCIP_SurgPts_BetaBlockers_Denominator,
CMS_90th_percentile.SCIP_CMS_90_Percentile,
Core_Measures_tbl.OUTAMI_Fibrinolytic_30ED_Numerator,
Core_Measures_tbl.OUTAMI_Fibrinolytic_30ED_Denominator,
Core_Measures_tbl.OUTAMI_Aspirin_Arrival_Numerator,
Core_Measures_tbl.OUTAMI_Aspirin_Arrival_Denominator,
Core_Measures_tbl.OUTSCIP_Proph_1hr_incision_Numerator,
Core_Measures_tbl.OUTSCIP_Proph_1hr_incision_Denominator,
Core_Measures_tbl.OUTSCIP_Proph_AbxSelection_Numerator,
Core_Measures_tbl.OUTSCIP_Proph_AbxSelection_Denominator
FROM Core_Measures_tbl LEFT JOIN CMS_90th_percentile ON
Core_Measures_tbl.Quarter = CMS_90th_percentile.Quarter;

Most likely the problem is that the Query that the subform is using as its
recordsource is not updateable. What's the Query? If you open it directly, is
there a new record? You could open the query in SQL view and post the SQL text
here if you can't figure out why it's not accepting new data.

 

[John W. Vinson]

FROM Core_Measures_tbl LEFT JOIN CMS_90th_percentile ON
Core_Measures_tbl.Quarter = CMS_90th_percentile.Quarter;

This is probably the problem: how, if at all, are the tables Core_Mesures_Tbl
and CMS_90th_percentile related in the Relationships window? Is Quarter the
Primary Key of either table?

You are *clearly* "committing spreadsheet" - that's far too many fields for
any properly normalized database, and many of them appear from the fieldnames
to be calculated values. Naturally, calculated values cannot be updated.