VBA Search for Record in a Subform

[Workingonit]

Good day!

I feel like I am very close to solving this but because I'm a little rusty in my vba I cannot seem to figure this one out.

I have a main form "Furniture_CatCodeAssets" which shows detail specifics about certain furniture item codes (width, height, colour etc.). I have a subform "Furniture_AssetsSubform" which shows all of the asset numbers associated with that furniture item code.

I have a combo box that allows you to filter by furniture category, that then updates a list box that shows only the item codes associated with that category. When an item code is selected in the list box the main form and the subform update to show what was selected. (I mention this only in case it is causing an interruption - they work perfectly.)

What I am trying to do now is add a text box and button that will allow you to search in the subform to find a specific asset number and go to that item code on the main form.

I have the following code on the command button:
Set rst = Forms!furniture_CatCodeAsset.Furniture_AssetsSubform.Form.RecordsetClone
rst.FindFirst "[Asset Number]=" & Me.textSearch
If Not rst.NoMatch Then
Forms!furniture_CatCodeAsset!Furniture_AssetsSubform.Form.Bookmark = rst.Bookmark
Else
MsgBox "No match found, please check your asset number and try again."
End If

Me.textSearch = Null
rst.Close
Set rst = Nothing
Which returns the message box that nothing was found even though I know I that asset number exists.

I am really hoping that this makes sense and that someone is able to help me figure out how to code this! Thank you in advance!

 

[RuralGuy]

Is [Asset Number] a TEXT field or a Number field?

 

[Workingonit]

Hi! It is a number field.

 

[Fuse3k]

Is [Asset Number] coming from a field in a continuous form? Keep in mind a continuous form will return it's [Asset Number] value of the currently selected record

What returns if you add:

MsgBox Me.AssetNumber

After "Set rst..."

Are you getting the Asset Number you're expecting to?

 

[Workingonit]

Adding that code just gave me a compile error "method or data member not found."

After doing some more research I know that even though the subform is continuous, because it is a subform it only recognizes that one item code that has the focus and ignores the others. So me trying to find the asset number for an item code that does not have the focus will not work.

I'm now wondering if it's possible to type the asset number you want to find, and press the search button which would then search the query for that asset number, but return the item code associated with it. Then have the form update to that item code, which could then highlight the asset number you're looking for?