Use listbox selection to populate editable fields in subform

Title: Use listbox selection to populate editable fields in subform

Greetings experts. I really appreciate your efforts. I have thoroughly researched this method before writing and now need your advice.
I can't get the solutions suggested in the various questions on the topic to work.

Table:       Inventory
Index:       ProductId
Form:       SearchEntries
Textbox:      txtCriteria      (after update, populate listbox "ListInventory")
Listbox:       ListInventory
ListInventory.Column(0): ProductId field

Goal: <DoubleClick> the "ListInventory" listbox to populate subform: "UpdateInventoryItem".

I have included the following code to accomplish this action:

Private Sub ListInventory_DblClick(Cancel As Integer)

DoCmd.OpenForm "UpdateInventoryItem", , , "ProductID = " & Me.ListInventory.Column(0)DoCmd.OpenForm "UpdateInventoryItem", , , "ProductID = " & Me.ListInventory.Column(0)      'This line opens form "UpdateInventoryItem" (temporary fix)

Me.UpdateInventoryItem.Form.Filter = "[ProductId]='" & Me.ListInventory.Column(0) & "'"
Me.UpdateInventoryItem.Form.FilterOn = True

End Sub

I get the error message: Method 'Form' of object '_SubForm' failed

Additional Info:

Properties Window
subUpdateInventoryItem
      Source Object:             UpdateInventoryItem
      Link Child Fields:       ProductId
      Link Master Fields:       ProductId


What am I missing?  


TEST---AJ-Adams-Specialty-Sales-.mdb
ajadams1Asked:
Who is Participating?
 
mbizupCommented:
I can't open your database, but...

1. If your ID field is numeric, drop the single quotes from your filter:

Me.UpdateInventoryItem.Form.Filter = "[ProductId]=" & Me.ListInventory.Column(0)


2. Since you have the Master/Child links set to productID, setting the filter on the main form should suffice:

Me.Filter = "[ProductId] =" & Me.ListInventory.Column(0)
Me.FilterOn = True
0
 
mbizupCommented:
Finally (If you need to refer to your subform, rather than applying the filter to the main form):

3.  From the properties that you posted, it looks like your subform control (the control that "houses" your subform) is actually named subUpdateInventoryItem.  This is the name that you need to use in your code (it may differ from the name of the subform as you see it in the database window):

>  Me.UpdateInventoryItem.Form.Filter

should be:

Me.subUpdateInventoryItem.Form.Filter

same thing for the FilterOn code...
0
 
ajadams1Author Commented:
Thanks, mbizup!

I just wonder why you couldn't open the database. It shouldn't have had any security or protection (or is it because you don't have Access installed).

Regards,

Andrew
0
 
mbizupCommented:
Hi,

<I just wonder why you couldn't open the database.>

Sorry - I should have been more specific. Some of my volunteer time here is during down-times at work. I can't download and open databases from my work computer.



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.