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]));

 

[vanlanjl]

I wouldn't even know how to begin with using code. I have gotten most of this stuff from forums and people like you and from looking at Access template databases, but thank you for all your help!

 

[vanlanjl]

So if I use this code:

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

it will populate the controls but will only list the Last Name in the combobox

 

[Remou]

Leave the combo selecting ID and name, this is the correct way, because the user can be sure they are getting the right person. Change the macro to something like:

Action = SearchForRecord
Arguments = , , First, ="[ID] = " & [Screen].[ActiveControl]

http://lessthandot.com

 

[vanlanjl]

Nope - still doesn't work

 

[vanlanjl]

Its almost like it wants to do it - it flashes the data but returns to a "new" in the controls

 

[Remou]

Ok. I am not good with macros, let us try some code, ok?

Can we say that you have an ID field in query1, that the combo row source is:

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

And that the ID field is numeric and unique for each person, an autonumber, for example? If so ...

Change the name of the combobox to cboGoTo.

Change the after update event to:

[Event Procedure]

Click the three dots ... to the right of After Update, on the same line, to open the module window, and cut and paste this code into the module:

Private Sub cboGoTo_AfterUpdate()
    Me.Recordset.FindFirst "ID=" & Me.cboGoTo 
End Sub

http://lessthandot.com

 

[vanlanjl]

Delivers a syntax error

 

[Remou]

Is the bound column of your combobox called ID and is it numeric?

http://lessthandot.com

 

[vanlanjl]

Now it just beeps

 

[Remou]

What is the table that your form is bound to and what are the fields?

http://lessthandot.com

 

[vanlanjl]

It is bound to Query1
File As
Contact Name
ID
Company
Last name
First Name
Initial
E-mail Address
Job title
Business Phone
Home Phone
Mobile Phone
Fax Number
Address
City
State/Province
Zip/Postal
Country/Region
User Name
Charge Code
Location Code

Design View:

File As: IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name]))

Ascending

Contact Name: IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]))

Ascending

tblContacts*
tblcontacts

sql view

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]

That is somewhat confusing. Is this the recordsource for the form? if so, it does not have ID listed. It is also very complicated.

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]));

http://lessthandot.com

 

[vanlanjl]

that is the sql view of query1

 

[Remou]

You have the same problem, it must include the ID field.

The simplest set up is to create a query that is the same as the table and use that as the Record Source for your form, or even use the table while you are trying this out.

If you create a simple form based on the table and add a combobox using the wizard, choosing 'find a record on my form' from the options and making sure you include the ID as the matching field, it will do pretty nearly everything you want to do for you. You can then examine this form and see how it applies to the form you are discussing here-

http://lessthandot.com

 

[vanlanjl]

Wow that was simple -- thanks -- works just fine