Search Subform data

[dgriffin]

I am trying to create a pop-up form that a user can use to search for a purchase order. I thought I could use a clone of the recordset for the subform (which contains the desc of items purchased). I don't get any errors, just no record movement on the form. In the debug screen I am verifying that "rst.NoMatch" is set true. But there are indeed matching records.

I have assigned the code to the "On Click" event of a button on a form called "DPO Edit Form" which contains a subform named "DPO Edit Subform". The data source for "DPO Edit Form" is "DPO Header" which contains purchase order information like POnumber, Date, RequesterName, PhoneNumber, etc. The data source for the subform "DPO Edit Subform" is "DPO Detail" which contains the items being purchased - POnumber, Description, UnitOfMeasure, Qty, Cost. These two tables are related by the key field POnumber.

The actual code as it reads in my form is:

Dim frm As Form, rst As Recordset
Set frm = Me![DPO Edit Subform].Form
Set rst = frm.RecordsetClone
rst.FindFirst "[Description] Like 'paint*'"
If Not rst.NoMatch Then
frm.Bookmark = rst.Bookmark
End If
rst.Close
Set rst = Nothing

The "DPO Detail" table contains 12006 records, of which approximately 79 of the Description field begin with the word "Paint".

What am I missing?

--------------------
Second question (provided I can get the first part working), is there a way to provide a pop-up form that would allow the user to enter mulitple criterion that would span BOTH tables. Such as: Find the PO that was created between this date and that date, by Requester so-and-so, that ordered an item description containing the word VALVE, within the price range of $100-$200.

That would really make my day! I've been chewing on this one for 3 months.

Anxiously awaiting any response,
Dan

 

[LonnieJohnson]

I have a form that has a subform. I do searches on my subform with a command button. Instead of opening another form, I use an input box. Not as pretty as your own personal form, but it gets the job done. Anyway the code for the search is as follows:

Private Sub cmdFind_Click()

Dim strFind As String
strFind = InputBox("What?&quot
Publishers.SetFocus
DoCmd.FindRecord strFind, acAnywhere, , acSearchAll, , acAll

End Sub

Pretty simple. I initialize and open the input box with lines one and two. I set the focus to my subform which is called Publishers in line three. Then I do the find command for the variable that came from the input box. This searches all fields for any occurance of the string.

Maybe you can take this and modify it for your needs.

B-)

ljprodev@yahoo.com
ProDev
MS Access Applications

 

[dgriffin]

Maybe I am missing something (probably really obvious). This approach seems to search only the subform records that are currently displayed. In my example above, that would equate to searching the 6 items (for example) that exist on the currently displayed PO.

I need to allow the user to enter a search string that will be used to find any detail record in the database, move the pointer to the corresponding PO and then display that PO on the main form (along with its detail records in the subform).

Am I making sense? It's hard to be sure that one is suffeciently explaining the situation when one is so close to it.

 

[njitter]

Hi,

You have a one-to-many relationship between the main and the subform.

I made a search-form by creating a new form that contained ALL the records (just select all fields from both tables to show in a field).

This way you can use the built in Search function.

Don't know if there's a fancier way to do this
---
There are no stupid Questions -- just stupid People