Automatically populate field in table based on data from Combobox on form

Question

All,

I have a database that has three associated table to hold the "complete data".  Table_FSRData, Table_Parts, and Table_TicketData.  I have a form that uses the "ticketdata" and "parts" tables to allow data entry by technicians.  On the form I have a combobox that allows the tech to choose his ID number (TechID) which is based on a table (Table_TechnicianDropDown) that contains an auto-number field, TechID field, and TechName field.

I have been trying to get Access to automatically populate the TechName field in the FSRData table based on the TechID that is chosen on the form in the TechID field but I am having trouble with the code.  I think in the After Update property on the TechID field it should be something like the following.....

Me.TechID = Me.TechID.Column(2) In Table.Table_FSRData.TechName

Or something along those general lines.  I keep getting runtime errors (or the macro's box) when tabbing out of the field however.  I have tried changing the code in every way I can think to change it.  What mistake am I making?  I am really a beginner when it comes to programming with SQL and VBA.

Answer 1

Thank you all for the help and suggestions.  Honestly I am still a novice about many aspects of Access.  Relationships being one of them (as well as the programming mentioned in the original post).  I will take the common suggestion and create (or at least try to create) a relationship between the TechIDDropDown and TicketData tables.  The reason behind what I was attempting was that on a report I need BOTH the TechID and TechName to show.  Creating the relationship should be easy (even for a beginner like me) and all I will have to do is change the control source on the TechName field on my report to the DropDown table (I think).  If I missed something please let me know.  As always, you folks are great.  Thank you again for the help.

Answer 2

As both John and Scott have pointed out you should not have a TechName column in FSRData as that introduces redundancy, and means the table is not normalized to Third Normal Form, which requires all non-key columns to be determined solely by the table's primary key.  It is consequently open to the risk of update anomalies.

So, having deleted the TechName column from FSRData, why not give your users the opportunity to either select the ID or the name?  You just need to set up two combo boxes on the form, with TechID as the ControlSource property of each.

To allow selection by TechID the RowSource property of the first combo box would be:

SELECT TechID FROM Table_TechnicianDropDown ORDER BY TechID;

It's other properties can be left as the defaults.

To allow selection by name the second combo box's RowSource property would be:

SELECT TechID, TechName FROM Table_TechnicianDropDown ORDER BY TechName;

The properties of this combo box would be:

BoundColumn:   1

ColumnCount:    2

ColumnWidths:  0cm

If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

Whether the user selects a TechID or a TechName the other combo box will automatically show the correct value.

One caveat: personal names can legitimately be duplicated, so if two or more technicians have the same name (not as unlikely as you might think - I worked with two Maggie Taylors) there will be two or more identical rows in the second combo box's list.  Ideally some other value(s) should be shown in another column or columns in the list to allow them to be differentiated.  You'll find an example in NotOnList.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the opening form differentiates between contacts of the same name by including their address data in the combo box's list.

Answer 3

Please review my blog on displaying data in related tables. It contains techniques to do what you should be doing. As John said, you do not want to store the tech name anyplace but in the techs table. All you need is the TechID as a foreign key to pull the data about the Tech from the Tech's table.

Answer 4

have a database that has three associated table to hold the "complete data". Table_FSRData, Table_Parts, and Table_TicketData 

I have been trying to get Access to automatically populate the TechName field in the FSRData table based on the TechID that is chosen on the form in the TechID field but I am having trouble with the code.

I think in the After Update property code below possible if you really want to save TechName duplicate in different tables.

Me.txtTechName=DLookup("TechName"," FSRData","TechID=CLng(" & Me.txtTechID & ")

Or set TechID Property Widths "0cm;1cm" if you just want display name in TechID comboBox.

you can run a buid in wizard to create combo which give step by step instruction.

Answer 5

You say: "I have been trying to get Access to automatically populate the TechName field "

Well... why? It's redundant. Use the "Grandmother's Pantry Principle": "A place - one place! - for everything, everything in its place". If you store the TechID you can look up the name whenever you want; on a Form just use a combo box displaying the tech name but storing the ID, or put a second textbox on the form with a control source

=TechID.Column(1)

to display the second column (it's zero based). In a Report you would join the two table and pull the tech name from its table.