set query crieria by reference of form control in access project

 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

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'

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'

Now in a regular Access .mdb database this could be easily achieved
using criteria as follows:

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

Many Thanks In Anticipation,

TechyGal :)

3/14/2008 9:33:46 AM
access 16762 articles. 3 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 27


Similar Artilces:

Access security problem
I have access file not open because of security . i have the access file and the MDW file too so i want to know how to open this file and see the tables and the data The easiest way is to set up a shortcut to open the .mdb file using the .mdw file. The Target entry in the shortcut needs to be of this form: "FullPathFileNameToAccess.exe" "FullPathFileNameToYourDatabase" /WRKGRP "FullPathFileNameToYourSecurity.mdw" Here's a real example of mine: "C:\Program Files\MicrosoftOffice2003\OFFICE11\MSACCESS.EXE" "C:\Documents and Settings...

Multithreading and accessing window pointer from thread
Hello All, There has been a lot discussion on this group about MFC Multithreading. Still I want to make sure one thing about How to access Window Pointer within thread I am passing Window handle as lParam of thread and want to access window pointer from thread, Code is like this void CMainFrame::StartThread() { AfxBeginThread(ThreadProc,this->m_hwnd); } UINT ThreadProc (LPVOID lpParam) { HWND hWnd = (HWND)lpParam; CMainFrame *pFrameWnd = (CMainFrame*)CWnd::FromHandle(hWnd); if(pFrameWnd && pFrameWnd->GetSafeHWnd()) { BOOL bFlag = pFrameWnd->m_bFlag; pFram...

If I want to buid a simple database for customer details, should I use Excel or Access?
I want to buid up a database for store detailed information of customers. The key words would be contained Customer's name, contact, visited time, email address links ( That can be easily used for sending emails). I wonder which program is better, Excel or Access? I am thinking that Excel is more common to use. And If I set up Excel, its data can be imported to Access. There are two clues here. The first is "database" - Access is the database application. The second is that, in spite of that, you fancy Excel, probably because you are more comfortable with Excel. It is yo...

send/receive settings #2
My co-worker and I are both having a problem with setting the leave a copy on the server. We click the box for saving and choose the amount of days to leave on the server. Then when we close outlook and reopen it is gone. The box is no longer checked to save email on the server. What's happening?? Is there a fix for this problem? ...

Portfolio Server
I am an admin on the entire account and it will not allow me to do so. ...

print quality settings box greyed out
Hi In the page setup is the print quality setting supposed to be greyed out? The reason I ask is because I am supposed to have all worksheets in my workbook the same print quality or I can't convert it in Adobe Acrobat 6. -- Regards ON2DVD Ph 1300799383 Certain settings are unavailable when you access SetUp from the Print Preview window. Start from <File> <Page SetUp>. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ======================...

Access Import Problem
I am trying to import an excel spreadsheet into Access 2002 w/SP3 loaded. All goes well until it actually starts the import then I get the following error: "You entered an expression that has an invalid reference to the property |." Does anyone know what causes this or how to fix it? I saw alot of posts that talked about coding errors but this is a simple import. Thanks for your help ...

this is very frustrating: i am unable to connect to the internet - heres the story: my system runs xp sp2 with an msi motherboard (ms 7093, ati radeon xpress 200, athlon 64 bit, rs480m2 (socket 939)), realtek alc658c audio that uses a realtek ac'97 driver and a creative sb x-fi soundcard, a siemens speedstream 4100 modem, a realtek 8139 family pci fast ethernet nic and a d-link g510 wireless pci adapter. my device mgr indicates that under network adapters, i have working a 1394 net adapter, d-link adapter, and a rtl8139. under "other devices," i only see a yel...

Write access to a LDAP server.
Is there any way to enable write access in Outlook to my LDAP server? The LDAP server allows this functionality, Outlook doesn't. Any plugins that can do this? Thanks. Derek ...

Error message when trying to use my access add-in with VS2008
I created a Access 2007 add-in with VS2008 C# and I'm trying to debug my access form that sends text to a textbox to be displayed after I click on a button. I can step into 'FillTextButton_Click()' in my C# app that gets called when I click on the access form button. So I have my button and textbox connected to Access2007 with the Access onLoad() event. Everything seems to be connected ok. But as soon as I finish running through the 'FillTextButton_Click()' I get a error message saying "The expression On Click u=you entered as the event property se...

sorts query while including <all> at top of list
I have a combobox whose data source is this query, which sorts the records correctly: SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject ORDER BY IIf(InStr(FixtureTypesNoProject.Type,"-")=0,Len(FixtureTypesNoProject.type),InStr(FixtureTypesNoProject.type,"-")-1), IIf(InStr(FixtureTypesNoProject.type,"EM")=0,[FixtureTypesNoProject.Type],Replace([FixtureTypesNoProject.Type],"EM","0")) ; however, when I add the following (to include the <all> word at the top of the combolist...) it no longer sorts UNION Select &q...

Outlook changes mail settings
I have two questions. 1. Every time I reboot my computer I have to go to outlook and change the settings (the username and the pop server). Is there a solution for this? 2. I am getting about 20 email that say they are from microsoft and they contain viruses I do not open then and I do not have any viruses on my computer, I have scanned about 20 times and have not found anything. I have also followed the steps from my antivirues software on removing the SWEN_A virus and there is nothing there. Is there away to get these email to stop other then changing email? (I love my email address)...

One primary key per table? Access 2003 newbie
I'm under the impression that there is only one primary key per table in an Access database. I've got a class project where the professor asks to create a table of music categories. He tells me what categories are used (6 total) and he then explains that "the primary key for each category should be a short code consisting of letters. Another field will be a description of the category". I am completely lost as to what the professor is expecting. Can anyone shed some insight as to what he is expecting me to create? Please no flames about me going and asking the professor...

Can I change the color of a form based on status?
I have a form for work orders. I track the work order number, part number, serial number, etc. I would like to add a combo box with work order status Open/Closed. I know how to do this part. I was wondering if I can link a command that will change the color of either the box or the form based on the current status of the work order. When I scroll through the work order form I rapidly know which work orders are open and closed based on the color. The parts are shipped out to various agencies for repair and sometimes it may be months before we see our parts back. Once a week I go t...

Getting data from Access to Excel?
How would you go about pulling data from Access database into Excel? (Like weekly reports) Troy, Data|Get External Data|New Database Query Select MS Access Database The Wizard should take you through the remaining steps of selecting the data you want to import and the criteria (if any) that you wish to use. PC "Troy" <> wrote in message news:nLDXa.3726$ > How would you go about pulling data from Access database into Excel? (Like > weekly reports) > > You can also push-it-out of Access to Excel by (While...

Setting up a preffered stock and a Reit in Money 2003
A Reit that is not yet publically traded and a Preffered stock is more like a Bond that a Stock. How best should they be entered and tracked? In, Bob Barker wrote: >A Reit that is not yet publically traded and a Preffered >stock is more like a Bond that a Stock. How best should >they be entered and tracked? I would set up the REIT as a stock, and I would set up the preferred stock as a stock too. Bonds are priced as a percent of par/face value. Why doesn't money have investments like Reits and Preffereds? They are becomming a more common...

Access 97 under Win 2k to Access 2003 under XP
I'm in a shop converting a large number of Access databases (over 500) for a large number of users. Some databases have one user, some have dozens. The rollout of XP is happening in a random manner, so we'd like to do some conversions before the users get XP. I figured we could convert the 97 databases using Access 2003 and save as a Win 2000 compatible mdb. Then when the users do get converted we simply generate a Windows 2003 mde. Are there any issues in testing a system as a Win 2k mdb then unleashing a 2003 mde or should it be plain sailing. Thanks - David. "mscertified...

need help with tables so form okat- multiple equipment for 1 job
Here goes. I know this should be simple not sure why I can't see this right. Here is what I want to end up with not sure the best way to set up the tables to get what I want. and/or best way to create the form(from a query) or using form wizard pulling in tables that I need. I want to END up with a Form for all possible JOBS(cleaning jobs) with equipment needed and soaps needed for each job. There are many pieces of equipment for each job - mop, bucket, etc.(can't figure out how to get this in a form without listing with a comma) Have so far - don't thinks the tables a...

Sorting Query
I have a range of data, say A1:N150. The data in column A reads Field 1, Field 2, Field 3 etc and so on up to a possible of Field 150. However these can be in any order at the start and there may be blanks. As you know if the data was sorted by this column the it would turn out Field 1, Field 10, Field 11 etc. I got round this by entering in column N1, =If(Len(A1)=0,200,Len(A1)) , then copying this down to N150. I then sort the data by column N, then sort by Column A. This then puts the fields in the correct order, and puts the blank entries at the bottom. I am not sure if this is a perfect so...

Need help setting up fields on a liquor store import file.
What is the best way to set up the fields on the excel sheet (import) for a liquor store. Should I put the size of the bottle with the extended description or should i put the size in the sub description? I also have sku's (item lookup codes) for each child item but do not have one for the parent (a case of the product). How should I handle this? Do I make up my own lookup codes or should i just not use the parent child relationship and store everything as a child? What benefits does each one have? I always include the size in the primary description field. I always keep the d...

Date in the query is always short date
I want to capture the LONG date from a query, so I create a parameter for the date (mm ddd yy) . In its properties, it's format is long date, BUT it always shows up the short date. WHY? Dates are stored as decimal number counting days from 12/31/1899 midnight with time as the decimal fraction of a day. Formats are just different ways to display the information. So you got to set the format. In design view of the query click the field of the grid that has the datetime field, right click, scroll down and select Properties. Click 'Format' and then in the pull down...

Place Access database into Word with styles?
I have an Access (2003) database with several tables. In one of these tables (let's call it "Books"), each row refers to a book. Fields include Title, Author, ISBN, CopyrightDate, Description, and so on. I also have a large Microsoft Word (2003) document containing documentation (let's call it "Reference") about a software product. I'd like to place the data from the Books table into the Reference document, but I want the benefit of the Word styles defined in the document so that I can customize the look of the exported info by using a Word style. For instance,...

Subquery nested in Update query
I am trying to update a columns with a column from a subquery and I receive "an operation must use updateable query" UPDATE MyTable SET MyField = (Select TheField from TheTable where TheField >1) How should I do this? On Thu, 14 Feb 2008 07:41:03 -0800, JoeA2006 <> wrote: >I am trying to update a columns with a column from a subquery and I receive >"an operation must use updateable query" > >UPDATE MyTable SET MyField = (Select TheField from TheTable where TheField >1) > >How should I do this? The subque...

Email all addresses returned in a sub-form
I have a form that is linked to a sub form. These are connected via a class name so when they user filters the main form to a specific class the sub form returns only the students in that class. The data returned contains email addresses. I would like to have the ability to send an email to everyone in the specified class so I don't have to type each name in order to update the students on scheduling or locations. I can currently send an email to every student in the data base using the following code in a function: strSQL = "SELECT tblEmailList.Email FROM tblEmailList WHERE...

Try Free Project Management Software
Try Free Project Management Software From Get it now!!!! For free account go to ...