Combobox has list but selected item in list does not populate

[oldager]

Hi,

Access 2003

I have finally got my list from a table [Agents] (with 3 columns AgentID, AgentName and Phone) to show in my combobox. However, if I click on any name [AgentName] in the list I get a Windows or MS Access error sound. The selected name in the list does not stay visible in the combox control but does stay in the combobox list.

Control Source: =[Agent].[Column](2)
Row Source Type: Table/Query


Row Source: SELECT AgentID, [AgentName] FROM Agents;
Bound Column: 2

Any help much appreciated and thanks in advance.

[ranman256]

i dont think you can set to Agent.column(2)
you can set text fields to columns in the AFTERUPDATE event when user picks from the combo:

sub cboBox_afterupdate()
txtBox = cboBox.column(1)
end sub

then you can query off the text box.
NOTE: in vb, columns begin with zero, so column 2 would be: column(1)

[Bob Fitz]

Try:

Control Source: NameOfBoundField
Row Source Type: Table/Query
Row Source: SELECT AgentID, [AgentName], [NameOfPhoneField] FROM Agents;
Bound Column: 1

Replace NameOfBoundField with the name of the field in the forms Record Source that the data will be saved in.

Replace NameOfPhoneField with the name of the field in the "Agents" table.

I have assumed that "AgentID" is an Auto-Number and is the Primary Key of the "Agents" table

[oldager]

Many thanks for your input, ranman256. However, Bob Fitz gave the answer that I needed. It is working as it should.

Thanks again Bob Fitz; star clicked!

Regards, oldager