Searching Tabbed Subforms 1

[Secretgeek]

Hi,

I have a main form and 7 tabbed subforms. When I carry out a search of my records it only appears to search the first subform and ignores the rest even if the particular subform that I know holds the information is active and open.

How do I ensure that a search covers all of the subforms?

Thanks in advance for your help.

 

[lameid]

Find is only going to search the form that is active or has focus...

Why do you have 7 forms that you want to search for the same data? To me this says the data probably belongs in one table and then one form or that it is data that all the forms are related to and you should have that table on the main form and search the main form for the record and look at the related sub form data.

Hopefully you can fix this through data organization otherwise, you will need to write code that jumps to the right record and run it for all the subforms. The code generated on the after update event on a combobox that finds records is a good place to start.

 

[Secretgeek]

Thanks for the tip lameid.

Unfortunately, data reorganisation isn't going to do it.

When someone carries out a search they're not necessarily going to know which subform the data will be in and even if they do, unless the data is on the first tab, Access doesn't search any other record except the current one.

What's really confusing me is that this only happens on all bar one of the subforms (the first tab being the exception) so I know that it can do the search it just won't and I'm not knowledgeable enough to know why.

If it helps the data is organised as follows:

Main Form ---Request
Subform Tab 1 -----Contact details
Subform Tab 2 -----Information Providers
Subform Tab 3 -----Questions
Subform Tab 4 -----Status Updates
Subform Tab 5 -----Related Documents
Subform Tab 6 -----Court details
Subform Tab 7 -----Received file details

As i say, if you do a search for something that you expect to find in e.g. Status Updates Access will only search the current record. If the data you won't is on the main form or in 'Contact details' then it will search all records.

 

[lameid]

If you are using master and child fields on the subforms or anything else that limits the records it will not search all the records. It is only going to search the ones that are available to it. The main form obviously has all records available to it as it is unfiltered (my inference).

It sounds like what you may want is a Union query with each statement looking for the criteria. Then use a report to show the reults... The user can then navigate to any of the records he is interested in.

 

[Secretgeek]

Thanks. You're correct in saying that the main form is (usually) unfiltered.

Excuse my ignorance, I'm one of those learn as you go types.

I've done a little experimenting and cleared up one part of my query. The reason the first subform is different i.e. searchable is because it's not actually a subform as such, merely fields placed on that particular tab and hence the information is as accessible as it would be on the main form.

The remaining subforms are all effectively seperate entities and going on what you've said already won't be searchable because Find doesn't look into those other forms.

I'm thinking out loud here.

So from what you've said I'm going to have to create a Union query that looks at all the data, subforms and all, and returns a list of matches that the user will then need to select from?

I better go and start learning about Union queries then :-/

 

[lameid]

union queries are easy if you know Sql at all... each sub-query/select statement has to have the same number of columns in the same order. Unions stack the results on top of each other...

Select Field1, Field2
From Table1

Union [red]All[/red]

Select Field1, Field2
From Table2

The red text is optional it says to return all records. Leaving it out will remove duplicates from the two sets of data. Honestly for just learning it is far easier to use the qbe to make two queries retrun the right fields and just use them....

Select *
From Query1

Union [red]All[/red]

Select *
From Query2

The catch here is you have to know not to change query1 or query2 without making them match.

 

[Secretgeek]

Many thanks for your help.

 

[formerTexan]

Just a caveat if you use "Select *" statements in a Union query. Each Select statement in the query must return the same number of fields.

Cheers, Bill

 

[Secretgeek]

So if I'm searching 7 tables each with different numbers of fields I can only go as high as the table with the fewest fields?

Interesting constraint. I can see I'm going to have fun with this.

 

[lameid]

I kind of assumed there would be only a handful of fields to search like a notes field in each table.

You could add more columns to each select that are blank but if they are different enough losing the column headings may not make it worth it.

An alternative would be to make a subreport for each table and then put each subreport in an otherwise empty main report. This way you could show all the results in one place and control formatting better. On the otherhand, you can't copy the data this way.

 

[Secretgeek]

The main table has approx 30 fields that may require searching whilst the other tables have between 1 and 6. On that basis it seems a little excessive to inflate all the other tables with 20-odd empty fields.

From what's been said it looks like the subreport->main report option is the way to go. Unless there is a simpler option.

Of course I could just always say 'You'll have to live with it I don't actually get paid for this' but I like a challenge. :-\

 

[lameid]

Simpler option... Let them look it up in each table / screen themselves...

More complex better option... A bunch of code that figures out what records in the main form have records in subforms that moves the main form to the first record for the subform as each is selected. Also have a button to move to the next related mainform record and in controls show how many main form records are relevent in aggregate and the selected subform. This is a nasty bit of work... I've been working with Access for over 10 years now and I think it would take me about 3 hours to implement it.

 

[Secretgeek]

Ok, for me at least it's 16:00 on a Friday afternoon and I think lameid you have provided me with sufficient info to fully answer this problem.

'Basically, I know what's happening, I know why it's happening but if you want it fixing get a pro in.'

I would love to have the time to put something along the lines of what you've suggested together. However, if it would take someone with your experience 3 hours I strongly suspect I'd be very lucky to sort it in 3 weeks.

Many thanks for all your help.

 

[lameid]

I hope you were not taking that as a dig... I just wanted you to have a relistic expectation of what the alternative might entail if you wanted to investigate that much.

 

[Secretgeek]

No, not at all! I'm just conscious of my own experience and how much time I can realistically spend on this.

Believe me as much as I like investigating and learning new things, like anyone my time is limited. Especially as this is a 'your the person who can do the database stuff' request.

Really, many thanks for your help and have a great weekend.

 

[formerTexan]

Sorry, but when I mentioned the caveat about matching numbers of fields in a UNION query, I should have mentioned that you can add filler or alias fields for padding. So if you are using all the fields from a 2 field table and a 3 field table, something like the following will work:

<code>
SELECT tbl1.Field1, "padding" AS Alias, tbl1.Field2
FROM tbl1

UNION ALL

SELECT tbl2.Field1, tbl2.Field2, tbl2.Field3
FROM tbl2;

Cheers,
Bill

</code>