#deleted in combo box for delete action

I am a programmer somewhat new to Access. Our company lost our Access ‘expert’ and I have been assigned an issue with a form in Access 2002. The form has a combo box in which employee name is selected. There is a subform under it with a combo box from which year is selected from a list of values. A subform under it lists the project name, allocation type, and forecast hours by month for the employee for the year selected above. The employee can have multiple project records. There is a union query that produces the project name and project identifier and an employee table with name and employee number. The employee table has a one to many relationship with a main table containing the employee number, year, project identifier, allocation type, and hours by month.

The form is for both data entry and maintenance. The form was designed for users to add, update, and delete records. The issue is with deletes. Sporadically, when the first record in the list of detail is deleted, ‘#deleted’ appears in the year combo box, all of the detail is erased from the form leaving only a blank record displayed. If the main table is viewed, the record is deleted, but unrelated records are added to the employee that had the record deleted. If the delete works as it should, the record is deleted, the year remains displayed in the combo box, and the remaining records are still displayed.

The main table did not have a primary key. In an effort to fix this, I did create composite keys, removed null values from the keys, and removed duplicate values in key records. This did not fix the issue. Please let me know if anyone has suggestions on what can be done to resolve this. I do not have cascade on delete checked.

Sounds like a refresh issue, if an item is deleted from a database and the data is not refreshed (me.refresh) sometimes you can get this deleted message particularly if you're using forms based on tables.

The easiest thing to try for now is to try and refresh your data after the delete is complete and possible requery your form (me.requery)

Should the code for me.refresh or me.requery be added to an event for the main form combo box for employee selection? There is the following code in the event tab of the properties for this combo box.

Private Sub Combo34_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[EmployeeID] = " & Str(Nz(Me![Combo34], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Also, should code for me.refresh or me.requery be used. I had seen the code for me.requery in a tutorial and wondered if this could be the issue. I soooooooooooo appreciate your assistance with this.

me.refresh forces your database to recognize any changes to any data anywhere in your database, this includes deleted items, new items and updated items.

me.requery forces a specific control to recognize any changes wrought by a new criteria or anything affected by me.refresh

For instance let's say you have a combo box that has the answers

YES
NO

You want to add a third possibility (MAYBE) and through your form you allow for that possibility (let's say a third pop up form for maintenance of the contents of a specific combo box). As part of your 'save' feature you should have me.refresh. This will force your database to see that you now have three options:

YES
NO
MAYBE

Now sometimes you only want to update a specific control let's say that based on your choice of YES/NO/MAYBE you want a second combo box to have a secondary list of choices you don't want to refresh the data you just want to requery the specifici combo box so you'd have something like

me.combobox2.requery

Without the database itself I can't tell which you want to use but REFRESH is the more powerful of the two and as long as your database isn't huge the delay shouldn't be too noticeable to your end users.

Where do I attach the code? Do I attach the code to the form, subform, or combo box properties? Also, what event do I code for? I tried on_change, before_update, and after_update. I'm really clueless on this. I do believe I know the VB code to use though.

If you've got a button that triggers the behavior you're talking about I'd connect it as the last item on your button for instance let's say your button is named DELETE_STUFF

in the ON CLICK event of the DELETE_STUFF button you'd have something like

There is no delete event or button. Users just select the line (record) they want to delete and press the delete key on the keyboard. That is the problem, we don't know what event to use as there is no specific button for that action.

Ok, now I understand.

I'm specifically interested in these two things:

What is the datasource for the YEAR subform?
What is the datasource for the YEAR combo box?

I suspect that one or both may be connected to the table that is holding your jobs or running off a query that is connected to your jobs table.

In access you can use combo boxes such that the list of available options can be drawn from the table holding the data you're doing data entry against (it's circular and dangerous). On top of that, if you then change the option LIMIT TO LIST to be NO a person can end up changing the list to match what they want. This is what I suspect is going on in your database. The YEAR combo box is drawing information from your job table and when you delete the record it's referencing it can't find the record anymore so it's falling back on #deleted.

Further I suspect that you're only using a form/subform not a form/subfrom/sub-subform.

The Form would be the parent record that shows the current employee
The subform would be the child record from the jobs table.

Does this only happen on when there is only 1 job for the year you're interested in or does it happen when there are multiple jobs for the year in the combo box?

There is a form called 'Teammate Log', a subform called 'Teammate Log Project Subform', and a subform called 'Teammate Log Project Year - Subform'. The record source for the form is the table Employee Information. The record source for the subforms is the table ForecastHours. The control source for the YearField combo box is the field YearField from the table ForecastHours. The row source type is a value list. Row source has years from 2005 to 2015 listed. Limit to list is 'no'. The #deleted happens for one project and it happens for mulitiple projects. I am leaving town tomorrow and won't have access to a computer again until Wednesday. So I may not see your next reply until then. You have been extremely patient with my lack of expertise. Hopefully we will get to the bottom of this eventually.

Ok what's the record source for the detail (the last subform)

Form: Teammate Log
(Teammate Combo Box)
Content comes from table EMPLOYEE INFORMATION

Subform: Teammate Log Project Year
(List of Years combo box)
Content comes from table FORECASTHOURS

Is the content of the table listing the years just a list of years? or does it contain other data?

Sub-Subform: TEAMMATE LOG PROJECT YEAR - SUBFORM
(Detail associated with year in subform)
Contents come from table (What is the table name?)

If the detail section (TEAMMATE LOG PROJECT YEAR - SUBFORM) uses the same source table (FORECASTHOURS) as the previous subform (TEAMMATE LOG PROJECT YEAR), either the table itself or through a query involving that Table this is what's causing the problem. I'm still trying to duplicate and so far no luck. So are the Subform (TEAMMATE LOG PROJECT YEAR) and the second subform (TEAMMATE LOG PROJECT YEAR - SUBFORM) sharing any of the same tables, either as a data source or in any part of their combo boxes?

The problem exists between your two subforms that's what I'm trying to get at, bolded the questions for easier reading.

The comob box with the issue contains the following data properties:
Control source: Yearfield
Row source: 2005:2006;2007;2008;2009;2010;2011;2012;
Bound column:1
Limit to list; no
Auto expand; yes
Enabled; yes
Locked; no

Both subforms use data from ForecastHours table which has the following fields:
Source
Identifier (project number)
Yearfield
Employee ID
Allocation
A field for each of the 12 months

The project year subform has the combo box above containing the Yearfield

The project subform has the following combo boxes.
Identifier
Control source: Identifier
Row source type: Table/Query
Row source:
Select projectList.identifier,projectList.projectName from projectList
Bound: 1
Limit to list: Yes
Auto expand: Yes
Enabled: Yes
Locked: No

Allocation
Control source: Allocation
Row source type: Value list
Row source: "Expense";"Capitol"
Bound: 1
Limit to list: Yes
Auto expand: Yes
Enabled: Yes
Locked: No

Feb => one for each month
Control source: Feb
Validation rule: Is not null
Validation text: This field can not be null.
Enabled: Yes
Locked: No
Filter lookup: Database default

This is your problem. Using the same table for two subforms on the same data entry page is asking for trouble. I would strongly recommend you change your data entry so there is only *1* subform. The link being the employee. You can have the same functionality you have now and avoid this #deleted display problem. It's going to be the easiest way to fix your issue. And I would also very strongly suggest you stay away from using combo boxes where limit to list is disabled. (limit to list property set to NO). It causes a lot problems that it solves. Instead I would have a table that stored years and every time you opened the form you could have a function that checked the current year vs. the maximum in your table and added it if it was absent. In my enclosed example I just appned a record to a the year table where the year is set as the primary key because it's never going to be a large page and I was too lazy to do the check.

Secondly notice that I can choose a person and a year (currently mickey mouse is the only one that has any history) along with a year and their data appears. I'm using the year part of the starting date to determine which project year it is but you can adapt it to whatever setup you're using.

Lastly, I would recommend you stay away from any object names in your database with spaces in the name. It's going to cause you headaches in the long run. I know you inherited this database and it's likely too big a headache to change at this stage but keep it in mind for the future.

Let me know if this does not answer your question. The only other thing you could try is instead of allowing the user to use their delete button (the key on the keyboard 'del') you provide a button button on the data entry form that does the delete for them and as part of that delete function refreshes the data with the me.refresh command. I have *no* idea what would happen if you did this because I still have been unable to duplicate your error.

Thanks so much for the attachment and detailed instructions. I did try creating a 'year' table and changing the year combo box to query it rather than a list of values. I also changed limt to list to 'yes' and added the requery. Now I am not getting the display of 'delete' and the data that is refreshed is correct. At this time, I wanted to keep the 'fix' as easy as possible. It is likely I will need to enhance this form in the near future, and at that time, I will change the design to eliminate the second subform. My manager is arranging an Access class so I will have more expertise down the road as well. Your help has been invaluble...thanks again!!!