Search Code

  • Thread starter Thread starter Jacinda
  • Start date Start date
J

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
 
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
 
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.
 
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
 
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
Click to expand...
The query is:

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

---Jacinda
 
Back
Top