Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2011-02-22
Medium Priority
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 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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 85
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".


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


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 85
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 85
ID: 35181200
http:#a34957485 resolves the original question. Subsequent followups, regarding proper structure and such, were secondary to the original question.

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

670 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