cannot add record(s); join key of table not in recordset

[tbalci]

Hello,

I'm trying to build a simple CRM database. I have attached it to this message.



I created a form to enable the user to enter company information. The name is frm_company. Within the form, there is a listbox which allows the user to select one or more tags for the company (lst_tag). There is another listbox, to enter one or more sector information for the company (lst_sector).

The first problem I ran into is that the form does not display information from the connected tables.

The second problem is lst_tag. When I try to select a value, it displays "cannot add record(s); join key of table 'tbl_companytag' not in recordset" error. Checking the form's Record Source, I see tbl_companytag in the records.

I'm more than confused

Anybody can give me a hand?

Thank you,
Tolga

[ranman256]

Your frmCo is too complex. you need a table: tblCO ONLY connected to this form.
the sector info goes into tblSector (it has the CO.CoID field)
the form frm_Co is connected to tblCO


1. is the form.recordsource connected to the correct table? that table...tbl_CO?
2. does the table lst_companytag' have the co. key from tbl_CO?
3. you cannot add items to lst_companytag' until there is a company record in tbl_CO to match it to. Is this so>?

[tbalci]

Hi ranman,

Thank you for your reply.

1. My frm_company records data for 5 tables: tbl_company, tbl_companylink, tbl_companynote, tbl_companysector and tbl_companytag. I wanted to make data entry simple for the user, that's why I consolidated all into one form. If, as you said, this form is too complex for such an operation, would it be better to go with subforms rather than having everything on one form?

2. What I want to do with the listbox lst_companytag is to record CompanyID and TagID to tbl_companytag. There can be multiple tags for one company. I want the user to select tags for the company from lst_companytag (this is exactly the same for lst_companysector). To reply your question, tbl_co does not have lst_companytag data.

3. Following #2 above, lst_companytag records tags for the companies. Yes, there must be a company to record tag information.

Just some notes: I can make design changes to the database, no problem. I want to future-proof it, I want a solid foundation so that when it grows I do not want to go back and make changes. On the other hand, I do not have any VBA knowledge, so I need to go drag-and-drop most of the time.

Thank you for your assistance

Tolga