Stuck on form and sub-form

novoiceleft

Registered User.
Local time
Today, 11:41
Joined
Jul 4, 2004
Messages
65
Hello All. Please can anyone help me build the following simple thing. I am stuck!! <<NON-TECHIE ALERT MODE>>

I'm actually not sure whether my problem is with the forms - or a query or the tables.

I have one table called Employees – with all the details of each employee. The primary index field is EmployeeID

Each employee is to be given a set of scores against a list of parameters. The list of parameters is in another table called Parameters

ParameterID
ParameterName

What I want to achieve physically, is a form with all the details of the employee on it (ALREADY DONE), with a sub-form on which will appear the list of all the current parameters - with a box to enter a score out of 10 for each parameter … Given that the number of parameters might change, I want the sub-form to reflect the current list of parameters whether the scores have been given or not. Every time a new employee is created, the sub-form will automatically show ALL the current parameters, with any scores that have been entered and blank scores if they have not yet been entered.

I have been trying to get this working using a join table called EmployeeParameterScores. I’m not sure that is the right way.

What I am confused about is how to build my join table with the right relationships – so that the sub-form will automatically load up the list of current parameters with completed or blank scores. OR should my form be based on a Query. I have tried the Query approach, but it won’t let me enter scores (presumably because I have the wrong joins.)

Aaargh. All help appreciated.

NoVoiceLeft
 
As you have described your current structure, there's no relationship between the Employees table and the Parameters table. You described it something like this (please correct me if I'm wrong):

t_Employees: EmployeeID, EmployeeDetail1, EmployeeDetail2, ... EmployeeDetailx

t_Parameters: ParameterID, ParameterName

What ties those together? (You didn't provide any structure detail for EmployeeParameterScores, so I had no idea what you had in there.)

What you need is an intermediary table that has a common field between the two existing tables. Make sure you have "EmployeeID" and "ParameterID" set up to be primary keys. Then make another table that will store the scores, like this:

t_Employee_Scores: EmployeeID, ParameterID, ParameterScore

Note that this structure is pretty much a big part of what data normalization is, so you may want to investigate that topic a little bit too.

Finally, in your subform, left join t_Employees.EmployeeID into t_Employee_Scores.EmployeeID and t_Parameters.ParameterID into t_Employee_Scores.ParameterID.

If you need more help on that (or if I read what you needed wrong), post back.

~Moniker
 
Last edited:
Thanks Moniker. I think I am getting there.

In your answer, can the table you describe as the "intermediary table" be the same as the new table you describe? If so, I have now got three tables as follows:

t_Employees: EmployeeID (primary key), EmployeeDetail1, EmployeeDetail2, ... EmployeeDetailx

t_Parameters: ParameterID (primary key), ParameterName

t_Employee_Scores: EmployeeID (primary key), ParameterID (primary key), ParameterScore

By the way - I have built the relationships without referential integrity.

For the subform, I have attempted to build a query. I needed to build it in two stages - because I was getting an ambiguous join error.

My query is not producing what I want. I need it to show EVERY parameter for EVERY Employee - whether the score has been filled in or not.

I cannot write SQL, so I used the Query Build tool - and this is what it tells me I now have.

Query1
SELECT Employee_Scores.EmployeeID, Parameters.ParameterName, Employee_Scores.ParameterScore
FROM [Parameters] LEFT JOIN Employee_Scores ON Parameters.ParameterID = Employee_Scores.ParameterID;

Followed by Query2
SELECT Employees.EmployeeID, Query1.ParameterName, Query1.ParameterScore
FROM Employees LEFT JOIN Query1 ON Employees.EmployeeID = Query1.EmployeeID;

Have been reading all about Left and Right joins - but still very confused. Have I done something in the wrong order?

NoVoice
 
See the attached. I've created a sample DB for you with this in it:

Tables:
t_Employees
t_Parameters
t_Employee_Scores

Queries:
q_AllParams

Forms:
f_Employee_Main
f_Employee_Sub

The tables should be pretty obvious as to what they are doing and how their structured. Note that in the design in t_Employee_Scores, I limit the field "ParameterScore" to be a number from 0 to 10. The earlier you can limit things like that, the better.

The query shows you the structure on how to link each employee to a parameter and a score, regardless of if there's anything in that particular parameter's score.

The forms show you the form/subform structure you need to show one employee name with multiple parameters. (Note you can use that form for editing as well.) There is very liitle code here (three lines total between the two forms), but I'll briefly explain what I've done:

f_Employee_Main:
There are two controls on this form, the combo box with the employee names and the subform (which is considered a control). The combo box displays the full name of the employee, but is bound to the employee ID. The RowSource looks like this:

SELECT EmployeeID, EmployeeFName & " " & EmployeeLName AS EmployeeFullName FROM t_Employees;

In other words, I'm concatenating the Employee's First Name and Last Name into a new field I called "EmployeeFullName". The code behind this form changes the Record Source of the subform, which is how you sync the employee drop-down value to the subform. That way, the subform will show parameter values pertinent to that employee. That code should be self-explanatory.

f_Employee_Sub:
This form is forced into datasheet view because there are multiple parameters and since it's just a parameter name and the parameter score, a continuous form looked wrong (to me). The link between the Main Form and the Subform is the EmployeeID. However, we don't want to show the employeeID number down there, so the one line of code in the subform hides the column named "txtEmployeeID".

Note that in Datasheet View, setting a control's "Visible" property to false doesn't do anything. That's why you hide the column. I force the column to be hidden each time the form is opened in code, just in case someone unhides the column for some reason.

You should be able to take it from here. Realize that I've only provided one way to handle this situation. There are several ways to do most anything in a programming language. I tried to go with the easiest method I could think of, just for clarity's sake. However, you may want to look into LinkChildFields and LinkMasterFields for help in having a main form change a subform's contents. It's a little more complex, but it requires less maintenance in the long run.

~Moniker
 

Attachments

Thanks for the reply. Thats exactly what I want - but I tried to replicate it without success.

I copied your example on to my PC and worked with it a bit to try and diagnose the issue. I discovered that your query does not update itself when a new employee is added - therefore the sub form does not appear for a newly added employee.

Am I missing something?

NoVoice
 
Last edited:
You'll need to run a separate query to do that (included here). It's actually two queries, but one of them is a "data prep" query.

Make a new query called "q_AddEmployee_Prep", go to SQL View and copy/paste this in:

Code:
SELECT t_Employees.EmployeeID, t_Parameters.ParameterID
FROM t_Employees, t_Parameters
GROUP BY t_Employees.EmployeeID, t_Parameters.ParameterID;

You can view that in the design view if you want to see what it is. It's a simple aggregate select between employee and parameters. If you run it, you'll see it's just a list of the possible employee parameter combinations.

Now, add one more new query called q_AddEmployee_Append, go to SQL View and copy/paste this in:

Code:
INSERT INTO t_Employee_Scores ( EmployeeID, ParameterID )
SELECT q_AddEmployee_Prep.EmployeeID, q_AddEmployee_Prep.ParameterID
FROM q_AddEmployee_Prep LEFT JOIN t_Employee_Scores ON q_AddEmployee_Prep.EmployeeID = t_Employee_Scores.EmployeeID
WHERE (((t_Employee_Scores.EmployeeID) Is Null));

Again, you can view this in design mode if you like as well. Note that it uses a LEFT JOIN and not an INNER JOIN. The LEFT JOIN says, "Show me everything in q_NewEmployee_Prep and only records from t_Employee_Scores that match it." Therefore, if there's no match in t_Employee_Scores, then t_Employee_Scores it's going to return a NULL but q_NewEmployee_Prep will still return records. It will only have a match for those entries in t_Employee and not in t_Employee_Scores, which is exactly what we want. Using an INNER JOIN here wouldn't work because it only returns rows where both tables contain the entry.

When you run it, this query will copy new employees into t_Employee_Scores, along with a parameter value for each one. Go ahead and add a new employee to t_Employees and then run this query, and you'll see it work.

Now when you go back to the form, that new employee will have a match both in the combo box on the main form and the parameters subform.

Finally, note that you'll only ever have to run the seond query (q_NewEmployee_Append). This is because it's based on q_NewEmployee_Prep, the first query, and that first query will run everytime. Therefore, when you add new employees, they are always in the first query. So, from here, you could potentially make yourself an "Add New Employee" form with the Control Source set to t_Employees, allow the user to add all the new employee information, and then have a button that says "Add New Employee". That button would just run the "q_NewEmployee_Append" query.

~Moniker
 
Last edited:
Thanks Moniker

I have made good progress.

I have implemented all of this successfully - and several other similar instances around my database.

Right now, the append process relies on a manual run of the query - so I now need to automate it by building that button you suggested (on the employee form).

I know how to build a command button - but I don't know the code I need to make it run the query q_AddEmployee_Append. Or maybe its a macro? (whats the difference)

NoVoice
 
On the command button's click event, the command is:

DoCmd.OpenQuery [Your_Query_Name_Here]

If [Your_Query_Name_Here] is a string literal, put it in quotes, like:

DoCmd.OpenQuery "Your_Query_Name_Here"

Keep in mind that if you haven't turned off the warnings, the above will give you a "You are about to append X rows" etc. message. There are two ways around that:

DoCmd.SetWarnings False
DoCmd.OpenQuery [Your_Query_Name_Here]
DoCmd.SetWarnings True

--- OR ---

CurrentDb.Execute "INSERT INTO t_Employee_Scores ( EmployeeID, ParameterID ) SELECT q_AddEmployee_Prep.EmployeeID, q_AddEmployee_Prep.ParameterID FROM q_AddEmployee_Prep LEFT JOIN t_Employee_Scores ON q_AddEmployee_Prep.EmployeeID = t_Employee_Scores.EmployeeID WHERE (((t_Employee_Scores.EmployeeID) Is Null));"

CurrentDb.Execute automatically bypasses the warning messages, but you put the actual query text there (or the query text as a string). If you have huge SQL, you could make your code cleaner by doing this:

CurrentDb.Execute CurrentDb.QueryDefs("q_AddEmployee_Append").SQL

EDIT:
Err, I just noticed you asked the difference between a macro and code. A macro will run, in order, a predetermined set of built-in functions, queries, code, or any combination thereof. Once the macro starts, you don't have much control over it as you can't pass macros variables (directly) and well, they are sort of the "my little sister did this" version of programming. (Apologies to any little sisters reading this.)

Code is harder, but it's in your direct control all the time. Code (in this case, VBA) is much more powerful that running Macros because of the amount of control you have over it. However, Macros do have a few good features.

1) You can assign a macro to a shortcut key. (To do this, set the Macro Name to a shortcut key. This is the Macro Name field when editing a macro, name the name of the macro itself. To see the Macro Name field if it's missing, right-click the title bar of an open macro definition, and select "Macro Name" from the menu.) Therefore, if you're like me and you hate the "You are about to append" and "You are about to update" and "Are you sure you want to Delete?" messages, you can make two macros, one that turns all the warnings off and another that turns them back on, and then assign these to shortcut keys so that, for example, Ctrl+Alt+F could run a "SetWarnings False" and Ctrl+Alt+T could run a "SetWarnings True". Therefore, while you're doing design work, you can turn off all those messages, and before the program runs, you can make sure they get turned back on. (You could even turn them on programmically by putting "DoCmd.RunMacro <your_macro_name>" in the form_load event of your main form.)

2) Things like "AutoExec" (which gets executed the moment a DB is opened) are macros. If, for example, you are trying to hide toolbars and such so that end users don't accidentally click on something they shouldn't, placing an AutoExec macro with the right commands in it would do just that.

Macros are good for other things as well -- I have a few macros here and there that will run a series of 10+ "Refresh" queries for me instead of me having to click each one -- but for the most part, they are too limiting for "real" Access programming.

HTH
~Moniker
 
Last edited:

Users who are viewing this thread

Back
Top Bottom