After getting help yesterday, that led me to simplify my database (this worked fabulously for the particular set of data) I decided to try and simplify the rest of my db down to the minimum number of tables
I had 3 separate tables listing
These are all similar in that they have the following fields:
Name of Skill/Professional Expertise/Language
I decided to make this one table of skills so that there is one combo box linked to this table that fills the StaffSkills table which is:
Name of Skill/Professional Expertise/Language
Type of Skill (Skill Central to Job, Professional Expertise, Language)
SkillID (This is where the combo box for Name of Skill comes in).
Level of Skill (1-5)
The StaffSkills table was also 3 separate tables to record each staff members specific type of skills.
I have put a query on the Combo box so that the Skill table shows the correct type of skill e.g. Languages for the Language form.
I have also set the On Open event of the forms (I have a separate one each for the three types of Skill) to go to a new record. If I don't do this the records for the previous form for that person are easily overwritten as they don't display the skill name. Doing this leads to an error that I can't go to the specified record, although the form will open and allow entries after this. Although if the form is re-opened it shows the recent entries, not a new record.
Am I doing something that is overly complicated? In my mind I was using the separate tables because that is how the different skills are thought about at this organisation, but I realise there are only naming differences hence merging into one. I was also trying to simplify the SQL that is needed to query the database without making it complicated for other staff members to fill in. It is for this reason that I am trying all this because the list of Skills is quite long and annoying if you only want to find 1 language out of a group of only 8 but the skill list is a couple of hundred items. Also it will mean clicking through and entering ALL the different skills rather than neatly dealing with say Languages as one task. I can see the StaffSkills Table working fine but it is the structure of my form that is causing me trouble.
Okay, my problems yesterday where mainly due to not realising the "many to many" relationship between Skill Types. So I had to make a separate table of Skill Types and record the Skill Type ID with the Skill and with the Staff Skills.(I also had my combo box set up wrong!)
The skilltypeid number needs to be recorded with the skill BUT each form is for a type of skill so I wanted to put a field for skilltypeid with the default value as the value for that form. E.g. the Language form has default of 3 because that is the Skilltype for Languages.
This value overrides the other default values of 1 and 2 in the other 2 forms.
These forms are all based on the same table but they are separate forms and I don't know why the default value for this field cannot be different on different forms?
I don't know why the default value for this field cannot be different on different forms?
It should. I have used different default values on the same form for different users, so there is no reason why the Default Value property for an object can't be different for different forms. Is there a Default Value at the table level as well? If there is, try removing it. It shouldn't make a difference though... as I believe the default value for a form overrules a default value for a table... but I could be wrong on that.
Hmm, I thought it might have been one of those Friday problems that would have sorted itself by today but no such luck. There isn't a default value on the table so I'm still no clearer.
Going about my problem another way; I can achieve the result I want but it involves running a filter (from the Advanced Filter option) each time the form is opened and I want this to be user friendly so I'd rather the filter would run on opening. Let me describe.
The first form displays a persons name and contact details. A command button opens for example the Languages form. This has a default value of the staffmemberid from the first form. This means that the Filter option on the form is [staffidskill]=5(for example). I can then go to Records-Advanced Filter and select skilltypeid=3
(((tblStaffSkills.staffidskill)=5) AND ((tblStaffSkills.skilltypeid)=3))
This filter displays what I want: the Language (skilltypeid=3) for the person that the form is open for.
I saved the Filter as a Macro and set it to run when the form is opened but I can only set it for a particular person's id number. It also overrides the original form Filter.
Is there some SQL I should use to run the filter for whichever staff member the form is on?
For a particular form the staffidskill will change but the skilltypeid will always be = 3.