Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2007
    Posts
    11

    Unanswered: how can i add fields using forms then automatically add it in my queries..

    do you get my question?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you area approaching this the wrong way round

    best approach is to change the table(s), adding new columns there first
    then change any queries as required
    then change any forms or reports as required

    you can change a form first if you wish, but you will still need to change the tables & queries.

    If the data alrady exists, and you merely want to change the underlying query then edit that query
    Last edited by healdem; 01-24-07 at 07:58.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2007
    Posts
    11

    i knew it..

    that means i cant just make a form saying add a new column?

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    You could but it would be a huge amount of work and a complete pain in the backside.

    the idea behind database is that the tables are fixed and don't need to be regularly changed if yours do then you've probably made a mistake in your design though i can't say that for certain without more information.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    the basics would be to open the record-set in VBA for the table you want to add the column. then construct a on the fly "Make table" query that includes your extra column then delete the original table and rename the new one as the old one. if your queries use the select * parameter the new field will be included into the query otherwise you have to read the query's SQL into a VBA string variable edit the string to include the new field and overwrite the query's SQL.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by jor
    that means i cant just make a form saying add a new column?
    you can add as many control as you like on a form. however Access forms can be either bound or unbound. bound means they are linked to a specific column in a data table and will change every time you move to a new record, unbound means that the control holds the value irresepctive of when you move to anew record (unless your code does soemhting with the control).

    so 'just' adding a control means that access doesn't qutomatically add a new element to the underlying data.. that isnt the function of the form designer. If you consoder that Access isnt a database its actually a database front end designe system... and quite a nifty one at that. It can talk to a large number of database systems to allow the Access form designer the ability to add columns to databases would be complex at best. it would also be a nightmare with peolel adding columns willy nilly not taking into account all the relevant paramters that may need to be set (eg null, validation rules, triggers, format rules etc).
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    going back to the original question.
    add a field to a table from a form:sure you can!
    like almost any A-question, the answer is YES. supplementary questions include easy, wise, fast, etc

    currentdb.execute "ALTER TABLE........"

    is it a smart idea? (methinks no!)
    can you discover the syntax? (not easy, not ANSI)
    is it something that you should be doing (your decision)

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Actually the entire original question was:

    "how can i add fields using forms then automatically add it in my queries..."

    The answer is you can't! Not if by automatically you mean that you add the field to the form and Access will do the work of adding it to the table/query for you! Sorry!

    As has already been stated, you've don't have the bull by the horns, but rather by the other end! Not a recommended procedure! But don't feel badly, lots of people make the same mistake at first! It's much more fun designing neat looking forms than sitting down and doing such dreary things as drawing up flow charts and planning table structures.

    And adding a field doesn't have to be a big production! We've all gotten well into developing a db then realized that we forgot to have a field where we can enter the name of the client's prizewinning pet bull! You simply:

    1) Go into the table's Design View and add the field to the table
    2) Go into the query's Design View and add the field to the query
    3) Now, go into the form's Design View and add the control for the field to the form

    Now you can enter the mission-critical fact that the client's prizewinning pet bull is named Daisy!

    Good Luck!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Jan 2007
    Posts
    11
    oww.. now i know.. anyway.. i just decided not to add it.. thanks..

    i hope you all can help me next time

  10. #10
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Quote Originally Posted by Missinglinq
    Actually the entire original question was:

    "how can i add fields using forms then automatically add it in my queries..."

    The answer is you can't! Not if by automatically you mean that you add the field to the form and Access will do the work of adding it to the table/query for you! Sorry!

    As has already been stated, you've don't have the bull by the horns, but rather by the other end! Not a recommended procedure! But don't feel badly, lots of people make the same mistake at first! It's much more fun designing neat looking forms than sitting down and doing such dreary things as drawing up flow charts and planning table structures.

    And adding a field doesn't have to be a big production! We've all gotten well into developing a db then realized that we forgot to have a field where we can enter the name of the client's prizewinning pet bull! You simply:

    1) Go into the table's Design View and add the field to the table
    2) Go into the query's Design View and add the field to the query
    3) Now, go into the form's Design View and add the control for the field to the form

    Now you can enter the mission-critical fact that the client's prizewinning pet bull is named Daisy!

    Good Luck!
    not quite as i mentioned you can do it but it's a pain in the posterior and a bad idea to boot.

    Can you do it? Yes
    Should you do it? 99% of the time no

    you should do it the way you mention
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •