I am tyring to update the display (for some related fields) in an Access ADP form, after I have edited one related field on the form called 'Organization_ID'. For this project, I'm using an Access ADP for the front-end and a SQL Server 2000 back-end.

I have a field that is listed in an combo box on a data entry form with a Control Source called 'Organization_ID'. The Row Source for the combo box listing the Organization_ID is the following SELECT statement:

SELECT Organizations.Organization_ID, Organizations.Address, Organizations.City, Organizations.State_Provence, Organizations.Zip, Organizations.Country FROM Organizations

The same data entry form is bound to a SQL Server view called 'Contacts_Organizations_View' and contains the following SQL:

SELECT TOP 100 PERCENT dbo.Organizations.Organization_ID AS Org_ID,
dbo.Organizations.Org_LongName,
dbo.Organizations.Address, dbo.Organizations.City,
dbo.Organizations.State_Provence, dbo.Organizations.Country,
dbo.Organizations.Zip, dbo.Organizations.MainPhone_Comm,
dbo.Organizations.MainPhone_DSN, dbo.Contacts.LastName,
dbo.Contacts.FirstName,
dbo.Contacts.Contact_ID AS ContactID, dbo.Contacts.Title,
dbo.Contacts.Organization_ID AS Organization_ID,
dbo.Contacts.Phone_Comm, dbo.Contacts.Fax_Comm,
dbo.Contacts.Phone_DSN,
dbo.Contacts.Fax_DSN AS Fax_DSN, dbo.Contacts.E_MAIL,
dbo.Contacts.ResourceAdvisor, dbo.Contacts.BudgetAnalyst,
dbo.Contacts.ResourceManager, dbo.Contacts.Active,
dbo.Contacts.CostCenterManager, dbo.Contacts.Level_ID,
dbo.Contacts.ALOContact
FROM dbo.Organizations RIGHT OUTER JOIN
dbo.Contacts ON
dbo.Organizations.Organization_ID = dbo.Contacts.Organization_ID
ORDER BY dbo.Contacts.LastName, dbo.Contacts.FirstName

What this view does in a nut shell is assign a Contact person (POC) from the 'Contacts' table to an organization in the 'Organizations' table.

This data entry form has 'Updateable Snapshot' as a Recordset type, and the UniqueTable property is set to 'dbo.Contacts'.

When I select a Organization_ID (e.g. '11 WG/FMZ') in the combo box, I want those fields from the Organizations table that are related to the Organization_ID like address, city, state, zip to automatically update based on the Organization_ID that I selected in the combo box.

This used to happen automatically in the old version of this database in Access 2000 (MDB). Did not have to include extra code for the form to refresh.

I tried entering a SQL string in the data entry form's ResyncCommand property. Something like:

SELECT Organizations.Organization_ID, Organizations.Address, Organizations.City, Organizations.State_Provence, Organizations.Zip, Organizations.Country FROM Organizations
WHERE Organizations.Organization_ID = ?

But this did not work. Besides, I only want some of the fields on the form to update, not all of them.

Also, when I select an Organization_ID from the combo, the related fields are not updated. But if I go to the next record with the record selector, then click back to the previous record that I was working on then the related fields have been updated.

But I want them to update without have to flip forward then back again.

Can anyone help?

Thanks,

Cheryl