Combo Box based on query. Cannot type in combo box to jump to person.

[Phred]

Access 2007, SQL Server 2008, In development not production,

Not sure which Forum is best so I posted here.

Problem:



I am joining an Entity (person or corporation) to a piece of Real Property using a join table.

dbo_Property Property Table
dbo_Entity Person or Corporation Table
dbo_PropertyEntity Table Joins dbo_Property to dbo_Entity

The following query runs when you click on the down arrow of a combo box located on the Property Form. The query runs fine and selects the correct information for you to choose from. The user can see the Entity Name and Address to verify they are selecting the correct Entity. When they select an Entity the Combo box places the EntityID PK into the join table just fine. However if I just place the cursor in the combo field and try to type a user name it won't jump to the user. I must scroll down to find the person and there may be thousands. The problem is not the query. The question is when you populate a combo box from a query, does it somehow disable the type and jump process?

SELECT dbo_Entity.EntityID, IIf([dbo_Entity]![LastName]<>"",[dbo_Entity]![LastName] & ", " & [dbo_Entity]![FirstName],[dbo_Entity]![NonPersonEntity]) AS Name, [dbo_EntityAddress]![EAddress1] & ", " & [dbo_EntityAddress]![EAddress2] & ", " & [dbo_EntityAddress]![ECity] & ", " & [dbo_EntityAddress]![EState] & ", " & [dbo_EntityAddress]![EZip] & ", " & [dbo_EntityAddress]![EAddressType] & ", " & [dbo_EntityComm]![EPhone] AS Address
FROM (dbo_Entity INNER JOIN dbo_EntityAddress ON dbo_Entity.EntityID = dbo_EntityAddress.Entity_EntityID) INNER JOIN dbo_EntityComm ON dbo_Entity.EntityID = dbo_EntityComm.Entity_EntityID;

I can’t find any articles or posts that directly address this.

Thanks,

Fred

[June7]

A combobox RowSource can be an SQL statement. I don't understand why the SQL is using concatenation.

Is combobox AutoExpand property set to Yes?

[Phred]

The client wants to see the Company Name, First Name, Last Name, and Address to be able to choose amongst lets say several John Smiths. So I just concatenated them together to make it easier since only the EntityID PK is written to the table. Would that cause the problem with typing?

AutoExpand property is set to Yes.

Fred

[June7]

Oh of course, that does make sense.

The AutoExpand should still work.

What are other properties of combobox?

ControlSource
BoundColumn
ColumnCount
ColumnWidths

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

[Phred]

June7

Other Properties of Combobox

Controlsource = EntityID
Row Source = SELECT [JoinEntityToProperty].[EntityID], [JoinEntityToProperty].[Name], [JoinEntityToProperty].[Address] FROM [JoinEntityToProperty] ORDER BY [Name];

Bound Column = 1
Column Count = 3
Column Width 0";2";5"
Allow Value List Edits = no
Inherit Value List = yes
Show only Row Source Values = no
Enables = Yes
Locked = No
AutoExpand = yes

Respectively 0 is primary key, 2= Entity Name, 3= Address concatenation.

I connect ODBC to a Sql Server 2008 r2. I can only send you the screen. Would that help?

Thanks Fred

[June7]

I doubt image would help.

What table is this form bound to - what table is form intended for data entry/edit? Are you sure the combobox ControlSource is bound to field from that table?

Otherwise, I can't see anything wrong with the setup.

Would have to analyse db. If you want to make copy and import data and provide file, follow instructions at bottom of my post.