Question relating to auto-completion of field in a linked table

[MisterFalck]

I have the following problem:




I have a table named "Database", where details about different firms are listed.

I have a table named "Industry Registry", which lists out all 96 industries as listed by the North American Industry Classification System (NAICS, 2017).

I have a table named "Broader Industry Registry", which lists out the 19 broader industry classification as listed by the NAICS.

The Relationships are listed below:



Industry ID will be selected as the data entry is done in "Database". What would I have to do in order to have the field "Broader Industry ID" autocomplete after registering an industry ID?

Each Industry ID corresponds with only only Broader Industry ID.


Thank you for your attention.

[pbaldy]

How about:

http://www.baldyweb.com/Autofill.htm

[MisterFalck]

Thank you for your response pbaldy,

I am sorry for not understanding your response. It appears to me that it makes use of a text box. I am hoping to calculate a column in my database automatically based on already provided information in previous columns. I have very little experience calculating columns or making use of intermediately advanced access functions.

If you might be able to break it down simpler I would greatly appreciate it, if not then thank you for your time.


Sincerely,
MisterFalck

[davegri]

I think you have a many-to-many requirement with Database table as the junction.

as the data entry is done in "Database".

You will not be able to autopopulate from another table if you are working directly in the Database table, you should be working in forms.
If you post the DB here that you have (the tables), I think I can create and set up the forms to do what you need.

[ssanfu]

Be aware that "Database" is a reserved word in Access and shouldn't be used as an object name.

I have a table named "Database", where details about different firms are listed.

Better names might be "tblFirms" (my choice), "Firms", "Companies" or "Businesses".
And there shouldn't be spaces in object names.....

[davegri]

Falck-davegri-v01.zip

See attached.
I took liberty of using the solid advice of ssanfu to rename some objects and fields.
I added a Firm name field as there was nothing to identify the firm other than the autonumber.
Added referential integrity to the relationships.
DB adds one form to do the job.

[MisterFalck]

Wow, thank you all very much for your effort and help! I really appreciate it.

I have opened the file your sent davegri and it is very interesting.

I see that I have not included this in the original relationship image, but I have also linked to a separate table which has a one-to-one connection with the auto-number of what is now named tblFirms. The reason for separating this out has been in order to keep the portfolio companies anonymous, as I use sensitive information from the portfolio companies of different VC funds, without losing track of which one is which.

I will spent some time looking over the implications of the way you have structured the database.

Final note: It seems as though upon entering new information in the form I can pair any industry with any broader industry. Is there any known way to have the broader industry autocomplete upon selecting an industry?


Thank you again so much for your input, this has been very helpful.


Sincerely,
Hans

[davegri]

Is there any known way to have the broader industry autocomplete upon selecting an industry?

The DB I provided will do exactly that. When you select an industry from the dropdown, the broader industry instantly fills into the broader industry combobox, does it not?

[MisterFalck]

My apologies, I had not enabled content in the db.

This addresses my concerns, thank you very much for taking the time and effort to answer my query.


Sincerely,
Hans L. Falck

[MisterFalck]

I will not be offended if my lack of knowledge inclines you not to answer me any further, davegri - I ask my question just in case:

Am I correct in assuming that it is the line of SQL code with the expressions INNER JOIN that has the Broader Industry category auto-complete upon selecting an industry ID?

I have copied your code into my database.
Referential integrity has been added.
I have added a form and created combo boxes as in the database you have sent.
The Industry ID is selected by name, then the ID is stored in the table.

However, the Broader Industry combo box still does not auto-complete when selecting the industry.
Also the Broader industry list repeats itself for each industry entry there is within the broader industry.

--------

I again apologize for wasting every ones time. If there is a course I should take before returning to this forum I would be happy to be pointed in its direction.

[davegri]

assuming that it is the line of SQL code with the expressions INNER JOIN

My solution DB has no query or SQL code included, so don't know about this.

However, the Broader Industry combo box still does not auto-complete when selecting the industry.

In your attempting to include my code into your DB, your DB is more complex and considerations must be taken to handle issues that I know nothing about.
I think you will need to post your zipped DB here at the forum. You can remove any sensitive private data.