Combo Box headache

V

vanlanjl

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

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

CODE
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.

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

Danny Lesandrini

The combo box will, after a selection, show what's in the first column. So,
you've got to combine first and last names in your query's first column,
like this ...

SELECT [Last Name] & ", " & [First Name] & " " & [Initial] As FullName
FROM Query1;
 
D

Danny Lesandrini

what text fields?

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



vanlanjl said:
It still is not populating the txt fields when the name is clicked.

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

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

CODE
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.

CODE
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]));
Click to expand...
Click to expand...
 
D

Danny Lesandrini

I'm sorry, I think I got lost in the explanation below and replied too
quickly.

What you need to do is to add an additional field to the query that is the
concatonation of First, Last and Initial. That way, whatever code is
reading the combo box to pull First and Last will still work.

I would add the concatonated field first, followed by the individual field
values. Set the Columns property to 4 and the ColumnWidths property to
1;0;0;0; to hide all but the first column. Then the code that references
the combo box columns will have to reflect this new change. (You don't show
that code here, do you?)

Where you might have previously called this ...

strFName = Me!cboContact.Column(0)

It will now need to read ...
strFName = Me!cboContact.Column(1)

.... because the zero element is now the full name.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



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

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

CODE
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.

CODE
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]));
Click to expand...
 
V

vanlanjl

text feilds (controls)
txtID
txtCompany
txtLastName
txtFirstName
txtE-MailAddress
txtJobTitle
txtBusinessPhone
txtMobilePhone
txtFaxNumber
txtAddress
xtxCity
txtState
txtZip
txtCountry
txtUserName
txtChargeCode
txtLocationCode

Danny Lesandrini said:
what text fields?

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



vanlanjl said:
It still is not populating the txt fields when the name is clicked.

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

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

CODE
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.

CODE
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]));
Click to expand...
Click to expand...
Click to expand...
 
D

Danny Lesandrini

I think I'm still confused.

If Query1 is used to populate the form, then leave it alone and create a
Query2 to populate the drop-down box. The question now becomes, in my mind,
what happens in the AfterUpdate event of the drop-down box? Why, in fact,
is the box even there? I'm just not getting it.

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



vanlanjl said:
text feilds (controls)
txtID
txtCompany
txtLastName
txtFirstName
txtE-MailAddress
txtJobTitle
txtBusinessPhone
txtMobilePhone
txtFaxNumber
txtAddress
xtxCity
txtState
txtZip
txtCountry
txtUserName
txtChargeCode
txtLocationCode

Danny Lesandrini said:
what text fields?

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



vanlanjl said:
It still is not populating the txt fields when the name is clicked.

:

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

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

CODE
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.

CODE
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]));
Click to expand...
Click to expand...
Click to expand...
 
V

vanlanjl

What code do you want to see?

Danny Lesandrini said:
I'm sorry, I think I got lost in the explanation below and replied too
quickly.

What you need to do is to add an additional field to the query that is the
concatonation of First, Last and Initial. That way, whatever code is
reading the combo box to pull First and Last will still work.

I would add the concatonated field first, followed by the individual field
values. Set the Columns property to 4 and the ColumnWidths property to
1;0;0;0; to hide all but the first column. Then the code that references
the combo box columns will have to reflect this new change. (You don't show
that code here, do you?)

Where you might have previously called this ...

strFName = Me!cboContact.Column(0)

It will now need to read ...
strFName = Me!cboContact.Column(1)

.... because the zero element is now the full name.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



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

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

CODE
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.

CODE
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]));
Click to expand...
Click to expand...
 
D

Danny Lesandrini

Well, I guess I'm not picturing this correctly yet. Are you saying you have
a form with a combo box where a user can select a contact by Last/First
Name? If so, what happens when they select it? Does the form filter for
that person?

As I said in a different post, try using different queries for the combo box
and for the form recordset.

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



vanlanjl said:
What code do you want to see?

Danny Lesandrini said:
I'm sorry, I think I got lost in the explanation below and replied too
quickly.

What you need to do is to add an additional field to the query that is
the
concatonation of First, Last and Initial. That way, whatever code is
reading the combo box to pull First and Last will still work.

I would add the concatonated field first, followed by the individual
field
values. Set the Columns property to 4 and the ColumnWidths property to
1;0;0;0; to hide all but the first column. Then the code that references
the combo box columns will have to reflect this new change. (You don't
show
that code here, do you?)

Where you might have previously called this ...

strFName = Me!cboContact.Column(0)

It will now need to read ...
strFName = Me!cboContact.Column(1)

.... because the zero element is now the full name.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



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

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

CODE
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.

CODE
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]));
Click to expand...
Click to expand...
Click to expand...
 
V

vanlanjl

If i use this code:
Select [tblContacts].[Last Name], [tblContacts].[First Name],
[tblContacts].[Initial] FROM tblContacts;

it will populate the controls when the name is selected, but it only shows
the last name in the combo box. I need it so it will show the Entire name as
ther are employees that have the same last name and even the same first and
last name.

When i use this code:
SELECT [Last Name] & ", " & [First Name] & " " & [Initial] AS FullName FROM
Query1;

It will show the entire name but will not populate the controls and instead
flashes and then goes to a new record format.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

need help for change full name code 1
Create Report Using Form Parameters 2
Expressions 1
File Name 2
help with expression to display customer details 2
How do I get IIf(IsNull.... to work?? 3
Dlookup problem 6
Combo Box Problem 6

Top