Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250

    Subform to enter multiple 1:N items

    Hello,

    this might be a dumb question and I feel like I should know this, but I'm a bit lost.

    I have got this database:
    - tblInvoices
    - tblTaxDocuments (for advance payments)

    Every tax document needs to be eventually included in an invoice.

    Every tax document can only be included in a single invoice, and an invoice can contain multiple tax documents (therefore typical 1:N relationship).

    I have a form for invoices: frmInvoices.


    What I want to do here is to have a subform to enter one or more tax documents that should be included with this invoice.

    Of course I know how to do it the other way around: open frmTaxDocuments, select an invoice via combo box and that's it. But I want to do it from frmInvoices (the "1 side", not the "N side").

    How should I do this subform? I only want to select tax documents (assign InvoiceID to them), not modify the original records.

    Thanks a lot in advance.
    Tomas

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I'm a bit confused. Can you post your tables and their relationships.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Sure...


    tbl1Invoices
    ===
    InvoiceID
    InvoiceDate
    CustomerID
    Notes
    ===


    tblTaxDocuments
    ===
    TaxDocumentID
    Amount
    InvoiceID (FOREIGN KEY REFERENCES tbl1Invoices)
    ===

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I think you have your relationship the wrong way round. I think your tables should be more like:

    tbl1Invoices
    ===
    InvoiceID
    InvoiceDate
    CustomerID
    TaxDocumentID (FOREIGN KEY REFERENCES tblTaxDocuments)
    Notes
    ===


    tblTaxDocuments
    ===
    TaxDocumentID
    Amount
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Actually an Invoice can be associated with multiple tax documents and a tax document can only be associated with 1 invoice, so I think that my design is correct here

    An example:
    I have a project worth 1000 EUR.
    First advance payment is 250 EUR - I receive this payment and create a Tax Document for it (TaxDocument 01)
    Second advance payment is also 250 EUR - upon receiving, I create TaxDocument02

    Then I deliver the project and create an invoice for it:
    Invoice 01:
    - Project 1000 EUR
    - TaxDocument01 -250 EUR
    - TaxDocument02 -250 EUR
    ===
    TOTAL TO PAY: 500 EUR


    So when creating an invoice, I want to have a sub form where I select Tax Document 01 and Tax Document 02 to be a part of this invoice.

    Hope it makes sense now

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Thomasso View Post
    So when creating an invoice, I want to have a sub form where I select Tax Document 01 and Tax Document 02 to be a part of this invoice.
    What you want is a MAIN form, to represent your invoices, and a SUB form to represent the Tax Documents.
    To my mind that is clearly a One to many relationship. One Invoice to many Tax Documents.

    EDIT
    Sorry. You have the correct table design.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I've attached a very simple example of how the relationship might be represented
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #8
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Thanks, this makes sense.

    However, I don't want to CREATE tax documents via frmInvoice. I only want to be able to SELECT AN EXISTING Tax Document (or more) without being able to modify it.

    The tax documents are created elsewhere, at a different time (before the invoice).

    For example:
    frmInvoice:
    Invoice01, 27.2.2023

    Subform: SELECT TAX DOCUMENTS TO BE ASSOCIATED WITH THIS INVOICE
    (Combo Box With Tax Documents where InvoiceID = NULL)

    When I select a tax document, it gets assigned InvoiceID and is shown in this invoice's subform.

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    You need tables like
    tblInvoices: InvoiceID, InvoiceNumber, InvoiceDate, CustomerID, ...;
    tblTaxDocuments: TaxDocumentID, ...;
    tblInvoiceRows: InvoiceRowID, InvoiceID, TaxDocumentID, ...

    The form based on tblInfoiceRows will be a source of subform in form based on tblInvoices. User selects a tax document for every row of invoice. To assure every tax document will be attached to single invoice only, and only once, the tblInvoiceRows must have field TaxDocumentID indexed as unique. (And of course you can use e.g. a query which returns a list of tax documents not included in any invoices as rowsource for tax document combo in subform, to avoid messages that the document was linked to another invoice - but indexing is only way to avoid accidental double linking anyway!)

  10. #10
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I only want to be able to SELECT AN EXISTING Tax Document (or more) without being able to modify it.
    If you only want to view the tax doc data then surely a single form based on tax doc table will suffice.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Link your main form (for invoices) to the subform (for tax documents) via the PK\FK and add a combo to select the documents that filters out the ones already assigned to an invoice (set criteria for Is Null in the InvoiceID field of the combo's row source; you will need to have a Me.cboTaxDoc.Requery in its Enter or GotFocus event to refresh it every time you use it).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Thank you everyone for the tips.

    However, the problem is that when I link frmInvoices (data source tblInvoices) to subform frmTaxDocuments (source tblTaxDocuments) via InvoiceID, the subform creates new tax document records with that InvoiceID, instead of choosing an existing one.

  13. #13
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Thomasso View Post
    However, the problem is that when I link frmInvoices (data source tblInvoices) to subform frmTaxDocuments (source tblTaxDocuments) via InvoiceID, the subform creates new tax document records with that InvoiceID, instead of choosing an existing one.
    Did you read my post? You mustn't have frmTaxDocuments as subform of frmInvoices! You must have a form frmInvoiceRows as subform there instead, and you must have TaxDocumentID as foreign key in InvoiceRows table!

    I.e. you register an invoice. Then you can register any number of rows for this invoice. To every row of invoice, you can lik a single tax document.

  14. #14
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    In post #8 you said:
    However, I don't want to CREATE tax documents via frmInvoice. I only want to be able to SELECT AN EXISTING Tax Document (or more) without being able to modify it.
    ..............
    When I select a tax document, it gets assigned InvoiceID and is shown in this invoice's subform.
    How will the InvoiceID be asigned?
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  15. #15
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Quote Originally Posted by ArviLaanemets View Post
    Did you read my post? You mustn't have frmTaxDocuments as subform of frmInvoices! You must have a form frmInvoiceRows as subform there instead, and you must have TaxDocumentID as foreign key in InvoiceRows table!
    I do have frmInvoiceRows of course, but I don't want to mess it up with Tax Documents. I want to link Tax Documents to invoices itself, not invoice items... ! !

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multiple Items Form grouped items
    By Tuckejam in forum Forms
    Replies: 2
    Last Post: 04-29-2020, 10:00 AM
  2. Replies: 1
    Last Post: 10-01-2015, 10:02 AM
  3. Replies: 4
    Last Post: 08-19-2015, 08:32 PM
  4. Replies: 18
    Last Post: 01-27-2012, 12:53 PM
  5. Replies: 2
    Last Post: 09-30-2011, 04:57 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