I won't argue with your observation that you are seeing fewer records in
the table. So something seems to have occurred.
A "terminology" correction -- a combo box gets the data for its list from
a Row Source; you said Record Source, but those are used by forms and
reports for their data. The field to which a combo box is bound is in a
form's Record Source.
For normal operation of a combo box (you didn't indicate if the Limit To
List property is set to No, but I'm assuming that it is based on what you
say the users can do), simply deleting a value in the combo box and then
typing in a new one will not delete a record in the form's Record
Source -- what it will do is change the value of the combo box's bound
field in the current record to whatever bound column has a second column
whose value matches what was typed into the combo box. If there is no such
value to be matched, the combo box will get a Null value and that will be
written into the bound field as its value, replacing what the original
value was.
In order to delete a record via entering data into a combo box, you would
need to run a macro or programming that would do the deletion.
Now, depending upon how you've set up the form, it's possible to delete a
record by pressing the delete key, which is not related to the combo box.
If your form has Record Selectors property set to Yes, then it's possible
to delete the current record from the form's Record Source if one clicks
onto that record by clicking the "record selector box" at left side of the
record on the form, and then pressing the delete key. If your database has
the "Confirm record changes" flag set to No (see Tools | Options |
Edit/Find tab), then there will be no warning to the user that a record is
about to be deleted -- it'll just be deleted. This sounds more like what
you're observing -- and it's not related to the combo box itself at all.
Additionally, it's possible to "hide" the warning via programming so that
the user does not get notified. However, from your description of the
setup, it doesn't appear that you have any programming set up in the form.
Make a list of all the properties that are set for your form and post them
here. Also check that the Confirm record changes is set to Yes.
--
Ken Snell
<MS ACCESS MVP>
Donna said:
The UserID combo box is using a table named InputUsers, when I go to this
InputUsers table where the data should be, the records are gone. That's
what
has me so troubled. The InputUser table with a line item for UserID L4 is
completely gone from the table. Its as if the user (L4 in this case) has
gone to the table and deleted the entire row of data. The number of
records
in the table is less. I don't think its hiding. The same thing occurred
with
the accounts table. Instead of selecting account number 951, they deleted
it
and typed it in manually. When I go look at the accounts table that the
combo box is using as its record source, the line item for acct 951 is
gone.
The number of records in the table is reduced from 80 to 79. It just
wipes
it out, and for the life of me, I don't understand how this can be
happening. I didn't think it would be possible to delete an entire record
from a table. Its a mystery to me.... and something I must prevent before
I
can put this database out there for the personnel to use.
As for the
InputData table that the form is sending its data to, all the information
the user keyed in is there, but since they wiped out certain items from
the
reference tables, its as if L4 user doesn't exist nor does account 951.
not
good if you know what I mean
Ken Snell said:
As I said, I doubt that any records have been deleted... what is
happening is that the change to the data likely is "hiding" the records
from being selected by the queries.
--
Ken Snell
<MS ACCESS MVP>
Thanks very much Ken! First thing Monday morning when I get back to
work I'm going to do all these things you suggested. I toyed with
another form and set the Locked property to 'yes'. It did not allow me
to type anything at all and that really isn't what I want. Another one
of my combo lists on the previously mentioned form has about 80 items
in the list and I want the user to be able to enter a few numbers to
get to the item in the list. I have it set up with the key field in
column one. What is really strange to me is HOW did they wipe out the
entire item in the table? I can't make that happen. When I try deleting
the selection in the combo box, and I look at the combo box's table,
everything is still there. Only the record that is going to the form
has the blank spot.
2 people have actually deleted the entire row of the recordsource table
for the combo box.
I have 3 more combo boxes on that same form. The combo box that
contains about 80 records contains account codes. They completely
deleted one entire line item. There is an account code
[primary](integer), acct code name (text) and acct code desc (text) in
the table. On the form's combo box, it is set up with the acct code in
column 1 and bound to column1 as I should have set up the ID box. When
the user selects the acct code it is supposed to go into the acct code
field of the InputData table.
I have Relationship set up, and row level tracking is checked if I
remember right... Perhaps that is how it is getting deleted? This is
really bugging me. I need to understand why it's happening. I wish I
could duplicate the action. The 2 people who wiped out the records from
the recordsource of the combo box, only know they tabbed into the form
field and starting typing in what they wanted. They did not go back to
the table itself.
Thanks again... Donna
From your description, I don't believe the records have been deleted.
However, the identifying value for the records, namely, the UserID,
likely has been replaced by a Null value in the table, and thus those
records have been "orphaned" - they're still there, but have no
identifying ID that allows you to join to them.
I would make the following changes to your combo box setup to prevent
this problem:
(1) Change the Row Source query to have the UserID be the first column
and the UserName be the second column.
(2) Change the Bound Column property to 1.
(3) Change the Column Count property to 2.
(4) Change the Column Widths property to 0";2".
(5) Change the Limit To List property to Yes.
(6) Put this code on the NotInList event (you'll need to change
NameOfComboBox to the actual name of your combo box throughout the
code):
Private Sub NameOfComboBox_NotInList(NewData As String, _
Response As Integer)
MsgBox "You must select your name from the list.",
vbExclamation, _
"Select Your Name"
Response = acDataErrContinue
Me.NameOfComboBox.Undo
End Sub
Now, the combo box will work just as you wanted -- the user can type
in letters from his/her name -- but the user cannot change the names
that are in the dropdown list.
--
Ken Snell
<MS ACCESS MVP>
Using Access 2000
I have a serious problem with a combo box and need to find out why
it's happening and how to stop it. The combo box looks up the value
in another table and stores that value in a field on the form. Upon
testing it - somehow, when a user hardkeys the UserID in the combo
box instead of making a selection, it actually deletes the record in
the underlying table. I don't understand how this can happen, and
even more important, I need to know how to prevent this. My first
thought is to set the combo box's Locked property to YES, and leave
the Enabled property to YES.
The form is based on a query, InputData_qry. The first stop on the
form is the UserID; a combo box, based on a table called
InputUsers_tbl. The InputUsers_tbl contains 3 fields: UserID (text),
UserName (text) and UserDept (text). The UserID field is set as the
Primary key. There is a one to many relationship set on the UserID in
the InputUsers_tbl and the InputData table (and the form's
recordsource InputData_qry).
In the combo box's recordsource I used the SQL query builder, used
the UserID_tbl and put the UserName in the first column and the
UserID in the second column. I set the 2nd column as the bound
column; and the control source to be UserID. This way, the user can
select their UserName from the combo box by using the letters in
their name rather than the abbreviated UserID, and the bound column,
UserID, will be placed in the UserID field of the InputTable. That is
what is supposed to happen. but....
(there's always a but) when it was being tested by another user
unfamilar with Access, they deleted the UserName in the combo box and
manually typed in their name. The person entered a dozen records,
each time typing in their own name, and when it came to testing the
reports, nothing worked of course.
Will locking the combo box prevent the data in the underlying table
from being deleted/edited? (I've used combo boxes for years, but
never have seen data get wiped out until now....guess I have been
lucky)
Thanks for the help,
Donna
