How to update main form from search on subform data

[dgriffin]

I have a form with subform displaying purchase orders from a pair of tables with a one-to-many relation. The main table is "PO Header" which contains POnumber, Requester, Date, etc. and a child table "PO Details" which contains POnmber, Description, Quantity, Cost, etc.

Displaying the appropriate PO for any search criterion that is contained in the "PO Header" table is no problem. But so far I have only been able to get the popup search form to find data contained in the currently displayed child records (PO Details).

I need to be able to find a key word that a user supplies in the Popup Search Form in the detail records, such as Description, and then display that PO on the underlying form.

I suspect one would open the child table in code, find a matching record, then move the record pointer of the main form to that POnumber. But I am not familiar enough with Access 97 to know exactly how that would be done. Assuming this would indeed be the appropriate way to accomplish this.

Some detailed guidance would be greatly appreciated.

 

[Aivars]

Hi!
You can search necessary sub form record in the Popup Search Form as follows.

'Codes of the Popup Search Form:

Dim frm As Form 'Declare object

Private Sub Form_Load()
'Set sub form
Set frm = Forms("PO Header&quot("PO Details&quot.Form
End Sub

Private Sub lstSearchCriteria_AfterUpdate()
Dim rst As Recordset

Set rst = frm.RecordsetClone 'Set sub form's recordset
'Find record of sub form
rst.FindFirst "POnmber=" & Me.lstSearchCriteria
'If field "POnmber" type is "Text":
'rst.FindFirst "POnmber='" & Me.lstSearchCriteria & "'"
If Not rst.NoMatch Then
frm.Bookmark = rst.Bookmark
End If
rst.Close
Set rst = Nothing
End Sub

Aivars

 

[dgriffin]

Maybe my wording was a little misleading. The subform isn't displaying the PO's... the main form is displaying the header information like name, date, vendor, etc. and the subform is displaying the 7 items that were purchased on this one PO. There are thousands of items (records) in the PO Details table that have been purchased over time on numerous PO's. It's this PO Details table that I am trying to search, using a popup form, from the main form (that is currently displaying only one PO).

The code you provide above searches only the 7 detail records currently displayed for the one PO currently being displayed. I want the user to be able to enter some search criterion that will be used to search the entire database of thousands of records... and when a match is located, update the main form to display the one PO that contains the matching information.

As I mentioned in the first message, I *think* I know how to find the detail record using vb code to simply open the detail table and search. My stumbling block (if the search code works) is how to get that information back to the main form so that I can cause it to display the "found" PO.

HTH

P.S. Your quick response was greatly appreciated. In most forums I've experienced you were lucky to get a response within a week!

Dan

 

[Aivars]

Hi again!

You can do selection by using lot of criterias.

Dim frm As Form 'Declare object

Private Sub Form_Load()
'Set sub form
Set frm = Forms("PO Header&quot("PO Details&quot.Form
End Sub

Private Sub cmdButtonForSelection_Click()
Dim strCriteria as String
dim ctl as control
Dim rst As Recordset

if not isnull(Me.CriteriaControlBox1) then
strCriteria = "Field1=" & Me.CriteriaControlBox1
end if
if not isnull(Me.CriteriaControlBox2) then
if strCriteria <> &quot;&quot; then
strCriteria = strCriteria & &quot; And &quot;
strCriteria = &quot;Field2=&quot; & Me.CriteriaControlBox2
end if

if not isnull(Me.CriteriaControlBox3) then
if strCriteria <> &quot;&quot; then
strCriteria = strCriteria & &quot; And &quot; 'or &quot; Or &quot;
strCriteria = &quot;Field3=&quot; & Me.CriteriaControlBox3
end if
'...............
'etc.
'...............
'...............
'Also you can use For...Next or For Each...Next statements for compose of criteria's string
if strCriteria = &quot;&quot; then 'No selction's criterias
exit sub
end if

Set rst = frm.RecordsetClone 'Set sub form's recordset
'Find record of sub form
rst.FindFirst strCriteria
If Not rst.NoMatch Then
frm.Bookmark = rst.Bookmark
End If
rst.Close
Set rst = Nothing
End Sub

Good luck!
Aivars