pass parameter from table, yes-no does not work

[smile]

Hi, please see attached db with my problem.

I have a parameter table with field "param1"
Then products table with yes/no field.

I made query from above tables and would like to be able to enter data.

When the parameter table is included in the query calculations work fine.
If parameter table is removed I can add edit data but calculations fail.

How do I solve this?

 

[stopher]

I'm guessing that you want to be able to use the parameter as well as edit the records.

The reason you can't add records is because you have created a cartesian product. This is where there is no relationship between the tables and therefore the result is every combination of record from both tables. In your case, since there is only one record then this is not so obvious. But it is still the case and therefore Access cannot decide where new records should go.

I recommend you create a parameter ID field in both tables and then use this as a relationship. I attach the example with a new query (Query2).

hth
Chris

 

[smile]

Thanks it worked

 

[smile]

Is it possible to use VBA module to read values from tbl_myparameters and store them as variables, then use those variables in query formula to avoid use relationship to another table specificaly creating FK ID?

I say this because if 5 fields needs variables configurable in central configuration tables I will need to create 5 FK ID's and link them to parameter table.

 

[smile]

anyone ?

 

[smile]

Found a simple way, any comments. Is the formula correct? I mean is it a good alternative?

Tried to use =DLookUp("my_field_name";"tbl_my_table_name")
as default value in property sheet. It works for text box and yes/no field did not try others.