Updating entries in combo boxes

[Richard12]

Hi



I wonder if anyone can help. I have two tables and their associated forms. Agent Firm and Agent Person. I have created a one (Agent Firm) to many (Agent Person) relationship because a Firm can have many Persons (not vice versa).

I have also created a combo box autofill so that some of the information from the Agent Firm can be posted on the Agent Person.

Query one: I tried using text for the AutoFirm ID because each firm's name is unique. However, when I tried to see what happened if the firm changed its name, still having a unique value, I was not allowed to. Why? and can I over-rule this? For the time being I have used an autonumber as an ID, but I feel that is inefficient.

Query two: I have set up a combo-box, which works, using the following:
Private Sub cboAgentFirmID_Change()
Me.txtAddress1.Value = Me.cboAgentFirmID.Column(2)
Me.txtAddress2.Value = Me.cboAgentFirmID.Column(3)
Me.txtAddress3.Value = Me.cboAgentFirmID.Column(4)
Me.txtTown.Value = Me.cboAgentFirmID.Column(5)
Me.txtPostcode.Value = Me.cboAgentFirmID.Column(6)
End Sub

I originally had a first line Me.txtAgentFirm.Value = Me.cboAgentFirmID.Column(1) but deleted it because the AgentFirm is being entered in the AgentFirmID box (why??)

AgentFirmID, which is the autonumber primary key (see query 1) in Agent Firm is the link between the two tables in the relationship.

Query three: A major feature should be that if an AgentFirm changes its name, or address, phone number, etc this is reflected immediately across all related entries. And it does not happen: each one has be updated manually. (Imagine if a bank changed its name and had to alter each account manually!) How do I make it work as I am sure it is meant to?

Thanks

Richard

[June7]

If you link on the text AgentFirm fields, cascade update should allow the PK to be edited and it would propagate to related records. This is not the relationship in the posted db. I changed the tables and it does work.

Other data saved into related tables will not automatically update. Ideally, the address data would be in only one place, tblAgent_Firm. Then the AgentFirmID would be saved into tblAgentPerson. Address info would be retrieved by query that joins tables.

Number type PK/FK supposed to be more efficient - faster indexing than text values.

[Richard12]

Hi June7

Thank you for a very swift reply.

The db as it stands is just a very small part of what I hope will be a much larger one with several strands, for example, a) Contacts and their Agents, b) Projects, Contracts, and Contractors, c)Payments, by category (access, compensation, etc). I hope that they will all be able to be queried and reported pretty much every which way.

You say the address data should be in only one place. And I understand that completely from an entry point of view. But, at least initially, I would like to be see individual strands of data in a flat non-relational way. So address data is only entered once, (in the AgentFirm form) but it will be shown when I look up an individual AgentPerson.

I am also very concerned that data is not updated. Why? Surely, if a firm changes a detail in its contact data, in the model I wish to have any change should be instantly reflected in all related tables, forms etc?

Amended db attached.

What am I missing?

Thanks

Richard

[Richard12]

Further to my last reply, here is a thing. I have made AgentFirm, which is alphabetic and unique, the primary key in the Table and Form AgentFirm. It is the link in the relationship

I have also deleted the redundant AgentFirmID, which was an autonumber primary key, from the AgentFirm table and form, from the AgentPerson form, and from the One-to-Many relationship. It is no more; It is a dead parrot. However, when I try to delete it as a field from the AgentPerson table the AgentFirm value is indeed removed from the AgentFirm field and but the remaining fields in the combo are shifted left, which is a nonsense.

What is going on? And why?

Richard

[June7]

The posted db still has the autonumber ID fields as PK/FK.

Editing the address fields in tblAgent_Firm will not affect address fields in tblAgentPerson. Why should it? Just because fields in different tables have same names means nothing. There is no inherent link between these fields. These fields are not 'aware' of each other.

A primary concept of relational database is to not duplicate data between tables. The purpose of PK/FK is to link tables in queries so that related data can be viewed.

[ssanfu]

These sites might 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


Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm

[Richard12]

Thank you for your help. My replies are slow because I am trying to read up and practice Access so I can become more adept.

This time I have added the correct db: the PK in Agent Firm is now alphabetic. And is editable. Ssnafu, I can easily revert to having an autonumber PK but this will take me back to my original question about its use in the Combo.

June, I thought one of the primary concepts of a relational database is to eliminate duplicate data entry, but not necessarily viewing. At this stage I want to be able to have single data entry (obviously), but to be able to view part of the Agent firm data in Agent Person. It may seem naïve and old-fashioned, but when I draw out a particular Agent Person, I actually want to see some of the details of who he works for. As I get more experienced I may change my view but that is as it is now.

So I was under the impression that a Combo box with many column was the answer, and in part it seems to be. But I do not understand why alterations to the information that is in the underlying table is not posted automatically.

June, you say: "Editing the address fields in tblAgent_Firm will not affect address fields in tblAgentPerson. Why should it? Just because fields in different tables have same names means nothing. There is no inherent link between these fields. These fields are not 'aware' of each other." I realise the names themselves mean nothing and that a link needs to be created. It has been - the Combo box. also I really do not understand why the field AgentFirmID, which no longer exists in either the AgentFirm form or table and is not referred to in the Combo, cannot be removed from the AgentPerson table without ruining the link.

Again, thank you for your assistance. I will prevail, even though it may take time!

Richard

[June7]

View related data by joining tables in queries.

What link is ruined? Had no problem deleting AgentFirmID field. Combobox works.

[Richard12]

Hi June7

Thank you for your help.

And yes now the deletion of the redundant AgentFirmID works! Please believe me it used not to. Sometimes I think it depends on which side it its bed Access gets out of in the morning as to whether it does what I want it to.

On the updating matter, I am still not happy with this. I changed some of the details in the DB I sent you (I attach both) - I changed the AgentFirm Beta to Delta and some of its address fields. This was reflected in the AgentFirm table. It also changed the AgentFirm details in AgentPerson form and table. But it did not update the address fields until I went to the AgentPerson form and updated each record manually. Given that they are linked by the Combobox, why not? You will notice that the first AgentPerson works with AgentFirm Omicron. The address fields do not reflect this until manually updated in the entry form.

Btw, you answered a very similar problem on 12/14/14 entitled "Enter employee name, and their company automatically populates in a table" thus:

Users should not work directly with tables and queries. Can't really do what you want in a table. Use form.

Have a multi-column combobox that includes fields for employee info, including their company. An expression in textbox can reference columns of combobox to display the info.

So I am not sure why you are encouraging me to use a query now. Particularly because at the entry stage you do not know the query to ask and queries are not end-user friendly.

Thanks

Richard12

[June7]

I am saying you (the developer) build queries and use them as form/report RecordSource and combobox/listbox RowSource. Actually, I more often build SQL statement directly in those properties instead of a referencing a query object. Users (your clients) should not work directly with tables and queries.

A combobox/listbox/textbox can be bound to only one field. That is the only field the control can make edits into. I don't know how better to explain. Editing the address info in AgentFirm table will not modify the duplicate address info in AgentPerson. Period. Should not duplicate save address info in AgentPerson. There is no 'connection' between these fields, not even with the combobox. Could delete the address fields from AgentFirm and the address fields in AgentPerson would be unaffected.

[Richard12]

Hi June 7, and others, Thank you for your continuing help.

As I understand it 'Users' should mainly be involved with Forms, for inputting, and Reports, for outputting. They should not be using Queries and Tables, which are mostly limited to the 'developers'. Is this correct?

I understand that the connection between Comboboxes and the more than one field is pretty weak. They will enter the information correctly but not update when that data is changed. This seems a pretty serious flaw. What is the solution? It may be a subform, or something. Please advise me. It cannot be a query because that presupposes the question.

What I am after at this stage is a form that looks like an old-fashioned file card. On it will be information that is unique to the entry, and additional information that is drawn from other tables so as to avoid repeating data entry. It must also update this additional information if it gets altered without my having to change each record individually and manually.

Btw, this is merely one strand (Contacts) of a much more complex database which will ultimately involve Projects and Accounts and maybe more.

Thanks

Richard

[June7]

Correct, users should not interact with tables and queries.

The rest of your post does not make sense to me. I don't recognize the 'flaw' you perceive.

[Richard12]

Hi June 7

Do you ever sleep?

Glad to know I have got something right!

I can explain the problem more clearly perhaps by going on to the next step. Once I sort this out I will be adding a table/form called Contacts. The form will look much like an old-fashioned index card. On it will be details unique to the contact (name, address, phone etc), and details that might be common to many (Agent and the minimal Agent details). So I might end up with many Contacts, all different people, but sharing one agent. The agent details can be added using the combobox system as I am using it now. Fine, except that if the Agent moves firm or the office address changes these changes are not automatically reflected in the overlaying tables and forms.

My user wants to be able to enter changes to the Agent Firm (name of firm, address) etc on the Agent Firm form and see these changes reflected on the Agent Person form. Likewise, when opening the Contact form, changes to the Agent Person and Agent Firm should change on existing Contacts without having to visit every entry individually.

Thanks

Richard

[June7]

Save only the AgentFirmID into AgentPerson record. Options for displaying the associated AgentFirm address info on form, in order of preference:

1. multi-column combobox listing the AgentFirm records, then textboxes can reference columns of combobox

2. the form's RecordSource is a query that joins the two tables

3. DLookup()

If address info is edited in AgentFirms, it will be reflected in the AgentPerson form.

An alternative that does not use combobox is a form/subform arrangement. Main form bound to AgentFirms and address info displayed on main form. Subform bound to AgentPersons. Enter record into subform and the AgentFirmID will automatically be saved into AgentPerson record.