Looking for New Approach

[Sash]

This is a tough one to describe, but here goes....

In order to help syncronize data in three systems, I offloaded item masters
from all three systems. I currently have a main form that contains three
unrelated or unlinked subforms that display datasheets. Each subform,
currently has a parameter query associated with it.

So Mainform would have
-Subform / Datasheet A
-Subform / Datasheet B
-Subform / Datasheet C

The user will key in the word "mesh" and each subform populates with all
instances of items containing the word mesh. If no match is found, the user
can press a button next to the subform to requery the data using a different
word.

Once the data is displayed the user can review specific items from each item
master in one location and make appropriate changes/additions required. It
works nicely with the datasheet as the user can sort, resize, etc. The only
issue is that the user has to key in mesh 3 times for the main form to
populate.

If I have an unbound text box to key the search word 1x and open the main
form, I loose the ability to requery each item master separately. I know
parameter queries are discourage. I'm not sure why my brain isn't
functioning on this one, but any suggestions would be appreciated. If you
need further explanation on the project, I'd be happy to provide additional
examples.

 

[Barry A&P]

Sash
I do a similar thing.. i have a main form with Three unbound combo boxes
and a search button in the forms detail section in my instance one is for
part number and one is for description when the "Search Button is pressed"
the code builds a filter string and sends it to my subdatasheets. The
subdatasheets are in the Forms footer section and the Footers visible
property is set to NO so when the form is opened the footer with the
subDatasheets are not visible thereby the Subdatasheets are not active with
thousands of unfiltered records..

It would be easy to add two more subforms to the end of this code

There are some tricky things to look out for where "'s and ""'s are involved
i hacked this code out of some i have been using and i pretty sure i didnt
miss anything but please remember to backup and be carefull.

here is the code

Private Sub Clear_Click() 'Add a Clear button to reset the form
DoCmd.Close
DoCmd.OpenForm "F_Parts_Search"
End Sub



Private Sub Search_Click()
Dim strwhere As String
Dim strError As String

strwhere = "1=1"

'If Category (finds EXACT Match on Number in field)
If Nz(Me.Categorysearchbox) <> "" Then
'Add it to the predicate - exact match
strwhere = strwhere & " AND " & "T_PartNumbers.CategoryID = " &
Me.Categorysearchbox & ""
End If

' If Part Number (finds match on Text in middle of field)
If Nz(Me.PartNumberSearchbox) <> "" Then
' Add it to the predicate - match on leading characters
strwhere = strwhere & " AND " & "T_PartNumbers.PartNumber Like '*" &
Me.PartNumberSearchbox & "*'"
End If

' If Description (Finds text in the middle of your field by using *
before and * after)
If Nz(Me.DescriptionSearchbox) <> "" Then
' Add it to the predicate - match on leading characters
strwhere = strwhere & " AND " & "T_PartNumbers.Description Like '*"
& Me.Descriptionsearchbox & "*'"
End If

If strError <> "" Then
MsgBox strError
Else

If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.YourSubDatasheet1.Form.Filter = strwhere
Me.YourSubDatasheet1.Form.FilterOn = True

Me.YourSubDatasheet2.Form.Filter = strwhere
Me.YourSubDatasheet2.Form.FilterOn = True
End If
End Sub

Hope this helps

Barry