asked on

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!

Jeffrey Coachman

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 Coachman

Here is a sample of a basic main subform
Database177.mdb

EASCOA

ASKER

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.
COAStreet-2013-10-23--No-Data---.accdb

Jeffrey Coachman

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

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

JeffCoachman

EASCOA

ASKER

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 Coachman

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.

EASCOA

ASKER

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 Coachman

<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?

EASCOA

ASKER

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 Coachman

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.

EASCOA

ASKER

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.
COAStreet-2013-10-23--No-Data---.accdb

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial