Enabling controls when entering a new record

[Guest]

I have a form that uses cascading combo boxes to show data from three
different tables - Areas, Skills and Targets. Each skill is associated with a
single Area and each Target is associated with a single Skill. Hence when I
am entering data in the form I want to be able to first select an Area, to
bring up the subset of associated Skills and then enter the Target data.

I have used the Microsoft recommended method
http://support.microsoft.com/default.aspx/kb/209576/en-us to create the combo
boxes. This works OK (with a bit of tweaking) to enter my data but when
viewing existing data I want to be able to disable the combo boxes as it
doesn't make sense to change these values in current records. I'm sure there
should be an event that I can trap when a user creates a new record but I
can't find it. Do I need to do a test on the current record when the
Form_Current event is fired? If so what is the test?

 

[George Nicholson]

(Form_Current)
If Me.NewRecord Then
' (Make sure focus is not on MyCombo before trying to disable it)
MyCombo.Enabled = False
Else
MyCombo.Enabled = True
End If

However, I have a question about your desire to disable your combos: when
viewing existing data you *never* expect existing data to be be modified,
just viewed?

HTH,

 

[Guest]

Thanks for your reply! That makes sense.

I think you are correct thought that this is not necessarily the best
approach but TBH I can't think of another way to do this (I'm quite new to
Access).

Background is:

I have 3 tables defined as as follows
tblAreas
--------
AreaID (AutoNum, PK)
Area (Text)

tblSkills
--------
SkillID (AutoNum, PK)
AreaID (Number)
Skill (Text)

tblTargets
----------
TargetID (AutoNum, PK)
SkillID (Number)
TargetLevel (Number)
TargetText (Text)

I want a form where I am able to select an Area, which will list relevant
Skills and then be able to enter a TargetLevel and TargetText against this.
To do this I udes the cascading comboboxes technique I mentioned below. The
form recordset is based on the following query qrySkillsTargets:

SELECT tblAreas.Area, tblSkills.Skill, tblTargets.TargetID,
tblTargets.TargetLevel, tblTargets.TargetText, tblTargets.SkillID,
tblSkills.AreaID
FROM (tblAreas INNER JOIN tblSkills ON tblAreas.AreaID = tblSkills.AreaID)
INNER JOIN tblTargets ON tblSkills.SkilllID = tblTargets.SkillID
ORDER BY tblAreas.Area, tblSkills.Skill, tblTargets.TargetLevel;

I have four columns in the form
cboAreaSelect: ControlSource=None; RowSource=qryAreasList
cboSkillSelect: ControlSource=SkillID; RowSource=qrySkillsList
txtTargetLevel: ControlSource=TargetLevel
txtTargetText: ControlSource=TargetText

and have the following code defined:
Private Sub cboAreaSelect_BeforeUpdate(Cancel As Integer)
Me!cboSkillSelect.Requery
'Me!cboSkillSelect.SetFocus
End Sub
Private Sub cboSkillSelect_GotFocus()
Me!cboSkillSelect.Requery
End Sub
Private Sub Form_Current()
Me!cboAreaSelect = AreaID
Me!cboSkillSelect.Requery
End Sub


Note I have had to comment out the 'Me!cboSkillSelect.SetFocus statement in
the cboAreaSelect_BeforeUpdate sub because I kept getting runtime error
'2108' telling me to save the field before executing the SetFocus method.

Because of the structure of the data I wouldn't expect someone to change the
Area or Skill for a given TargetLevel/TargetText, hence the desire to disable
these.

I hope this makes sense. Any further guidance would be much appreciated -
surely this is the kind of thing people need to do all the time so I am
concerned I'm trying to reinvent the wheel (and my wheel has a few square
edges ;-)).

TM