Linking a Combo box to variable SQL query in subform

I am in the process of creating a relatively small D'Base that will monitor 
employee competency.
There are several tables and relationships within this, but I don't consider 
this to be a big project (although my first post-graduation in the real 
world).

I have configured the records to be spread over two tables. One is the 
tbl_ReviewHearder (containing employee info', review date), the second, 
tbl_ReviewContent contains the competency information. This is based on a 
1-many relationship (1 header, lots of content)

I have written VB to filter the combo boxes, but I am having trouble getting 
the final combo box [Element] to load a subform containing the Criteria. I 
have written a simple query, but that requires that I manually enter the 
Element value. I would like this to be past to the query automatically, or 
possibly on a button click event.

Further info' listed below.

Any pointers or help is always greatly appreciated.

    Mark



tbl_ReviewHeader:
    ReviewID      (autonumber)
    Employee_ID (lookup from tbl_employee)
    JobTitle         (lookup from tbl_jobs)
    ReviewPeriod (lookup from tbl_reviewperiod)

tbl_ReviewContent:
    ReviewContentID (autonumber)
    ReviewID            (linked to tbl_ReviewHeader)
    Unit                    (combo selection)
    Cluster                (combo selection)
    Element                (combo selection)

tbl_Element_Criteria_Subform:
    Criteria
    Rating
    Notes

I am aiming to get the list of criteria that are under any given 'Element' 
to be loaded in the subform as a list. I can then commit these to a record

The competencies are linked on a one to many basis e.g.

Unit = 1
Cluster = 1.1 (1.2, 1.3, 2.1 etc)
Element = 1.1.1 (1.2.1, 1.3.1, 2.1.1 etc)
Criteria = 1.1.1.1 (1.2.1.1, 1.3.1.1, 2.1.1.1 etc) 


0
Mark
1/18/2008 10:10:18 AM
access.forms 6864 articles. 2 followers. Follow

1 Replies
1011 Views

Similar Articles

[PageSpeed] 14

to pass a criteria to a query from a form ; you need to reference the correct 
Form control in the query's criteria:  Forms!FormName.ControlName

When that control is a text box it is this simple.

If the control is in a subForm - then you have to reference the subform 
first and follow some literal syntax:

Forms!FormName.SubFormName.Form!ControlName

If the control is a combobox then the value of that control is the bound 
column - not often what you visually see in the combobox.


-- 
NTC


"Mark" wrote:

> I am in the process of creating a relatively small D'Base that will monitor 
> employee competency.
> There are several tables and relationships within this, but I don't consider 
> this to be a big project (although my first post-graduation in the real 
> world).
> 
> I have configured the records to be spread over two tables. One is the 
> tbl_ReviewHearder (containing employee info', review date), the second, 
> tbl_ReviewContent contains the competency information. This is based on a 
> 1-many relationship (1 header, lots of content)
> 
> I have written VB to filter the combo boxes, but I am having trouble getting 
> the final combo box [Element] to load a subform containing the Criteria. I 
> have written a simple query, but that requires that I manually enter the 
> Element value. I would like this to be past to the query automatically, or 
> possibly on a button click event.
> 
> Further info' listed below.
> 
> Any pointers or help is always greatly appreciated.
> 
>     Mark
> 
> 
> 
> tbl_ReviewHeader:
>     ReviewID      (autonumber)
>     Employee_ID (lookup from tbl_employee)
>     JobTitle         (lookup from tbl_jobs)
>     ReviewPeriod (lookup from tbl_reviewperiod)
> 
> tbl_ReviewContent:
>     ReviewContentID (autonumber)
>     ReviewID            (linked to tbl_ReviewHeader)
>     Unit                    (combo selection)
>     Cluster                (combo selection)
>     Element                (combo selection)
> 
> tbl_Element_Criteria_Subform:
>     Criteria
>     Rating
>     Notes
> 
> I am aiming to get the list of criteria that are under any given 'Element' 
> to be loaded in the subform as a list. I can then commit these to a record
> 
> The competencies are linked on a one to many basis e.g.
> 
> Unit = 1
> Cluster = 1.1 (1.2, 1.3, 2.1 etc)
> Element = 1.1.1 (1.2.1, 1.3.1, 2.1.1 etc)
> Criteria = 1.1.1.1 (1.2.1.1, 1.3.1.1, 2.1.1.1 etc) 
> 
> 
> 
0
Utf
1/19/2008 12:11:01 AM
Reply:

Similar Artilces:

C# programs startup with an error dialog box
Hello, I have a problem with .NET programs (C# written using Visual Studio 2005) that are running on a particular computer. When I execute the programs, there is a delay followed by a dialog box with the name of the executable on the title bar. There is an error icon with an OK button. There is no other information displayed. After pressing the OK button the application exits. I tried to compile a simple WinForms application. It runs okay on my apps development PC, but when transferred to and run on the other PC, it crashes with the same error. I tried to reload the dot Net 2....

cannot install Office 2001 Combo update 9,0,4
When I try to launch it on my G4, the Classic Environment is automatically launched. Then I get the following message: "The default location for this install cannot be found.This install will be cancelled." I have no trouble with other installs. Hi It sounds like you may have put your Office 2001 installation in a place other than the default location and the updater can't find it. Is that possible? -Jim Gordon Mac MVP All responses should be made to this newsgroup within the same thread. Thanks. About Microsoft MVPs: http://www.mvps.org/ Search for help with the free Googl...

Populate a combo box 02-01-08
I am working in Access 2003 I have a combo box with two columns. It is bound to a yes/no field called "Accept". The forms displays the word "Accepted " in the second column in the combo box) but records -1 or Yes to the "Accept" field. How do I get the report to show the words "Si" or "Accepted" (english and spanish- I will be using two boxes for this) on the report based on what is entered on the form and subseqently the table (the form does a refresh upon opening of the report) Thanks in advance Deb Place a text box on the report, with i...

check boxes 02-22-10
Hello, I'm trying to insert text boxes into a document. I have found how to add them the problem is I'm getting a gray color behind them and can't find where to get rid of it at. I have tried everything. I knew how to do it in previous versions of word but not in 7. Can anyone help, please and thank you! :) -- Kathy Are you referring to the Check Box Form Field and Text Form Field controls on the Legacy Forms palette (Legacy Tools on the Developer tab)? There is a button on that palette to turn field shading off and on. -- Suzanne S. Barnhill Micro...

SQL Delete Layaway at HQ
Looking for an SQL command that will delete an entire Layaway in HQ. Example delete Layaway number 57. Thanks for any help. Neil Never mind. I used DELETE from [Order] WHERE [Order].ID = '57' "nt8378" wrote: > Looking for an SQL command that will delete an entire Layaway in HQ. Example > delete Layaway number 57. Thanks for any help. > > Neil Don't forget to delete any of the Item information from the OrderEntry table for that order also. TomT "nt8378" <nt8378@discussions.microsoft.com> wrote in message news:9B17957F-2696-446C-B08...

Query Text in the Mid of a memo
I am trying to query off a memo field [Description] the text that follows "User Name: " until the first space the "User Name: " is not in the same position nor is the text of the user name a fixed size. I am working with an InStr function but I don't know how to stop at the " " after the name. Thanks so much for any help. AngieSD wrote: >I am trying to query off a memo field [Description] the text that follows >"User Name: " until the first space the "User Name: " is not in the same >position nor is the text of the user n...

Allow Multiple Values check box not showing up
I am trying to create a Multivalued Lookup Field. I am using the Lookup Wizard, but when I get to the place where is should let me choose to store multiple values for the lookup that section does not come up. I went online and printed the instructions off the help page so I know I am doing it right. Help! IMO, multivalued and lookup fields are not a good idea. You didn't mention what version of Access you are using. -- Duane Hookom Microsoft Access MVP "mschurter" wrote: > I am trying to create a Multivalued Lookup Field. I am using the Lookup > ...

SQL Error 'CoProcess' fail accessing to SQL data
I have two companies in my Great Plains Server, i make a backup of first company and restore that in the second. but when a attempt to access to second company whith any user, and I have this error: una obtener/cambiar primero operacion en la tabla 'coProcess' ha fallado al acceder a los datos SQL Hi Before you restored the company1 to company2, did you add the users to have acces to company2? If not you will need to add the users to company 2 within Great Plains system menu (uncheck the SQL options to apply this). Can you logon as the 'sa' user? Regards James &qu...

Chart Title or Text Box Linked to cell & sheet
I need to create charts from data collected via a com port. As i stands, the data is written to sheet 1. Chart 1 is ready to plot dat as it is written. Once the data is complete, I copy sheet 1 and char 1. This results in sheet 1(2) and Chart 1(2). All references to shee 1 on the newly created chart 1(2) automatically changes to refer t sheet 1(2). This applies to data ranges, Chart Title, Axis names. When creating text boxes refering to sheet1!$A$1, the reference doe not automacially change to sheet 1(2)!$A$1. Can this be done? If not, can I create multiple chart titles? Any help o...

change date in a sql statement from a cell
Hello, I am using Excel 2007 I have a pivot table that gets refresh everyday. The data from the pivot table is based on a sql statement, which the data is connected to a AS/400 table. Here is my problem every morning I go in the connection properties and change the SQLstatement (date) in the command text. I don't want my user to do this. What other option can I do? I was thinking change the date in a cell (A1) and somehow the SQL statement picks up the new date or maybe some sort of parameter, but I am clueless in how to do this. Any tips or website to visit I will a...

Local OLAP cubes in Excel/MS Query
Hi Can someone please recommend some good book or web page about creating local OLAP cubes using MS Query. I have some data in Access that I want to work with using cubes. Kind regards IgorM http://wang.se/en/CreateOLAPCube.html/ Ed Ferrero Hi Thanks for response. Unfortunately I still don't know if the show-details-after-double-click feature is available in pivottables linked to OLAP cubes. I tried all three connection types. With the two first options (rebuild at once and rebuild when needed) I still cannot make the pivot table to create a sheet on the fly and provide detai...

Drop Down Box Help
Hi I am new to using Excel more interactively. I am able to create drop down box so a user can choose a day of the week for example. But I want to be able to populate other cells in the column dependin on the choice from the drop down list. e.g. choose Tuesday from a list of days of the week in A2 and have B2 C2 etc filled with related data. Is this at all possible? Thank you in advance -- smcka ----------------------------------------------------------------------- smckay's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3582 View this thread: http://www.exc...

Option Buttons Group Box
I am constructing a questionaire with responses from 1 - 5. After using the "group box" tool to group the option buttons for a response to the second question, the response from the first set is eliminated. How do I keep the responses seperate for each set of questions? Also, how do I make the border for the group box disappear? First, did you use the optionbuttons from the Forms toolbar--not the controltoolbox toolbar? (it sounds like you may have mixed and matched your controls.) If you did use optionbuttons from the control toolbox toolbar, you can "group" them b...

Web link to Excel file
I'm trying to link to an Excel 97 file from a ColdFusion web page. The first time I click the link, the file opens fine in the browser (I.E. 5.5). If I close the file and click the link again, I get a message stating 'filename.xls is currently being modified by "my user name here". Open as read only?'. I can still open the file as read only, but am wondering why I get this message, because I'm not making any modifications. I've had other people try this link, and they get the same thing, except it tells them that they're the one modifying the fi...

Entity framwork accessing private member variables
(The following was posted in the ADO.NET newsgroup but got no responses. Thus the posting here.) Doing some testing with Entity Framework, I have been able to get POCOs to save using the public Properties. Now I am trying to switch from using public properties to private member variables. Originally the getter and setter of the ID property were public. I have changed the getter and setter to private access and changed the name to 'id' the name of the underlying member variable. I get an error: "Mapping and metadata information could not be found for Entity T...

How to change appearance of the links, pointing to subdocuments in the master document
Hi, I want to change the appearance of the links, which are pointing to subdocuments in the master document (Word 2003). Specifically I want those links to look more descriptive with regards to their contents - specifically making those links to appear like those are entries in the Table Of Contents (like it would be looking if those subdocuments were just paragraphs in the single monolitic document). Please note that I DO NOT want to rename the filenames of those subdocuments - I just want to change the appearance of the links to those documents, in the way I already described above....

Duplicate the Record in a Form and many SubForms
I am using Allen Browne's tip that is available in his web site. However, I get a compile error stating that the variable dbFailOnError is not defined. I have search the MS Knowdledgeable base and found out the DBEngine (0) (0).Execute is no longer used in current versions. I change it to the following but I still get the same compile error message. If Me.SF_Adjacencies.Form.RecordsetClone.RecordCount > 0 Then strSql = "INSERT INTO [Adjacencies] ( DataRmID, AdjID,_ AdjRoomName, AdjRelevance ) " & _ "SELECT " & lngID &...

View query results in MsgBox
I have developed qryQuickLook that brings up rptQuickLook when cmdQuickLook is clicked, everything works great but I would rather see the results (never more than 10 line items) in a MsgBox than have a seperate report window open up. Is there a way to do this? Thanks Dave On Nov 23, 11:00 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com> wrote: > Why "MsgBox"? You can display the results in a dialog form with a command > button or two. > > -- > Duane Hookom > Microsoft Access MVP > > > > "maces...@gmail.com" wrote: > > I have deve...

SQL Recovery Models
Hello, I am currently running CRM 1.2 and GP 7.5. I am using the backup utility in SQL 2000 to backup the databases for CRM, GP, and Integration. What recovery models are people using for GP and CRM? Some of the databases and configured for Simple while others are setup for Full recovery model. I would like to have full backups run daily and then backup the transaction logs hourly. Are there any advantages to running Veritas Backup Exec (or 3rd party backup software)? Any information would be appreciated. Thanks, Mike I would use Full recovery where possible. You won't...

How do you define variables in excel?
I am setting up a spreadsheet to keep track of my students. I want to use excel if possible to keep track of lates, left early, attendance, etc, as my grades are kept there already. I was wondering if you can assign values to variables to accomplish this and how to define them. Thanks Why do you want to assign to variables, why not just use worksheet cells? -- HTH RP (remove nothere from the email address if mailing direct) "BigRon" <BigRon@discussions.microsoft.com> wrote in message news:D246057F-E2D9-474F-85A2-A31440E48142@microsoft.com... > I am setting up a s...

Unreadable Comment Boxes
I created a workbook, and some of the cells on the sheets hace comments. The workbook is used by about 10 different people, everyone uses office 2000. When some of us open the workbook and put the pointer over a cell with a comment we see the comment without any problem. 2 or 3 of the people when they put the pointer over a cell with a comment all they see is a black square and they can't read the comment. The sheets are locked. Any Idea why this is happening and how I can fix it so everyone can see the comments? Thank you. -- Gordon Hi Gordon, Q173798 -- XL97: How to Change the Fo...

Linking Excel drop down lists
I am trying to set up a billing spreadsheet. On one spreadsheet I have a list of product codes in one column, the description in the next column and the price in the third column. On another spreadsheet I would like to pick the appropriate code from a drop-down list (no problem with that) and I would like the description and price to fill in automatically once I choose the code. I thought this would be easy but I am stuck!! Having drop down lists in each column is not an option since there are 163 things in each drop down - the descriptions are wordy and I want to garantee I g...

Access conversion to SQL
Hi all. I have an application that currently connects to an Access database, now the customers are asking for a MySql and MsSql server back end database. MySql handles True and False values through the bit field without any problems, MsSql doesn't. I can't even use the words True or False in an Sql statement. This means I have to write two loads of code for every transaction where there is a true/false value involved, a complete pain. It also gives me the problem of setting controls true/false value by checking a table field: chkAdmin.Checked = GetField("select admin...

installing sql server 2008 express or 2010 express
I get always errors and im not able to continue,,,,,,can anyone help me plz? Thanks On Jan 5, 10:55=A0am, "eusclide" <e...@me.it> wrote: > I get always errors and im not able to continue,,,,,,can anyone help me p= lz? > Thanks 1. This is not the appropriate discussion group for SQL Server issues. 2. If you expect any response at all, you need to indicate the exact errors you are getting. More specifics. "eusclide" <eus@me.it> wrote in news:Ouk449hjKHA.1536@TK2MSFTNGP06.phx.gbl: > I get always errors and im not able to continue,,,...

Hyper links in email
Why can I not open hyper links in email ? I have Outlook 2002 with SP3 installed . The message I get back is the action cannot be done due to restrictions on my computer. Please contact your admistrator. Thanks in advance Kodiak <kodiak62at@hotmail.com> wrote: > Why can I not open hyper links in email ? I have Outlook 2002 with SP3 > installed . The message I get back is the action cannot be done due to > restrictions on my computer. Please contact your admistrator. If your web browser is Internet Explorer, open IE and click Tools>Internet Optons>Programs>Rese...