Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2016
    Posts
    14

    Unanswered: How to update two joined tables through a query?

    Hi, I'm a newbie who is trying to build a personal DB and I have some issues.

    The first one is that I can't update two joined tables through a query.
    For example I have the following tables:
    Click image for larger version. 

Name:	Two tbl.jpg 
Views:	7 
Size:	18.1 KB 
ID:	16703

    I put them in a simple query which shows all fields (except "CustomerName" which source is "tblOrders") from the two tables.
    What I want is when there is a new "CustomerName" (no record in "tblCustomers") to be able to add it along with the "TelNumber" through the query.

    Thank you in advance!

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You don't! Data entry should really be done through the use of Forms, not Queries. Most Multi-table Queries are Read-Only. This is a classical one-to-many relationship, so you need to do this using a Main Form/Subform design.

    The Main Form would be based on your tblCustomers.

    The Form the Subform would be based on would have tblOrders as its RecordSource.

    Because very, very few names are unique, you really need to have a CustomerID on both Tables to use to relate the two, rather than using Customer names.

    If you use the same name (i.e. CustomerID) in both Tables, the Subform Wizard will automatically use these Fields to link the two, when you add the Subform.

    As you move from Record-to-Record in the Main Form, the Records in the Subform will adjust appropriately.

    Linq ;0)>
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jan 2016
    Posts
    14
    Thank you for your reply!

    My example was simplified so that I could easily explanation my issue. However my DB is little bit more complicated.
    I've already built a form which source is the query I have problems with. I want to make my form very neat and to look professional. So when I add subform it doesn't look very good.
    The data in the subform is presented as datasheet. Is there something I'm missing? How to make the subform to look like a normal "text box".
    Or is there any other solution to update the filed (CustomerName) in the "tblCustomers"?

    Best regards

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Kensei View Post

    ...I want to make my form very neat and to look professional...

    ...The data in the subform is presented as datasheet...
    Well, having the Subform show in Datasheet View is considered, by the vast majority of experienced developers, to be 'profession looking,' which is why it is the default view for Subforms! Subforms are generally used when there is a one-to-many relationship, and when there are 'many' Records to be displayed at one time, Datasheet View is generally the way to do that.

    To change this, in Form Design View, select the Form in the Subform Control (the Form itself, not the Subform Control) and go to Properties - Format and change the Default View Property to whatever you want.

    Linq ;0)>
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Jan 2016
    Posts
    14
    Thank you very much for the help!

Posting Permissions

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