search form that find records in multi tables

[Twillz]

Thanks for your help. Can anybody help me? I would like to create a search form that can find records from multi tables that have forms linked to the tables. And when the record is found it will open the form that is linked to the table where the record is stored. Can somone please help me or point in the right way.

Thanks
Will

 

[MakeItSo]

Hi WIll,

first of all: you shouldn't open the respective form.
It isn't "linked" to the table, it is just to display data.
Since you say, the data comes from several tables, this would invoke several forms making the whole thing messy...

Create a Form with unbound text/combo boxes and an "OK" or "Search" button and save it as your SearchForm.
Create a query containing all your necessary fields and in the "Criteria", enter Forms![YourSearchForm]![YourField].
Create a Results Form with the SearchQuery as data source and distribute your results fields on it.

In the Click() event of your OK/Search button, enter this code:
DoCmd.OpenForm "YourResultsForm"

Tell me, if you need more.


MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[Twillz]

Thank you! I'll give it a try. Will the user be able to update the record and it will save in the right table?

Thanks
Will

 

[MakeItSo]

Yes.
If for some reason you can't, check the "lock" option in the form and set it to "none".

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[Twillz]

Hi Andreas

Thank you so much you are a lifesaver. I have one more question (sorry about so many qustion new when it comes search form, and finding records) I made the forms like you said but when I enter a ID the results form opens but no record is shown, and in the query do I need to add all the tables and fields even if the fields are the same just different tables

Thank you so much
Will

 

[MakeItSo]

No.

You need to add one of these identical fields and create a join:
Open your query in design mode.
Just put your mouse over the field in the field list of the table and drag it over to the respective field of the other table(s).

Beware: If your tables contain a field named "ID", Access will automatically join these fields, even if they are not identical! remove those links by right clicking the line between the tables and selecting "delete join"

Else you will not receive any records, which might be just what you have encountered.

Let me know, if there are still problems... ;-)
MakeitSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[Twillz]

Andreas

Thank you for taking time on me, but I still can't it get to work.

When I check the search button the result form open but no record,

I have tables, shipping, parts, built, (theres more but if I get those tables I can get the rest)each table have the following fields: Ran, Customer, Reason, Turn_around, Ship_date,Rep. I want to use the "Ran" field to search

One table works, but when I join the "ran" no record comes up. Can you help me with the query setup.

Thank You
Will

 

[MakeItSo]

Sure.

Can you paste the query SQL here? I must see it, so I find the point... In fact, I just thought you might not even need a join.
Here's an excerpt from one of my Search forms:

SELECT Tab_Bugs.ID, Tab_Bugs.Description, Tab_Bugs.Solution, Tab_Bugs.Status, Tab_Bugs.Fixed_by, ...
FROM Tab_Bugs
WHERE (((Tab_Bugs.Description) Like [Forms]![Frm_SearchDialog]![desc_field]) AND ((Tab_Bugs.Fixed_by) Like [Forms]![Frm_SearchDialog]![login_name]) AND ((Tab_Bugs.Category) Like [Forms]![Frm_SearchDialog]![cat_field]) AND ...;

Just open your query in design mode, switch to SQL view, mark and copy/paste the statement.

We'll get this baby rollin... ;-)


Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[Twillz]

Andreas

Here is the SQL statement

SELECT [Did not ship due to other reason].Ran, [Did not ship due to other reason].Customer, [Did not ship due to other reason].Reason, [Did not ship due to other reason].Turn_Around, [Did not ship due to other reason].Rep, [Did not ship due to other reason].ship_date, [Did not ship due to other reason].move_to, [Did not ship due to other reason].id, Did_not_ship_due_to_parts.Ran, Did_not_ship_due_to_parts.Customer, Did_not_ship_due_to_parts.Reason, Did_not_ship_due_to_parts.Turn_Around, Did_not_ship_due_to_parts.Rep, Did_not_ship_due_to_parts.ship_date, Did_not_ship_due_to_parts.id, [Did not ship due to other reason].move_to, Orders_in_shipping.Ran, Orders_in_shipping.Customer, Orders_in_shipping.Reason, Orders_in_shipping.Turn_Around, Orders_in_shipping.Rep, Orders_in_shipping.ship_date, Orders_in_shipping.id, Orders_in_shipping.move_to
FROM [Did not ship due to other reason], Did_not_ship_due_to_parts, Orders_in_shipping
WHERE ((([Did not ship due to other reason].Ran)=[forms]![SearchForm]![ran]) AND ((Did_not_ship_due_to_parts.Ran)=[forms]![SearchForm]![ran]) AND ((Orders_in_shipping.Ran)=[forms]![SearchForm]![ran]));

Thank you and let me know if you need any more information.

Thanks,
Will

 

[MakeItSo]

First of all - sorry it took me so long, couldn't get into the internet this weekend.

Forgot one important thing, Sorry.
Your SQL looks good, and I'm sure it is not the cause for the missing records:

You must set "*" as standard value for all controls in your search form! Guess that's missing, correct? ;-)

Andy

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[Twillz]

Thanks Andy

I can see the records when there's only one tabe in the query but I can't get the query to work with around 10 tables. If I get the query to work maybe the form would be easier to work on.

Thanks,
Will

 

[MakeItSo]

Hi Will,

If ther query works with the three tables as posted above, and it doesn't with ten tables, check your table relations and your joins (if any) in the query.
Do you get an error message like
"Your query contains ambigious inclusions.." when you try to run it or open SQL view?
If yes, then check if you really need all the tables. You have then used joins that don't go together.

And: Yeah, the query is the hardest part... ;-)

Andy

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[Pire]

Great post.

For the most part I got it to work beautifully. I do have a problem when entering date criteria such as '<1977' . It doesn't work when entered into the search form but the very same criteria entered into the query itself works.


Thanks much,

Trent

 

[MakeItSo]

Hi Trent!
In that case you must either use
year([your date field])<1977
(query designer might do this automatically, that's why it works there...) or try this:
< #*/*/1977#
If you're handling date criteria in forms, the SQL call must enclose the date field in hashes (#).

Twillz: Still alive? Did it work?

Cheerio,
Andy

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[Twillz]

Sorry Andreas,

Work put me on a new project. But I did not get it to work. What I did was I used the query to ask the user for the record that they wanted to look up and it would show the record on the search form. Sorry it took so long