Datasheet field value for a combobox displays correctly until the database is split

[miteleda]

I have a table called Fruit. It has 3 columns


Id - autonumber column
FruitName
Available - When false we have run out of that fruit.


I have a second table called KidsChoice. This table identifies the kid and their choice of fruit so it has columns
KidName
FruitId - contains an Id from the Fruit table


I have a form which displays the KidsChoice as a datasheet. The FruitId column is a combo box displaying the FruitName. It's row source by default is all the Fruit records. When the combo box gets focus its row source is changed (via GotFocus event) to only the available fruit. When the combo box loses focus its row source is changed (via LostFocus event) back to all fruit.


This very nicely allows unavailable fruit to be displayed in the datasheet where it had been once available and chosen in the past. At the same time it restricts any new fruit selections to available fruit. This is exactly what I want. Further more, I can arrow through the cells, traversing over no longer available fruit choices or available fruit choices and the cells display their fruit names happily at all times.


Now I split the database into front end and back end. And my problem begins (both in version 2003 and 2007).


When I display the datasheet and give focus to a cell containing an invalid fruit and then arrow up or down (not left or right) to a different record, the invalid fruit name is no longer visible, a blank cell appears instead. When I arrow back to the cell, the name appears again. If I arrow left to the KidName the invalid fruit name appears and if I now arrow to another record the invalid fruit name remains displayed.


I'm trying to understand why it works in the unsplit database and not in the split one. More importantly I'm trying to get the bugger to work. Any assistance is appreciated thanks.


Attached are 2 zip files with (one for 2003 version and other for 2007)
FruitChoices database to demonstrate the nicely working display of invalid fruit
FruitChoicesSplit databases to demonstrate the misbehaving display of invalid fruit
2007 version - FruitChoicesSplit.zip
2003 version - 2003FruitChoicesSplit.zip

In the databases "Orange" is the unavailable fruit. It correctly displays in the first but not in the last.

[June7]

One solution is not to use lookup. Instead of saving the fruit ID, just save the fruit name. The names are short, database is small, memory is cheap. No code and no issue.

In testing both split and unsplit, I get different results depending on whether I use tab, enter, cursor keys, or mouse into combobox. Even the split version works fine using tab, enter, left, right keys. The UP and DOWN cursor keys and mouse mess up in the split but not in the unsplit.

[miteleda]

Thanks for taking a look June7. That would be a great solution in this little database. Unfortunately I produced this to isolate the issue. In the real database it is possible for the equivalent of the fruit name to have duplicates. I realise this is not good design but I was leading the client through the software process in as gentle a manner as possible. Some issues I stood firm on, others I allowed. The client wants, the client gets.

[Rainlover]

Get rid of the Lookup in the Table. Never use Lookups in Tables. Only use them in Forms.

Delete the code behind the Combo Box. I do not see any advantage in its use.

Change your SQL to show only Available Fruit.

BTW I could not create an error.

[June7]

Rainlover, I agree about lookups set in table and I never do that. However, that is not cause of issue here. I think OP wants the fruit name previously saved in record to display regardless if it is now not an available choice. That does make me wonder why it is necessary to show the previous choices if purpose of this form is for child to make a selection from the currently available options.

[orange]

I think the OP should tell us in plain English what she needs exactly.
Perhaps a sample with what she has and whats she wants would help.

[June7]

Orange, I agree seems we need more info on requirements but did you see the files attached to original post?

[orange]

I didn't originally, but have looked now.
I extracted the fe and be databases.

My first suggestion is to get rid of the lookup in FruitId in the KidsChoice table, as others have said.
I would not use a table and form with the same name.

I don't know what the OP is trying to do.
What is the purpose of the Availability flag if you don't use it?

Tell us explicitly what you want to happen, and I'm sure someone will have an answer.

I haven't tried the various keys as June7 has, but what do you want to happen.
We are seeing something that has been built and seem to be being asked to explain something without knowing what you really want.

In my view, you have a specification and you build to make it work. The fact that you have built something, are using lookups at the table level, and are asking general questions, to me at least, is not focusing on the problem.

[miteleda]

Table lookup is now removed. Thanks for the tip.

The Requirement

My client is a nursing agency. I have a form which details a nurse's work day. In it is the nurse and date as well as travel time and travel kilometres. It also has a subform displaying all patients visited that day. It is possible to navigate to prior days to see nurses' work days in the past.

Patients get better, patients die. These become inactive patients. When entering a patient that has been visited by a nurse, the patient name is selected from a combo box. We do not want to see the inactive patients. They get quite numerous over time. If the combo box is filtered to only retrieve active patients then the inactive ones show as blank when looking at prior days containing those patients when they were once active. If no filter is used then the list is undesirably cumbersome.

So the requirement is to be able to display patient names on the sub form of the nurse work day form regardless of whether they are active or inactive AND to restrict the combo list to active patients only.


It seems a popular way to address this is to sort the combo list so inactive are at the bottom. This works reasonably for mouse but for keyboard use where the first part of a patient name is typed it is not so good. It is also not so good because of how long the list becomes.

I have attempted to address it by changing the rowsource query in the gotfocus and lostfocus events which works perfectly on the single database but failed on the split database. And that is why I have the code behind the combo box.

[June7]

Well, knowing that your 'real' db is not concerned with children and fruit does give a different perspective on the situation. However, I still can't see a resolution to your issue other that what is already offered. It is a well-known one but as far as I knew nonsplit as well as split db exhibited the behavior. I am surprised by the variance.

[miteleda]

I was hoping someone had seen the situation before of the split versus non-split and had determined a solution. Thanks for your thoughts.

[Rainlover]

If you change the Row Source of the Combo Box you are going to change what the Combo Box displays.

The only possible solution that I can think of is for you to perfect the code behind the Box. (I did say before it was wrong but I have changed my mind.)

You need to change it to show a restricted choice when selecting then back to a show all when finished.

My memory is vague on this but in 2003 the installing of the service packs can cause problems with Combo Boxes. To fix this install the Hot Fix. I remember doing this some years ago but I can't remember the full circumstances.

Finally you should fix your design. There are no Relationships and there should be. I know this is just a Demo but you should always show your best effort it you want others to give their best attention. In other words don't post garbage when you know better. Don't mean to be rude just getting a point across.

[Rainlover]

I was hoping someone had seen the situation before of the split versus non-split and had determined a solution. Thanks for your thoughts.

Does this mean you are done and don't want any further help.