Loop up or Combo Box (1 Viewer)

[amarton]

Hi,
I have the following issue and I can't find a satisfactory solution. Pls note I have not looked into VBA to solve this, so I might missed the obvious solution.

I have a form, where I store all details about a site, including the parent company name. The original version of the db, which was done by somebody else, I have a combo box, where parent company can be selected by name. Once it is selected, it goes to a LOOKUP field on the Site table, than by some magic, the Company Name field is populated in the same table. There is also a hidden field for the Company Name on the original form.
I replicated everything on the original way, but it is not working,

I tried to use the Combo Box, selecting the option to look up details from a different table, then selected the Parent Company ID and Name and I selected to store this to the Parent Company field on the Site table. Got the combo box and the ID is save into the right place, but I can't put the name into that field.

I created an other combo box on the same form, selecting the sales employee, did the same steps and the name is appearing in the Site Details table .

Do you have any idea what do I do wrong?
Your help would be much appreciated.

 

[June7]

Is employee field in Site Details table a LOOKUP setup?

Is the company field also a LOOKUP setup?

I NEVER build LOOKUP fields in table.

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

 

[amarton]

Is employee field in Site Details table a LOOKUP setup?

Is the company field also a LOOKUP setup?

I NEVER build LOOKUP fields in table.

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

Thanks, I will send it soon.

 

[amarton]

Is employee field in Site Details table a LOOKUP setup?

Is the company field also a LOOKUP setup?

I NEVER build LOOKUP fields in table.

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

employee is a normal combo box, but I removed it from the db
company field is lookup

here it is!

thx in advance

 

[moke123]

You should get rid of the lookup fields in your table as well as the attachment field.

 

[mike60smart]

employee is a normal combo box, but I removed it from the db
company field is lookup

here it is!

thx in advance

Adam
Does 1 Company have 1 or More Sites?

 

[June7]

Consider:

Should not save company name in b_Site_Details, only company ID.

Don't use spaces or punctuation/special characters in naming convention. CamelCase is better than ALL CAPS.

Don't build LOOKUP in table.

Should "SITE COURCED" field be named "SITE SOURCED"?

 

[mike60smart]

employee is a normal combo box, but I removed it from the db
company field is lookup

here it is!

thx in advance

Your SiteDetails Table needs to be normalised.
The Multiple Site Contacts should be in a separate table

 

[amarton]

Adam
Does 1 Company have 1 or More Sites?

1 company have multiple sites

 

[mike60smart]

1 company have multiple sites

Hi Adam

Your relationships are wrong between Company and Sites

Company table has PK CompanyID

Sites Table needs CompanyID added as FK - Number DataType

You then link CompanyID to Company ID and Enforce Referential Integrity

 

[amarton]

Consider:

Should not save company name in b_Site_Details, only company ID.

Don't use spaces or punctuation/special characters in naming convention. CamelCase is better than ALL CAPS.

Don't build LOOKUP in table.

Should "SITE COURCED" field be named "SITE SOURCED"?

Should not save company name in b_Site_Details, only company ID. = I know, but this is the only thing I can't convince the customer to get rid of
Don't use spaces or punctuation/special characters in naming convention. CamelCase is better than ALL CAPS. = I'm working on this
Don't build LOOKUP in table. = This is what I would like to get rid of. The problem is that the customer want to see the Company Name in the tbl, don't ask my why....
Should "SITE COURCED" field be named "SITE SOURCED"? = Yes, I'm terrible with spelling already fixed it in the customer version.

 

[mike60smart]

Should not save company name in b_Site_Details, only company ID. = I know, but this is the only thing I can't convince the customer to get rid of
Don't use spaces or punctuation/special characters in naming convention. CamelCase is better than ALL CAPS. = I'm working on this
Don't build LOOKUP in table. = This is what I would like to get rid of. The problem is that the customer want to see the Company Name in the tbl, don't ask my why....
Should "SITE COURCED" field be named "SITE SOURCED"? = Yes, I'm terrible with spelling already fixed it in the customer version.

Is this database in actual use yet or is it still in development?

 

[amarton]

Your SiteDetails Table needs to be normalised.
The Multiple Site Contacts should be in a separate table

I'm sorry, I don't 100% understand what do you mean.

I have the site contacts in a separate tables (4 actually based on the contact type). The way I see it working is that when a new site is added, the company is selected from a combo box, to create the link between the site and the company. The problem is that the client wants to duplicate the Client Name on the Site Details table. With the normal combo box, only the recorded, in that field, so probably I need to create an append query to bring the name over once the new site is saved, but this might not be the right solution.

The site details will work on the same way as the client company.

Again, the problem is that the client is insisting to duplicate the name fields.

 

[amarton]

Is this database in actual use yet or is it still in development?

This is the only pending issue I need to fix, but it is in use.

All to other functions are working fine (which are not included in the example). All the forms, exports, import, macros are perfect, only this name duplication is giving me a headache Probably because it is wrong to do and I'm not that advance to implement a slick solution.

 

[mike60smart]

I'm sorry, I don't 100% understand what do you mean.

I have the site contacts in a separate tables (4 actually based on the contact type). The way I see it working is that when a new site is added, the company is selected from a combo box, to create the link between the site and the company. The problem is that the client wants to duplicate the Client Name on the Site Details table. With the normal combo box, only the recorded, in that field, so probably I need to create an append query to bring the name over once the new site is saved, but this might not be the right solution.

The site details will work on the same way as the client company.

Again, the problem is that the client is insisting to duplicate the name fields.

If a site can have Multiple Contacts with each Contact having a different role then you need a structure as follows:-

tblSite
-SiteID - PK - Autonumber
-Sitename

tblSiteContacts
-SiteContactID - PK - Autonumber
-SiteID - FK - Number (Linked to PK from tblSite)
-SiteContactID - FK - Number (Linked to PK from tblContacts)
-ContactTypeID - FK - Number (Linked to PK from tblContactTypes)

tblContactTYpes
-ContactTypeID - PK - Autonumber
-ContactType

 

[amarton]

If a site can have Multiple Contacts with each Contact having a different role then you need a structure as follows:-

tblSite
-SiteID - PK - Autonumber
-Sitename

tblSiteContacts
-SiteContactID - PK - Autonumber
-SiteID - FK - Number (Linked to PK from tblSite)
-SiteContactID - FK - Number (Linked to PK from tblContacts)
-ContactTypeID - FK - Number (Linked to PK from tblContactTypes)

tblContactTYpes
-ContactTypeID - PK - Autonumber
-ContactType

Thanks this is clear now. Thanks! I have very similar, without the roles, I kept them into separate tables.
To only question is when a new site is created on the form with new site contact how it will update the table and how the names and the details will appear on the Site tbl... probably I just need to test it out

 

[moke123]

The problem is that the customer want to see the Company Name in the tbl, don't ask my why....

The user should not see the tables, nor have direct access to them.

 

[mike60smart]

Thanks this is clear now. Thanks! I have very similar, without the roles, I kept them into separate tables.
To only question is when a new site is created on the form with new site contact how it will update the table and how the names and the details will appear on the Site tbl... probably I just need to test it out

Ok we have a Main Form based on the Company Details with
a SubForm displaying the Site Details.

Which field details are not displaying correctly ?

 

[mike60smart]

Thanks this is clear now. Thanks! I have very similar, without the roles, I kept them into separate tables.
To only question is when a new site is created on the form with new site contact how it will update the table and how the names and the details will appear on the Site tbl... probably I just need to test it out

Hi Adam

I mentioned that your Site Table was not normalised.

All of the fields relating to Site Contact need to be in a separate table.

What does QS Name stand for?

Each site can have 1 or more charges/fees and these should be stored in a separate table.

 

[June7]

Tell client saving company name in Details is a VERY VERY VERY BAD IDEA. But if you must, will require code (macro or VBA). Real trick is figuring out best event. So, maybe combobox AfterUpdate: Me!CompanyName = Me.Combo91.Column(1)