Inteligent combos

[RainLover]

I had a bit of a look around at your DB.

One thing I noticed is that your Code will not compile.

You need to fix this ASAP, Now would be better. The problem is that you code will fail now as you are working on it.

Some error trapping would not go astray either.

You have a table to store Dates. Is this really necessary.

Watch out for your reserved words. Time, Date. Now. are examples of names you should not use as names for any object. You could do a Google on this.

ID is a terrible name for a field. Even if Microsoft use it. Use something similar to the name of the Table.

eg tblCustomer would give you CustomerPK

Hope these little pointers help you out.

 

[guinness]

Thanks rainlover

The db wouldn't compile as it was missing an End Sub. Sorted now.

Again part of the problem is your seeing part of a database not all. The table of dates is actually a table of working days between now and December 2020. One of the things the full database does is a variety of calculations based on working days between now and the end of the year or working days between April 1st and March 31st. It also compares the days scheduled against the dates available.

What I keep coming back to however is the problem at hand. How do amend the sql in the row source of Trainer name to lookup Trainer_Name on the Trainers_Projects table if Project_Title is filled in or look up Trainer_Name from the employee table if Project_Title is blank. I realise there are a lot of other things I could do but this is the thing I really want to do.

I do have the option to create a Training_Type of blank and a Project_Title of blank and align the trainers to that but it isn't my preferred solution.

Thanks for taking a look

Guinness

 

[jdraw]

guinness,

Did you see the query I posted in post #14? You would need a query along that format. You could pick the Projectname from a field on your form (combo), then execute the query after update of the combo, or via a button click. However, I don't see how the Resourcing form --as it stands -- would fit.

You probably don't want to hear this, but I'm going to suggest you stop with the forms and subforms, and go back to basic design of tables and relationships.

Here's the Basic Approach as per Barry Williams (DatabaseAnswers.org)

In this General Approach we define the Steps in a structured method to design a Database, and there is another Approach at the bottom of the page.

You can have a look at this Page to see how this Approach applies to the design of a Database for an HR Department.
The Approach defined here is aimed at beginners and experienced practitioners.
It makes some recommendations to simplify basic design decisions on key structures.

These are the Steps in a Top-Down Approach :-

Define the Scope as the Area of Interest,(e.g. the HR Department in an organization).
Define the "Things of Interest",(e.g. Employees), in the Area of Interest.
Analyze the Things of Interest and identify the corresponding Tables.
Consider cases of 'Inheritance', where there are general Entities and Specific Entities.
For example, a Customer is a General Entity, and Commercial Customer and Personal Customer would be Specific Entities. If you are just starting out, I suggest that you postpone this level of analysis.
At this point, you can produce a List of Things of Interest.
Establish the relationships between the Tables.
For example, "A Customer can place many Orders", and "A Product can be purchased many times and appear in many Orders."
Determine the characteristics of each Table,(e.g. an Employee has a Date-of-Birth).
Identify the Static and Reference Data, such as Country Codes or Customer Types.
Obtain a small set of Sample Data,
e.g. "John Doe is a Maintenance Engineer and was born on 1st. August, 1965 and lives at 22 Woodland Street, New Haven.
"He is currently assigned to maintenance of the Air-Conditioning and becomes available in 4 weeks time"
Review Code or Type Data which is (more or less) constant, which can be classified as Reference Data.
For example, Currency or Country Codes. Where possible, use standard values, such as ISO Codes.
Look for 'has a' relationships. These can become Foreign Keys, or 'Parent-Child' relationships.
You need to define a Primary Key for all Tables.
For Reference Tables, use the'Code' as the Key, often with only one other field, which is the Description field.
I recommend that names of Reference Data Tables all start with 'REF_'.
For all non-Reference Data Tables, I suggest that you simply assign an Auto-increment Integer for each Primary Key.
This has some benefits, for example, it provides flexibility, and it's really the only choice for a Database supporting a Web Site.
However, it complicates life for developers, which have to use the natural key to join on, as well as the 'surrogate' key.
It also makes it possible to postpone a thorough analysis of what the actual Primary Key should be. Which means, of course, that it often never gets done.
Confirm the first draft of the Database design against the Sample Data.
Review the Business Rules with Users,(if you can find any Users).
Obtain from the Users some representative enquiries for the Database,
e.g. "How many Maintenance Engineers do we have on staff coming available in the next 4 weeks ?"
Review the Results of Steps 1) to 9) with appropriate people, such as Users, Managers,
Development staff, etc. and repeat until the final Database design is reached.
Define User Scenarios and step through them with some sample data to check that that Database supports the required functionality.

It may seem like a step back, but getting your tables set up to match your business is key.

 

[RainLover]

I just watched the U Tube Tut on Candidate Keys.

This is from JD's link to another link etc.

I cannot support it. It is way too complicated of a system to train a new person. It would get them into more trouble than it is worth.

He even talks about linking one Primary to another Primary in order to maintain a relationship between two Databases.

JD, I am sorry if I made a mess for you.

 

[guinness]

Thanks All

I got round my problem without putting any Iif commands into the rowsource that would change the record source.

I'v simply created a project title using a full stop as the title and aligned the full team against it. So if the user enters a full stop in project title they have the option the schedule the full team.

Not good design I know but then I'm not known for good design

I just wanted to thank you guys for helping me so far and guarantee that more questions will inevitably follow.

Cheers