How to link a List created from a table to a lable or textbox 1

Triacona · Jun 21, 2011

Dear All,

Thanks for a great forum [smile]

!!

I have a form which has a list (ListCp).
ListCp gets its values from a table called
tblReportsAvailable. with the following fields.

Code:

Query Name
ctlComment
PageName
Action
SortOrder
Report Name
PrintEnable
PreviewEnable
ExportEnable
ExportTemplate
GenericEnable
GenericFormName
GenericCaption

ListCp uses Query Name as its values.
ctlComment is a field I want to use in a textbox/lable.
I have achieved it with a combobox and list.
But these are not ideal especially the list, and the combo box has a drop down.
Is there a way to use the textbox/Label in VB?

Any help would be greatly appreciated.
Thank you! [bigsmile]

Thank you,

Kind regards

Triacona

kjv1611 · Jun 21, 2011

So, you want to update what shows in the label according to what is selected in the listbox?

If so, it'd be something like:

Code:

Private Sub MyListBox_AfterUpdate
   MyLable.Caption = MyListBox.Selected
End Sub

I've not tested it, but that's the general idea. I may not be using the correct properties.

Give it a test and see if that covers it.

TheAceMan1 · Jun 21, 2011

How are ya Triacona . . .

Triacona said:
[blue]ListCp uses Query Name as its values.[/blue]

Be more specific about this ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue] [thumbsup2]

Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]

Triacona · Jun 22, 2011

Hi All,

Thanks for the replies, really appreciate them! [smile]

@The TheAceman
I have a table tblReportsAvailable.
It has the values as stated.
I use this table to populate ListCp.
I have limited ListCp to 1 column QueryName.
So based on the selection in ListCp I want to populate the lable/textbox with the ctlComment from tblReportsAvailable.
I hope that makes things clearer.
Thanks for all your help [bigsmile]

@KJV
I have tried your code substituting Commentlb.Caption = ListCp.Selected
It doesn't seem to work.
How does it display ctlComment from tblReportsAvailable based on ListCp's QueryName field which comes from tblReportsAvailable?

Thanks for your help! [bigsmile]

Thank you,

Kind regards

Triacona

MajP · Jun 22, 2011

row Source: "Select [Query Name], ctlComment from tblReportsAvailable"
column count: 2
column widths: 1";0"

that shows only query name

Private Sub listCP_AfterUpdate
Commentlb.Caption = ListCp.column(1)
End Sub

the columns index are 0 based so 1 is your second column (ctlComment), which is not visible in the listbox

If your not saving the query name and it is not bound then you can simplify this to

boundColumn: 2

Private Sub listCP_AfterUpdate
Commentlb.Caption = ListCp
End Sub

Triacona · Jun 22, 2011

Dear All,

So in the statement

Code:

Private Sub ListCP_AfterUpdate()

Commentlb.Caption = DLookup("[ctlComment]", "tblReportsAvailable", [ListCP])

End Sub

It only pulls the first ctlComment from the table, it is not linked to the list.
I have tried to use criteria in the DLookUp but that does not seem to work??

Any thoughts or help would be greatly appreciated.
Thank you!! [bigsmile]

Thank you,

Kind regards

Triacona

Triacona · Jun 22, 2011

Dear Majp,

Well done! Great logic!
I have tested it and it works with a few tweaks! [2thumbsup]

Code:

Private Sub ListCP_AfterUpdate()

Commentlb.Caption = ListCP.Column(1)

End Sub

And in the properties box.
Row Source: SELECT tblReportsAvailable.QueryName, tblReportsAvailable.ctlComment FROM tblReportsAvailable;
Bound Column: 1
Column Count: 2
Column Widths: 2.544cm;0cm
Thank you again!!!
Brilliant!!

Thank you,

Kind regards

Triacona

Triacona · Jun 22, 2011

MajP have a star!!

Thank you,

Kind regards

Triacona

Triacona · Jun 22, 2011

Dear All,

I have now created a subform - SubFrmInput for the input functions.

I have the following case statement:

Code:

Private Sub ListCP_AfterUpdate()

Select Case ListCP.ItemData(ListCP.ListIndex)

Case "CpSotLetter", "CpSotFsaXml", "SotView", "MissingRecordsCheckFsaXmlAndCpListResult", "CpAuditorInfo"
'Nothing enabled
    'enabled state T/F
    Me.SubFrmInput.txtStartDate.Enabled = False
    
end select
end sub

The field name txtStartDate is in the Subform - SubFrmInput.
But it comes up with an error message.

Compile Error said:
Method or data member not found

What am I doing wrong in referencing these fields in the subform.
Oh yes the case statement is for a list - ListCP in the form MainScreen

Thank you! [smile]

Thank you,

Kind regards

Triacona

MajP · Jun 22, 2011

me.nameOfsubFormControl.Form.ControlOnSubForm

Me.subFrmInput.Form.txtStartDate.enabled

Triacona · Jun 22, 2011

Dear MajP,

Thanks so much for your help! [smile]

It works well!
Example below:

Code:

Private Sub ListCP_AfterUpdate()
'*COMMERCIAL PREMISES CP LIST

Commentlb.Caption = ListCP.Column(1)

Select Case ListCP.ItemData(ListCP.ListIndex)

Case "CpInspectionsDue", "CPInspectionsMissed"
'Start and end date enabled
   'enabled state T/F
    Me.SubFrmInput.Form.txtStartDate.Enabled = True
    Me.SubFrmInput.Form.txtEndDate.Enabled = True
    Me.SubFrmInput.Form.cmdCalDate.Enabled = True
    Me.SubFrmInput.Form.cmdCalDateEnd.Enabled = True
    Me.SubFrmInput.Form.CmbOff.Enabled = False
    Me.SubFrmInput.Form.CmbStat.Enabled = False
    Me.SubFrmInput.Form.CmbRpTyp.Enabled = False
    Me.SubFrmInput.Form.cmbSrTyp.Enabled = False
    Me.SubFrmInput.Form.cmbCpXresult.Enabled = False 'Use
    Me.SubFrmInput.Form.cmbCpTradeAs.Enabled = False
    Me.SubFrmInput.Form.ListCpXresult.Enabled = False
    Me.SubFrmInput.Form.CpListInspLiab.Enabled = False


 Case Else
'Nothing enabled
    'enabled state T/F
    
    Me.SubFrmInput.Form.txtStartDate.Enabled = False
    Me.SubFrmInput.Form.txtEndDate.Enabled = False
    Me.SubFrmInput.Form.cmdCalDate.Enabled = False
    Me.SubFrmInput.Form.cmdCalDateEnd.Enabled = False
    Me.SubFrmInput.Form.CmbOff.Enabled = False
    Me.SubFrmInput.Form.CmbStat.Enabled = False
    Me.SubFrmInput.Form.CmbRpTyp.Enabled = False
    Me.SubFrmInput.Form.cmbSrTyp.Enabled = False
    Me.SubFrmInput.Form.cmbCpXresult.Enabled = False 'Combo Use
    Me.SubFrmInput.Form.cmbCpTradeAs.Enabled = False
    Me.SubFrmInput.Form.ListCpXresult.Enabled = False
    Me.SubFrmInput.Form.CpListInspLiab.Enabled = False

End Select

End Sub

Thanks again for all your help! [2thumbsup]

Thank you,

Kind regards

Triacona

Triacona · Jun 22, 2011

Dear MajP,

There is a little snag..
It seems when selecting between different items on the list and error is given.

Run-time error '2164': said:
You can't disable a control while it has the focus.

The controls still enable or disable after the above error, but only after I click on End.

What is causing this?
In my Subform SubFrmInput I have disbled all controls/textboxes...

What am I doing wrong?

Thanks again so very much for your help [bigsmile]

Thank you,

Kind regards

Triacona

Triacona · Jun 22, 2011

Dear MajP,

I might have fixed it...
I had a Case Else statement in the ListCP Case Statement, I have now removed it, and that seems to make it work.

Thanks! [smile]

Thank you,

Kind regards

Triacona

MajP · Jun 22, 2011

if disabling or hiding a control you must first set the focus away from it.

Triacona · Jun 22, 2011

Dear MajP,

You're right, it still gives me the problem when the textbox has a value in it...
How do I set the focus away from it?
Thanks for all your brilliant help! [2thumbsup]

Thank you,

Kind regards

Triacona

MajP · Jun 22, 2011

me.someothercontrol.setfocus

Triacona · Jun 22, 2011

Dear MajP,

Thanks for such a quick response! [smile]

Um I used the following and it give me an error.

Me.SubFrmInput.Form.cmdCalDate.SetFocus = False

Run-time error said:
Object doesn't support this property or method.

Thanks again for all your help! [2thumbsup]

Thank you,

Kind regards

Triacona

MajP · Jun 22, 2011

it is not true/false. The method is "setFocus" which sets the focus to another control.
Me.SubFrmInput.Form.SomeOtherControlBesidescmdCalDate.SetFocus

Triacona · Jun 22, 2011

Dear MajP,

Thanks again for a speedy response!
Erm I don't want any controls enabled on this List choice...I have tried setting it to other controls, but it won't let me, I think because all of the controls with this selection are disabled, and they all start out disabled..

Code:

Private Sub ListCP_AfterUpdate()
'*COMMERCIAL PREMISES CP LIST


Commentlb.Caption = ListCP.Column(1)





Select Case ListCP.ItemData(ListCP.ListIndex)

Case "CpSotLetter", "CpSotFsaXml", "SotView", "MissingRecordsCheckFsaXmlAndCpListResult", "CpAuditorInfo"
'Nothing enabled
    'enabled state T/F
    
    Me.SubFrmInput.Form.txtStartDate.Enabled = False
    Me.SubFrmInput.Form.txtEndDate.Enabled = False
    Me.SubFrmInput.Form.txtStartDate.SetFocus
    Me.SubFrmInput.Form.cmdCalDate.Enabled = False
    
    Me.SubFrmInput.Form.cmdCalDateEnd.Enabled = False
    Me.SubFrmInput.Form.CmbOff.Enabled = False
    Me.SubFrmInput.Form.CmbStat.Enabled = False
    Me.SubFrmInput.Form.CmbRpTyp.Enabled = False
    Me.SubFrmInput.Form.cmbSrTyp.Enabled = False
    Me.SubFrmInput.Form.cmbCpXresult.Enabled = False 'Combo Use
    Me.SubFrmInput.Form.cmbCpTradeAs.Enabled = False
    Me.SubFrmInput.Form.ListCpXresult.Enabled = False
    Me.SubFrmInput.Form.CpListInspLiab.Enabled = False
    
    
Case "CpSotCertificate"
'cmbCpTradeAs enabled
    'enabled state T/F
    Me.SubFrmInput.Form.txtStartDate.Enabled = False
    Me.SubFrmInput.Form.txtEndDate.Enabled = False
    Me.SubFrmInput.Form.cmdCalDate.Enabled = False
    Me.SubFrmInput.Form.cmdCalDateEnd.Enabled = False
    Me.SubFrmInput.Form.CmbOff.Enabled = False
    Me.SubFrmInput.Form.CmbStat.Enabled = False
    Me.SubFrmInput.Form.CmbRpTyp.Enabled = False
    Me.SubFrmInput.Form.cmbSrTyp.Enabled = False
    Me.SubFrmInput.Form.cmbCpXresult.Enabled = False 'Combo Use
    Me.SubFrmInput.Form.cmbCpTradeAs.Enabled = True
    Me.SubFrmInput.Form.ListCpXresult.Enabled = False
    Me.SubFrmInput.Form.CpListInspLiab.Enabled = False
    
    
Case "CpCmbResults", "CpFoodPremTypeSearch"
'CmbCpXresult only
    'enabled state T/F
    Me.SubFrmInput.Form.txtStartDate.Enabled = False
    Me.SubFrmInput.Form.txtEndDate.Enabled = False
    Me.SubFrmInput.Form.cmdCalDate.Enabled = False
    Me.SubFrmInput.Form.cmdCalDateEnd.Enabled = False
    Me.SubFrmInput.Form.CmbOff.Enabled = False
    Me.SubFrmInput.Form.CmbStat.Enabled = False
    Me.SubFrmInput.Form.CmbRpTyp.Enabled = False
    Me.SubFrmInput.Form.cmbSrTyp.Enabled = False
    Me.SubFrmInput.Form.cmbCpXresult.Enabled = True 'Combo Use
    Me.SubFrmInput.Form.cmbCpTradeAs.Enabled = False
    Me.SubFrmInput.Form.ListCpXresult.Enabled = False
    Me.SubFrmInput.Form.CpListInspLiab.Enabled = False
    
Case "CpListResults"
'ListCpXresult only
    'enabled state T/F
    Me.SubFrmInput.Form.txtStartDate.Enabled = False
    Me.SubFrmInput.Form.txtEndDate.Enabled = False
    Me.SubFrmInput.Form.cmdCalDate.Enabled = False
    Me.SubFrmInput.Form.cmdCalDateEnd.Enabled = False
    Me.SubFrmInput.Form.CmbOff.Enabled = False
    Me.SubFrmInput.Form.CmbStat.Enabled = False
    Me.SubFrmInput.Form.CmbRpTyp.Enabled = False
    Me.SubFrmInput.Form.cmbSrTyp.Enabled = False
    Me.SubFrmInput.Form.cmbCpXresult.Enabled = False 'Use
    Me.SubFrmInput.Form.cmbCpTradeAs.Enabled = False
    Me.SubFrmInput.Form.ListCpXresult.Enabled = True
    Me.SubFrmInput.Form.CpListInspLiab.Enabled = False
    
Case "CpListResultsInsLiab"
'CpListInspLiab only
    'enabled state T/F
    Me.SubFrmInput.Form.txtStartDate.Enabled = False
    Me.SubFrmInput.Form.txtEndDate.Enabled = False
    Me.SubFrmInput.Form.cmdCalDate.Enabled = False
    Me.SubFrmInput.Form.cmdCalDateEnd.Enabled = False
    Me.SubFrmInput.Form.CmbOff.Enabled = False
    Me.SubFrmInput.Form.CmbStat.Enabled = False
    Me.SubFrmInput.Form.CmbRpTyp.Enabled = False
    Me.SubFrmInput.Form.cmbSrTyp.Enabled = False
    Me.SubFrmInput.Form.cmbCpXresult.Enabled = False 'Use
    Me.SubFrmInput.Form.cmbCpTradeAs.Enabled = False
    Me.SubFrmInput.Form.ListCpXresult.Enabled = False
    Me.SubFrmInput.Form.CpListInspLiab.Enabled = True

Case "CpInspectionsDue", "CPInspectionsMissed"
'Start and end date enabled
   'enabled state T/F
    Me.SubFrmInput.Form.txtStartDate.Enabled = True
    Me.SubFrmInput.Form.txtEndDate.Enabled = True
    Me.SubFrmInput.Form.cmdCalDate.Enabled = True
    Me.SubFrmInput.Form.cmdCalDateEnd.Enabled = True
    Me.SubFrmInput.Form.CmbOff.Enabled = False
    Me.SubFrmInput.Form.CmbStat.Enabled = False
    Me.SubFrmInput.Form.CmbRpTyp.Enabled = False
    Me.SubFrmInput.Form.cmbSrTyp.Enabled = False
    Me.SubFrmInput.Form.cmbCpXresult.Enabled = False 'Use
    Me.SubFrmInput.Form.cmbCpTradeAs.Enabled = False
    Me.SubFrmInput.Form.ListCpXresult.Enabled = False
    Me.SubFrmInput.Form.CpListInspLiab.Enabled = False
    
 
    
End Select

End Sub

Any further help would be greatly appreciated [smile]

!

Thank you,

Kind regards

Triacona

MajP · Jun 22, 2011

If you do not have a current control to use, could you put an unbound text box on the sebform and keep it enabled? Size it down to almost 0 by 0 (might be actually able to size it to 0 by 0). Make the backcolor the same as the detail section, give it no border. Basically it would look invisible (I think that is an oxymoron).

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

How to link a List created from a table to a lable or textbox 1

Technical User

New member

Programmer

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor