[HELP]: Cannot add record(s); join key of table not in recordset.

[R3dbull]

Hello everyone,




I'm a student of accountancy and I have a class which consist in creating a database in access.


We learned the very basic of access.


My problem is:


I'm creating a BD which consists in a company which have "several banks".


So I created the several tables:


Bank, Clients, Accounts, Manager.
Relationships tables:
BAN_CLI_MAN - would associate the clients and managers in the respective bank.
CLI_MAN_ACC - would associate the accounts and the managers of the accounts in the respective client.
Its all n:n so a ternary case.


I wanted to create a form of accounts which would have the fields Clients and Manager also, which would come from CLI_MAN_ACC table updating this table with the respective info client - account - account manager. I created the form and when I tried to add any info to the fields clients or manager it give me the error in the title, but if I change the form to a new record or existing one and then come back to the one I was creating it allows me to add info into the clients and manager fields, and updating the table without any problem.


Can you guys help me solve this problem, so I can finish my database project?


Best regards,
João Neves

[R3dbull]

I can upload my DB if necessary.

[rzw0wr]

Upload your database.
I will instruct you if I can however I will not do the coding or work.

Dale

[R3dbull]

Thank you so much!

I don't want you to do all the work, I want to learn how to do this without any error.

DB: https://www.dropbox.com/s/vm7j2ayeaz...co-teste.accdb

[rzw0wr]

You are talking about the Account form. Correct?

Dale

[R3dbull]

Yes, no_cliente and no_empregado fiels

[rzw0wr]

To start.
I usually bind my forms to a query.
You may want to start by making a query for the account form.
Bid problem is I can not read your language. But I think we can work through that.

[R3dbull]

If you want I can change the fields to English quickly.
Query with all fields of account ?

[R3dbull]

New DB https://www.dropbox.com/s/vm7j2ayeaz...co-teste.accdb

[rzw0wr]

I think we have it.
The combo boxes where bound.
Unbind them.
The last combo box will be empty because the table you are using is empty.
Both combo boxes should have bound Column set to 1. This is the data you want to reference.
The column Width for both should be 0";1. You are want ti hide the ID column in the combo box and display the Client and Manager name. 0" hides. a number larger than 0 displays that column.
Read up a little on combo boxes. They can be confusing until you learn them.

If you still have problems post back.

Dale

[R3dbull]

How can I unbind the comboboxes?

I checked everywhere and I can't find it. I'm sorry I'm a newbie at this

Thanks for the help.

[rzw0wr]

Not a problem, We all started as newbies.
Property sheet, data tab. Remove the name under Row Source.
You are not using a Row Source you are using a query/SQL.
When an SQL statement is written in the control source it can be considered a query.

Dale

[R3dbull]

In your database, in the account form does the field no_cliente and no_empregado get the date in the clients and manager tables?

And when creating a new account adding the no_cliente and no_empregado does it update the CLI_MAN_ACC table?

In my Database this does not work at all. I think I did something wrong.

[rzw0wr]

No. It won't.
The combo boxes are just for search.

Dale

[R3dbull]

I probably expressed wrong.

What I want to happen is that the no_cliente and no_empregado in the Account form shows the data in the Client and Manager tables, and by creating a new account this fields, no_conta (account), no_cliente (Client), no_empregado (Manager) to be updated in the CLI_MAN_CON table.

I could do this already but I had to swap between the records and back to the original to update the no_cliente and no_empregado fields.

Do you understand what I want?

Thanks for all the help,

Best Regards,
João Neves