Search d/s view subform from main form

[joanl]

Hi,

I've "inherited" this database from someone that has departed our office.
I've been brain-dead and am in need of help...please. I have a database with a Main table where RecordID is the primary key - autonumber. I have another table called DMC where RecordID is set up in a relationship with Main table RecordID. Records in DMC can have more than one entry per RecordID. The users enter the information in a Main form. On the Main form, I have a subform (datasheet view) based on the DMC table. This subform includes three fields: RecordID, DMC#, and DMC Date. The users now would like to do a search on the DMC# field on the subform. I have a Find button on the Main form, but it only works on the Main form. The prompt appears for their criteria, but the message says there are no records. They want to enter a DMC#, and have the appropriate record show. There could be more than one RecordID with the same DMC# and Date. Can I code this button to also search the subform, or is there a better way to go about it? Or does this need to be reworked? I'm probably making it harder than it really is. Any help is appreciated.

 

[Aivars]

Hi!
Create command button on the Main form for search in sub form.

On click event (approximately):

dim frm as form
dim rst as recordset
set frm=forms("MainFormName)("SubFormObjectOnMainFormName&quot.Form
set rst=frm.recordsetclone
rst.findfirst <your criteria>
if not rst.nomatch then
frm.bookmark=rst.bookmark
end if
rst.close
set rst=nothing

Regards!
Aivars

 

[dgriffin]

I have the precise same problem. However, I have been unable to get the code snipit that you provided to work. I don't get any errors, just no record movement on the form. In the debug screen I am verifying that &quot;rst.NoMatch&quot; is set true. But there are indeed matching records.

I have assigned the code to the &quot;On Click&quot; event of a button on a form called &quot;DPO Edit Form&quot; which contains a subform named &quot;DPO Edit Subform&quot;. The data source for &quot;DPO Edit Form&quot; is &quot;DPO Header&quot; which contains purchase order information like POnumber, Date, RequesterName, PhoneNumber, etc. The data source for the subform &quot;DPO Edit Subform&quot; is &quot;DPO Detail&quot; 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 &quot;[Description] Like 'paint*'&quot;
If Not rst.NoMatch Then
frm.Bookmark = rst.Bookmark
End If
rst.Close
Set rst = Nothing

The &quot;DPO Detail&quot; 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 struggling over this one for 3 months.

Anxiously awaiting any response,
Dan