Results 1 to 6 of 6
  1. #1
    winterh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    110

    Need some advice on Forms and Database design

    At the moment I have a number of tables but most of the data is on one table which I have designed a form from, problem I have found is if I change or amend any part of the table the form stops working or askes the user to enter data which is not needed.

    My thinking was to run querys to get the data, for example one for client names and one for products, then build the form using a linked query by doing this I would be able to go into the query to remove the field before I update the table so I hope stop the problem.

    Any help would be great as I would like to get this sorted today.

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Do you have a specific question?

  3. #3
    winterh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    110
    Think so.....is it better to build a form from a query than straight from the table.

  4. #4
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    All the 'data' Forms I've worked on professionally have been built on Tables because that's where the new or modified data on the Form was going to be placed.
    I've always built my data Forms on Tables so that whatever data on the Form is new or changed [edited] can go straight to the Table.

    So - I think you should build your Form on a Table if you are going to be adding Data to the Table
    Once you build your Form, it will expect each of the fields on it to match the data type and specs of the field on which it was based when it was created. If you change the Table after the fact, you could be causing inconsistency problems.

    I'm not sure how complex your data is that you're working with - so I can't say if you need to start inserting data programatically . . . through queries.
    If you can give me some more details, I might be able to make a couple of suggestoins.

    Let us know if you have any more questions.

  5. #5
    winterh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    110
    I have quite a large table and only need a part of it so was thing a query for the data then the form.

    And the other problem I had is when I built the form from the table and removed some of the fields on the table the form stopped working as well, so I was thinking if I build the form from a query if I needed to amend or remove fields from the table I can just amend the query which should keep the form working.

    Is this the best way as I am trying to move from a couple of big tables to a number os smaller ones.

    Thanks

  6. #6
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    1.
    I have quite a large table and only need a part of it so was thing a query for the data then the form.
    I would suggest you still base the Form on the Table if you are going to be using the Form to Add to - or Change - data in the Table.

    2.
    And the other problem I had is when I built the form from the table and removed some of the fields on the table the form stopped working as well, so I was thinking if I build the form from a query if I needed to amend or remove fields from the table I can just amend the query which should keep the form working.
    If you remove fields from the Table, you can also remove them from the Form - no?

    May I ask why you are thinking of splitting your Table into several smaller tables?
    How many records are in your big Table? More than a million?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Getting Advice From Access/Database Consultants
    By ajetrumpet in forum Tutorials
    Replies: 0
    Last Post: 09-21-2011, 06:02 PM
  2. Database Structure Advice
    By Douglasrac in forum Access
    Replies: 9
    Last Post: 07-16-2011, 07:14 PM
  3. Database advice
    By PRINCE SWAGG in forum Access
    Replies: 29
    Last Post: 06-21-2011, 03:56 PM
  4. design advice
    By Sarge, USMC in forum Database Design
    Replies: 4
    Last Post: 10-06-2010, 07:53 AM
  5. Table Design w/ forms
    By DrossZro in forum Database Design
    Replies: 9
    Last Post: 09-07-2010, 09:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums