Solved Record related to one table or another (1 Viewer)

ramasco

New member
Local time
Tomorrow, 00:11
Joined
May 15, 2020
Messages
28
Hi,
I made one form to save contacts of two separate tables for Brokers or Clients. In the contacts's table I made a relation with each table (screenshot attached).
When the form is open by the Brokers's form the client field with the ID is empty. When I open it from the client's form it goes viceversa.
When I save the record of a broker's contact (same for client's contact) it shows up the error. It says is not possible to save because there's no relation with the other table.
I need to keep only one table for the contacts.
Is there a way to skip the need to the other relation?
Here the code for saving:

Code:
Private Sub cmdSalva_Click()   
'On Error Resume Next

Dim numIDSymphony As String
Dim numIDBroker As String
Dim NomeFonte As String

    If IsNull(Me.txtIDSymphonyCont) Then
                numIDBroker = Forms("Contatto_Ins")!txtIDBrokerCont
                NomeFonte = Forms("Contatto_Ins")!txtNomeBroker
                        
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.GoToRecord , "", acNewRec
  
            Forms("Contatto_Ins")!txtIDBrokerCont = numIDBroker
            Forms("Contatto_Ins")!txtRagioneSociale = NomeFonte
          
        Forms("Contatto_Ins")!cboTitolo.SetFocus

        MsgBox "Contatto salvato" & vbCrLf & " Inserisci ulteriore contatto" & vbCrLf & _
        " oppure " & vbCrLf & " Annulla per uscire", vbOKOnly, "Eseguito!"
    
    End If

    If IsNull(Me.txtIDBrokerCont) Then
                numIDSymphony = Forms("Contatto_Ins")!txtIDSymphonyCont
                NomeFonte = Forms("Contatto_Ins")!txtRagioneSociale
                      
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.GoToRecord , "", acNewRec

            Forms("Contatto_Ins")!txtIDSymphonyCont = numIDSymphony
            Forms("Contatto_Ins")!txtRagioneSociale = NomeFonte
          
        Forms("Contatto_Ins")!cboTitolo.SetFocus

        MsgBox "Contatto salvato" & vbCrLf & " Inserisci ulteriore contatto" & vbCrLf & _
        " oppure " & vbCrLf & " Annulla per uscire", vbOKOnly, "Eseguito!"
  
    End If
End Sub
Thank you in advance for any help

Massimo
 

Attachments

Last edited:

June7

AWF VIP
Local time
Today, 14:11
Joined
Mar 9, 2014
Messages
3,101
Who are brokers - your employees? Could they also be a client? Are your clients always individuals or also organizations? What is the table that is cut off in image?
 

ramasco

New member
Local time
Tomorrow, 00:11
Joined
May 15, 2020
Messages
28
Thank you for your answer.
I have two lines of selling. Direct or by brokers.
Brokers are companies. They are sellers and they can have one or more Clients. They are not Client.
Clients are only organizations.
Contacts are persons/workers in the brokers company or Clients company. Some time a contact can be in both.
All the relations are in the page attached. The cut table is about contracts

Thank you again
 

Attachments

June7

AWF VIP
Local time
Today, 14:11
Joined
Mar 9, 2014
Messages
3,101
A database can be functional without any relationships set. Or perhaps you just need to turn off Enforce Referential Integrity.

Which form is opened - Contracts data entry?

If you want to provide db for analysis, follow instructions at bottom of my post.
 

ramasco

New member
Local time
Tomorrow, 00:11
Joined
May 15, 2020
Messages
28
All the Enforce Referential Integrity are off.
Sorry, my english is not that good. I'm not sure about understanding your question. do you mean Contracts or Contacts form?
I press Save button on the Contacts form. And bug show up on this line:
Code:
DoCmd.RunCommand acCmdSaveRecord
In that moment the contract form, that i call Polizza_Ins, is open but not active.

Thank you
 

June7

AWF VIP
Local time
Today, 14:11
Joined
Mar 9, 2014
Messages
3,101
Well, which form are you referring to when you say "When the form is open by"?

If referential integrity is not enforced, I don't understand why Access insists there must be a related record. Could remove all relationships and see what happens.
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 17:11
Joined
Feb 28, 2001
Messages
17,035
From the viewpoint of normalization, there is always a question of purpose. Here, you have clients and brokers, both of whom are your customers. They should be in the same table. If you need extra data in one vs. the other, Access is actually pretty efficient at storing blank fields (for the case that one type of entry has less data than the other type of entry.)

If you have one table of PEOPLE, you don't have two relationships. Then you can put a flag in the table that says "this person is a broker." But you can still sell to that person just like you can sell to the "ordinary" client.
 

ramasco

New member
Local time
Tomorrow, 00:11
Joined
May 15, 2020
Messages
28
Well, which form are you referring to when you say "When the form is open by"?

If referential integrity is not enforced, I don't understand why Access insists there must be a related record. Could remove all relationships and see what happens.
I deleted all the relationships and nothing change. I'm making all the changes possibile since last night.
The project works like this: I start entering a new CONTRACT in form Polizza_Ins. From this form I have to connect the CONTRACT to a client and if it's present, I have to connect the broker too. both from a combo.
If the client or the broker is not present jet I can add it by pressing "new" (the same for brokers). This button opens the form Azienda_Ins where I add the client data. If I have to add a new broker the button "new" opens Broker_Ins.
Same path if I need to modify the records when is needed. (Pressing "modify" instead of "new")
Till now all right.

I have always to add the contact of every client or broker. Therefore on Azienda_Ins and Broker_Ins I can press the "contact" button. At this point the project detect if this client or broker already has contacts related. If it has already, it opens the form Contatti_Lista where I can add new or modify data. ( I also have problems on this path...)
If it's a new client or Broker, it doesn't have any contact. Therefore opens directly Contatto_Ins. And here is the problem.
Can I attach something to make the pic more complete?

Thank you for your help
 

ramasco

New member
Local time
Tomorrow, 00:11
Joined
May 15, 2020
Messages
28
From the viewpoint of normalization, there is always a question of purpose. Here, you have clients and brokers, both of whom are your customers. They should be in the same table. If you need extra data in one vs. the other, Access is actually pretty efficient at storing blank fields (for the case that one type of entry has less data than the other type of entry.)

If you have one table of PEOPLE, you don't have two relationships. Then you can put a flag in the table that says "this person is a broker." But you can still sell to that person just like you can sell to the "ordinary" client.
Thank you, the idea is good. This db is projected for the administration and payment of "royalty".
In my job, brokers are not clients, they just connect me to the customers. For this I have to pay them. They do nothing and the relationship between me and the customer is direct.
Here Brokers are like personal trainers. Sometimes customers and brokers fight and "divorce". If it happens I do stop paying them. And I keep a direct relationship with the client.
For all this reasons I have to keep them separate.

I would like to do what you suggest with the contacts, just relating them to one or to the other. Here comes out the problem.
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 17:11
Joined
Feb 28, 2001
Messages
17,035
If it's a new client or Broker, it doesn't have any contact. Therefore opens directly Contatto_Ins. And here is the problem.
This problem is one that would normally show up when you have relations defined between the contact table and the brokers or clients. IF there is a relationship, you have to have something on the other side of the relationship. It is here that my earlier advice becomes crucial. Please understand that I am not clear on your business relationships, but it SOUNDS like you want to have a list of contacts BUT the contacts have to be EITHER a client OR a broker. This is where my suggestion crops up.

IF I have understood your problem correctly, you CANNOT create a relationship leading back to tables for clients and for brokers if they are in separate tables because Access has no mechanism to say "This relationship goes to table A" or "This relationship goes to table B" based on a single field in a single table as the base of one end of that pair of relationships. It is not structurally possible to define that relationship.

If your Brokers and Clients were in the same table, you would have no issue. One link to one contact, and you are done.

Your desire to keep them separate perhaps depends on looking at this another way. Keep them together but when you are looking at a list of clients, get that list from a QUERY, not from a table, and have the query only show you folks where the Broker flag is FALSE. Then your list of brokers is another query where that broker flag is TRUE. If you have supplemental tables, you can manage them through selective queries that limit themselves based on the Broker T/F flag.

Now, here is where your problem of "broker" and "client" relationship has to be managed. Look up something called a JUNCTION TABLE, which is a way to relate two tables in a many-to-many setup. BUT... it can also relate a table to itself, so that a person who is a broker can be listed in the junction table and the broker's customer is just another person in the same table. You have to look at and understand junction tables to make this work. But keeping the brokers and clients in separate tables yet having some points in common? Looks like you still have hair, but if you try to make the two-table system work, your hair will begin to more closely resemble mine as you tear it out. That would not be a good thing.
 

ramasco

New member
Local time
Tomorrow, 00:11
Joined
May 15, 2020
Messages
28
Now, here is where your problem of "broker" and "client" relationship has to be managed. Look up something called a JUNCTION TABLE, which is a way to relate two tables in a many-to-many setup. BUT... it can also relate a table to itself, so that a person who is a broker can be listed in the junction table and the broker's customer is just another person in the same table. You have to look at and understand junction tables to make this work. But keeping the brokers and clients in separate tables yet having some points in common? Looks like you still have hair, but if you try to make the two-table system work, your hair will begin to more closely resemble mine as you tear it out. That would not be a good thing.
Thank you so much!!!!!
it works perfectly. I had followed your advice. I made a Junction table and used it to make all the querys I need.
I also took seriously the point about the hair 😅 and decided to make it easier. Soo if I have the same contacts in Brokers or Customers I record it twice with a note in the note field. ;)
Thank you again!!!
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 17:11
Joined
Feb 28, 2001
Messages
17,035
If it makes YOUR job easier, then I did my job. And you are quite welcome.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom