Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Access Database

Posted on 2013-10-23
Medium Priority
Last Modified: 2013-11-08
I have a  subform that has a primary key-permit number-which I think should be autopopulated when the form is opened for input.  The field is not populating until you try to enter something on the form. (Which may be working as designed)   It doesn't matter what field you start to enter...the primary field will populate BUT a error occurs that displays the following:  You can't assign a value to this object *The object may be a control on a read-only form. *The object may be on a form that is open n Design view. *The value may be too large for this field.  I get the OK and a key is populate and everything is working properly-I can enter several more records without the error. But if I get out of the database and come in later on the first record I get the error again. None of the reasons for the error are valid in this situation---when I open the single form not as a subform I don't get this error at all.  Help---thoughts---suggestions---answers.

Thanks in Advance!
Question by:EASCOA
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39594453
Not sure I understand fully what you are describing here, ...but...
In a standard Main/subform,  ...when you enter a record in the subform, the linking field value from the main form will be inserted into the subform automatically

For example: Customers(main) and Orders(sub)
Create a new order for a customer, and the CustID will populate automatically in the subform.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39594492
Here is a sample of a basic main subform

Author Comment

ID: 39595354
I am attaching the Database...there is lots of stuff out there.  But the initial form is "frmEnterUtilityCutsPermit"  The first field is the "Permit Number" which is the primary key that should auto-populate and the date is populated by default.  Now if you try to enter anything on the form the above mentioned error comes up.  You click OK and the field will be populated and you can proceeded.  But everytime you open the db  and enter for the first time the error will happen.
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39596225
Not sure buddy,
I don't get any errors.

You might check with the person who designed this.
There is a lot there that I don't have time to figure out.
There is no referential integrity established between any of the tables, so it is hard to figure out the relationships.
The main table also has a left join in it's recordsource
A lot of the tables do not have ant records
...etc I would first seek to confirm if this design is optimal for what you are trying to do.
If the design is flawed, then the form will never work smoothly.


Author Comment

ID: 39597708
I'm not sure why you didn't get the error.  It happens when you open the "frmEnterUtilityCutsPermit" for the very first time..and you try to enter any information in any field.  The error pops can click Ok and proceed.  But the next time you enter the database for the first time and does the same procedure the comes up again.  The post confusing thing is when you try to enter the from the subform has a stand along no error occurs.  The subform is "frmUtilityCutPermitEnter".  There is a lot of stuff out there, but currently the main focus is those two forms and this error.  We believe the design to be good.

Thanks for taking the time to help, deeply appreicated.  

Does anyone else have any suggestions or thoughts?
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39597777
I opened and closed the database a few time and no error...

Perhaps you need to be in a certain "Mode" for the error to happen:
A new main form record?, ...a new sub-form record?
new records for both? Blank subform record?

Not sure, but again, because I was not able to follow the relationships, this issue is hard to troubleshoot.

Author Comment

ID: 39597936
And you tried to enter something on the form and no error?  Please don't take offense, but I have to ask are you using Access 2007 and Windows 7?  The error happens on input of a new record.  I appreicate your help and your time, again please don't be offened by my questions.  Error happens for us when we try to enter a new record the first time only.  If you continue the error doesn't happen again, but it will happen if you close the database and come back in again to enter a new record.  Thanks again for your time.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39598017
<Please don't take offense, but I have to ask are you using Access 2007 and Windows 7? >
No offense taken,...

I'll clarify...
At home I am using acc 07 and win XP and I did get the error ...once, ...but after I got the error the first time, it never happened again. (even when I closed and reopened the db.)

Then same thing happened at work with Acc 07 and win 7.

Seems that whatever "Mode" your sample is in, is the mode that triggers the error.
Because after the error occurs once, I never got it again.

This is why I suspect the relationships...
Perhaps once the record is populated, the error does not occur?

Author Comment

ID: 39598201
When you open the database for the first time, no records exist, so the form starts with a new record.  If you enter anything into the form, you get the error.  

When you exit the database and reopen, the previous record is saved.  Modifying that record will not cause it to error again.  You will only get the error again if you close and reopen the database, use the navigation buttons on the subform or the main form to start a new record (the >* button), and then try to enter data into the new record.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39598566
Yep, ok, I can still get the error now..

I am not sure, I still suspect this has something to do with the relationships. or the recordsource for the main form.

If I were you I would create a form with just these two forms (not with all the other subforms on the other tabs)
...and test it out.

Again in a very basic main subform this all works fine.
So I would also create a simple one to many relationship with two simple tables:
Customers and Orders
Then create a main/subform from them, this should work fine.

Your forms should work in the same way.

Author Comment

ID: 39598660
We removed every table, form, and query from the database, except for the main form, the permit subform, and the table that the subform is bound to.  The error still occurs.

Based on some information we found on another forum (, it appears the issue has to do with the linking of the master/child using the autonumber field.  Though the underlying technical specifics still are not clear, it does seem like the problem has to do with the autonumber not being available at the specific moment that the main form is looking for it.  After clicking "ok", the autonumber populates, and all is well.  Not sure where to go from here.
LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 total points
ID: 39598783
Again, this points to the design...
So please verify this first.

Again, it a typical main/subform. the Primary Key value *already exists*, in the main form, so creating child records in the subform is straightforward.

In your case you see like you are wanting to enter Child records before the main record exists.
This is great if the parent record already exists, ...if not, then you get the error.

So the question you must ask the person who designed this form is; why the form is set up in this manner?
When you open that main form, there is no way to add a main form (parent) record first.
So it is confusing that the form lets  you click the new record button for the main form, yet provides no way for the user to easily add the new main form record...?

A simple fix might be to just display the main form fields on the main form...
Then a least the user will see that they must enter a main record first. 9or see that the main record exists and is ready for subform records.

I hate to sound like a broken record, but you really need to verify the basic table design, first before ever worrying about forms or vba code.
...else the form may never really work smoothy

For example this could have all been caught in the design phase.
If you established Referential Integrity between the parent and the child table, then you could have simply tried adding a child record to the child table directly.
You would immediately get a message saying that you cannot add the child record because the main record does not exist yet.

*verify* this design first, else you will always be running into issue like this...


Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question