ComboBox Question

[Bonnie]

Hi,

I have a combobox with 5 columns. The first field is a company name, the
second the street address, etc. Some company names have multiple addresses -
when I select the second occurrence of the company name and exit the
combobox, I programmatically fill the address information. My problem is
that when I select the second occurrence of the company name it defaults to
the first occurrence and fills the address info with it instead of the one
that I selected?

Any body have any suggestions?

Thanks,

Bonnie

 

[Larry Linson]

What data does it reference? How is the data organized? What is the query
for RowSource of the Combo Box? I can only guess at the problem without more
detail; please clarify.

Larry Linson
Microsoft Office Access MVP

 

[alemanp]

Hi,

I have a combobox with 5 columns.  The first field is a company name, the
second the street address, etc.  Some company names have multiple addresses -
when I select the second occurrence of the company name and exit the
combobox, I programmatically fill the address information.  My problem is
that when I select the second occurrence of the company name it defaults to
the first occurrence and fills the address info with it instead of the one
that I selected?

Any body have any suggestions?

Thanks,

Bonnie

When you have a combobox that doesn't use a unique identifier as the
first field, it could cause problems. Your example is a good one
because you may the following values in a combo box. (colons are
delimiters)

ABC Co.: 123 Main St.: St Cloud: MN:56301
ABC Co.: 400 2nd St SE: Willmar: MN: 56201

If you select the Willmar location, your combobox vb code will
probably issue a findfirst command. If so, the command will retrieve
the St Cloud location. If this is true of your combobox, then include
a unique identifier in your combo box list, like below.

1:ABC Co.: 123 Main St.: St Cloud: MN:56301
2:ABC Co.: 400 2nd St SE: Willmar: MN: 56201

This will resolve your problem. However, you must do the following to
your combo box properties:
Column Count - Increment your column count by 1 to include the unique
identifier.
Column Widths - (important) Make your first column width 0. So that
it will be there, but not visible. Should look something like this:
0;1.2;1;0.2;0.5

Also, in your code to populate the address, you must use the Column
property of the combobox to get the address:
Address1 = Me.AddressCombobox.Column(1)
City = Me.AddressCombobox.Column(2)
State = Me.AddressCombobox.Column(3)
ZipCode = Me.AddressCombox.Column(4)

HTH

Pete Aleman
Vancouver WA

 

[Bonnie]

Sorry, your absolutely right. Here's the query for the data source.

Thanks,

SELECT DISTINCT TEST1.OFFICEORG, TEST1.SLNUM, TEST1.ADDRESS1, TEST1.CITY,
TEST1.STATE, TEST1.ADMINNAME, TEST1.SCHOOL, TEST1.SPECCODE, TEST1.SPECIALTY,
TEST1.CERT, TEST1.ZIP FROM TEST1 WHERE (((TEST1.LIST)="OKMTG") And
((TEST1.LICTYPE)=FORMS!BATCHmtg.TEXT11));

 

[Bonnie]

Thanks for replying Aleman,

I actually do understand this and I fooled with a calculated value (company
& address), the problem is that I need the bound column to be just company?
So the field value needs to end up being just company after selecting from
the combobox.

Bonnie

 

[alemanp]

Thanks for replying Aleman,

I actually do understand this and I fooled with a calculated value (company
& address), the problem is that I need the bound column to be just company?  
So the field value needs to end up being just company after selecting from
the combobox.

Bonnie











- Show quoted text -

Bonnie,
I understand. Change the Bound Column property of the address combo
box to match the Company Name column. (It looks like it is the .) And
keep the first column as a calculated value to be unique. I'll try to
put an example together from the row source you posted earlier.

This is what you posted:
SELECT DISTINCT TEST1.OFFICEORG, TEST1.SLNUM, TEST1.ADDRESS1,
TEST1.CITY,
TEST1.STATE, TEST1.ADMINNAME, TEST1.SCHOOL, TEST1.SPECCODE,
TEST1.SPECIALTY,
TEST1.CERT, TEST1.ZIP FROM TEST1 WHERE (((TEST1.LIST)="OKMTG") And
((TEST1.LICTYPE)=FORMS!BATCHmtg.TEXT11));

Change it to the following:
SELECT DISTINCT (TEST1.OFFICEORG & TEST1.ADDRESS1) AS ROWKEY1,
TEST1.OFFICEORG, TEST1.SLNUM, TEST1.ADDRESS1, TEST1.CITY,
TEST1.STATE, TEST1.ADMINNAME, TEST1.SCHOOL, TEST1.SPECCODE,
TEST1.SPECIALTY,
TEST1.CERT, TEST1.ZIP FROM TEST1 WHERE (((TEST1.LIST)="OKMTG") And
((TEST1.LICTYPE)=FORMS!BATCHmtg.TEXT11));

I am assuming that the TEST1.OFFICEORG can be used as the company
name. Simply use whichever column is the company name in its place.

HTH

Please call me 'Pete'.

 

[Bonnie]

I think that's what I'm looking for Pete. Thanks very much - I'll try it
out in the morning.

Thanks again.

 

[Bonnie]

Hi Pete,

I've been trying to get this to work but I'm still not having success. What
column should be bound to have the officeorg as the value of the field? I've
tried them all and not having success??

Thanks,

Bonnie

 

[m3]

I think your problem is on how you move your data form the combobox to your
fields.
Everytime you click on the combobox, Access will keep track of which row of
data you've select and will only return column data in that row(e.g.: with
combobox.column(n)). It does NOT matter whether it is the same as in the
other row or not.

 

[Bonnie]

No, if you re-read above the problem is that if there is a bound column that
has the same data as other rows - though I select the second occurrence, it
defaults to the first occurrence.

 

[Bonnie]

Got it Pete - your solutions worked. Must have been programmer error - I
redid everything and it worked.

Thanks,

Bonnie