• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

Access Database

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 error..click 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!
  • 7
  • 5
1 Solution
Jeffrey CoachmanMIS LiasonCommented:
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.
Jeffrey CoachmanMIS LiasonCommented:
Here is a sample of a basic main subform
EASCOAAuthor Commented:
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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Jeffrey CoachmanMIS LiasonCommented:
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

...so 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.

EASCOAAuthor Commented:
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 up...you 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?
Jeffrey CoachmanMIS LiasonCommented:
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.
EASCOAAuthor Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
<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?
EASCOAAuthor Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
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.
EASCOAAuthor Commented:
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 (http://www.access-programmers.co.uk/forums/showthread.php?t=244148), 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.
Jeffrey CoachmanMIS LiasonCommented:
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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now