Re: Populating labels with code

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Marshall Barton (marshbarton_at_wowway.com)
Date: 08/27/04 

Date: Thu, 26 Aug 2004 21:51:25 -0500

John Contact wrote:
>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.

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. 

-- 
Marsh
MVP [MS Access]

Relevant Pages

  • Re: ADO Form bind
    ... setting the control source to County makes the text not updateable that I ... I don't believe you can bind an updatable form to an ADO recordset using the Jet provider. ...
    (microsoft.public.access.formscoding)
  • Re: Form not creating a recordset
    ... A form doesn't have a control source, ... Me.RecordSource is empty, it's an unbound form and doesn't have a ... method it is not creating a recordset set, ...
    (microsoft.public.access.formscoding)
  • Re: Option Groups Linked?
    ... Hi, Jeanette - Thank you very much for your reply, but I'm still up a tree. ... If I leave the Option Groups unbound, how do I record the choices? ... That means that they don't have any control source. ...
    (microsoft.public.access.forms)
  • RE: Control Source in a textbox
    ... you use the Default Value property rather than the Control Source. ... value will then populate the field in the form's recordset. ... It is probably not necessary to use query in the DLookup. ... source of a textbox to pull the user location based on the user login ...
    (microsoft.public.access.forms)
  • RE: Option Group Produce Script in Text Box
    ... Where can I find if it is bound or unbound? ... what is the control source for Text131? ... >>> Do you have tblScript open as a recordset? ... >>> Does it have a field named Msg? ...
    (microsoft.public.access.formscoding)