QBF Issues

[zakster]

Hi all,

I'm a newbie when it comes to Access and are therefore running into difficulties. So hopefully this should be an easy one.

I want to use a query as a data source for one of the fields in a form. For this query to work it will need to be parameterised so that it only grabs the information relevant to the current record being viewed.

I have tried to do this, but every time I go to view a record the field with the query as a data source shows - #Name?.

This is the set up I have been trying:

Query - qryProductClient

SELECT c.chrCompanyName AS client
FROM tblContact AS c, tblProduct AS p, tblQuote AS q
WHERE p.lngzQuote=q.idsID 
AND q.lngzClient=c.idsID 
AND p.idsID=Forms!frmProduct!txtID;

All of the data that is needed (i.e. the contact, product and quote) is all there. The form frmProduct has a field on it named txtID.

The field using the query as the data source has the following control source entered:

=qryProductClient!client

Can someone please tell me what I am doing wrong?

Thanks for your time,

Cheers,
Alicia.

 

[shudini]

if all you're returning is a single value, why not just use the DLookup() function as the textbox value?

 

[zakster]

In essence isn't the DLookUp function the same as a query anyway?

When using the DLookUp function are you able to incorporate three tables in the criteria? Or can you only use the lookup table and the current form object, because I need 3 tables to make the query?

I would really like to know why the query doesn't work. I have tried taking out the section that refers to the current product ID from the form (Forms!frmProduct!txtId) and replaced it with a known product ID. This works.

Any ideas?

 

[ajetrumpet]

I want to use a query as a data source for one of the fields in a form..

So, you have a table-based form with 3 fields, and a separte control that pulls a value from one field in a QUERY, right??

First thing I noticed is that you've got the control name where you want to store the queried values as an actual criteria parameter in your query. That obviously won't work.

For this query to work it will need to be parameterised so that it only grabs the information relevant to the current record being viewed.

Sounds like autopopulating.

I wonder if you could run the query code on an event property (like "GotFocus")?? You'll have to explain a bit more to go further...
i.e. "form fields bound??", etc...?

 

[zakster]

Ok I did a very bad job of explaining my problem and set up. Sorry - I will try again.

Okay I have three relevant tables:
tblContact - contact information
tblQuote - quote information (links to contact)
tblProduct - product information (links to quote)

Each product has an associated quote, and each quote has an associated contact.

I am trying to develop a form (frmProduct) for tblProduct. At the moment I have frmProduct bound to tblProduct.

I don't have any issues displaying all the normal tblProduct fields in frmProduct. But I would also like to display the contact associated with the Product (i.e. through the product->quote->contact link). This would not be an editable control on the form.

I have come to the conclusion, that I really do not know the correct way of doing this.

Hope that makes things a bit clearer.

Thanks,
Alicia.

 

[Brianwarnock]

Hi Alicia

Try replacing your From with the following and abandon the Where


From tblcontact INNER JOIN (tblproduct INNER JOIN tblquote ON tblproduct.lngzQuote = tblquote.idsID) ON tblcontact.idsID = tblquote.lngzClient;

Brian

 

[zakster]

Thanks - that did the trick. All is working now.