set query crieria by reference of form control in access project

[TechyGal]

Hi There,

I am developing an Access 2007 .adp Project (so really access 2000)d
atabase using SQL 2000 as the data backend.

I have a subform which contains a combo box where you can select a
'Service'. There is then a subform which contains a combo box where
you can select a 'Task' which needs to be filtered according to the
'Service' selected on the previous subform. I then have a third
subform which will be used to select 'Products', again using a combo
box filtered according to the 'Task' selected.

The combo boxes all have a row source which use a Query on a lookup
table to determine the values to display.

All of the 'Services', 'Tasks' and 'Products' are stored in a single
look up table which contains the following fields:

ID - An int field set to Identity Seed with an Increment of 1.

Type - Nvarchar Combobox to select 'Service', 'Task' or 'Product'.

Name - Nvarchar to enter the name of the 'Service', 'Task' or
'Product' e.g. Purchasing

Reference - Int to reference the ID of the 'Service' or 'Task' that
the record relates to e.g:

A 'Service' called 'Planning' has an 'ID' of '1' and a 'Reference' of
'0';

A 'Task' related to 'Planning' is called 'Equipment Planning' which
has an 'ID' of '4' and a 'Reference' of '1';

A 'Product' related to 'Equipment Plannning' is called 'Schedule of
Locations' which has an 'ID' of '6' and a 'Reference' of '4';

All 'Services' have a referencve of 'O'.

Any 'Task' belonging to 'Service' ID '1' have a Reference of '1'
etc..

My Problem is that I need to be able to set the Row Source of the
'Task' combo box with criteria which looks at the value selected in
the 'Service' combobox so that only records with the selected
'Service' 'ID' as their 'Reference' are displayed in the 'Task'
combobox.

Now in a regular Access .mdb database this could be easily achieved
using criteria as follows:
Forms!MainForm!Subform1.Form!Service_Type

Service_Type being the name of the combobox on the first 'Service'
subform which contains a rowsource which diplays all records with a
criteria for 'reference' set as =0.

This method however, does NOT work using .adp project databases and
the following error message appears:

'Data Type Error in Expression'...

Does anyone PLEASE know of any way that I can get this to work and
reference a control on a subform as part of the criteria in the
query???

Many Thanks In Anticipation,

TechyGal

 

[Paul Bexon]

Hi Techygirl

Did you get any solution to this? I have an identical .adp database application to yours - choosing a 'service name' at login, then requiring the combo boxes/drop-downs to filter by the 'service id'.

As you say, it works in mdb's, but not in adp.

Regards

Paul



TechyGal wrote:

set query crieria by reference of form control in access project
15-Mar-08

Hi There

I am developing an Access 2007 .adp Project (so really access 2000)
atabase using SQL 2000 as the data backend

I have a subform which contains a combo box where you can select
'Service'. There is then a subform which contains a combo box wher
you can select a 'Task' which needs to be filtered according to th
'Service' selected on the previous subform. I then have a thir
subform which will be used to select 'Products', again using a comb
box filtered according to the 'Task' selected

The combo boxes all have a row source which use a Query on a looku
table to determine the values to display

All of the 'Services', 'Tasks' and 'Products' are stored in a singl
look up table which contains the following fields

ID - An int field set to Identity Seed with an Increment of 1

Type - Nvarchar Combobox to select 'Service', 'Task' or 'Product'

Name - Nvarchar to enter the name of the 'Service', 'Task' o
'Product' e.g. Purchasin

Reference - Int to reference the ID of the 'Service' or 'Task' tha
the record relates to e.g

A 'Service' called 'Planning' has an 'ID' of '1' and a 'Reference' o
'0'

A 'Task' related to 'Planning' is called 'Equipment Planning' whic
has an 'ID' of '4' and a 'Reference' of '1'

A 'Product' related to 'Equipment Plannning' is called 'Schedule o
Locations' which has an 'ID' of '6' and a 'Reference' of '4'

All 'Services' have a referencve of 'O'

Any 'Task' belonging to 'Service' ID '1' have a Reference of '1
etc.

My Problem is that I need to be able to set the Row Source of th
'Task' combo box with criteria which looks at the value selected i
the 'Service' combobox so that only records with the selecte
'Service' 'ID' as their 'Reference' are displayed in the 'Task
combobox

Now in a regular Access .mdb database this could be easily achieve
using criteria as follows
Forms!MainForm!Subform1.Form!Service_Typ

Service_Type being the name of the combobox on the first 'Service
subform which contains a rowsource which diplays all records with
criteria for 'reference' set as =0

This method however, does NOT work using .adp project databases an
the following error message appears

'Data Type Error in Expression'..

Does anyone PLEASE know of any way that I can get this to work an
reference a control on a subform as part of the criteria in th
query??

Many Thanks In Anticipation

TechyGal

EggHeadCafe - Software Developer Portal of Choice
Speed Up Sql Server Data Inserts With SqlBulkCopy
http://www.eggheadcafe.com/tutorial...18-cee8a2357251/speed-up-sql-server-data.aspx