error message when adding new record

[muffinimal]

Hi,

I get an error message when I add a new record to my database. It occurs when I open my form and use the [arrowright*]-button and when I use the switchboard button to add a new record using a form.

An error message is displayed, but when I dismiss the error (click OK), then everything seems to be normal and the record is added normally with the entered info. It is a bit annoying, though.

Since I have the dutch version, I'll try to translate the message:
"You can not add/append a value to this object

* The object possibly is a control-element in a read-only form
* The object is part of a form that is opened in the design view
* The value is too large for this field"

The error occurs right after I have typed the first character into a field on the form. The form consists of a few subforms. And I don't think any of the by access suggested errors have anything to do with this...


Any help is appreciated,
Thanks in advance,
MuFfiNimal

 

[MStef]

Hello muffinimal!

Check that field, I think you try to fill it up via VBA, and via CONTROL SOURCE properties. Only one is enough.

 

[muffinimal]

Thanks, MStef,

But I'm afraid your guess wasn't right. I did check the fields however. Nothing strange...

The thing is: I get it with all the fields on the form, not just one...

 

[muffinimal]

I figured out what is causing the problem. I am not closer to finding a solution however ;-(

The problem is staggeringly simple. I am very sure I am overlooking some silly thing, but any help out of my misery is appreciated.

The problem:
- 1 subform contains some fields (in which de data is to be entered)
- 1 form contains this subform. (the forms are linked so that I can use the recordchooser (not sure how they are called. Those nice navigational buttons <<,<,[1],>,>>,>*)

I use the recordchooser-buttons to navigate through the records. No problem. But when I try to enter a new record on the subform I get the error message as described in the post above. When I do the very same thing on the subform (so, when it is not a subform (and when I enable the record chooser buttons), no problem arises.

Anyone any thoughts? Anyone?

 

[muffinimal]

Bump!

Sorry to bump this so soon, but the post had gone to the second page so soon and I really, really want this solved before the weekend. I'll do anything for a reply. Buy a random person flowers, smile at everyone until all my facial muscles ache and keep on smiling, I'll erm... erm... Anything. You name it and I'll make the world a better place. Oh... And I would like it if the reply would solve my problem as well ;-)

 

[ScottGem]

How is the mainform and subform linked? You problem is clearly an issue of how they are linked.

 

[muffinimal]

I hope you are on this something here, but I can't think of another way to link these forms:
- as both the mainForm and the subForm contain info about the same table, the linking between the two is a 'id' linked to 'id'.

Is this what you wanted to know?

 

[ScottGem]

Can you be more detailed? Are both linking fields primary keys? What is the relation between the 2 tables? Are the linking fields the same datatype?

 

[muffinimal]

Can you be more detailed? Are both linking fields primary keys? What is the relation between the 2 tables? Are the linking fields the same datatype?

Ah, I didn't tell that yet... Sorry.

My main form is a form where loads of other forms are put into. One of these subforms contains the data of the main table (that would be the only one with an auto-incrementing primary key). All the other subforms link to this with a primary key.

I hope this clears things up a little.

So... To come back to my problem:

• I have a form (let's call it formMain) which is bound to a table (let's call it tblClient)
• This form does not need to be bound since it only contains subforms, but I have bound it nonetheless because of the header which shows the user which client is dealt while he is entering data in the form.
• I have several subforms, but to recreate the problem you only need one, so I will only bother with just one (let's call it frmClient). This one is bound to the same table as formMain, tblClient. The main form and the subform are both linked to each other with 'id' (which happens to be the same (primary) key).

I hope I am not leaving out important stuff this time. Feel free to ask for a better description.


Thanks in advance,
MuFfiN

 

[ScottGem]

Ok, That's your problem. I would not use a form as a shell for subforms, especially an unbound one. That's not the purpose of subforms.

Secondly, I would not use 'ID' as the name of a primary key. Your keys should be named so that when used as a foreign key it will be easy to figure what table they relate to. iIf you have tblClient, then use ClientID as your PK.

Thirdly, If you have the main form bound to tblClient, then put your controls bound to fields in tblclient on the main form, not a subform. If you have a subform also bound to tblclient and linked on ClientID then you will not be able to add new records in the subform since a corresponding record doesn't exist in the main form.

 

[muffinimal]

Hi again ScottGem,

Thanks for taking the time for answering my questions. I really appreciate it!

Ok, That's your problem. I would not use a form as a shell for subforms, especially an unbound one. That's not the purpose of subforms.

Hmm... Okay... I remember reading something like this somewhere on the forum. My mainForm would have loads of tabs (A nesting of 3-4). I thought that the use of subForms would make the lot easier to maintain and to re-use... You think I would have done better to incorporate the lot (about 15-subforms (some of which have continous subforms)) into one?

Secondly, I would not use 'ID' as the name of a primary key. Your keys should be named so that when used as a foreign key it will be easy to figure what table they relate to. iIf you have tblClient, then use ClientID as your PK.

Yeah, I have noticed that Access isn't very friendly about saying which is which. I have always used id as a pk for a table since it is obvious that the name 'id' in table 'tableX' is THE id of 'tableX'. It has worked great for me using mySql... Sadly Access has a rather nasty way of (not) showing names, which makes it necessary for people to use awkward names like tableXID (imho).

Thirdly, If you have the main form bound to tblClient, then put your controls bound to fields in tblclient on the main form, not a subform. If you have a subform also bound to tblclient and linked on ClientID then you will not be able to add new records in the subform since a corresponding record doesn't exist in the main form.

Ah, it's as I feared then ;-( Too bad. What exactly do you think forms are for, btw?

I have applied a rather elegant workaround to the problem, with which I am rather happy. The user first has to add a client. And then later on he can add all the extra info. However, I am looking forward to your views on your first point.


Thanks a lot,
Muffinimal

 

[ScottGem]

No, I would use separate forms. I don't see much of a value in using one form as a shell.

I don't think its awkward. Access can show the names when necessary. But the key is not within the same table. Its not a problem in a single table to just call the PK ID. But when you want to use the PK as an FK, iy makes it easier to know what table its from.

Subforms are for displaying recrods from relating tables.

 

[muffinimal]

No, I would use separate forms. I don't see much of a value in using one form as a shell.

So... You agree with me using subforms? I guess not?

I don't think its awkward. Access can show the names when necessary. But the key is not within the same table. Its not a problem in a single table to just call the PK ID. But when you want to use the PK as an FK, iy makes it easier to know what table its from.

I would much rather refer to the right id by using consistent names. So, 'id' for a pk in a table and using names with an underscore(_) to show links between tables. It really is a very useful convention if all people you are working with stick to it. I've been doing it for years without a single problem. It can be confusing in the beginning, but it'll pay off. I have little use for a way of easier knowing. I just want it to be clear.

I am curious to know what you mean by Access showing names when necessary. The thing I am bugged about most is Access making me confuse table-stuff with form-stuff.

Subforms are for displaying recrods from relating tables.

Ah, ok. Well... I am doing that for the most part. All my subforms are forms for data in related tables. Except for the first record... There some general info about the mother of all records is shown and edited... I think I need to do some thinking about a way to get the data from the subform into the main-form and let the subs have all the subs... That shouldn't be much of a fuss... I hope...


Thanks again!
MuFfiNimal

 

[ScottGem]

I am curious to know what you mean by Access showing names when necessary. The thing I am bugged about most is Access making me confuse table-stuff with form-stuff.
Thanks again!
MuFfiNimal

I don't find this confusing. I find it a necessary issue due to the fact that a form and a table are different objects. You don't have fields on forms, you have controls. A control is a separate and different object used for managing forms. A control may or may not be bound to a field in a table. Therefor a form has its own properties including its own name. There are naming conventions to prefix controls to show what they do. If as control is bound to a field, the Controlsource will tell you what field.

As for the PK naming. If you name your FKs tablename_ID you are doing a similar thing to what I've said. I just find using the same naming for both the PK and the FK is visually easier to follow.

If all the subforms are bound to children of the table in the main form, then putting them on one form can make sense. But if they aren't, then the form becomes cumbersome.

 

[muffinimal]

I don't find this confusing. I find it a necessary issue due to the fact that a form and a table are different objects. You don't have fields on forms, you have controls. A control is a separate and different object used for managing forms. A control may or may not be bound to a field in a table. Therefor a form has its own properties including its own name. There are naming conventions to prefix controls to show what they do. If as control is bound to a field, the Controlsource will tell you what field.

As for the PK naming. If you name your FKs tablename_ID you are doing a similar thing to what I've said. I just find using the same naming for both the PK and the FK is visually easier to follow.

If all the subforms are bound to children of the table in the main form, then putting them on one form can make sense. But if they aren't, then the form becomes cumbersome.

Ok... I know what you mean. I might be a matter of taste or it is just me getting used to the fact that Access has both the database and the frontend built into it. For my next project I'll try a new naming scheme...

Phew... All my subforms are bound to the very same table of the children of the main form. I'm glad I did something right ;-)


Thanks again!

 

[llkhoutx]

I get a similar error when posting to a subform when the parent form (and Master Link Field) of the parent form has not been populated.

 

[muffinimal]

I get a similar error when posting to a subform when the parent form (and Master Link Field) of the parent form has not been populated.

Hi,

My mail told me there was a reply to this message, so I thought I'd check it out. I am no longer frequenting these forums and my access-knowledge still is still very substandard compared to most people around here.

I am afraid I can't help you. Judging from my post (about the elegant solution) and from tunneling into the dark reaches of my memory, I think my solution was a workaround: first populate the parent form: I (probably) did so by getting some rudimentary information on a preceding form.