Searching Records in a Database 1

[AndiChoin]

I have a form which searches certain fields in my database for the information entered and displays the results. I am having trouble understanding why only certain records are returned even though more records match the query. I think it has something to do with the fact that the form searches multiple tables which are linked and it has problems if a linked table has no matching records. If you think this may be the problem, does any one know any work arounds or other methods to accomplish the same thing?

This is my code for when you click on the search form button:
DoCmd.OpenForm "Proj_Results", acNormal, , "(Proj_Name Like '*" & Proj_Name & "*') AND (Proj_ID Like '*" & Proj_ID & "*') AND (Approved_By Like '*" & Approved_By & "*') AND (Requested_By Like '*" & Requested_By & "*') AND (Map_ID Like '*" & Map_ID & "*') AND (Report_ID Like '*" & Report_ID & "*') AND (SQL_ID Like '*" & SQL_ID & "*')", acFormReadOnly

The Map_ID, Report_ID, and SQL_ID are in other tables and not every Project is connected to Maps, Reports, or SQL data.

-Andi

 

[scking]

Is the form 'Proj_Results' bound to the data through a query? The question is how does the form display it's data. If you have multiple tables you need to click on the join lines and determine what type join you want. The default type join only displays the data if there is a relational link between the two files, however, if you change the join you can cause either of the tables to always display data and the other if there is a relational link. It's common to use this technique (LEFT JOIN) to verify there are no orphans between the tables as represented below.

SELECT tblPN.*
FROM tblPN LEFT JOIN tblToRef ON (tblPN.PN = tblToRef.PN) AND (tblPN.Cage = tblToRef.Cage)
WHERE (((tblToRef.ID) Is Null));

If the form is then filled with the data properly your rather detailed criteria has a chance of succeeding. A was of working this out is to use one criteria until that works and then adding more criteria until everything works.


-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[AndiChoin]

Thanks. No the form is not attached to a query perse, the code I wrote is the where clause on the open form. I forgot about the whole different types of join thing. That would help out tons... Thanks for solving my all my problems.