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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.


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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

820 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