By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,677 Members | 1,709 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,677 IT Pros & Developers. It's quick & easy.

Subform ADODB Troubles!

P: 158
I keep getting the following error:

The expression after update you entered as the event property setting produced the following error: a problem occurred while Microsoft Office Access was communicating with the OLE server or ActiveX control

Whats happening is that i have the user click a drop down box (which is populated by a table) then choose the item and it fills in the description and price with the following vba code: (Also this is in a subform)

Expand|Select|Wrap|Line Numbers
  1. If (Not IsNull(Me.Part_Number) And Not IsNull(Me.Qty)) Then
  3. Dim rs As ADODB.Recordset
  5. Set rs = New ADODB.Recordset
  7. rs.Open "ItemMaster", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
  8. rs.MoveFirst
  9. rs.Find "Item='" & Me.Part_Number & "'"
  11. If rs.EOF = True Then GoTo Stepout
  13.     Me.Description.Value = rs.Fields("Description").Value
  14.     Me.Item_Cost.Value = rs.Fields("Cost").Value
  16. Stepout:
  18.     rs.Close
  19.     Set rs = Nothing
  21.     Me.Total_Cost_of_Item = Me.Qty * Me.Item_Cost
  23. End If

Can i not run adodb in a subform?

Any help would be extremely appreciated!
Aug 26 '08 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 2.5K+
P: 2,545
Hi. In my opinion this is not a problem caused by your code, but by some form of corruption of the form itself. I had exactly the same error in an on-click event within a subform two days ago - the code itself was fine and had been working perfectly throughout test. Without warning the error message you quote appeared. Compact/repair made no difference.

There was no clue as to what had gone wrong. I resolved my problem by saving a copy of the subform - right clicking on the form's name in the Access form tab, Save As, save under a new name, renamed the troublesome original, renamed the fresh copy back to the same name as the original was. Why this resolved it I don't know, but it did.

Aug 27 '08 #2

Expert 2.5K+
P: 2,653
If that is all the code, then there is no need to open recordset using adOpenKeyset cursor. Try to use adOpenDynamic instead.

As well as there is no need to open whole table and use Find method then. Why not to use a simple SQL expression with criterion in WHERE clause?

And finally you may well use DLookUp() function here.

And even more finally :), if Part_Number control is combobox you may retrieve [Cost] and [Description] values into invisible columns to always keep them on-hand.

Aug 27 '08 #3

P: 158
Thanks for the responses! Stewart was right my database was corrupted...turns out the library files were overwritten with an older version of access...i've rebuilt the database and it works fine now.

Thanks Again!
Aug 27 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.