Refresh has no effect

[Devon]

My problem takes quite long to explain properly, so let me start with a general explanation. If more information is needed to help clarify where things go wrong, I'm happy to oblige.

There are three tables that play a part in the issue:

• tblAssignment - Holds assignments for all the employees
• tblAssignmentColleague - Has an N-1 relation with above table, so several people can be related to one assignment
• tblColleague - Has a 1-N relation with above table and holds a field OfficeId

I have a form that contains all the assignments. It has a SQL string as its recordsource. The string can either refer to a person, or to an office. So the form either shows a person's assignments or the assignments of all the persons that work for an office. The string is set on another form that has the comboboxes cboColleague and cboOffice. The latter leads to the following SQL string:

"SELECT     qryAssignment.AId, Subject, Client, OfficeId <and then some>
 FROM       (qryAssignment
 INNER JOIN tblAssignmentColleague ON qryAssignment.AId = tblAssignmentColleague.AId)
 INNER JOIN tblColleague ON tblAssignmentColleague.PId = tblColleague.CId
 WHERE      OfficeId = " & cboOffice

So far, so good. I get the records I expect to see. The problem starts when I edit a record. The form that holds the list of assignments cannot be altered, all the fields are disabled. A third form holds every field from tblAssignment and that's where users can edit data. It is bound directly to tblAssignment.

I use a form refresh on the 'list form' after data is edited on the 'details form'. Until recently, when the 'list form' merely showed all the assignments without the comboboxes to zoom in, the 'list form' got updated right away. It showed updated information as soon as the data were changed on the 'details form'. But now that I use SQL strings to only show the assignments of one office, or one person, the 'list form' is no longer updated. I need to press F5 before the changes appear.

I am desparately trying to avoid having to use a requery. I'm not fond of having to jump through the form. So the big question is; why does a refresh no longer work, yet pressing F5 does?

 

[CJ_London]

your problem is you are trying to update a multi table query - Access does not understand which table to update. The usual solution is to have the assignment table only as the recordsource to your main form, and tblAssignmentColleague as a continuous subform utilising the linkchild/linkmaster properties to 'link' the two. Then in your subform, you would have a combo box with tblcolleagues as a rowsource to link tblcolleagues to tblAssignmentColleague

The other alternative, tho' I don't think it will work in this case is to change the form recordset type to dynaset - inconsistent updates.

 

[pbaldy]

This may help with the requery problem:

http://www.baldyweb.com/Requery.htm

 

[Devon]

Thanks for your quick replies. I decided to rebuild the query and bind the list form to that query. That worked. Strange thing is that even if I use the SQL string that I mentioned in my first post, the refresh works as well. So either Access is behaving inconsistently, or I might have overlooked something.

@CJ London: The Dynaset (inconsistent updates) didn't work, like you suspected.

@pbaldy: Thanks but as I wrote in my previous message, I was trying to avoid having to use a requery. It is slower than a refresh and lets the user jump through the recordsets.