469,331 Members | 1,879 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,331 developers. It's quick & easy.

Search in subforms

Hi everyone,

I'm an access advanced-beginner, mainly self taught and I'm building a fairly simple parent/child database. I have a table of companies, and a table of contacts at those companies linked by the CompanyID.

I have a master form displaying company info and then a subform showing all the contacts at that company.

I know how to search the master form for a particular company and display that record, and I presume it would be straightforward to search within the contacts for that company (i.e. those currently being displayed on the subform) but...

...is there a way to search from the master form for a particular contact from the whole database and have it display the company which that contact is linked to?

My only idea (although I have no idea how to do it) would be to somehow pass the search criteria out of the form, then use it to lookup the CompanyID from the Contacts table and then pass the CompanyID back into the form and search in the normal way. does this sound possible? I'm not expecting complete answers but a few pointers would be much appreciated. or please tell me if I'm wasting my time!

thanks

Neil
Oct 19 '07 #1
1 1612
Rabbit
12,516 Expert Mod 8TB
You can retrieve the CompanyID of a Contact using the DLookup function.

Expand|Select|Wrap|Line Numbers
  1. DLookup("FieldName", "Table/Query Name", "Criteria")
  2.  
  3. DLookup("CompanyID", "tbl_Contacts", "[Contact Name] = '" & txtContactName & "'")
  4.  
Make sure you account for when there is no matching Contact Name. In which case, I believe DLookup returns a Null.
Oct 19 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by Christopher Koh | last post: by
reply views Thread by Jack | last post: by
3 posts views Thread by Stu | last post: by
2 posts views Thread by Robert Jacobs | last post: by
3 posts views Thread by Elainie | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.