Sub form error

[expublish]

Hi,

I have a main form with two subforms. Details are entered on the main form, then options selected from sub form 1 and sub form 2. In the subforms (shown in datasheet view) when I try and select a value from a combo (only 1 field) I get a message "This property is read only and can't be set". I have looked through the properties for the sub form and the field itself and cannot find anything about read only. How do I made it 'readable'?

I have checked the locked property which is set to no and allow editions and additions are set to yes and data entry is set to yes.

But I still can't work out what is wrong. Can anyone help?

TIA

Scott.

 

[David R]

Make sure the Row Source and the Control Source for the field are correct. It sounds like somehow you have it looking up to a locked field and so it gets confused when you try to change it.


[This message has been edited by David R (edited 05-23-2002).]

 

[expublish]

Here is the structure:

Tables

Personal(Name(k),Address, etc)
Jobs(Job Title(k))
Unit(Unit Name(k))
Name_Unit_Job(ID(k),Name(lookup),Job Title(lookup),Unit Name(lookup))

I have a form based on Personal with all fields included.

Within this there are two subforms.

1)Has a single field of Unit Name. Record source is table Name_Unit_Job and the control source of the field is Unit Name. The row source looks up Unit Name from table Unit.

2) This is the same as 1 except using Job Title from Job instead of Unit Name from Unit.

From what I can see I am looking up values from tables Unit and Job and storing them in the link table Name_Unit_Job. Is this right?

When I view the subforms on their own (not in the main form) they both work OK. I think it must be something to do with how they are inserted into the main form. The Parent/Child links are correct.

Any ideas?

Scott

- I can send you it by email if it would make it easier?

 

[David R]

I misspoke earlier and said record source (a form-level property) when I should have said Control Source. I apologize if that confused you.

Personal(Name(k),Address, etc)
Jobs(Job Title(k))
Unit(Unit Name(k))
Name_Unit_Job(ID(k),Name(lookup),Job Title(lookup),Unit Name(lookup))

If I understand correctly, then the Unit subform should have a row source of fields from table Unit (Unit.Unit_Name?) and control source of Name_Unit_Job.Unit_Name. The Job subform should have a rowsource from table Jobs and control source of Name_Unit_Job.Job_Title.

See if that helps.
David R

[This message has been edited by David R (edited 05-23-2002).]

 

[expublish]

David,

My form properties are already as you explain, sorry if also misspoke in my forst post.

Anymore ideas?

E-mail me if you can have a look at the actual DB.

Scott.

 

[expublish]

I have even tried completely re-making the forms with and without the wizard but it does the same. This is really annoying me now.

Anyone out there help???

Scott.

 

[expublish]

OK

this is the last time i am going to push this thread back to the top. if anyone can help i can email them the db.

anyone???

 

[David R]

I do not understand what could be wrong with your structure, so try emailing it to me if it is fairly small. Compact your database and place it in Acc2k format if you please.

 

[expublish]

Thanks David, it is on its way to you - compacted, I use 2K anyway.

Scott.

 

[David R]

Scott, there is no easy way to say this.

Your structure was highly irregular, and I'm not sure if you just confused yourself or actually have not taken a database design course or read a book on it yet. A few tips:

1) "Name" is a reserved field name, and should not be used. FirstName, LastName are better because a) they break up the parts of the name into separate fields for later use, and b) they avoid the reserved word problem.
2) The table Name_Unit_Job had ID as an Autonumber, but linked to the main table on the field Name. The main table had no usable Primary Key. Use PersonalID instead, and include PersonalID in the joining table as a Long Integer, not an Autonumber (the types are compatible).
3) Your lookup tables Units and Jobs had no ID field, instead looking up to the field itself. Put in a UnitID field and a JobID field and link to those with Name_Unit_Job.
4) Referential Integrity and Cascade Update/Delete was not enforced in any of your table relationships. Go to Tools>Relationships to set these.
5) If a person really can have multiple job titles and multiple units, are these related? In other words, does a certain Job Title go with a certain Unit assignment? Or are they independent? If they're independent, you'll need two joining tables: Personal_Unit and Personal_Job. If they're linked, and linked in such a way that one defines the other, consider only storing one of them. This would only require one subform as well.
6) You still had fields Unit 1, Unit 2, Unit 3, Job 1, Job 2, Job 3, etc. in your Personal table. I assume these were from an original setup and were going to be deleted. However you also have three Address fields. Are these the three lines of your address, or separate addresses?
7) Check your field lengths and input masks for things like postal codes and telephone numbers. If you don't know what I mean, look them up in Access help.
8) Please read up on database normalization and naming conventions. You'll save yourself an incredible amount of hassle if you can design things from the ground up in an efficient way. If someone's given you the task of designing a complex database when it was not in your job skills, you need to be up-front with them about your need for training. I'm not trying to be cruel, I was in the SAME position as you 10 months ago. However I took classes and read up and now have a working understanding of relational database design (though the gods know I could use more some days).

I'll email you the changes I made, but be aware that they are piecemeal and not a total solution. Hopefully it will give you an idea of what went wrong in some places, however.
Good luck,
David R