2 form controls accessing the same data but independently

[Mr_Si]

Hi there,

Here's an interesting one. Which I can't understand how to get to work with form controls.

I have an enquiry form and on it is a contact details page where we put the client details. However, sometimes the person or company who contacts us is not our direct client and is only acting as an "Agent".

Therefore I need to replicate what happens on our current paper enquiry form where we have a section for Agent and a section for Client (the client is the invoice address).

However, the data needs to come from the same tables because an agent will be a contact, as will the client.

I hope that makes some form of sense.


The form will want to look something like this:

Currently, the Client ComboBox and the Agent ComboBox are referencing exactly the same field in the tblEnquiry table, which is lngzContactID (a foreign key lookup to the tblContact table). So if one changes, so does the other. This is the bit I'm not wanting to happen!

I think I need to use a two queries, one called qryClientDetails and one called qryAgentDetails which both reference tblContact and then add them to the enquiry table as lookup fields, in replacement of the single contact lookup field. Does that sound right?

Thanks greatly! (in advance)

 

[Rabbie]

See this link for a possible way of resolving your issue.

 

[Mr_Si]

Oh that makes sense!

So there is a way to do it! Seems so easy - I love Allen Browne! (in a non-gay sort of way)

Thanks Rabbie! Shall read it and then database life will get a lot nicer I think.

 

[Rabbie]

Glad to hear you found the link useful. I have picked up a lot of good things from Allen's site

 

[Mr_Si]

Yeah Allen's proved very useful to me too.

Ok so I'm still struggling through this one...

My enquiry form is now based on a query that I've made rather than directly to a table like it was previously.

The query design is like this:

But the results come up with nothing at all:

the form design now looks like this: (note the client. and agent. prefixes) but when I open the form in form view, it is blank.

I don't get it, argh! Help!


Edit: I should note that the Client and agent aliased tables are based on a query which works. The original un-aliased query result is called qryContactComplete (it includes the info from the contact table, the contact type table, the company table, the address table, telephone numbers table, emails table and websites table).

 

[Mr_Si]

I've tried it with inner joins too, (as opposed to the left joins shown) but to no avail.

 

[Mr_Si]

Sorted it, all I did was to create a query, copy it and call them qryClient and qryAgent. Then I included 2 lookup fields in the tblEnquiry which looked up to the results of the respective queries.

Sorted!