Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2023
    Location
    Modena, Italy
    Posts
    2

    Lightbulb Two tables to be correlated with two others to handle sets of hyperlinks

    Problem: I would like to correlate a table of attachments (made however with hyperlinks) between two different tables, I explain by first making the example with one.
    I have a table (tblDataA) that contains various fields, although to simplify in the example I only put primary key (ID_DataA) and short text (Description).
    This table should contain groups of attachments (hyperlinks).
    I made them with a tblAllegati table and a tblAllegato table with a one-to-many relationship.
    In addition, tblDataA is also corrected to tblAllegati with a one-to-many relationship.
    Click image for larger version. 

Name:	Immagine1.png 
Views:	26 
Size:	17.5 KB 
ID:	49404

    So it works very well for me because if I create a mask with the wizard and insert tblDataA, tblAllegati and tblAllegato I get a mask that for each tblDati record allows me to insert (new) groups of tblAllegati records with relitive tblAllegato records linked. I can create and see everything from the created mask. The wizard already puts me the sub-masks of tblAllegati and tblAllegato.
    If I wanted to do this for a second table tblDataB, I could replicate the schema with two tables (tblAllegatiB and tblAllegatoB) so I could create a fully functional mask as in the case of only table tblDatiA.
    I wonder, though: is there a way to bind tblDatiB to a unique tblAllegati?


    I tried creating a mirrored tblDatiB table and adding a numeric field ID_DataA to tblAllegati, creating the relationship:
    Click image for larger version. 

Name:	Immagine2.png 
Views:	24 
Size:	30.0 KB 
ID:	49405

    but it doesn't work because when I enter the data with the mask done before, I get the error 'Unable to add or edit record. A related record is needed in table "tblDataB'' '.
    I know that "he" is right, however, is there a way to have a tblDataB and related form, which works for me as it did when there was only one table, but which always "fishes" from a single table of attachments?

    Thank you very much... Be patient for my poor English.
    Paolo Ferraresi, Italy.
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Why have tblDatiA and tblDatiB? Have one table with another field for a category (A, B). If you want two tables then neither tblDatiA nor tblDatiB can be the main form, they would have to be lookup sources for comboboxes on tblAllegati form.

    "This table should contain groups of attachments (hyperlinks)."
    If tblDatiA is supposed to have attachment links, why does tblAllegato have Link field?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Maybe the problem is that you have default values of 0 (zero) for the ID_datiA and ID_datiB fields in the tblAllegati. If a tblAllegati record is not related to a tblDatiB record, 0 is an invalid value for ID_datiB as there is no tblDatiB record with ID_datiB=0.
    So remove the default value for both fields.
    Groeten,

    Peter

  4. #4
    Join Date
    Jan 2023
    Location
    Modena, Italy
    Posts
    2
    Meanwhile, thank you both for your quick responses.
    To the first one (June7) I have to apologize because when I had the problem on a personal project I am working on, I had the problem of formulating a simple request and so I simplified a lot.


    In reality, the two tables are very different. I am making a database to manage the design and technical modifications of my engineering department, so it is not possible to unify them in one table.
    It was a good trick but unfortunately I didn't tell you everything.
    The fact is that both the projects table and the changes table need to contain several attachments (in the form of hyperlinks so that I don't gamble with the maximum size of the Access file). Also, the solution I had already found of making one attachments table for table A and a different one for table B does not appeal to me because I would like to keep all the attachments in one table.
    So this way it doesn't come out. I am sorry I did not give you this explanation right away but I thought it would complicate the problem even more.


    Instead, I tried doing as xps35 said and it actually worked great.
    I hadn't thought of that, unfortunately. That the primary key could not be zero I knew but something didn't snap in my head, because usually if the link is unique, the primary key is passed, but in this case the problem is effectively the other one (the primary key of the other table).


    I have read so many Access books to prepare for this activity but this kind of relationship I had never investigated it thoroughly and had missed the problem.


    Instead, by removing the default zero value, the field becomes null when not needed and only one of the two between ID_DataA and ID_DataB is hooked to the respective ID in the relevant data table.
    Therefore
    [tblDataA.ID_DataA] = [tblAllegati.ID_DatiA] with [tblAllegati.ID_DatiB] = null, it works.
    [tblDataA.ID_DataB] = [tblAllegati.ID_DatiB] with [tblAllegati.ID_DatiA] = null, it works.


    If, on the other hand, one of the two between [tblAllegati.ID_DatiB] and [tblAllegati.ID_DatiA] is zero (not null) then it goes into error, just as you explained, xps35.
    I thank you because I didn't think of that.


    Thank you both, because by reasoning with you we arrived at the solution that works for me!!!


    Paolo Ferraresi.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Search Form that sets query criteria and sets Operands
    By mainerain in forum Programming
    Replies: 2
    Last Post: 04-29-2021, 10:27 AM
  2. Correlated query to find cheapest manufactuer
    By rafnews in forum Queries
    Replies: 8
    Last Post: 03-27-2015, 11:16 AM
  3. Hyperlinks in Tables
    By jmb123 in forum Access
    Replies: 1
    Last Post: 03-03-2015, 07:58 AM
  4. Adding Hyperlinks to Linked Tables
    By rwilso29 in forum Access
    Replies: 1
    Last Post: 07-02-2013, 09:08 AM
  5. Entry of Large Data Sets into multiple tables
    By bcouzens in forum Access
    Replies: 8
    Last Post: 05-26-2011, 02:22 PM

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