Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Los Angeles
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FLAT FORM vs. SUBFORMS (2000)

    My question is very basic, but it seems to be at the heart of many of my problems with Access.
    I have TWO tables - Customers and Orders. I have set the relationship as ONE to MANY (a 1 is next to the Customer, the infinity next to the Order in the relationship window. I have checked enforce referential integrity.) A customer can make multiple orders, but an order only is related to the one customer.

    What I can't figure out is how to make a form based on ORDERS. Using the Wizard, it only let's me create a form based on Customers, and the Orders is the subform. The ORDER needs to be the main record in the form, and then I'd like to be able to CREATE A NEW CUSTOMER, or, if the customer already exists, ENTER THE CUSTOMER who is placing the order. I'd like to avoid subforms - is there anyway to make this appear FLAT, as if it were all in one table? In my attempts, I am unable to add a record - the customer doesn't exist, so it won't add the record.

    THANK YOU! (Is there some big picture thing I am missing??)

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: FLAT FORM vs. SUBFORMS (2000)

    Try making the form based on a query that selects the data from both tables. If both Tables contain a Customer ID, you can link the Customer ID in the Customer table to the Customer ID in the Order table as a one to many relationship. After creating the form based on a query that selects all of the data, set the form up such that you can update the Order table Customer ID and applicable data. Thus, when you input the Customer ID in the Order table, it will add the record to your Customer table if it doesn't already exist. This will give you the flat file look you want.

    Good Luck
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FLAT FORM vs. SUBFORMS (2000)

    When you create a referential one-to-may relation between Customers and Orders on CustomerID (which is a sensible choice) you are telling the system, that a customer must exist (i.e. stored in the Customers table) in the database before placing an order for that customer.
    You CAN have a flat form for the order entry even without a query as long as you make sure that either 1) you select an existing customer for the order or 2) you have a mechanism to detect that the customer does not exist and then create and store the new customer to the Customers table before saving the new order.
    An effective way of doing/ensuring this is to use a combobox (bound column = CustomerID) for the customer selection on the order entry form. You must have a NotInList event for the combobox, if you want to enter a new customer via the form. Code in the NotInList event will have to make the end user choose whether to accept or cancel when the customer name typed in the combobox does not already exist in the Customers table. In the case of accept you will have to create and store the new customer entry in the Customers table. Personnaly I prefer to let the NotInList event open a customer entry form using addnew and dialog mode.
    You can find help for the NotInList event in Access help and I think you should also take a good look at the Nothwind sample application (in particular the Orders mdb) on your Office CD.

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Los Angeles
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FLAT FORM vs. SUBFORMS (2000)

    THANK YOU! This worked perfectly. COMBOBOX is the solution I was seeking.

Posting Permissions

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