Combo box selection issue

Hi all,

I have a database which includes two tables - tblCustomer (a list of
customers), and tblSite (a list of sites). Every customer must have
at least one site, but may have any number.

There is a form (bound to tblJob) used to record deliveries to a
site. In order to select which site the delivery went to, I have a
combo box which is bound to tblJob.[siteID]. I populate the rows with
a query which returns two fields - the [siteID] from tblSite, and a
concatenated string composed of [siteID], the name of the customer
from tblCustomer, and the description of the site from tblSite.

So for example, if we had this example data:

tblCustomer (customerID, customerName)
------------------
1, "Texaco"
2, "Shell"

tblSite (siteID, customerID, siteDescription)
---------
1, 1, "Dockside"
2, 2, "Uptown"
3, 2, "Downtown"

The combo box would be populated with these three rows:

1, "1 Texaco (Dockside)"
2, "2 Shell (Uptown)"
3, "3 Shell (Downtown)"

The idea is that the user can just type the site number to jump to the
right row since that is provided on the paper slips.

The problem is that it does not work correctly for customers that have
multiple sites. If the user types 3, in the example, Access will jump
to the correct row, but it will not be highlighted and when the combo
box loses focus an error appears saying they have entered text which
does not appear in the list. If they instead select row 3 with the
mouse, it works fine.

What is the issue here?

Re: Combo box selection issue

On Oct 19, 7:35 am, AllenWhite wrote:
> Hi all,
>
> I have a database which includes two tables - tblCustomer (a list of
> customers), and tblSite (a list of sites). Every customer must have
> at least one site, but may have any number.
>
> There is a form (bound to tblJob) used to record deliveries to a
> site. In order to select which site the delivery went to, I have a
> combo box which is bound to tblJob.[siteID]. I populate the rows with
> a query which returns two fields - the [siteID] from tblSite, and a
> concatenated string composed of [siteID], the name of the customer
> from tblCustomer, and the description of the site from tblSite.
>
> So for example, if we had this example data:
>
> tblCustomer (customerID, customerName)
> ------------------
> 1, "Texaco"
> 2, "Shell"
>
> tblSite (siteID, customerID, siteDescription)
> ---------
> 1, 1, "Dockside"
> 2, 2, "Uptown"
> 3, 2, "Downtown"
>
> The combo box would be populated with these three rows:
>
> 1, "1 Texaco (Dockside)"
> 2, "2 Shell (Uptown)"
> 3, "3 Shell (Downtown)"
>
> The idea is that the user can just type the site number to jump to the
> right row since that is provided on the paper slips.
>
> The problem is that it does not work correctly for customers that have
> multiple sites. If the user types 3, in the example, Access will jump
> to the correct row, but it will not be highlighted and when the combo
> box loses focus an error appears saying they have entered text which
> does not appear in the list. If they instead select row 3 with the
> mouse, it works fine.
>
> What is the issue here?

The problem is the user has not yet entered anything other than "3".
What if one of the entries was 3a? or 34?

The user needs to enter (the enter key) in order to select the
suggested (with autofill) row.

- Larry Engles
Developer since day 1 of Access 1.0.

Re: Combo box selection issue

On Oct 19, 1:14 pm, eng...@ridesoft.com wrote:
> On Oct 19, 7:35 am, AllenWhite wrote:
>
>
>
> > Hi all,
>
> > I have a database which includes two tables - tblCustomer (a list of
> > customers), and tblSite (a list of sites). Every customer must have
> > at least one site, but may have any number.
>
> > There is a form (bound to tblJob) used to record deliveries to a
> > site. In order to select which site the delivery went to, I have a
> > combo box which is bound to tblJob.[siteID]. I populate the rows with
> > a query which returns two fields - the [siteID] from tblSite, and a
> > concatenated string composed of [siteID], the name of the customer
> > from tblCustomer, and the description of the site from tblSite.
>
> > So for example, if we had this example data:
>
> > tblCustomer (customerID, customerName)
> > ------------------
> > 1, "Texaco"
> > 2, "Shell"
>
> > tblSite (siteID, customerID, siteDescription)
> > ---------
> > 1, 1, "Dockside"
> > 2, 2, "Uptown"
> > 3, 2, "Downtown"
>
> > The combo box would be populated with these three rows:
>
> > 1, "1 Texaco (Dockside)"
> > 2, "2 Shell (Uptown)"
> > 3, "3 Shell (Downtown)"
>
> > The idea is that the user can just type the site number to jump to the
> > right row since that is provided on the paper slips.
>
> > The problem is that it does not work correctly for customers that have
> > multiple sites. If the user types 3, in the example, Access will jump
> > to the correct row, but it will not be highlighted and when the combo
> > box loses focus an error appears saying they have entered text which
> > does not appear in the list. If they instead select row 3 with the
> > mouse, it works fine.
>
> > What is the issue here?
>
> The problem is the user has not yet entered anything other than "3".
> What if one of the entries was 3a? or 34?
>
> The user needs to enter (the enter key) in order to select the
> suggested (with autofill) row.
>
> - Larry Engles
> Developer since day 1 of Access 1.0.

It makes no difference... in the actual application, one of the
affected sites is #128. So the user types 1-2-8, Access autofills the
correct row, and whether the user hits Enter, Tab or uses the mouse to
transfer focus, the error message appears.

One odd thing is that Access usually highlights the autofilled entry,
but it does not do so on any site that causes the error.

Re: Combo box selection issue

On Oct 19, 10:42 am, AllenWhite wrote:
> On Oct 19, 1:14 pm, eng...@ridesoft.com wrote:
>
>
>
>
>
> > On Oct 19, 7:35 am, AllenWhite wrote:
>
> > > Hi all,
>
> > > I have a database which includes two tables - tblCustomer (a list of
> > > customers), and tblSite (a list of sites). Every customer must have
> > > at least one site, but may have any number.
>
> > > There is a form (bound to tblJob) used to record deliveries to a
> > > site. In order to select which site the delivery went to, I have a
> > > combo box which is bound to tblJob.[siteID]. I populate the rows with
> > > a query which returns two fields - the [siteID] from tblSite, and a
> > > concatenated string composed of [siteID], the name of the customer
> > > from tblCustomer, and the description of the site from tblSite.
>
> > > So for example, if we had this example data:
>
> > > tblCustomer (customerID, customerName)
> > > ------------------
> > > 1, "Texaco"
> > > 2, "Shell"
>
> > > tblSite (siteID, customerID, siteDescription)
> > > ---------
> > > 1, 1, "Dockside"
> > > 2, 2, "Uptown"
> > > 3, 2, "Downtown"
>
> > > The combo box would be populated with these three rows:
>
> > > 1, "1 Texaco (Dockside)"
> > > 2, "2 Shell (Uptown)"
> > > 3, "3 Shell (Downtown)"
>
> > > The idea is that the user can just type the site number to jump to the
> > > right row since that is provided on the paper slips.
>
> > > The problem is that it does not work correctly for customers that have
> > > multiple sites. If the user types 3, in the example, Access will jump
> > > to the correct row, but it will not be highlighted and when the combo
> > > box loses focus an error appears saying they have entered text which
> > > does not appear in the list. If they instead select row 3 with the
> > > mouse, it works fine.
>
> > > What is the issue here?
>
> > The problem is the user has not yet entered anything other than "3".
> > What if one of the entries was 3a? or 34?
>
> > The user needs to enter (the enter key) in order to select the
> > suggested (with autofill) row.
>
> > - Larry Engles
> > Developer since day 1 of Access 1.0.
>
> It makes no difference... in the actual application, one of the
> affected sites is #128. So the user types 1-2-8, Access autofills the
> correct row, and whether the user hits Enter, Tab or uses the mouse to
> transfer focus, the error message appears.
>
> One odd thing is that Access usually highlights the autofilled entry,
> but it does not do so on any site that causes the error.- Hide quoted text -
>
> - Show quoted text -

Trap the "after update" event on that field. I suspect you'll find
out what you want to know from that.

Good luck.

-- Larry Engles
Access developer since day 1 of Access 1.0

Re: Combo box selection issue

On Oct 19, 7:35 am, AllenWhite wrote:
> Hi all,
>
> I have a database which includes two tables - tblCustomer (a list of
> customers), and tblSite (a list of sites). Every customer must have
> at least one site, but may have any number.
>
> There is a form (bound to tblJob) used to record deliveries to a
> site. In order to select which site the delivery went to, I have a
> combo box which is bound to tblJob.[siteID]. I populate the rows with
> a query which returns two fields - the [siteID] from tblSite, and a
> concatenated string composed of [siteID], the name of the customer
> from tblCustomer, and the description of the site from tblSite.
>
> So for example, if we had this example data:
>
> tblCustomer (customerID, customerName)
> ------------------
> 1, "Texaco"
> 2, "Shell"
>
> tblSite (siteID, customerID, siteDescription)
> ---------
> 1, 1, "Dockside"
> 2, 2, "Uptown"
> 3, 2, "Downtown"
>
> The combo box would be populated with these three rows:
>
> 1, "1 Texaco (Dockside)"
> 2, "2 Shell (Uptown)"
> 3, "3 Shell (Downtown)"
>
> The idea is that the user can just type the site number to jump to the
> right row since that is provided on the paper slips.
>
> The problem is that it does not work correctly for customers that have
> multiple sites. If the user types 3, in the example, Access will jump
> to the correct row, but it will not be highlighted and when the combo
> box loses focus an error appears saying they have entered text which
> does not appear in the list. If they instead select row 3 with the
> mouse, it works fine.
>
> What is the issue here?

I actually recreated what you have described. I created a form with a
combo box and a command button. I could not recreate the combo box
behavior you have described. I even changed the BoundColumn property
on the combo box to 2 and it still worked. I even added more
customers and added two or more sites per customer. I typed the site
number and hit the tab key to change focus to the command button and
it worked just fine.

Some things to check:
* Maybe the database file is currupted? Try a compact and repair.
* Check the way you concatenate the second column of the combo
box. Maybe there's extra white space at the beginning of the string?
* If you have code in the combo box's AfterUpdate event, check that
code.

Re: Combo box selection issue

On Oct 20, 6:21 am, Technolust wrote:
> On Oct 19, 7:35 am, AllenWhite wrote:
>
>
>
> > Hi all,
>
> > I have a database which includes two tables - tblCustomer (a list of
> > customers), and tblSite (a list of sites). Every customer must have
> > at least one site, but may have any number.
>
> > There is a form (bound to tblJob) used to record deliveries to a
> > site. In order to select which site the delivery went to, I have a
> > combo box which is bound to tblJob.[siteID]. I populate the rows with
> > a query which returns two fields - the [siteID] from tblSite, and a
> > concatenated string composed of [siteID], the name of the customer
> > from tblCustomer, and the description of the site from tblSite.
>
> > So for example, if we had this example data:
>
> > tblCustomer (customerID, customerName)
> > ------------------
> > 1, "Texaco"
> > 2, "Shell"
>
> > tblSite (siteID, customerID, siteDescription)
> > ---------
> > 1, 1, "Dockside"
> > 2, 2, "Uptown"
> > 3, 2, "Downtown"
>
> > The combo box would be populated with these three rows:
>
> > 1, "1 Texaco (Dockside)"
> > 2, "2 Shell (Uptown)"
> > 3, "3 Shell (Downtown)"
>
> > The idea is that the user can just type the site number to jump to the
> > right row since that is provided on the paper slips.
>
> > The problem is that it does not work correctly for customers that have
> > multiple sites. If the user types 3, in the example, Access will jump
> > to the correct row, but it will not be highlighted and when the combo
> > box loses focus an error appears saying they have entered text which
> > does not appear in the list. If they instead select row 3 with the
> > mouse, it works fine.
>
> > What is the issue here?
>
> I actually recreated what you have described. I created a form with a
> combo box and a command button. I could not recreate the combo box
> behavior you have described. I even changed the BoundColumn property
> on the combo box to 2 and it still worked. I even added more
> customers and added two or more sites per customer. I typed the site
> number and hit the tab key to change focus to the command button and
> it worked just fine.
>
> Some things to check:
> * Maybe the database file is currupted? Try a compact and repair.
> * Check the way you concatenate the second column of the combo
> box. Maybe there's extra white space at the beginning of the string?
> * If you have code in the combo box's AfterUpdate event, check that
> code.

Thanks for everyone's input. I found the problem, although I don't
understand why it occurs. It may be an Access quirk.

By coincidence, the customers which had multiple sites also had a
Roman numeral at the end of their name - so "Export Company i", for
example - this is a legacy of the old, non-relational design I
inherited. The users who entered the data used lower-case letters for
some reason.

Now, when you use the mouse to select the entry in the combo box, the
"i" is still lower case. If you start typing in the site number, the
autofill operation changes the "i" to uppercase, and the selection no
longer matches the data in the table!

Changing the Roman numerals to uppercase in the database table
eliminates the problem.