"Update or CancelUpdate without AddNew or Edit"

[caddcop]

My database is now setup using linked, external tables. I recently setup a form with a subform and after adding a new feature, the subject message began appearing in a message box. The help, is no help.
The form lists staff members. The subform lists descriptions of tasks performed on projects by these persons. The data is coming from two tables. Both have an ID field that is my join. On the main area of the form, I added a list box that is populated from a query of the two tables. What appears as a result of the query is a list of "descriptions" from a Description field on the subform's source table, but only those descriptions that apply to the current staff member as contained on the main form.
For example, if the current record of the main form has 5 sub-records on the subform, this list box will list the 5 values of the Description field as found on the 5 sub-records.
I added code to sort both the records and the listbox so that I could use the subform's CurrentRecord value to set the listbox Selected property (subtracting 1 from the value) so that as you navigate the sub-records, the listbox indicates the current record.


And for ease of use, I also made it so you could select a record via the text box.
I believe it is somewhere in that move that introduced this problem.
I will not post any code until someone indicates they think they can help, as this post is already pretty wordy. TIA

[pbaldy]

I doubt anyone would be able to help without seeing the code for the event that throws the error. If it's a compile error, it should take you to the offending code.

[SteveH2508]

The one thing that springs to mind is that you cannot use the Seek command on linked tables.

[caddcop]

Let me give a little more background:
The form and subform have three procedures in their code pages. I tried putting a breakpoint in each sub. The forms throws the error even if the breakpoints are never activated. If focus is in any of the main form text boxes, it throws the error when I try to navigate to the next main form almost every time. Note, I said almost.
If I can get focus in my listbox, or in one of the subform textboxes, I can navigate every which way through the records.
I've attached a picture of the form.
So one of my problems is figuring out what is actually throwing the error so I can post code that seems responsible.
This code is fired off when a user clicks on one of the descriptions in the list on the right of the dialog.

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

    Set rs = Me.Resumes_Expsubform.Form.Recordset.Clone
    rs.FindFirst "Description = '" & Me!
[List_Descriptions] & "'"
    If Not rs.EOF Then Me.Resumes_Expsubform.Form.Bookmark = rs.Bookmark
End Sub

And this code fires off when the user navigates to a new record in the main form. This is when I see the error message.

Private Sub Form_Current()
    RequeryList
    Me.List_Descriptions.Selected(0) = True
End Sub

Public Sub RequeryList()
    Dim ctlListbox As Control

    ' Return Control object pointing to a combo box.
    Set ctlListbox = Forms![Staff Resumes]!List_Descriptions

    ' Requery source of data for list box.
    ctlListbox.Requery
End Sub

And this code fires off when a user navigates to a new record on the subform

Private Sub Form_Current()
    
'    RequeryList
    Forms![Staff Resumes]!List_Descriptions.Selected(Me.CurrentRecord - 1) = True
End Sub

I never get the error while navigating subform records. Only when navigating main form records does it throw the error.
Each form's record source is a linked table in another database. There is a query that is the source of the Listbox:

SELECT DISTINCT Resumes_Exp.Description
FROM Resumes INNER JOIN Resumes_Exp ON Resumes.ID = Resumes_Exp.ID
WHERE (((Resumes.ID) In (Select Resumes_Exp.ID from Resumes_Exp 
WHERE ([forms]![Staff Resumes]![ID])=[Resumes_Exp].[ID])));

Hope this helps...

[caddcop]

OK, I found, on the UK version of this forum
(http://www.access-programmers.co.uk/...ad.php?t=77904)
a "solution" in so much as it explained that the code in my 3rd block of code, that is designed to select a row in a listbox is the one throwing the error. And in that thread, the solution for that user was to move the offending line further down in the procedure.
My code was already at the bottom of the on current procedure, so I moved it to a new sub procedure and called that from my on current procedure and the error is now a thing of the past.
OK, there's more.
I wanted my list box to highlight the selected record's data. That required my listbox to use the Selected(CurrentRecord-1) = true. But I also want the first record highlighted and nothing highlighted if the subform has no records. And the last piece of the puzzle was to also allow users to click on one of the items in the listbox and have that record become the current record. When all of these were put together, the error reappears. The trigger on this last occurrence is that when the user clicks on a record, it highlights that record. And when my code tried to highlight the record because it needs to when the on current event fires, bang! The error appears.
The full solutions was two tests, before highlighting the row.

If Me.List_AssignedDescriptions.ListCount > 0 Then
        highlightCurrentDescription
End If

This only runs my highlight procedure when the listbox has data. Next I needed this, in my highlight procedure:

    If Not Me!List_AssignedDescriptions.Selected(Me.CurrentRecord - 1) Then
        Me!List_AssignedDescriptions.Selected(Me.CurrentRecord - 1) = True
    End If

which basically checks to see if the current record's item is already selected, and if not, then it executes the highlighting code.

[caddcop]

In the immortal words of Russel Casse...
Hello Boys, I'm Back!
I cannot lick this problem. I have three subforms - two on one tab and one on another. On all of these, we want a list of a particular field from all matching records. One hold a Description "name" as a sort of heading to a larger memo field. The other two, on the same tab, will be a list of names and a list of companies. I want the current record highlighting the appropriate row in the list boxes, when a user navigates the subform records. But it seems that being also able to click on the row in the list, to jump to the record is problematic. The most annoying part, is that once I click on the error messages dialog box, and then press the escape key, the form controls all seem to work as intended.
I cannot figure out where the problem truly lies.

[pbaldy]

Can you post the db?

[caddcop]

It will take a little time to bundle into something that can be tested. I have a main database with no tables of its own, and two data databases - one holds the tables which my main database can be used to review or edit and a second that holds tables used to build certain controls in my main database.
I want to give you a version that has all of needed tables, forms and modules in one MDB file and also that contains only a small subset of our actual data.

[caddcop]

I think I have it fixed!!!!
I added a line of code to set the focus on my listboxes, before it sets the selected property.
Error messages gone! Access closing due to problem - gone.
Will be marking this solved - again...

[pbaldy]

Glad you got it sorted out.