add new table field via form

[eve2eva]

Is it possible to add field into a table via form??
if could, How??

[FishVal]

Is it possible to add field into a table via form??
if could, How??

You could, but what for?

Kind regards,
Fish

[NeoPa]

From Access select Help / MS Access Help.
Search for "Alter Table".
As Fish says though, why would you want to? I would almost go as far as to say that wanting to implies that you've got something wrong somewhere else. Possibly not - but the indications are strong - be careful.

[jaxjagfan]

Please gives us some details on what you are trying to accomplish.

As the others have stated - it can be done but most of time (especially if dealing with endusers) your dataset and application would benefit greatly having a predefined dataset.

Do you need to add a field or do you want to make a textbox or other control visible when an action occurs?

[eve2eva]

the red highlight is the programme field in my databse. If the user want to add new field by just click at the form, then how to do it.. Or maybe my table design is wrong??..

what is table alter anyway?

[eve2eva]

Do anyone have any suggestion on how to improve my table design..

[eve2eva]

From Access select Help / MS Access Help.
Search for "Alter Table".
As Fish says though, why would you want to? I would almost go as far as to say that wanting to implies that you've got something wrong somewhere else. Possibly not - but the indications are strong - be careful.

How to use the Alter Table??
NeoPa, can you give any code sample??
TQ..

[NeoPa]

We need you to answer the questions before we can be of any further help I'm afraid. I appreciate that you've posted a helpful picture of what's in the table, but without some explanation of what it's for and what it means then we're still very much in the dark.

From what I can guess though, adding fields to your table design seems like a very bad idea. You would be creating an awfully complicated system to try to maintain. I'm afraid I can offer little help without a better explanation of what you're trying to implement though.

[mshmyob]

I don't know why Eve2Eva wants users to change a table design after it has been distributed to the end users but I think there are times when it might be done.

For instance and correct me if I am wrong but in CRM's like Outlook you can add new custom fields (I use this term losely). I don't know if these are real fields added to the database itself. I just used Outlook as an example because Goldmine also allows you to do it.

[FishVal]

Hello, eve2eva.

You have a dilemma here.
On the one hand your table structure should be normalized.
On the other hand in Access there is no native mechanism of modifying cross-tab data.

Just to got some ideas you may read the following thread.
Crosstab Behaviour required for Data Input

Regards,
Fish

[missinglinq]

You're trying, as John Vinson says, "to commit Spreadsheet by Access!" In relational databases you add data by adding rows not columns! You would be, in point of fact, turning over the design and development of your database to the end users. Users adding fields! What datatype? What kind of formatting? How do the new fields interact with the other data? Of course, after making a hash of it, the same end users would expect you to come in and clean up the mess!

Welcome to TheScripts!

Linq ;0)>

[eve2eva]

The red highlight is the programme field. there is about 9 programme field in that table with same data type as "yes/no". My problem is, If the End User wants to add another programme, a new programme. The End User just adding the new programme from the form, not as a record, but as a field in the table. The new field formating and data type will be the same as the other 9 field that I highlighted..

Fish, do you mean that I should normalize the 9 programme field and create another table??

Or

should I used multivalue field to store the data??.. do access 2k3 support multivalue field like access 2k7??

[NeoPa]

I tried to explain before but I don't get the feeling I got through.
Storing data whose definitions change is not a good idea. Changing the structure of a database is something that should only be done with careful consideration. Changing data is fine, but if such things are likely to change then the structure should be managed within the data. If there are extra categories for instance, they should be designed to fit in the data - as per a category table. Not added on the fly into the structure of the database itself.

[mshmyob]

What you need to do is put a bridge table in your design. That way you don't ever change the structure and users can add as many programmes as they wish.

The red highlight is the programme field. there is about 9 programme field in that table with same data type as "yes/no". My problem is, If the End User wants to add another programme, a new programme. The End User just adding the new programme from the form, not as a record, but as a field in the table. The new field formating and data type will be the same as the other 9 field that I highlighted..

Fish, do you mean that I should normalize the 9 programme field and create another table??

Or

should I used multivalue field to store the data??.. do access 2k3 support multivalue field like access 2k7??

[missinglinq]

And no, Access 2k3 doesn't support multivalue field like Access 2k7.

Linq

[mshmyob]

As Linq said only Acc2007 supports multi value fields. I would NEVER use multi value fields because it is improper design and you cannot port your database to another backend (like MySQL) without changing the whole design. Always use a bridge table when you have a many-many relationship and you can use your design with any database program in the future.

[eve2eva]

Then It should be like this??..

When I make a query, It display multiple same data..then how I gonna make 1 subjName to display many programme in Query or Form..

There are 2 Color study displaying in my Query with diff progCode..
How to make it just like the one that I highlighted in my previous table Image??

[mshmyob]

Your design doesn't look right.

Why is subName being replicated in 2 different tables? That is a NO-NO in database design.

What exactly is the tblArt table?

Could you explain your scenario and I will try to help you redesign it.

Also when it is correct you can display it properly with a form/subform design.

Then It should be like this??..

When I make a query, It display multiple same data..then how I gonna make 1 subjName to display many programme in Query or Form..

There are 2 Color study displaying in my Query with diff progCode..
How to make it just like the one that I highlighted in my previous table Image??

[eve2eva]

I has edit the tblSubject and change the subjName to subjCodeName.

My database is about Course Code & Couese Field for Educational Programmes.

tblART is a subject table. There is about 21 Subject, mean I have to create 20 more table such like the tblART. The objective is, the user want to know the courseCode of the subject. The courseCode is combination of SubjCode, codeNo, serialNo and creditHour and Programme that handle the subject. The user also want to select the programme by just select it in the check box(frmART*)..

tblART*

frmART*

In the red box is the programme. the programme is fix, I cannot add any more programme, because it a table field. I'm asking "Is It possible to add new table field via form".. Many says that I can, But I dunno how..I do like u said, use a bridge table.. but In my qryART it display 2 Subject with same name but diff programme(Previous post*), and how do I display the programme just like the above Image..I just want to make It display the subject with many programme in my form..btw, this is my first database, could you helping me..??

let me know if you want more info.. TQ

[mshmyob]

Looking at your design and by your explanation of what you want to do I figure your design is a bit mixed up.

First thing you want to do is use proper naming convention to make life easier. Your table names should reflect the entity they represent such as.

tblSubject
tblCourse
tblProgramme
tblSubjectDetail

tblArt does not describe anything.

Second your field names should then correspond to your table names. Such as
Subject_ID
Subject_Name

Course_ID
Course_Name

Programme_ID
Programme_Name

I think you need 4 tables to do what you want.

I assume your business rules are as follows:

Each SUBJECTcan have many COURSES
Each COURSE can have 1 SUBJECT

Each SUBJECT can have many PROGRAMMES
Each PROGRAMME can have many SUBJECTS

Therefore you need only 4 tables

tblProgramme links to tblSubjectDetail
tblSubjectDetail links to tblSubject
tblSubject links to tblCourse

tblProgramme has Programme_ID as the primary key
tblSubject has Subject_ID as the primary key
tblCourse has Course_ID as the primary key
tblSubjectDetail is the bridge table so it has Programme_ID and Subject_ID as the primary key.

Credits should have nothing to do with the primary key and just be a field in Detail table or the subject table depending on your business rules.

I has edit the tblSubject and change the subjName to subjCodeName.

My database is about Course Code & Couese Field for Educational Programmes.

tblART is a subject table. There is about 21 Subject, mean I have to create 20 more table such like the tblART. The objective is, the user want to know the courseCode of the subject. The courseCode is combination of SubjCode, codeNo, serialNo and creditHour and Programme that handle the subject. The user also want to select the programme by just select it in the check box(frmART*)..

tblART*

frmART*

In the red box is the programme. the programme is fix, I cannot add any more programme, because it a table field. I'm asking "Is It possible to add new table field via form".. Many says that I can, But I dunno how..I do like u said, use a bridge table.. but In my qryART it display 2 Subject with same name but diff programme(Previous post*), and how do I display the programme just like the above Image..I just want to make It display the subject with many programme in my form..btw, this is my first database, could you helping me..??

let me know if you want more info.. TQ

[NeoPa]

In case you're wondering if this is just a personal opinion - It's not. Mshmyob has laid out in fair detail how your table structure should be set up.
If you're interested in learning the concepts that this illustrates you could do worse than to check out Normalisation and Table structures. It always helps to have a clearer understanding of what you're doing and why.
Good luck with your project.

[eve2eva]

If I put all the subject in 1 table, dont you think it could be mess up.. because the serialNo cannot be same on each Course.

Example

In Course ART(art & Design) there is 9 subject, which mean the serial is from 01 to 09 ans so on..In Course BUS(Business) there is 7 subject so I must start with 01 to 07 and so on..So if all the subject data is in 1 table, maybe it could have a dupplicate serialNo..and in my form, I use VBA to auto increase the value after a cmdButton is being click. So it will search the most highest value to +1..If I want to add a subject in Course BUS it will find that 09 is the highest value in that table so My new subject for Course BUS will be 10 not 08 right..

-------------------------------

Is this diff,

Each Course can have Many Subject
Each Subject can have 1 Course

With

Each Subject can have Many Course
Each Course can have 1 Subject

On my Idea, 1 Course can have Many subject. Example, for subject "Color Study", can only have at Course ART (Art & Design) and cannot have at Other Course such as Course BUS(Business), MKT(Marketing) and so on..

Please Correct me if I'm wrong and my lack of understanding..TQ for the reply..

[mshmyob]

I have created the database for you and a form to show how to use subforms so that when you pick a subject the programmes associated with that subject only show up. You can also add new programmes for a specific subject like you want without modifying the database structure.

[eve2eva]

That helps a lot..Thanks.. Now I can start my project..

Thanks everyone..

[mshmyob]

Good Luck.......................

That helps a lot..Thanks.. Now I can start my project..

Thanks everyone..