COmbo Box Headache

[vanlanjl]

Okay I have a form that has a combo box on it titled "cmbSearchForUser"

Row Source:
SELECT [Query1].[Last Name], [Query1].[First Name], [Query1].[Initial] FROM Query1;

Row source type: table/query
bound coulmn:1
limit to list:yes
allow value list edits:yes
inherit value list:yes
show only row source values:no
enabled:yes
locked:no
auto expand:yes

Here's my problem, the way it is set up now when i select a name in the list, a.) it only shows the last name ( this is a problem because there are multiple users with same last name, in fact i have some users that have the same last name and first name, so i added the Initial field in the tblContacts ), when i click the last name it is showing it will populate the txt boxes on the form.

No i have tried to change the combo box so it would show the last name, first name and middle initial.
What i did was in row source i took out the above sql statement and selected Query1. (code follows)
And it worked, kind of, it did show the last name first name and middle initial, but when selected it would no longer populate the txt boxes in the form, it would set it to a new record. Any ideas what I am doing wrong here? Any help appreciated.

SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name])) AS [Contact Name], tblContacts.*
FROM tblContacts
ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])), IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]));

 

[Remou]

You need an ID field in the first or bound column:

SELECT PersonID, LastName & "," & FirstName & "," & Initial FROM Query1

Tables should always have a unique ID.

http://lessthandot.com

 

[vanlanjl]

I don't think I understand. Do you mean that the code you wrote needs to be in my Row Source instead of the one I have in there?

 

[Remou]

It is an example of the sql statement you need, it will require editing to suit your application. The important points are that the statement I showed includes a unique ID and joins all the fields the user needs to see into one column (surname, forename, initial).

http://lessthandot.com

 

[vanlanjl]

When i put the [ID] part in there it just gives me a number in the drop down box

 

[MajP]

column count: 2
bound column: 1
column widths: 0";1

 

[vanlanjl]

Okay changed those setting and now it just shows the first name

 

[vanlanjl]

with

SELECT [ID], [Last Name] & "," & [First Name] & ", " & [Initial] FROM Query1;

it will show the full name but will not populate the txt fields now, it just goes to a new record

 

[Remou]

If you are populating the controls* from the columns of the combobox, you need to include those columns, but usually it is better to base the form on the query or table, which it seems you have done, and bind the controls to the fields of the table or query.




-----------------------------
* Forms have controls and tables have fields.

http://lessthandot.com

 

[vanlanjl]

Okay the form was bound to tblContacts for the record source so i changed it to Query1. but it still does the same thing.

 

[Remou]

What are your controls bound to?

For example:

Name: txtForename
Control Source: Forename

http://lessthandot.com

 

[vanlanjl]

they are bound to the corresponding field of the query1.

 

[Remou]

You have the fields bound to controls and the combo is finding the right record but the controls are not populating, is that correct?

What happens if you use the navigation keys, do the controls populate?

http://lessthandot.com

 

[vanlanjl]

In response to your question you are correct. As far as navigation keys? Under the property sheet for the form there are two options under format called "Navigation Buttons" = on and "Navigation caption" which is empty. I don't see these for the actual controls themselves though.

 

[Remou]

The navigation keys for navigating through the records, they are normally at the bottom of the form:

|< < 999 > >|

However, that is not important, what I wanted to know is whether the controls populate when you use something other than the combobox to move through the form.

http://lessthandot.com

 

[vanlanjl]

If i use the navigation tool at the bottom of the form and go through the records they do populate the controls. I s this what you mean?

 

[Remou]

Yes. So, what code is attached to the After Update event for the combobox?

http://lessthandot.com

 

[vanlanjl]

it is a macro

Action = SearchForRecord
Arguments = , , First, ="[Last Name] = " & "'" & [Screen].[ActiveControl] & "'

 

[vanlanjl]

Private Sub Combo42_GotFocus()
Combo42.Requery
End Sub

 

[Remou]

You would be much better off with code. If you must use a macro, you will need to change it to match your new set-up, that is, set it to find the ID in the contact table.

http://lessthandot.com