Search Bar

[sparker75]

Hello,

I would like to add a search bar on the top of my tabular form that will filter my list. Here is the code I have so far but it is giving me an Compile error: Method or data member not found


Private Sub Command433_Click()
Dim SQL As String

SQL = "SELECT tblOrderNotifications.CreatedOn, tblOrderDetails.WBS, tblOrderNotifications.Notification, tblOrderDetails.Revision, tblOrderDetails.OrderType, tblOrderDetails.OrderNum, tblOrderDetails.Sortfield, tblOrderNotifications.CreatedBy, tblOrderDetails.PlannerGroup, tblObjectStatus.Status, tblObjectPriority.Descripton FROM tblOrderNotifications LEFT JOIN (tblObjectStatus RIGHT JOIN (tblObjectPriority RIGHT JOIN (tblOrderDetails LEFT JOIN tblOrderStatus ON tblOrderDetails.OrderNum = tblOrderStatus.OrderNum) ON tblObjectPriority.PrioID = tblOrderStatus.PRIOID) ON tblObjectStatus.SID = tblOrderStatus.SID) ON tblOrderNotifications.Notification = tblOrderDetails.Notification" _


& "Where (OrderNum) LIKE '*" & Me.TxtKeywords & "*' " _
& "Order BY tblOrderDetails.[OrderNum]"

Me.frmOrderStatus.Form.RecordSource.SQL
Me.frmOrderStatus.Form.RecordSource.Requery

End Sub

[ranman256]

dont use sql, make a query. The query shows all records.
use a continuous form bound to the query.

then when the user enters the item in the text box, filter the results:

sub txtFind_Afterupdate()
const Q = """"

if isNull(txtFind) then
   me.filterOn = false

else
   me.filter = "[OrderNum] like '*"  & txtFind & "*'"
   me.filterOn = true
end if

[Minty]

You are missing a space at the end of here

blOrderNotifications.Notification = tblOrderDetails.Notification " _

Add a Debug.Print SQL before the setting record source line and you'll be able to see the string as Access see it.

[sparker75]

Ranman. I would like this search box to look in more than one column and filter from there. They can search order number or WBS or Notification. I dont think your approach will work?

Minty: I added the space but get the same result. Compile Error: Method or data member not found. The Me.frmOrderStatus.Form.RecordSource.SQL is highlighted

QL = "SELECT tblOrderNotifications.CreatedOn, tblOrderDetails.WBS, tblOrderNotifications.Notification, tblOrderDetails.Revision, tblOrderDetails.OrderType, tblOrderDetails.OrderNum, tblOrderDetails.Sortfield, tblOrderNotifications.CreatedBy, tblOrderDetails.PlannerGroup, tblObjectStatus.Status, tblObjectPriority.Descripton FROM tblOrderNotifications LEFT JOIN (tblObjectStatus RIGHT JOIN (tblObjectPriority RIGHT JOIN (tblOrderDetails LEFT JOIN tblOrderStatus ON tblOrderDetails.OrderNum = tblOrderStatus.OrderNum) ON tblObjectPriority.PrioID = tblOrderStatus.PRIOID) ON tblObjectStatus.SID = tblOrderStatus.SID) ON tblOrderNotifications.Notification = tblOrderDetails.Notification " _
& "Where (OrderNum) LIKE '*" & Me.TxtKeywords & "*' " _
& "Order BY tblOrderDetails.[OrderNum]"

[Minty]

Actually unless you are trying to reference a sub form your syntax is incorrect

Me.frmOrderStatus.RecordSource = SQL

[sparker75]

It is not a subform. What should the syntax be?

[isladogs]

You omitted an equals sign before SQL

Assuming this is run from the same form, replace these two lines
Me.frmOrderStatus.Form.RecordSource.SQL
Me.frmOrderStatus.Form.RecordSource.Requery

With
Me.RecordSource=SQL

I don't think you'll need to requery but if you so then it's just:
Me.Requery

[sparker75]

I get this error now

[ranman256]

a query will show you what is wrong.
SQL will not.
use a query.

[sparker75]

Ranman.

will this filter through each column?

sub txtFind_Afterupdate()
const Q = """"

if isNull(txtFind) then
me.filterOn = false

else
me.filter = "[OrderNum] like '*" & txtFind & "*'"
me.filterOn = true
end if

[sparker75]

Ok. This is working now!!

Private Sub Command433_Click()

Dim SQL As String

SQL = "SELECT tblOrderNotifications.CreatedOn, tblOrderDetails.WBS, tblOrderNotifications.Notification, tblOrderDetails.Revision, tblOrderDetails.OrderType, tblOrderDetails.OrderNum, tblOrderDetails.Sortfield, tblOrderNotifications.CreatedBy, tblOrderDetails.PlannerGroup, tblObjectStatus.Status, tblObjectPriority.Descripton FROM tblOrderNotifications LEFT JOIN (tblObjectStatus RIGHT JOIN (tblObjectPriority RIGHT JOIN (tblOrderDetails LEFT JOIN tblOrderStatus ON tblOrderDetails.OrderNum = tblOrderStatus.OrderNum) ON tblObjectPriority.PrioID = tblOrderStatus.PRIOID) ON tblObjectStatus.SID = tblOrderStatus.SID) ON tblOrderNotifications.Notification = tblOrderDetails.Notification " _
& "Where (tblOrderDetails.OrderNum) LIKE '*" & Me.TxtKeywords & "*' " _
& "Order BY tblOrderDetails.[OrderNum]"
Me.RecordSource = SQL

End Sub




I would like to search more than just OrderNum. How would I add multiple criteria?

& "Where (tblOrderDetails.OrderNum) LIKE '*" & Me.TxtKeywords & "*' " _
& "Where (tblOrderDetails.WBS) LIKE '*" & Me.TxtKeywords & "*' " _
& "Where (tblOrderNotifications.Notification) LIKE '*" & Me.TxtKeywords & "*' " _
& "Where (tblOrderDetails.Revision) LIKE '*" & Me.TxtKeywords & "*' " _
& "Where (tblOrderDetails.OrderType) LIKE '*" & Me.TxtKeywords & "*' " _
& "Where (tblOrderDetails.Sortfield) LIKE '*" & Me.TxtKeywords & "*' " _
& "Where (tblOrderNotifications.CreatedBy) LIKE '*" & Me.TxtKeywords & "*' " _
& "Where (tblOrderDetails.PlannerGroup) LIKE '*" & Me.TxtKeywords & "*' " _
& "Where (tblObjectStatus.Status) LIKE '*" & Me.TxtKeywords & "*' " _


I really appreciate your feedback and help!

[sparker75]

Sovled. Thank you!

[sparker75]

One last request.

If I wanted to include '& "Where (tblOrderDetails.Revision) LIKE '*C*' " with the & "Where (tblOrderDetails.Revision) LIKE '*" & Me.TxtKeywords & "*' " _ So that Revisions would have to have a C or TxtKeyword

This way the list will be limited to the C and what is typed in the search



Here is the code as it stands.



Private Sub Command433_Click()

Dim SQL As String

SQL = "SELECT tblOrderNotifications.CreatedOn, tblOrderDetails.WBS, tblOrderNotifications.Notification, tblOrderDetails.Revision, tblOrderDetails.OrderType, tblOrderDetails.OrderNum, tblOrderDetails.Sortfield, tblOrderNotifications.CreatedBy, tblOrderDetails.PlannerGroup, tblObjectStatus.Status, tblObjectPriority.Descripton FROM tblOrderNotifications LEFT JOIN (tblObjectStatus RIGHT JOIN (tblObjectPriority RIGHT JOIN (tblOrderDetails LEFT JOIN tblOrderStatus ON tblOrderDetails.OrderNum = tblOrderStatus.OrderNum) ON tblObjectPriority.PrioID = tblOrderStatus.PRIOID) ON tblObjectStatus.SID = tblOrderStatus.SID) ON tblOrderNotifications.Notification = tblOrderDetails.Notification " _
& "Where (tblOrderDetails.Revision) LIKE '*" & Me.TxtKeywords & "*' " _
& "And (tblOrderDetails.OrderNum) LIKE '*" & Me.TxtKeywords & "*' " _
& "Or (tblOrderDetails.WBS) LIKE '*" & Me.TxtKeywords & "*' " _
& "Or (tblOrderDetails.Sortfield) LIKE '*" & Me.TxtKeywords & "*' " _
& "Or (tblObjectStatus.Status) LIKE '*" & Me.TxtKeywords & "*' " _
& "Or (tblOrderNotifications.Notification) LIKE '*" & Me.TxtKeywords & "*' " _
& "Or (tblOrderDetails.OrderType) LIKE '*" & Me.TxtKeywords & "*' " _
& "Or (tblOrderNotifications.CreatedBy) LIKE '*" & Me.TxtKeywords & "*' " _
& "Or (tblOrderDetails.PlannerGroup) LIKE '*" & Me.TxtKeywords & "*' " _
& "Order BY tblOrderNotifications.CreatedOn DESC"
'& "Where (tblOrderDetails.Revision) LIKE '*C*' "
Me.RecordSource = SQL
Me.Requery

End Sub