Problem with AllowAdditions Property

[vfisher]

My form has the Allow Additions property set to Yes. When I first created the form, everything worked fine. However, after working on the design, record source, code etc, suddenly the form will not allow records to be added. I'm calling the form with:

DoCmd.OpenForm stDocName, , , stLinkCriteria

So the property should not be overridden. This has happened multiple times and I have to go back to a prior version and recopy the code, etc. I know something must be overriding it, but there isn't any code setting it to No. I've made sure of that every time.

Any ideas what could be causing this problem?

Thanks

 

[Rolliee]

Why worry about WHY??? Just do a reset of the value as often as necessary - even at the 'on current' event. In the on current event just say me.control(?).additions = false or true. The question mark is for the identification of that control on the form.

Rollie

 

[vfisher]

Once it happens, I can't ever get it set to true again. The property sheet still has AllowAdditions = yes, and even when I try to set it in the code (I tried in the on current and activate events), it still doesn't allow an add.

 

[krsherm]

Some recordsets (the source of the data that is on your form) are not updateable... and therefore cannot be edited (or added to) REGARDLESS of the settings in your form properties. Why don't you take the recordset and run it as a query, and see if you can update the records in the query (or add to them).

A good idea would be (I seem to mention this in every post... isn't this in the FAQ's? to give us more information. Please show the SQL statement that underlies the recordsource for your form.

Rock ON!

Kevin

 

[Rolliee]

That is correct, Kevin. We can check for upgradeable and debug print it.

Rollie

 

[vfisher]

Thanks for your response. You are right, the recordset is not updatable. I really don't understand why. Here is the SQL code:
SELECT tblPolicyMaster.PolicyNumber, tblPolicyMaster.PolicySuffix, tblPolicyMaster.RecordType, tblPremiumMaster.CompanyCode, tblPremiumMaster.CycleDate, tblCessionsData.CessSeqNbr
FROM tblPolicyMaster LEFT JOIN (tblPremiumMaster LEFT JOIN tblCessionsData ON (tblPremiumMaster.PolicyNumber = tblCessionsData.PolicyNumber) AND (tblPremiumMaster.PolicySuffix = tblCessionsData.PolicySuffix) AND (tblPremiumMaster.RecordType = tblCessionsData.RecordType)) ON (tblPolicyMaster.RecordType = tblPremiumMaster.RecordType) AND (tblPolicyMaster.PolicySuffix = tblPremiumMaster.PolicySuffix) AND (tblPolicyMaster.PolicyNumber = tblPremiumMaster.PolicyNumber);

When I remove the table tblCessionsData, the recordset is updatable and works fine.

 

[krsherm]

Why do you have so many joining elements, when you are only joining 3 tables? Do you have relationships defined between the three tables, with a PrimaryKey/Foreign Key in each?

My guess is that if you properly define your relationships, you should be able to create an updateable recordsource without any problems. I've got recordsources involving substantially more tables that update without a hitch. (Did I just say "without a hitch"?).

Let me know if you have any questions and I'll work with you.

Rock ON!

Kevin

 

[vfisher]

There are multiple fields in the primary key of each table. That was probably a design mistake, but i'm in too deep to turn back now.

While I need data for the screen from all three tables, only two are actually being updated. Is there another way to do this other than having all the tables in the underlying query?

 

[krsherm]

Before we get into the coding necessary to do this, why don't you first try the following:

In the form's properties dialog, set Recordset Type to Dynaset (Inconsistent Updates). Let me know if that works.

As always, ROCK ON!

Kevin

 

[vfisher]

Kevin - First of all, I really appreciate all your help.

The Recordset type was already dynaset. Perhaps I can make this easier. There is really only one table being updated from this form. However, I need to verify data from at least two other tables before I allow adding or editing records. Rather than including those tables in the underlying query, should I just run a separate query in the code. If so, if there some way to do that instead of embedded SQL? Can you execute a query and check the resulting to recordset to see if the record exists?

As you can tell, I'm not proficient in Access coding - but if this was cobol I'd be done by now!

 

[krsherm]

vfisher,

I think you're reading too fast. There are three native recordset types that Access will allow as the basis of your forms:
1. Dynaset
2. Dynaset (Inconsistent Updates)
3. Snapshot

Before we move on, try option 2 and see if it works. Please let me know... if not we'll code.

Kevin