Solved

Access Database

Posted on 2013-10-23
12
161 Views
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 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!
0
Comment
Question by:EASCOA
  • 7
  • 5
12 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Here is a sample of a basic main subform
Database177.mdb
0
 

Author Comment

by:EASCOA
Comment Utility
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
0
 

Author Comment

by:EASCOA
Comment Utility
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?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:EASCOA
Comment Utility
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<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?
0
 

Author Comment

by:EASCOA
Comment Utility
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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.
0
 

Author Comment

by:EASCOA
Comment Utility
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
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
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...

JeffCoachman
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now