Table Within Form

[Dnallov]

Hi Guys



Reborn again newbie here. About 15 years back I built a complex Access DB from scratch for my HVAC company with no knowledge of Access, only learning from online guides and forums such as this one. However, never touched access since; but now I have volunteered to build a DB for a group of investors I'm involved with. I thought I would remember, but as soon as I opened MS Access I realised that I had forgotten everything! So back to the youtube videos and this forum .

So my first problem probably has a simple solution, but I can't find the answer. I have 4 tables with the following relationships (DB is in its very early stages)...



I want to set up a form where each page is the company, and that page has 2 tables; the first that lists the 'Loans' connected to that company, and the second that will list the 'Directors' connected to that company. I have successfully created a 'Companies' form that includes the 'Loans' table which works a treat (each company record shows which loan is connected to it), but if I add the 'Directors' table it just lists all the Directors in that table, not which director belongs to that business.

I can successfully add the 'companies-directors' table, and each 'Loans' record includes their unique Director, but I can only pick directors already in the 'Directors' table, whereas I want to be able to add new directors without having to first add them to the table.

Any help is appreciated

[ItsMe]

Are you having trouble adding records or displaying data?

If you create some queries to display your data, you can bind your form to one of the queries and bind a subform to another query.

Also, you should try and avoid special characters and spaces to names of columns, tables, queries, etc. The underscore is an exception to that rule. Also, you want to avoid using reserved words for names, e.g. 'Name'.

[Dnallov]

Yes; shortly after I posted my post I realised that I should be using Unique IDs on those records, and will proceed to add them (and change the relevant child records to numbers). There is no data in the DB ATM so it won't be a problem.

I'll have a good attempt at adding the queries, but thought there might be a simpler answer, as I just need the 'Directors' Table to add the unique records.

[ItsMe]

... I just need the 'Directors' Table to add the unique records.

The simplest way to do this will likely be via a ComboBox Control that is bound to a column in table Company-Director. The Combobox Control's RowSource will be table Directors.

When I am in the planning stages of my databases, I will use query objects to test my table structure. Afterwards, I can use the queries as recordsources for forms and rowsources for combos. It is an oversimplified explanation, but I feel it is relevant.

Also, there are tools in the relationships window that will allow you to create rules in your database, rules to enforce Constraints on your data. You should determine how you will enforce Constraints and maintain referential integrity now. I choose to use table properties and VBA, avoiding the Relationships Window 100%.

[ssanfu]

No offense to ItsMe , but I do use the Relationships Window 100% of the time. It allows me to see the relationships easier.

I didn't like your naming convention (again no offense meant), so I thought I would show you how I would the name fields.
Any field with a "_PK" is a primary key field.
"_FK" is a foreign key field (Long Integer).

All PK fields are Autonumber. PK fields that are text are slower when indexing. And if you have a text PK field that is the company name and the company name changes while they have an active loan, how do you change the FK field to keep the records related??

My version:



Also, maybe these two sites will be of interest:

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

[ItsMe]

No offense taken. It is simply a tool I prefer not to use. Because I ignore it, I am ignorant to many of its applications and nuances. For me, it gets in the way when I am developing. So I depend on external ERD's.

[Dnallov]

No offense to ItsMe , but I do use the Relationships Window 100% of the time. It allows me to see the relationships easier.

I didn't like your naming convention (again no offense meant), so I thought I would show you how I would the name fields.
Any field with a "_PK" is a primary key field.
"_FK" is a foreign key field (Long Integer).

All PK fields are Autonumber. PK fields that are text are slower when indexing. And if you have a text PK field that is the company name and the company name changes while they have an active loan, how do you change the FK field to keep the records related??

My version:

Also, maybe these two sites will be of interest:

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

That's awesome! Far more than I expected, and many thanks for those links .

I will implement it tonight and report back with the results