Search Filter

[access7]

Hello everyone

Hoping someone can help... I currently have a filter in place so that when the user selects a prospect / client from the search box it opens up that prospect / client's details... I am wanting to take this one step further but keep getting stuck...
It is posisble to have many contacts to one prospect / client - at the moment each contact is presented as a separate row in the search box however, when you choose a contact from the list it still opens the company form at the first record (which may or may not be that contacts details...) What I would like is that when you select a line from the search list box it opens the form not only at the correct prospect / client details but also at the correct contacts details depending on who has been selected....

Any help / ideas are very welcome

Many Thanks!

 

[lcook1974]

so you basically want to reference the subform control via the DoCmd.Open for criteria?

Below is a link on referencing:
http://access.mvps.org/access/forms/frm0031.htm


I would think you would have to do something like this...but I'm not sure.

First put the contactId on the subform.(ContactRef field)

DoCmd.OpenForm "Frm_Company", , , "[CompanyRef] = " & Me!ListCompany " AND " Forms!Frm_Company!SubFrm_Contacts!ContactRef = & Me!ListCompany.column(1)


Not tested...just trying to point you in the right direction.
I'm not to sure how to reference a control like that through that command line.

Larry

 

[access7]

Thanks for the suggestion Larry; I understand the concept of putting the Contact Ref on the subform and have done so - I can tell the link is working as it does show the reference numbers for each contact as I scroll through the records... I am having problems with the line of code though... when testing it I keep getting errors... thanks for the building blocks - I am working on it now but if you can think of any other tips for how to write the code that would be much appreciated....

Many Thanks

 

[access7]

I have now put in the following code:

DoCmd.OpenForm "Frm_Company", , , "CompanyRef = " & Me!ListCompany & "Forms!Frm_Company!SubFrm_Contacts", , , "ContactRef = " & Me.ListCompany.Column(1)

But am getting the following error:

Run-time error '3075'
Syntax error (missing operator) in query expression 'CompanyRef=56Forms!Frm_Company!SubFrm_Contacts'

Can anyone give me a clue to what I am missing???

 

[access7]

Sorry to keep posting... but I have just put in the operator 'AND' so the code now reads...

DoCmd.OpenForm "Frm_Company", , , "CompanyRef = " & Me!ListCompany And "Forms!Frm_Company!SubFrm_Contacts", , , "ContactRef = " & Me.ListCompany.Column(1)

However I am now getting an error for a 'Type Mismatch'... am still looking into it...

 

[lcook1974]

DoCmd.OpenForm "Frm_Company", , , "CompanyRef = " & Me!ListCompany And "Forms!Frm_Company!SubFrm_Contacts", , , "ContactRef = " & Me.ListCompany.Column(1)

The syntax is wrong on this but unfortunatly I don't know how to reference that subform in that command line

Maybe try this:

DoCmd.OpenForm "Frm_Company", , , "[CompanyRef] = " & Me!ListCompany & " AND " & Forms!Frm_Company!SubFrm_Contacts!ContactRef = & Me!ListCompany.column(1)

If it were all on the same form it would read:

DoCmd.OpenForm "Frm_Company", , , "[CompanyRef] = " & Me!ListCompany  & " AND [ContactRef]  = " & Me!ListCompany.column(1)

Hopefully someone else can help you out. I'll be watching, cause I want to know too.