Form automatically adds new record

[David G.]

I have a form that every time I open it a new record gets added to
one of the tables used in the form's record source (which is an
embedded query).

The form is a pop up (though a record gets added regardless of how the
form is opened) and uses values from controls on a main form to fill
unbound combo boxes in the header. The combo box values are used to
filter the record source, and to provide default values for new
records.

tblMaterial is the table that gets the new record. The following 2
fields get values:
tblMaterialID PK autonumber
tblMaterial.tblClassID FK
The remaining fields in tblMaterialID are blank.

The record source SQL:
SELECT tblMaterial.tblClassID, tblMaterial.tblCategoryID,
tblMaterial.tblTypeID, tblMaterial.tblBrandID,
tblSample.tblMaterialID, tblSample.tblSampleID,
tblSample.tblSampleDate, tblSample.tblSampleBatchNumber,
tblSample.tblSampleBatchRev, tblSample.tblSampleTime,
tblSample.tblLocationID, tblSample.tblLineID, tblSample.tblVendorID,
tblSample.tblSampleSKU, tblSample.tblSampleDateCode
FROM tblMaterial INNER JOIN tblSample ON tblMaterial.tblMaterialID =
tblSample.tblMaterialID
WHERE (((tblMaterial.tblClassID)=1) AND
((tblMaterial.tblCategoryID)=[Forms]![frmNewSample]![cboCategoryID])
AND ((tblMaterial.tblBrandID)=[Forms]![frmNewSample]![cboBrandID]) AND
((tblSample.tblMaterialID)=[Forms]![frmNewSample]![cboMaterialID])) OR
(((tblMaterial.tblClassID)=2) AND
((tblMaterial.tblTypeID)=[Forms]![frmNewSample]![cboTypeID]) AND
((tblSample.tblMaterialID)=[Forms]![frmNewSample]![cboMaterialID]))
ORDER BY tblMaterial.tblClassID, tblMaterial.tblCategoryID,
tblMaterial.tblBrandID, tblSample.tblSampleDate,
tblSample.tblSampleTime;
THANKS!
David G.

 

[Keven Denen]

I have a form that every time I open it a new  record gets added to
one of the tables used in the form's record source (which is an
embedded query).

The form is a pop up (though a record gets added regardless of how the
form is opened) and uses values from controls on a main form to fill
unbound combo boxes in the header. The combo box values are used to
filter the record source, and to provide default values for new
records.

tblMaterial is the table that gets the new record. The following 2
fields get values:
        tblMaterialID PK autonumber
        tblMaterial.tblClassID FK
The remaining fields in tblMaterialID are blank.

The record source SQL:
SELECT tblMaterial.tblClassID, tblMaterial.tblCategoryID,
tblMaterial.tblTypeID, tblMaterial.tblBrandID,
tblSample.tblMaterialID, tblSample.tblSampleID,
tblSample.tblSampleDate, tblSample.tblSampleBatchNumber,
tblSample.tblSampleBatchRev, tblSample.tblSampleTime,
tblSample.tblLocationID, tblSample.tblLineID, tblSample.tblVendorID,
tblSample.tblSampleSKU, tblSample.tblSampleDateCode
FROM tblMaterial INNER JOIN tblSample ON tblMaterial.tblMaterialID =
tblSample.tblMaterialID
WHERE (((tblMaterial.tblClassID)=1) AND
((tblMaterial.tblCategoryID)=[Forms]![frmNewSample]![cboCategoryID])
AND ((tblMaterial.tblBrandID)=[Forms]![frmNewSample]![cboBrandID]) AND
((tblSample.tblMaterialID)=[Forms]![frmNewSample]![cboMaterialID])) OR
(((tblMaterial.tblClassID)=2) AND
((tblMaterial.tblTypeID)=[Forms]![frmNewSample]![cboTypeID]) AND
((tblSample.tblMaterialID)=[Forms]![frmNewSample]![cboMaterialID]))
ORDER BY tblMaterial.tblClassID, tblMaterial.tblCategoryID,
tblMaterial.tblBrandID, tblSample.tblSampleDate,
tblSample.tblSampleTime;
THANKS!
David G.

**"and to provide default values for new records."

I'm going to guess that you are seeing these new records when you open
the form in data entry mode but don't enter any information before
closing the form. If this is the case, the statement above is most
likely the cause of the new records showing up. If you fill in default
values in data entry mode, a record gets created even if you don't
enter any other data into the form.

If this isn't your circumstance, please let us know.

Keven Denen

 

[dweeber62]

Keven:
Thanks for your comments.

Setting the default values causes a New Record to be visible (when
displaying continuous forms), but wasn't adding the record to the
table. I had added code to a control's event (located in the header)
that set the value of one of the detail fields. Access naturally
responded to the change in the field as if I was trying to create a
new record.

Removing the code from the Event fixed the problem.

However, I don't like the way default fields cause a New Record form
to appear. I am trying to find a way to set the default values after a
new record is explicitly requested by the user.

I have a form that every time I open it a new  record gets added to
one of the tables used in the form's record source (which is an
embedded query).

The form is a pop up (though a record gets added regardless of how the
form is opened) and uses values from controls on a main form to fill
unbound combo boxes in the header. The combo box values are used to
filter the record source, and to provide default values for new
records.

tblMaterial is the table that gets the new record. The following 2
fields get values:
        tblMaterialID PK autonumber
        tblMaterial.tblClassID FK
The remaining fields in tblMaterialID are blank.

The record source SQL:
SELECT tblMaterial.tblClassID, tblMaterial.tblCategoryID,
tblMaterial.tblTypeID, tblMaterial.tblBrandID,
tblSample.tblMaterialID, tblSample.tblSampleID,
tblSample.tblSampleDate, tblSample.tblSampleBatchNumber,
tblSample.tblSampleBatchRev, tblSample.tblSampleTime,
tblSample.tblLocationID, tblSample.tblLineID, tblSample.tblVendorID,
tblSample.tblSampleSKU, tblSample.tblSampleDateCode
FROM tblMaterial INNER JOIN tblSample ON tblMaterial.tblMaterialID =
tblSample.tblMaterialID
WHERE (((tblMaterial.tblClassID)=1) AND
((tblMaterial.tblCategoryID)=[Forms]![frmNewSample]![cboCategoryID])
AND ((tblMaterial.tblBrandID)=[Forms]![frmNewSample]![cboBrandID]) AND
((tblSample.tblMaterialID)=[Forms]![frmNewSample]![cboMaterialID])) OR
(((tblMaterial.tblClassID)=2) AND
((tblMaterial.tblTypeID)=[Forms]![frmNewSample]![cboTypeID]) AND
((tblSample.tblMaterialID)=[Forms]![frmNewSample]![cboMaterialID]))
ORDER BY tblMaterial.tblClassID, tblMaterial.tblCategoryID,
tblMaterial.tblBrandID, tblSample.tblSampleDate,
tblSample.tblSampleTime;
THANKS!
David G.

**"and to provide default values for new records."

I'm going to guess that you are seeing these new records when you open
the form in data entry mode but don't enter any information before
closing the form. If this is the case, the statement above is most
likely the cause of the new records showing up. If you fill in default
values in data entry mode, a record gets created even if you don't
enter any other data into the form.

If this isn't your circumstance, please let us know.

Keven Denen

Hidi Ho,
The Dweeber