Solved

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

Posted on 2011-02-22
9
652 Views
Last Modified: 2012-05-11
Hi

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
0
Comment
Question by:martingo
  • 4
  • 3
9 Comments
 
LVL 84

Accepted Solution

by:
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.

0
 

Author Comment

by:martingo
ID: 34958551
Hi

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
0
 
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:

Clients
Products
Advisors
Illnesses
Providers

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

tClient
--------------
lnClientID
stClientFName
stClientLastName
stClientAddress
etc etc

tClient_Dependants
---------------------------
lnDependID
lnClientID
stDepFName
stDepLName
lnRelationship (i.e spouse, child, etc)
lnOccupation
bnSmoker
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:

tProducts
-----------
lnProdID
stProdName
etc etc

tClient_Products
-----------------------
lnClientProdsID
lnClientID
lnProdID
dtDatePurchased
dbAmount
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".



0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:martingo
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?
0
 
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.

0
 

Author Comment

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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

744 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

11 Experts available now in Live!

Get 1:1 Help Now