Hello again. I have two tables (tblA, tblB). tblA contains a list of names and a numeric ID (key). I use a combo box on a subform with tblB as control source and tblA as row source. This allows the user to scroll through a pre-defined list (tblA) of names and choose one to enter into tblB.

I allow users to enter new names into the combo box and tblB accepts the new names without issue. I'd like the new names to also be added to tblA so that they become a choice and I'd like to do it without using VBA.

(I've had an instance of all my VBA code vanishing for no reason and in 2 months I won't be here to fix this if it happens again.)

Thanks,

Stan

Did you not like the solution Doug Steele posted to your previous post
asking this same question? It is a correct solution, and is the one I'd
have posted if he had not done so first.

Of course, this time you added a requirement that the solution not use VBA
because you once had a bad experience with VBA. So let me address that issue
(note that this is almost identically the same answer I gave to poster
Sparks in this newsgroup, on the post subject: "No database codes work now".

If you have properly split the user interface part (front end) of the
database from the data, placed the data in a folder accessible to each user,
and installed a copy of the front end itself on each user's machine, then it
will be trivially easy to correct the loss of VBA code-- simply reinstall
the front end for the user experiencing the problem.



Chances are, however, that you have not split the database and given each
user a copy of the front end (which would likely/preferrably be an MDE or
ACCDE), because while it may not be possible to determine the cause of the
corruption, experience shows that it typically happens in a copy on which
design and implementation changes are being made, and, often, on a copy of
the database which contains both the user interface (front end part) and the
data (back end part).



And, of course, one should have a production copy (with a pristine,
unchanged copy saved to restore a user's copy) and a separate development
copy on which you make changes.


--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"Aikistan" <stan.mitchell1 (AT) gmail (DOT) com> wrote

Hello again. I have two tables (tblA, tblB). tblA contains a list of names
and a numeric ID (key). I use a combo box on a subform with tblB as control
source and tblA as row source. This allows the user to scroll through a
pre-defined list (tblA) of names and choose one to enter into tblB.

I allow users to enter new names into the combo box and tblB accepts the new
names without issue. I'd like the new names to also be added to tblA so
that they become a choice and I'd like to do it without using VBA.

(I've had an instance of all my VBA code vanishing for no reason and in 2
months I won't be here to fix this if it happens again.)

Thanks,

Stan

On Wednesday, June 27, 2012 10:52:11 PM UTC-4, Access Developer wrote:
Sorry for the double post. When I checked for replies to my original post,it did not appear in the group and I thought I'd abandoned it without posting.

Unfortunately, the (sole) user destined to work with this database has zeroexperience with Access and there is no hope of recovery if the code disappears again. Now I'm being laid off 2 years before anyone expected and haveto take a prototype database and turn it into production in 10 weeks...I have to make it as bulletproof as I can before I go.

Thanks for your help, though.

It seems to me that copying a file is (or ought to be) within the technical
ability of anyone allowed to use a computer. Note that is _all_ the user
has to do... copy your saved, pristine, original copy of the front end and
use that copy. No Access knowledge required.

Some sort of corruption that disables all or part of the code-behind-forms
has existed in Access since v 2.0; it's been reported to Microsoft in each
version and they've thought they had it fixed several times (but seems not
to be corrected in the version you are using).

If you are counting on finding someone who has a reliable workaround to
avoid the corruption you describe without following all those good practices
(which will significantly reduce the likelihood of it happening, but not
guarantee that it will not happen, _and_ make it trivially easy to recover),
then based on my observation of Access, Access developers, and Access users
since Access 2.0, you are likely to be disappointed.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"Aikistan" <stan.mitchell1 (AT) gmail (DOT) com> wrote

On Wednesday, June 27, 2012 10:52:11 PM UTC-4, Access Developer wrote:
Sorry for the double post. When I checked for replies to my original post,
it did not appear in the group and I thought I'd abandoned it without
posting.

Unfortunately, the (sole) user destined to work with this database has zero
experience with Access and there is no hope of recovery if the code
disappears again. Now I'm being laid off 2 years before anyone expected and
have to take a prototype database and turn it into production in 10
weeks...I have to make it as bulletproof as I can before I go.

Thanks for your help, though.

Larry,

I was able to find a work-around. Believe me, if I'd had the time to make this project right, I would.

Stan

"Aikistan" <stan.mitchell1 (AT) gmail (DOT) com> wrote

Don't keep us all in suspense . . . what was the workaround?

On Monday, July 2, 2012 2:38:44 PM UTC-4, Access Developer wrote:
Rather than try to populate a lookup table and the result table, I populated only the result table and used it as the source for lookup and requeried so it would be up to date as of the last entry. It's not perfect, but it works. As a side benefit, now it autocompletes entries.

Sounds good to me... I'm very fond of autocomplete.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"Aikistan" <stan.mitchell1 (AT) gmail (DOT) com> wrote

On Monday, July 2, 2012 2:38:44 PM UTC-4, Access Developer wrote:
Rather than try to populate a lookup table and the result table, I populated
only the result table and used it as the source for lookup and requeried so
it would be up to date as of the last entry. It's not perfect, but it
works. As a side benefit, now it autocompletes entries.