Subform ADODB Troubles!

[Jollywg]

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)

If (Not IsNull(Me.Part_Number) And Not IsNull(Me.Qty)) Then
 
Dim rs As ADODB.Recordset
 
Set rs = New ADODB.Recordset
 
rs.Open "ItemMaster", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
rs.MoveFirst
rs.Find "Item='" & Me.Part_Number & "'"
 
If rs.EOF = True Then GoTo Stepout
 
    Me.Description.Value = rs.Fields("Description").Value
    Me.Item_Cost.Value = rs.Fields("Cost").Value
 
Stepout:
 
    rs.Close
    Set rs = Nothing
 
    Me.Total_Cost_of_Item = Me.Qty * Me.Item_Cost
 
End If

Can i not run adodb in a subform?

Any help would be extremely appreciated!
Thanks

[Stewart Ross]

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

[FishVal]

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.

[Jollywg]

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!