Improve company productivity with a Business Account.Sign Up

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

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


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
  • 4
  • 3
1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

martingoAuthor Commented:

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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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".

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

martingoAuthor Commented:
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?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

martingoAuthor Commented:
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
http:#a34957485 resolves the original question. Subsequent followups, regarding proper structure and such, were secondary to the original question.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now