Deleting items in a Table

[Guest]

Hi,

I created a database that used combo boxes in forms for the Business Unit,
when the Business Unit is selected it will populate the other fields,
address, City, State, Telephon, etc... My boss went in to tried to update the
address on the table called directory which has all the information for the
combo box. In doing this the form now displays incorrect information for
example: If I select the Business Unit of Deleware the address for the
California Business unit populate the other fields. Can someone tell me how
to fix this? If more information is needed please let me know. I did use the
VBA code:
me.facility_Adress=me.facility_Bus_Unit.column(1) etc....

Any help would be appreciated.

Thanks
Helpless

 

[BruceM]

Yet another example of why uses should *never* be allowed to edit tables
directly.
You may need to look at the table to fix it. When you look at Delaware in
the BusinessUnit field, the correct address should appear in that row
(record). It's hard to know why it does not. Is there a pattern? What
happens if you select California as the Business Unit? Where is the VBA
code you mentioned? What is your table structure, and what are the
relationships between them? What are you using as the combo box row source?

 

[Larry Daugherty]

Wow! Is your self esteem really that low? Why not use some variant
of your name or a more positive and hopeful pseudonym?

It sounds like your boss has changed the Name of the Business Unit
California to Delaware. Two questions:

1. Is the LimitToList property for the cboBusinessUnit on? It
should be.

2 Is the Autoexpand property for cboBusinessUnit on? It should
be.

Typically, the business units would be in a table: ~tblBusinessUnit
and the query for cboBusinessUnit would be based on a query on that
table.

Since I haven't a clue as to the rest of the user interface and schema
you've provided I can't go further than that.

There is a fairly standard mechanism for adding elements to a lookup
table when the NotInList event fires. You can find it in HELP.

I don't know how your boss got at the data. If s/he got directly into
the tables, chew her/him a new sensitive spot. By going directly into
the tables people can bypass all safety measures you may have provided
in your user interface. Tell them that changing data directly in the
tables is tantamount to sabotage! Then it's up to you to provide the
tools in the user interface that enable them to do everything that the
business requires.

HTH

 

[Guest]

Yes, because my boss can go in and make all type of changes then I have to
clean up the mess... so I feel helpless!!!

To answer your questions If I click on the California Business Unit the
correct information comes up. I am using Facility Bus Unit as the combo box
and have the VBA code in the Before update property. The realationships
between the tables are one to many. The combo box row source is TBL Query. I
hope this is the information you are looking for.

Thanks again

 

[Guest]

Yes, my boss can make changes to my database he want to have access to make
changes and then I have to clean up the mess so I feel.....helpless!!!

Both autoexpand and limittolist are on. I think he did something other than
deleted two fields. I will try to figure it out. What other information did
you need from me please let me know,

Thanks for you help!!

 

[BruceM]

Maybe your boss is editing the table directly because the interface cannot
accomplish certain objectives.
What do you mean "If I click on the California Business Unit the correct
information comes up"? Do you mean when you select California Business Unit
in the combo box you see the correct information in the Address, etc. text
boxes? If so, then somehow California's record was copied (or maybe you
have duplicates) with Delaware instead of California in the BusinessUnit
field. Does this affect any business unit other than Delaware?
Do you have a Business Unit table in which each business unit (address,
phone, etc.) appears exactly once?
Knowing that you have one-to-many relationships is meaningless without
knowing anything about your table structure. It sounds to me as if you may
be copying a business unit's information into each record in some other
table; in other words, Name, Address, etc. appear over and over in another
table.

 

[Guest]

RESPONSE BELOW IN ALL CAPS!!!!!

Thanks again for all your help

Maybe your boss is editing the table directly because the interface cannot
accomplish certain objectives.
What do you mean "If I click on the California Business Unit the correct
information comes up"? Do you mean when you select California Business Unit
in the combo box you see the correct information in the Address, etc. text
boxes? YES!!!!!

If so, then somehow California's record was copied (or maybe you

 

[Larry Daugherty]

Most likely your boss went into the table and changed the value there.
See my earlier reply..

You have an untenable situation if your boss can change things on a
whim and they you have to assume the responsibility to make things
right. That sounds like a sadistic paradigm. Sick! How is the local
job market for your skill set?

I would make absolutely positively sure that he can't change the
design. Make sure that you have the only copy of the front end as an
MDB. If you haven't already split your application, look in Help for
assistance in doing it. Thereafter, you'll want to just make an MDE
available to the boss and other users. For further protection, hide
the back end in another folder and link to it before you convert to
MDE. Now keep the MDB of the front end in your own area alone.

On the other hand, you might try doing illogical things using the
interface you have provided and see if you can cause similar or any
corruption from the keyboard.

HTH

 

[Guest]

You are correct he went into the TBL Directory and deleted two business units
and in doing so caused havoc on my database. I have checked the table and
cannot see the problem. He later told me that he tried to add the information
back into the table.

Thanks for your help it looks like I am going to have to start from square
one.

 

[BruceM]

Perhaps I should have asked about database structure rather than table
structure. You are trying to add BusinessUnit information to records, but
to what records? Whatever those records, I assume the table in which they
are stored (I will call it tblMain) has some relationship to the
BusinessUnit table. What is the real-life situation your database is
intended to address? All I know is that you are trying to add BusinessUnit
information to another record. When you do so, what information from your
Directory table is being stored?