Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form Design - Basic Question (Sort of..) (Access2K)

    Ok, sales database. 3 tables - tblCustomers, tblOrders and tblOrderDetails.

    I want to be able to open a form, combo box to select customer, which fills in name, address etc... Similar to Orders form in Northwind EXCEPT I want a blank order, with a details subform.

    Am i right in saying i will need a form, subform and subsubform to achieve this???

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form Design - Basic Question (Sort of..) (Access2K)

    I don't know what you would need a subsubform for.

    You can set the DataEntry property of the form to Yes; this will make the form open to a new (blank) record each time.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Design - Basic Question (Sort of..) (Access2K)

    Ok, so am i trying to complicate things here??

    Looking at it again, i guess i should do a lookup in the tblCustomers to retrieve the name and address etc. Then should i bind the form to a query source to save the invoice and invoice details to??

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form Design - Basic Question (Sort of..) (Access2K)

    The setup would be:
    - Create a query based on tblOrders and tblCustomers, joined on the CustomerID field. Double click the join, and specify that you want to display ALL records from tblOrders. You should use the CustomerID field from tblOrders in the query grid, not that from tblCustomers. You can add other fields from tblOrders and from tblCustomers to the query grid.
    - A main form bound to this query. Put a combo box on the form bound to the CustomerID field, with tblCustomers as Row Source. The combo box should have (at least) two columns, with the first column hidden by setting its column width to 0.
    - A subform bound to tblOrderDetails, linked to the main form on OrderID.
    If the user selects a customer from the combo box, the other fields relating to the customer will be filled in automatically. You may want to lock the text boxes bound to those fields.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Design - Basic Question (Sort of..) (Access2K)

    Hans.

    Thank you for your time in replying. I am understanding what yousay, and think i became confused when i realised the source for the combo box would be different to that of the form.

    So last step, would be to use an AfterUpdate event on the combo box, and use the recordset method? to collect the address etc data and insert it into the relevent text boxes..

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form Design - Basic Question (Sort of..) (Access2K)

    No, if you set up the query as I described in my previous reply, and use it as record source of the main form, there is no need to use code.
    <hr>If the user selects a customer from the combo box, the other fields relating to the customer will be filled in automatically.<hr>
    Simple as that.

  7. #7
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Design - Basic Question (Sort of..) (Access2K)

    Doh!!! Thank you!!

    I set up the row source ok.... and I have to admit i did not read (correctly) your binding the combo box to the ID field.... Now it works!!!!

    Thank you again!!!

Posting Permissions

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