Populating labels with code

J

John Contact

Hi again,

Hoping you can assist me once again!

I have a form with a number of option groups and there
asscoiated lables. Depending on an option chosen from a
combo box the control source for the option group and text
to be displayed in the label is chosen from a multitude of
different options.

At the moment I have written truck loads of code for each
option i.e

If [Call_Type] = 1 Then

[Frame1].ControlSource = "Transactional_Skill_1"

[Label1].Caption = "Full payment of debt requested _
andoptions offered"

[Frame2].ControlSource = "Transactional_Skill_2"

etc. etc

ElseIf [Call_Type] = 2 Then

[Frame1].ControlSource = "Transactional_Skill_1"

[Label1].Caption = "Tenancy Agreement obtained if
Necessary"

etc. etc.

I am hoping there is way to shorten this whole damn thing
and put the label statements in a table and have the code
reference them. I had a look at the switchboard set up and
tried to adapte but seemed to have missed the boat.

Any assistance greatly appreciated.

Cheers


John
 
M

Marshall Barton

John said:
I have a form with a number of option groups and there
asscoiated lables. Depending on an option chosen from a
combo box the control source for the option group and text
to be displayed in the label is chosen from a multitude of
different options.

At the moment I have written truck loads of code for each
option i.e

If [Call_Type] = 1 Then

[Frame1].ControlSource = "Transactional_Skill_1"

[Label1].Caption = "Full payment of debt requested _
andoptions offered"

[Frame2].ControlSource = "Transactional_Skill_2"

etc. etc

ElseIf [Call_Type] = 2 Then

[Frame1].ControlSource = "Transactional_Skill_1"

[Label1].Caption = "Tenancy Agreement obtained if
Necessary"

etc. etc.

I am hoping there is way to shorten this whole damn thing
and put the label statements in a table and have the code
reference them.
Click to expand...


Definitely use a table. I think you'll need columns for the
call type, frame/label number, frame control source and
label caption. For example, let's say the table is named
CallOptions:

CallType Integer
FrameNum Integer
FrameSor Text
LabelCap Text

with sample data like:
1 1 Transactional_Skill_1 Full payment of debt requested ...
1 2 Transactional_Skill_2 something
1 3 whatever something else
2 1 Transactional_Skill_1 Tenancy Agreement obtained ...
.. . .

You can then open a recordset to retrieve the frame number,
control source and label caption for the specified call
type. The records in the recordset can then be used to
manipulate the control properties using something like this
air code:

Dim db As Database
Dim rs As Recordset
Dim strSQL As String

strSQL = "SELECT * FROM Calloptions " _
& "WHERE CallType = " & [Call_Type]
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
Do Until rs.EOF
Me("Frame" & rs!FrameNum).ControlSource =rs!FrameSor
Me("Label" & rs!FrameNum).Caption = rs!LabelCap
rs.MoveNext
Loop

rs.Close : Set rs = Nothing
Set db = Nothing

You may also want to create a form to make it easy for a db
admin person to edit the control source and caption fields.
 
Top