Search functionality in form

[baendres]

I have a database that contains 3 tables

Drawings
Plant_area
Vendors

two of the columns(fields) in the Drawings table are lookups from the other two tables in the database.

the Plant_area table and Vendors table both contain only two columns, the id and another field

The problem i am having is the search function the I have found/modified is not searching what is displayed in the form it is using the id of what is in the field. I have used mysql in the past and some of the stuff that access does is very different.

this is in an embedded macro on a "go" button

If [Froms]![Drawing list]![SearchBox] Is Null Then
End If

SetTempVar
Name strSearch
Expression = Replace([Forms]![Drawing List]![SearchBox],"""","""""")

SetTempVar
strFilter
Expression = "([Drawing_Number] Like ""*" & [TempVars]![strSearch] & "*"")"
SetTempvar
strFilter
Expression = [TempVars]![strFilter] & " OR ([Drawing_Title] Like ""*" & [TempVars]![strSearch] & "*"")"
SetTempvar
strFilter
Expression = [TempVars]![strFilter] & " OR ([Area] Like ""*" & [TempVars]![strSearch] & "*"")"
SetTempvar
strFilter
Expression = [TempVars]![strFilter] & " OR ([Vendor] Like ""*" & [TempVars]![strSearch] & "*"")"
SetTempvar
strFilter
Expression = [TempVars]![strFilter] & " OR ([Equipment] Like ""*" & [TempVars]![strSearch] & "*"")"
SetTempvar
strFilter
Expression = [TempVars]![strFilter] & " OR ([Notes] Like ""*" & [TempVars]![strSearch] & "*"")"
SetTempvar
strFilter
Expression = [TempVars]![strFilter] & " OR ([Digital_Drawing] Like ""*" & [TempVars]![strSearch] & "*"")"

ApplyFilter
Filter Name
Where Condition = =[TempVars]![strFilter]
Control Name
RemoveTempVar
Name strFilter
RemoveTempVar
name strSearch


SetProperty
Control Name cmdShowAll
Property Enabled
Value 1


If i search for the number 1, I get records that have an area and vendor with id number 1

I would expect something like this to go in place of [area], drawing.area.area
But i can't find any information on how MS Access handles this situation.

any help would be appreciated.

Thanks,
Brian

[orange]

What is your database about? What do the tables mean? How are they related?

[NTC]

If I understand your question; I think your issue is that the search is finding the underlying ID field's value and not the value you see displaying in the field. This is the way it is.

So what you need to do is modify your query (record set / data source of the form) to include the displayed field of the Plant_area table and Vendors table. use these fields and remove the lookup field from your record source and form.