Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2008

    Unanswered: form issues in access

    Not sure if this is the right forum but here goes, I am a newbee trying to create a form from multiple tables in access 2003, I have a couple of issues. The wizard allows me to create the form but wont allow me to enter info into all the fields some yes others no????? HELP

  2. #2
    Join Date
    Nov 2007
    Adelaide, South Australia
    Post up your structure and relationships.

    We can't help you unless you get a touch more specific. It may or may not be possible to do what you want, but without more details we can't tell.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Dec 2004
    Madison, WI
    If you're trying to base 1 form off of multiple tables (ie. joining the multiple tables together relationally), this may not be the best approach. If you try to base the 1 form off of multiple tables (not joined together), you won't be able to update some fields and it isn't a good approach (and will give you problems). You never want to base 1 form off of multiple tables not joined together and usually don't want to base 1 form off of multiple tables which are joined together.

    Usually you have 1 form based off of 1 table and create a subform (or popup form) based on another table (again, where the 2 tables are relationally joined). For example, FormA is based off of TableA. SubformA within FormA is based off of TableASub where TableA and TableASub are joined together by a field. You will notice when you look at the design properties of SubFormA in FormA that there is a link field property which is where you tell it what field to join on between the 2 tables.
    Last edited by pkstormy; 01-13-08 at 19:43.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Jan 2008

    DB help

    Not sure exactly what you want me to quote so here goes, tbl customers-first name,last naame,address,city,prov,postal,notes...tbl product-artist name,print title,print number...tbl order-invoice#,date...tbl employee-sales rep
    one employee many customers
    one customer many orders
    one order many prints.
    I am just trying to track customers to make future contacts easier and would like to create a single form to enter all the info taking the fields right off an invoice.

    Quote Originally Posted by StarTrekker
    Post up your structure and relationships.

    We can't help you unless you get a touch more specific. It may or may not be possible to do what you want, but without more details we can't tell.

  5. #5
    Join Date
    Jan 2008

    Describe the relationships

    One of the things your missing is the description of the relationships between each table. For example, your Products should be related to your Orders and your Orders related to your customers.
    Depending on the desired outcome will determine if you can do what you want.
    You mention you want to track the sales but you need to build all of the tables and make sure the relationships are set, once you have the relationships down you can go from there.
    As mentioned in an earlier post the best approach is using subforms to be able to input into the various tables.
    A basic design I have used many times would be Form A, alows you to select the customer from a drop down menu and then populates your customer details. You would then select the employee performing the transaction from another drop down that displays the employee name but only enters the employee ID into the Orders record.
    Then you would use Subform A to list either all the products or perhaps only the products associated with the customer selected in Form A.
    When the transaction is complete the order entry is created in the orders table with a customer ID from Form A including the employee ID as well as the Product ID from SubForm A.
    When you want to track this later the relationships will associate the ID's from the employee and product entries back to the description on those specific tables.
    Hope this doesn't confuse to much

  6. #6
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    if you are a newbie.....
    assuming that you have designed your tables
    have you defiend relationships between the tables, if so then the most likely reason why you cannot enter data in table C is that it may require data in Tbales A & B first. In a realtional system there is a precedence that data must be defined. classically this is called masterfile data.. sounds grand, but a "ferinstance"

    say you wanted to record details of cars
    you may have a table called manufacturers (containingg say Ford, Vauxhall, Honda, BMW, Morgan), and a table called Cars which say defined a specfic car (eg an X5)

    to fully define the car you would need to also need to insert the ID of the manufacturer.. to do that you need to have defined the manufacturer BEFORE entering the car, or leave the manufacturer ID null/empty until you have a manufacturer defined.

    I do0 hope that you avoided the Access horror that is the lookup wizard in table design.. it can be used successfully but it is a horror that catches many a developer just starting off.... its fine for things like say gender (male/female/indetermionate) where you may know all possible values at design time.... but it should never be used for something that may change over time. Persoanlly I wouldn't even use it for gender.. even though its only 2 possibly 3 options you never know when a change may come in.. say you get a diktat from on high to refer to men / wimmen, them / us or use a different language, or male / female / transgender.......

Posting Permissions

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