Combo Box field formation with some partial nulls

[Guest]

That probably doesn't describe things well, but here's what I need:

I have a combo box that, currently, uses a select query added to the
rowsource of the combo box to populate it. The query produces two rows, the
bound one is a contactID, and the unbound, displayed one is the contact name.

Some names have just a first and last name, and that's what my current query
returns. What I want to do is be able to return the full names, which means
I need to, I think, check each name field (first, middle, last) in order, and
if there is an entry for that field (len[field]>0), then I need to add it to
the variable that's returned as the name to be displayed in the control box
in question.

I've seen code that does this, but I can't find it now. It utilizes IF/Then
statements and, I think, a variable ROUGHLY like this:

Dim myvar as String

If len(firstname)>0 Then
myvar = myvar " & "firstname"

Same for middle and last names except myvar = myvar & " " & "[lastname]" or
something like that to add a space in the returned string.

I hope the above will be good enough so someone can understand what I need
the code to do, but I'm sure it's syntax is error-filled. I also don't know
exactly how to go about using the code....I assume it would NOT be able to be
typed in the row source of the control as my current select query is, but
that it has to go either in the object module or in some seperate module or
something.

I'm sure others have seen the code I"m talking about, and just finding that
would be half the battle.

Thanks,
CW

 

[Allen Browne]

No need to use code.
Just set the RowSource of your combo so it clearly spells out the full name,
like this:
SELECT [ContactID],
Trim([LastName] & ", " & [FirstName] & " " & [Middle]) AS FullName
FROM Contacts
ORDER BY [LastName], [FirstName], [Middle], [ContactID];

BTW, if you really do have fields named Last and First, you could run into
problems. These are reserved names.

For a list of the words you need to avoid, see:
Problem names and reserved words in Access
at:
http://allenbrowne.com/AppIssueBadWord.html

 

[Guest]

Thanks Allen!

Like talking to a celebrity...I've used several things from your site
already! Thanks.

Anywho, I DO have fields named [lastName] and [firstName], I hope you're not
saying those are problematic. Unfortunately, back when I started this
project I thought Access was a show about hollywood, so naming considerations
didn't seem like the "big issue" back then.

As for the rest of your post, thanks. I'll try it out. For some reason, I
was hoping to aviod users typing in commas between names, but I couldn't
really tell you why.

My main concern was/is that when I had a similar select query setup, it was
messing things up because it added in a blank space for the middle initial if
there was no middle initial, and the result was [FirstName] (TWO spaces)[
LastName] and, therefore, users could type a first/last name in the combo
box, go through the "notInList" event to add the name to the contacts table
and, therefore, add it back to the combo box, and then STILL be told their
name was "not in the list" because of the descrepency of one name having one
space between the first and last names (the one typed in the text part of the
combo box by the user) and the other (the one added to the combo box choices)
having two.

Anyway, thanks again for everything.
CW

No need to use code.
Just set the RowSource of your combo so it clearly spells out the full name,
like this:
SELECT [ContactID],
Trim([LastName] & ", " & [FirstName] & " " & [Middle]) AS FullName
FROM Contacts
ORDER BY [LastName], [FirstName], [Middle], [ContactID];

BTW, if you really do have fields named Last and First, you could run into
problems. These are reserved names.

For a list of the words you need to avoid, see:
Problem names and reserved words in Access
at:
http://allenbrowne.com/AppIssueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

That probably doesn't describe things well, but here's what I need:

I have a combo box that, currently, uses a select query added to the
rowsource of the combo box to populate it. The query produces two rows,
the
bound one is a contactID, and the unbound, displayed one is the contact
name.

Some names have just a first and last name, and that's what my current
query
returns. What I want to do is be able to return the full names, which
means
I need to, I think, check each name field (first, middle, last) in order,
and
if there is an entry for that field (len[field]>0), then I need to add it
to
the variable that's returned as the name to be displayed in the control
box
in question.

I've seen code that does this, but I can't find it now. It utilizes
IF/Then
statements and, I think, a variable ROUGHLY like this:

Dim myvar as String

If len(firstname)>0 Then
myvar = myvar " & "firstname"

Same for middle and last names except myvar = myvar & " " & "[lastname]"
or
something like that to add a space in the returned string.

I hope the above will be good enough so someone can understand what I need
the code to do, but I'm sure it's syntax is error-filled. I also don't
know
exactly how to go about using the code....I assume it would NOT be able to
be
typed in the row source of the control as my current select query is, but
that it has to go either in the object module or in some seperate module
or
something.

I'm sure others have seen the code I"m talking about, and just finding
that
would be half the battle.

Thanks,
CW

 

[Guest]

PS: My old select query that caused the problems with the spacing didn't
have the "Trim" part to it, which sounds like it might address the spacing
issue....

Thanks again,
CW

Thanks Allen!

Like talking to a celebrity...I've used several things from your site
already! Thanks.

Anywho, I DO have fields named [lastName] and [firstName], I hope you're not
saying those are problematic. Unfortunately, back when I started this
project I thought Access was a show about hollywood, so naming considerations
didn't seem like the "big issue" back then.

As for the rest of your post, thanks. I'll try it out. For some reason, I
was hoping to aviod users typing in commas between names, but I couldn't
really tell you why.

My main concern was/is that when I had a similar select query setup, it was
messing things up because it added in a blank space for the middle initial if
there was no middle initial, and the result was [FirstName] (TWO spaces)[
LastName] and, therefore, users could type a first/last name in the combo
box, go through the "notInList" event to add the name to the contacts table
and, therefore, add it back to the combo box, and then STILL be told their
name was "not in the list" because of the descrepency of one name having one
space between the first and last names (the one typed in the text part of the
combo box by the user) and the other (the one added to the combo box choices)
having two.

Anyway, thanks again for everything.
CW

No need to use code.
Just set the RowSource of your combo so it clearly spells out the full name,
like this:
SELECT [ContactID],
Trim([LastName] & ", " & [FirstName] & " " & [Middle]) AS FullName
FROM Contacts
ORDER BY [LastName], [FirstName], [Middle], [ContactID];

BTW, if you really do have fields named Last and First, you could run into
problems. These are reserved names.

For a list of the words you need to avoid, see:
Problem names and reserved words in Access
at:
http://allenbrowne.com/AppIssueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

That probably doesn't describe things well, but here's what I need:

I have a combo box that, currently, uses a select query added to the
rowsource of the combo box to populate it. The query produces two rows,
the
bound one is a contactID, and the unbound, displayed one is the contact
name.

Some names have just a first and last name, and that's what my current
query
returns. What I want to do is be able to return the full names, which
means
I need to, I think, check each name field (first, middle, last) in order,
and
if there is an entry for that field (len[field]>0), then I need to add it
to
the variable that's returned as the name to be displayed in the control
box
in question.

I've seen code that does this, but I can't find it now. It utilizes
IF/Then
statements and, I think, a variable ROUGHLY like this:

Dim myvar as String

If len(firstname)>0 Then
myvar = myvar " & "firstname"

Same for middle and last names except myvar = myvar & " " & "[lastname]"
or
something like that to add a space in the returned string.

I hope the above will be good enough so someone can understand what I need
the code to do, but I'm sure it's syntax is error-filled. I also don't
know
exactly how to go about using the code....I assume it would NOT be able to
be
typed in the row source of the control as my current select query is, but
that it has to go either in the object module or in some seperate module
or
something.

I'm sure others have seen the code I"m talking about, and just finding
that
would be half the battle.

Thanks,
CW

 

[egerds]

How would you update the combo box options from an array? (To reduce
disk operations, and to utilize existing branching decisions)

 

[Allen Browne]

There's a couple of ways to load a combo from an array.

If you set its RowSourceType to:
Value List
you can then set the RowSource to a delimited list of values, e.g.:
"cat"; "dog", "elephant"

In more recent versions of Access, you can use AddNew to add items to the
list.

The other alternative is a rather cryptic callback function you can use in
the RowSourceType of the combo. There's an example here:
http://allenbrowne.com/func-02.html