Search Code

[Jacinda]

The following code is used for a search screen, and it is very cool...

The table Talldocs, is created from a series of queries that pull info from
odbc tables and appends to Talldocs. The reason I create the table is because
the odbc database separates the "open orders" from the "closed orders" and
when I search I need to look for an order no matter it's status... is there a
way to simpify this process so that I can search multiple tables or sources
and put a result somewhere, like a sub datasheet form?
FYI - I can not query both the open and closed together because they don't
have anything in common.... no join.
I guess I'm trying to create more of a "google" atmosphere... so that I
won't have to create a table just to search data that is already available in
the database.

strWhere = "1=1"

'If Part Number

If Not IsNull(Me.Search) Then
'Add it to the predicate- match on leading characters

strWhere = strWhere & " AND " & "Talldocs.Order like '*" & Me.Search
& "*'"
strWhere = strWhere & " OR " & "Talldocs.Partno like '*" & Me.Search
& "*'"
strWhere = strWhere & " OR " & "Talldocs.Custno like '*" & Me.Search
& "*'"
strWhere = strWhere & " OR " & "Talldocs.CustNm like '*" & Me.Search
& "*'"
strWhere = strWhere & " OR " & "Talldocs.Pono like '*" & Me.Search &
"*'"
strWhere = strWhere & " OR " & "talldocs.docno like '*" & Me.Search
& "*'"
strWhere = strWhere & " OR " & "talldocs.status like '*" & Me.Search
& "*'"
strWhere = strWhere & " OR " & "talldocs.doctype like '*" &
Me.Search & "*'"

End If


If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "frmSearchResults", acFormDS, , strWhere,
acFormEdit, acWindowNormal

If Not Me.Detail.Visible Then
Me.Detail.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.Detail.Height
End If

Me.FAllResults.Form.Filter = strWhere
Me.FAllResults.Form.FilterOn = True
End If


-Jacinda

 

[Dale Fye]

Jacinda,

You can create a union query as the RecordSource for you form, something like:

Select field1, field2, field3 FROM table1
UNION ALL
SELECT field1, field2, field3 FROM table2

 

[Jacinda]

Thank you Dale... my only problem is that my ODBC fails when I try to run the
query.... which means that I would still need to dump the data into an access
table in order to work with it.

Is there anyway around the ODBC fail ?

It seems like it's just too much data, but I find that really hard to
believe.

 

[Dale Fye]

When you say the ODBC fails, what kind of error message are you getting?

If you view the Access Options, and Select the "Advanced options" there is a
section that displays a series of values:

Refresh Interval
Number of update retries
ODBC refresh Interval

What values do you have in those fields.
-- ----
HTH
Dale

 

[Jacinda]

my error is " ODBC-- CALL FAILED
[TOD][ODBC][GENESIS](Position 63)- yacc: syntax error (#23)


my values in that screen you referred to are:

Refresh Interval 60 sec
Number of update retries 2
ODBC refresh Interval 1500

The query is:

select id_ord,id_item_cust from orderline
union all
select id_ord, descr_1 from saleshistoryline

---Jacinda