Filter Source Object

[doodle]

Greetings all.
windows xp, access 97

I have created a main "switchboard" type menu with command buttons on
the left and an unbound subform on the right. If they click on the
"Enter Order" button, the source object for the subform is changed to
the Order Entry form.

All works well.

The problem is that I have created a search command at the top of the
switchboard menu where the user can enter a customer number to search
for and, in a perfect world, the subform would become visible with the
record for that customer number. I coded it so that it works to open
the form, but I don't want to open the form as a separate object, I
want it to be the source object of the unbound subform.

Search command button code:

Private Sub cmdSearch_Click()
Dim stLinkCriteria As String

stLinkCriteria = "[CustNum]=" & "'" & Me![optSearch] & "'"
DoCmd.OpenForm "frmOrderEntrySearch", , , stLinkCriteria

End Sub

Code to show source object, not sure how to modify to show the above:
Me!sbfMain.Visible = True
Me!sbfMain.SourceObject = "frmOrderEntrySearch"


Thanks in advance.

-doodle

 

[Guest]

Try this:

Dim stLinkCriteria As String

stLinkCriteria = "[CustNum]=" & "'" & Me![optSearch] & "'"

Me!sbfMain.Visible = True
Me!sbfMain.SourceObject = "frmOrderEntrySearch"
Me!sbfMain.Form.Filter = strLinkCriteria
Me!sbfMain.FilerOn = True

or you could base the frmOrderEntrySearch on a query with a parameter which
references the optSearch control on the parent form, e.g. to show orders by
the selected customer in descending order date order.

SELECT *
FROM Orders
WHERE CustNum = Forms!YourParentFormNameGoesHere!optSearch
ORDER BY OrderDate DESC;

in which case you simply assign the form name to the subform control's
SourceObject property with no need to filter it.

Ken Sheridan
Stafford, England

 

[doodle]

Ken,

Thanks for your response. I tried Option #1 so far, and am getting the
"Option doesn't support this property or method" error on this line:
Me!sbfMain.FilterOn = True

Here is my code:

Private Sub cmdSearch_Click()
If IsNull(optSearch.Value) Then _
MsgBox Prompt:="You must enter a value to search for.",
Buttons:=vbOKOnly
If IsNull(optSearch.Value) Then _
optSearch.SetFocus
If IsNull(optSearch.Value) Then _
Exit Sub
Select Case frmOptions.Value
Case Is = 1 'Customer Number
cntCust = DCount("[CustNum]", "tblOrders", "[CustNum] Like ""*"
& [optSearch] & "*""")
Select Case cntCust
'If there are matching records for search value, form
opens filtered to records
Case Is > 0
Select Case cntCust
Case Is > 1
MsgBox Prompt:="There are " & cntCust & "
records that match your search." & _
"Use the 'Next Record' button to see
additional records.", _
Buttons:=vbOKOnly, Title:="Message From
Adria"
Me!sbfDemoMain.Visible = True
Me!sbfDemoMain.SourceObject =
"frmOrderEntrySearch"
Me!sbfDemoMain.Form.Filter = "[CustNum]=" &
"'" & Me![optSearch] & "'"
Me!sbfDemoMain.FilterOn = True
Case Else
' DoCmd.OpenForm "frmOrderEntrySearch", , ,
"[CustNum]=" & "'" & Me![optSearch] & "'"
End Select
Case Else
MsgBox Prompt:="There are no records that match
your search.", Buttons:=vbOKOnly
End Select
Case Is = 3 'Reference #
'code for ref # option
Case Is = 4 'Order/Quote #
'code for order/quote # option
Case Else
MsgBox "You must select a search option", Buttons:=vbOKOnly
frmOptions.SetFocus
End Select

End Sub


-doodle

 

[doodle]

Ken,

I don't think that Option #2 is going to work because there are
actually 3 different search options with the possibility of adding
more. Unless I came up with some kid of dynamic SQL statement, I would
have to have a separate form for each search option.

-doodle

 

[Guest]

Doh! Mea Culpa I think; try:

Me!sbfMain.Form.FilterOn = True