Can't assign value to this object (driving me mad)

Posted on 2011-02-22
Last Modified: 2012-05-11

I have created a database which is turning to quite a complex project (based on my Access skills).

I kind of know where the error is coming from but have no idea how to get rid of it...

I have tabs which contain subforms which are linked to a form containing tabs with ClientID. The idea is to navigate through all forms from the main form containing tabs. Whenever new data is entered into one of the fields I keep getting "You can't assign value to this object" error. The autonumber for the subform (frmTable1) is created but not for the main form (frmForm1). I can then enter the data (say record 3) which is then no longer visible when I go to record 4 and then back to record 3. When I close Form1 and reopen the record appears with no problems. It happens all over again on a new record entry.

It appears that the Form1's ClientID field is not refreshed automatically when the new ClientID is created in the subform.

I wonder if there is any workaround or did I completely get it wrong with designing the tabs?

I have replicated the error with a simple database created in MS Access 2010 (attached) cant-assign.accdb
Question by:martingo
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
  • 4
  • 3
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 34957485
Your MainForm and your "Table1" subform are based on the same table. Did you mean to do this? In general, you should not have a Mainform and Subform based on the same table (and there's really no reason to do so). That's most likely where your error is coming from.

In general, Forms and Subforms should represent a "Master/Detail" sort of setup - sort of like an Invoice (the "Master") which can contain multiple LineItems (the "Detail" portion). In your case, you have a "master-master" relationship - that's not generally going to work.

What sort of data are you storing? If you can explain a bit more about your program and what you're doing we might be able to get more specific. As it stands - your forms are built incorrectly, given the limited amount of information we have at this point.


Author Comment

ID: 34958551

I am designing a database which will hold quite a lot of client info relating to insurance policies, dates etc. I have created couple tables holding seperate lots of info grouped in various areas. What I am actually trying to achieve is to have one form with tabs which is controlled by record selectors. Because of this obstacle I have decided to link tabs with the ClientID field (because that was the only solution that I figured out).

The idea is for the default tab to show client name, address, contact details etc. 2nd tab would contain income details, 3rd tab would contain a subform with another lot of tabs with different types of products.

I have attached my database which is still in development and hopefully will give you an idea of what I am trying to achieve.

Any ideas how to simplify it or a workaround relating to the error I am getting are welcome.
 DB.accdb DB.accdb
LVL 84
ID: 34959924
Your tables are not properly designed, and you're not storing data properly.

For example, your tblClients has 5 fields named "Kids1Age" through "Kids5Age". It has "Smoker1" and "Smoker2", "CB1" and "CB2", etc etc. Anytime you see data stored in this manner, you're dealing with denormalized data, and you must first fix this before you can move forward.

In general, a table should maintain the data for a SINGLE entity. In your case, it would seem that you have several of those:


Sine you need to capture data regarding members of a Family, your "Clients" table could further be broken down into several subtables:

etc etc

lnRelationship (i.e spouse, child, etc)
etc etc

Note that prefixs "ln" denote a Long Numeric value, "st" denotes a String value, and "bn" denotes a Boolean value.

Using this simple table structure, you could store any number of Dependents for a single Client.

You'd also have a "Join" table for Products, assuming your Clients can purchase more than one Product from you:

etc etc

etc etc

The above table is a "Join" table. It allows a SINGLE entity (i.e. your Client) to be related to MULTIPLE items (i.e. your Products). It also allows multiple ITEMS to be related to multiple CLIENTS. This is known as a Many-to-Many Join, and is the correct way to model these sorts of relatioships.

REgarding your Advisers - I"m not sure how this relates to the Client or Product, so it's difficult to say how it should be represented. If a Client can be related to ONE and ONLY ONE Adviser, then you can store the AdviserID in the Client table. If a Client can be handled by more than one Adviser (and you need to manage and store those relationships), then you'll again have to setup a JOIN table between Client and Adviser.

You've got some work ahead of you. Before moving forward, I'd encourage you to take a little time and review the sample databases on the Office website, and to do some reading on "database normalization". One of the best books on the subject is "Database Design for Mere Mortals".

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!


Author Comment

ID: 34963587
Hi many thanks for your comments. I guess the most important question is if the database is redesigned as per your post, would it actually get rid of my problem?
LVL 84
ID: 34969776
Yes, I'm confident that it would. Proper structure is a very important step in working with any database application. While you could force the issue (through unbound forms or temporary tables), you will be far better served if you get the design correct. Proper database design can be a difficult thing to grasp, and what works for one application may not be suitable for others.

One of the best things you can do is review the strucutre of working, well-built applications. There are many samples on the Office website, and of course there are a LOT of resources regarding normalization on the internet.

Sit down with pen and paper (away from the computer) and write out what your application is intended to do. Treat it as if you have product that your are selling to someone and NOT a dissertation of how the UI should work, or how the tables should be structured (think "marketing"). This should take 2 - 6 paragraphs, after which you'll generally have a good idea of what major Entities you'll need - like your Customer, for example, or your Product. Once you have those, work up a small flowchart defining how data will move through the system (i.e. "User enters new customer", "User enters family members", etc etc). As you work through design practices like this, you'll come up with a lot of questions regarding, which is what we can really help with.


Author Comment

ID: 34989338
OK I have now begun to redesigning all tables. The only thing is I truggle to come up with an idea on what would the proper design be with regards to linking all the tabs to the same client. Preferably I want the tabs to take most of the screen's place as there will be quite  alot of client related data.
Any suggestions on how it should be done properly please? It would be much appreciated.
LVL 84
ID: 35181200
http:#a34957485 resolves the original question. Subsequent followups, regarding proper structure and such, were secondary to the original question.

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 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