Autofill Combo Form Always Selects First Record If Same Name

[Wayne]

I have a contract billing form with a customer address subform.
Since some of our contracts are with the same customer the address is
the same so I created an address subform (with its own table) with a
combo box that autofills the address. We also have customers with
"different" addresses and here's the problem I'm having. I noticed if
the customer name dropdown combo box is the same it "always" selects
the first record for that name so I decided I need a unique name in
the combo like VONS Market(1), VONS Market(2), etc. Now how to do
that. I could do a dlookup on the address table and figure out what
is the next number I use for the same customer like; VONS Market(XX).
This seems to be a lot of work for such a simple thing. Is there an
easier way to do this?

 

[John W. Vinson]

I have a contract billing form with a customer address subform.
Since some of our contracts are with the same customer the address is
the same so I created an address subform (with its own table) with a
combo box that autofills the address. We also have customers with
"different" addresses and here's the problem I'm having. I noticed if
the customer name dropdown combo box is the same it "always" selects
the first record for that name so I decided I need a unique name in
the combo like VONS Market(1), VONS Market(2), etc. Now how to do
that. I could do a dlookup on the address table and figure out what
is the next number I use for the same customer like; VONS Market(XX).
This seems to be a lot of work for such a simple thing. Is there an
easier way to do this?

I'm not sure I'm visualizing your setup correctly! What tables do you have,
and how are they related? What is the combo's rowsource? How are you
"autofilling" the address?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/

 

[Wayne]

I'm not sure I'm visualizing your setup correctly! What tables do you have,
and how are they related? What is the combo's rowsource? How are you
"autofilling" the address?

Thanks for responding.

Three tables. The tblCustomerAddress(many) table has a 1 to M
relationship with the tblMainBilling(one) table so there are no
duplicate addresses. The tblMainBilling table has a subform for
billing details.

Combo's Control Name = cboCustomerName
Column Count = 5 (there are about 15 fields but just working with the
first 5 for now)
Column Widths = 1";0";0";0";0"
Combo's Row Sourse = qryCustomerAddress (query)

Autofill fields using formula in each of the 5 Control Source fields
like this: =[cboCustomerName].Column(4)

The autofill works good but always fills with first record found if
the CustomerName is the same.

I need a unique name in the combo field so I get the correct address
like above - VONS Market(8). I could write VBA code to search for the
last entry for example:VONS Market(XX) but that seems to be a pain.
Is there an easier way?

 

[Wayne]

I'm not sure I'm visualizing your setup correctly! What tables do you have,
and how are they related? What is the combo's rowsource? How are you
"autofilling" the address?
--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Forums/en-US/accessdev/http://social...

Thanks for responding.

Three tables.  The tblCustomerAddress(many) table has a 1 to M
relationship with the tblMainBilling(one) table so there are no
duplicate addresses.  The tblMainBilling table has a subform for
billing details.

Combo's Control Name = cboCustomerName
Column Count = 5 (there are about 15 fields but just working with the
first 5 for now)
Column Widths = 1";0";0";0";0"
Combo's Row Sourse = qryCustomerAddress (query)

Autofill fields using formula in each of the 5 Control Source fields
like this:  =[cboCustomerName].Column(4)

The autofill works good but always fills with first record found if
the CustomerName is the same.

I need a unique name in the combo field so I get the correct address
like above - VONS Market(8).  I could write VBA code to search for the
last entry for example:VONS Market(XX) but that seems to be a pain.
Is there an easier way?- Hide quoted text -

- Show quoted text -

Sorry. The tblCustomerAddress is the ONE side of the 1 to M with the
tblMainBilling table.

 

[Wayne]

Thanks for responding.

Three tables.  The tblCustomerAddress(many) table has a 1 to M
relationship with the tblMainBilling(one) table so there are no
duplicate addresses.  The tblMainBilling table has a subform for
billing details.

Combo's Control Name = cboCustomerName
Column Count = 5 (there are about 15 fields but just working with the
first 5 for now)
Column Widths = 1";0";0";0";0"
Combo's Row Sourse = qryCustomerAddress (query)

Autofill fields using formula in each of the 5 Control Source fields
like this:  =[cboCustomerName].Column(4)

The autofill works good but always fills with first record found if
the CustomerName is the same.

I need a unique name in the combo field so I get the correct address
like above - VONS Market(8).  I could write VBA code to search for the
last entry for example:VONS Market(XX) but that seems to be a pain.
Is there an easier way?- Hide quoted text -

- Show quoted text -

Sorry.  The tblCustomerAddress is the ONE side of the 1 to M with the
tblMainBilling table.- Hide quoted text -

- Show quoted text -

I have also noticed that the Combo Box always overwrites the first
record in the table. For example if I have Customer A, Customer B and
Customer C in the combo box and I select Customer C it autofills the
rest of the fields correctly but if I close the form and reopen it
Customer A has been overwritten with Customer C. How can I prevent
this?

 

[John W. Vinson]

Combo's Control Name = cboCustomerName
Column Count = 5 (there are about 15 fields but just working with the
first 5 for now)
Column Widths = 1";0";0";0";0"
Combo's Row Sourse = qryCustomerAddress (query)

Autofill fields using formula in each of the 5 Control Source fields
like this: =[cboCustomerName].Column(4)

The autofill works good but always fills with first record found if
the CustomerName is the same.

Of course. It's filling in the value that you selected from the combo box.

If the rowsource for the combo box has

VONS Market
VONS Market
VONS Market
VONS Market
VONS Market

how can you possibly expect Access to magically guess which of the five you
want??? Does your table have a unique MarketID?

I need a unique name in the combo field so I get the correct address
like above - VONS Market(8). I could write VBA code to search for the
last entry for example:VONS Market(XX) but that seems to be a pain.
Is there an easier way?

I'm still not clear on your table structure, but I'd be inclined to use a
table with field such as MarketID, MarketName, City, Address - displaying the
latter three, so you would see

VONS Market; Pasadena; 2355 E. Colorado Blvd.
VONS Market; Pasadena; 1390 N. Allen Ave.

The bound column would be the unique MarketID (not the market name), and the
user would be able to select the specific market they intend, rather than
blindly taking the first.

To respond to your later message today, if you're using this combo to *LOCATE*
or display an existing record, rather than to update an existing record, then
it must be unbound - nothing in its Control Source - and you'll need either a
macro or code in its AfterUpdate event to jump to the selected record. What's
the Recordsource for your form? What's the Rowsource of the combo?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Wayne]

Combo's Control Name = cboCustomerName
Column Count = 5 (there are about 15 fields but just working with the
first 5 for now)
Column Widths = 1";0";0";0";0"
Combo's Row Sourse = qryCustomerAddress (query)

Autofill fields using formula in each of the 5 Control Source fields
like this:  =[cboCustomerName].Column(4)

The autofill works good but always fills with first record found if
the CustomerName is the same.

Of course. It's filling in the value that you selected from the combo box..

If the rowsource for the combo box has

VONS Market
VONS Market
VONS Market
VONS Market
VONS Market

how can you possibly expect Access to magically guess which of the five you
want??? Does your table have a unique MarketID?

I need a unique name in the combo field so I get the correct address
like above - VONS Market(8).  I could write VBA code to search for the
last entry for example:VONS Market(XX) but that seems to be a pain.
Is there an easier way?

I'm still not clear on your table structure, but I'd be inclined to use a
table with field such as MarketID, MarketName, City, Address - displayingthe
latter three, so you would see

VONS Market; Pasadena; 2355 E. Colorado Blvd.
VONS Market; Pasadena; 1390 N. Allen Ave.

The bound column would be the  unique MarketID (not the market name), and the
user would be able to select the specific market they intend, rather than
blindly taking the first.

To respond to your later message today, if you're using this combo to *LOCATE*
or display an existing record, rather than to update an existing record, then
it must be unbound - nothing in its Control Source - and you'll need either a
macro or code in its AfterUpdate event to jump to the selected record. What's
the Recordsource for your form? What's the Rowsource of the combo?
--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com

Thanks John. I was displaying the results only so now I'm redesigning
so the query combo results and write directly to the the table fields
like; me.txtCustomerName=me.cboCustomerName.Column(1). It looks much
better now.