Trying to update the display on Access ADP Form

[Cheryl3D]

I am trying 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 dbrganizations.Organization_ID AS Org_ID,
dbrganizations.Org_LongName,
dbrganizations.Address, dbrganizations.City,
dbrganizations.State_Provence, dbrganizations.Country,
dbrganizations.Zip, dbrganizations.MainPhone_Comm,
dbrganizations.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 dbrganizations RIGHT OUTER JOIN
dbo.Contacts ON
dbrganizations.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

 

[neecie]

Hi Cheryl,
Supposedly,you can only update the table that you identified as unique if you are using a bound form (although I've seen it act differently).
Try using

Me.controlname.Requery

for each field control you want to update. Put the code in the combobox's After_Update event.

 

[Cheryl3D]

Hi neecie:

I tried exactly what you said to do. So that the code for the combobox's After_Update event looked like the following:

Private Sub Organization_ID_AfterUpdate()
Me.txtAddress.Requery
Me.txtCity.Requery
Me.txtState_Provence.Requery
Me.txtZip.Requery
Me.txtCountry.Requery
End Sub

But it did not work. Whenever I changed the Organization_ID nothing happened.

However, I did get it to work doing this in the combobox's AfterUpdate event:

Private Sub Organization_ID_AfterUpdate()
DoCmd.Close acForm, "frmContacts"
DoCmd.OpenForm "frmContacts"
End Sub

It appears to work just fine. But I wonder in a multi-user environment using SQL Server 2000 and and Access ADP front-end, would this be kosher, okay?

Since I'm using a true client/server technology and this code should only affect the client. It shouldn't be a problem, correct?

Thanks,
Cheryl

 

[RiverGuy]

Try this, but subsitute my textbox with your combo box:

Me.Recordset.Find ("ID = " & Text8.Text)

 

[RiverGuy]

Oh yeah, before that line, you might want to try a Me.Recordset.MoveFirst, because its searching forward by default.

 

[chrissie1]

try me.requery
Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[Cheryl3D]

Hi RiverGuy:

I tried the following per your posted instructions:

Me.Recordset.MoveFirst
Me.Recordset.Find ("Organization_ID = " & Organization_ID.Text)

Both the field name and the combo is named "Organization_ID".

I put this in the AfterUpdate event code for the combo box.

When I tried it I received the following error message:

Run-time error '3001':

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Any idea why?

Also, to chrissie1, I had already tried Me.Requery for the form. It did not requery.

Thanks,
Cheryl

 

[RiverGuy]

Cheryl,

I'll have to look. I think it has something to do with the combo box. Not getting the text value correctly or something. It sounds likes its sending over a query string such as "Organization_ID = ".

Or possibly, if its not a numeric data type, but I bet it is.

 

[RiverGuy]

I tried it on the change event of the combo box, and it worked nicely.

 

[Cheryl3D]

RiverGuy:

It's not a numeric data type. Should I add one to the table?

Cheryl

 

[RiverGuy]

No, if it is not numeric, your find string would be like:

Me.Recordset.Find ("Organization_ID = '" & Organization_ID.Text & "'&quot

Needs the single quotes.

 

[Cheryl3D]

Thanks RiverGuy, the error message is gone and the current record changes as expected, however after the change the ADP form automatically flips back to the first record in the recordset.

I want the current record to change as expected and stay on the same record after the change.

For example, I want to change the Organization_ID and related address items for record # 3 in the recordset, so I select a different Organization_ID in the combo, the change takes place, I see the different info displayed and I still on record # 3.

Currently, I change record # 3, then I'm automatically flipped to record # 1, when I navigate back to record # 3, I see that the change was successfull.
Can I use a bookmark or something to keep it from going to record # 1?

Thanks again,
Cheryl

 

[RiverGuy]

I'm not sure why it is doing that. It lets me change data, then I can go to another record through either the combo box or via the default nav buttons.

After I change the data in the physical text box, I stay on that record.

Are you using the On_Change event of the combo box?

 

[Cheryl3D]

No I am not. I don't have any code in the On_Change event of the combo box at all.

 

[RiverGuy]

I meant the CHANGE event...sorry.

 

[Cheryl3D]

To clarify, you mean the Change event for the combo box. The answer still is No I have not.

 

[RiverGuy]

Yes, the event for the Combo Box. Move that code to the change event of the combo box.

 

[Cheryl3D]

I moved it from the AfterUpdate to the change event for the combo box , but still the same result, it changes but goes to the first record.

 

[RiverGuy]

Im not sure at all. I'll post my code. My "Text8" is actually a combo box.


Private Sub Text8_Change()
Text8.SetFocus
Me.Recordset.MoveFirst
Me.Recordset.Find ("ID = " & Text8.Text)
End Sub

 

[Cheryl3D]

Morning RiverGuy:

Hope you get this message. I figured out something that seems to work well. I used part of the code that you sent me and added some bookmark code to it.

I used the following code:

Private Sub Organization_ID_Change()
Dim varBookmark As Variant
varBookmark = Me.Recordset.Bookmark
Me.Recordset.Find ("Organization_ID = '" & Organization_ID.Text & "'&quot, , adSearchForward, varBookmark
Me.Recordset.Bookmark = varBookmark
End Sub

It appears to behave exactly as needed.

Thank you very much for you help,
Cheryl3D