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

Subform ADODB Troubles!

100+
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
  2.  
  3. Dim rs As ADODB.Recordset
  4.  
  5. Set rs = New ADODB.Recordset
  6.  
  7. rs.Open "ItemMaster", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
  8. rs.MoveFirst
  9. rs.Find "Item='" & Me.Part_Number & "'"
  10.  
  11. If rs.EOF = True Then GoTo Stepout
  12.  
  13.     Me.Description.Value = rs.Fields("Description").Value
  14.     Me.Item_Cost.Value = rs.Fields("Cost").Value
  15.  
  16. Stepout:
  17.  
  18.     rs.Close
  19.     Set rs = Nothing
  20.  
  21.     Me.Total_Cost_of_Item = Me.Qty * Me.Item_Cost
  22.  
  23. End If

Can i not run adodb in a subform?

Any help would be extremely appreciated!
Thanks
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.

-Stewart
Aug 27 '08 #2

FishVal
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.

Regards,
Fish.
Aug 27 '08 #3

100+
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.