Using Subform for Searchcriteria + relating one searchfield to several columns

[Bongobob21]

Hello,



I have two questions for this post.

1. I created a form with some search-fields which are related to a query. Then I added a Subform in which I put some more Search criteria (So that I can easily hide and unhide those additional searchfields). It sounds strange but is necessary ;-). Now I related those searchfields in the subform to the same query. When I run that query a window pops up that I should put in a value in all those searchfields which are in the subform. But I told Access that it should display all rows, if there is no value in those searchfields. Just as I did it with the Searchcriteria in the Main form. Do I have to do something special, when I have a query which is related to two Forms?

2. I want a searchfield to search in three different columns. Usually the value will just be found in one of those columns. As the Table I search is very long and has many searchfields and multiple of those will relate to more than one column, is there an easy way to do it in VBA? As I did it by using the "or" field when designing a query, but this seems very slow and unstable.

Thanks in advance!

[ranman256]

Build a query using the key in the master form as criteria. Also use OR for the 3 fields you want to search in.

select * from table where [keyID] = forms!frmMAster!txtKey and (tbl.[field1] = forms!frmMaster!txtFind OR tbl.[field2] = forms!frmMaster!txtFind or tbl.[field3] = forms!frmMaster!txtFind)

[Bongobob21]

Thanks a lot, So I put this code in the Column it should search in the query. But what is the Form key? I never heard that term.

Thanks!

[ranman256]

Not a 'form key', but the table key for the data in the subform is ON the form.
The seach box would be on the header of the master form. You already know the master key, so your search would encompass the subform fields, USING the master key and those fields.

[Bongobob21]

The problem is, that I am working with linked tables, as an Excel-Spreadsheet is my datasource. As far as I know, it is not possible to put Primarry keys into a linked Table?

Furthermore I am linking usually one table with 2 - 3 other tables, therefore I don't know if a primary key would help.

(Sorry If I am writing bullshit, I am totally new to Access)

[June7]

Primary key is a unique identifier in one table that is also present in another table that serves as foreign key (usually not unique in the related table). A PK/FK can be composed of multiple fields - something I try very hard to avoid. So link the tables on as many fields as needed to uniquely identify the record association.

[Bongobob21]

I Linked all tables together, my issue is, that I am not able to determine a primary key, as the tables are not created in access. The tables are just linked to an excel spreadsheet. Can you give me an advice how to set a primary key when linking an excel table to access?