Continuous forms and querying another table (1 Viewer)

[johughes]

I have a continouus form that queries a table and displays various fields on the form. For each record in the master table displayed on the continous form I also need to show the result of a calculation in an unbound field.

The calculation needs to be based on the Count of the rows of a particular type in another table. This other table will have many different types of record - and I have a query that returns the count. This query is based on a SQL WHERE clause that is fed in a value from a field in the master table. To be concret a field in the master table has the name ID_component. This field has to be then used in the query to determine how many rows in this other table has a type of ID_component. From what I can tell right right now is that the query which is triggered from a combo box in the form is not getting the ID_component value on a record by record basis.

My question therefore is whether using continous forms one can get an unbound field to do a query on another tabel when the query has to be fed in a parmeter from a field in the original tabel?

If this is not possible - any ideas on how I can achieve something similar to I can obtain a count of rows and feed it into a continous form

thanks in advance

John

 

[carash77ash]

how do you have the query linked to the form.

Ash

 

[johughes]

The main form is populated by SQL query that selects a number of records according to some criteria froom one table (tbl_componentDefinition). A number of fields on the form are bound to specified fields in the table that the SQL query operates on.

There are also a number of unbound fields that contain the results of calculations based on the values in some of the bound fields. One of the bound fields contains the ID_component value of each fetched row - which will be different for each row. I want to be able to take the ID_component and do a query on another table (tbl_componentLevel) to Count how many rows in this other table have a ID_component of this value. This count can then be used as input to perform other calcualtions. Currently I have a stored query that is used by an unbound field with WHERE clause that is against tbl_componentLevel. The WHERE clause refers to the [fld_ID_component] form field. In a continous form the SQL just picks up the first fetched row's value of fld_ID_component. I want to know if there is any way around this implementation of Access


John

 

[carash77ash]

can you post your db so i can have a closer look.

ash

 

[johughes]

after some research it seems its a more fundemental problem - and perhaps my lack of understanding on how to parameters are fired into queries.

I have the query qry_count_implementationLevels.

This is defined as:

SELECT Count(*) AS Expr1
FROM tbl_implementationLevels
WHERE ((([tbl_implementationLevels].[ID_componentName])=[fld_ID_componentName]));

If I execute the query directly it asks me for the value of fld_ID_componentName via a dialog box. I enter an appropraite value - and the query returns the correct value.

In my form I try and execute this query from an unbound combo or text field. Neither work. When I try an unbound text box I have in the Control Source:

=[qry_count_implementationLevels]!fld_ID_componentName


however when I have the above in the text field it should pick up the value from the fld_ID_componentName field - but doesnt. The field just displays #Name?

when I try and execute the query from a combo box I just get returned the value 4 - no matter what the value of fld_ID_componentName. So my theory is that somehow I'm not firing into the query the correct parameter.


John

 

[carash77ash]

does your form have the field id_componentName. If so make the where criteria [forms].[formname].[id_componentname]. It will fire the query based on form.

Ash

 

[johughes]

I need to be able to call the same query from a number of different forms. So I guess I will re-implment the query in VBA - and use Me to specifically obtain the value of the field.

I guess what this illustrates is that when a query is called the specfic form is not passed over - and that one needs to explclity refer to the field in the heirarachy


John