Setting Label Caption

[Anand]

Hello,
I have a table which contains Parameter values for different components. The
components are stored in another table. Something like this:
Component #1: Param #1, Param #2
Component #2: Param #1, Param #3

I have two forms Form1 and Form2. Form1 takes user input in selecting the
Component. Form2 is an unbound form with a set of TextBox controls.

Here is what I want to do: Everytime the user selects a Component in Form1 I
want Access to open Form2 and fill the parameter values of that component in
the Caption of the Label controls. I also want to hide the TextBox controls
that were not used for that component.

I know how to call the data from the underlying tables from VBA. I want help
with setting the value of the Captions and hiding the unused TextBox controls.

Thanks in advance
Anand

 

[Ofer Cohen]

I'm not sure if that what you are looking for, but to change a caption of a
lable use:

Me.[LableName].Caption = SomeValue

============================
To hide text box on a certain condition

If Condition = True Then
Me.[TextBoxName].Visible = True
Else
Me.[TextBoxName].Visible = False
End If
=============================
Or. to make it shorter

Me.[TextBoxName].Visible = (Condition = True)

 

[Anand]

Hello Ofer, Thanks for your response.
Here is the code I have written. Likely, you will get a better idea of what
I am trying to achieve. This code is executed in the OnOpen event of Form2
mentioned in my first question.

Private Sub Form_Open()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim stSQL As String
Dim frm As Form
Dim ctl As Control
Set frm = Me

Me.CompoID = Forms!frmPOMaterialDetails.ItemType 'Gets the Component#
from Form1

stSQL = "SELECT tblCompo_Param.pkCompoParamID,
tblComponents.ComponentName, tblParameters.ParameterAbbrv, " _
& "tblParameters.ParameterName " _
& "FROM tblComponents INNER JOIN (tblParameters INNER JOIN
tblCompo_Param " _
& "ON tblParameters.pkParameterID =
tblCompo_Param.fkParameterID) " _
& "ON tblComponents.pkComponentID = tblCompo_Param.fkComponentID
" _
& "WHERE tblCompo_Param.fkComponentID = " & Me.CompoID
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(stSQL, dbOpenSnapshot)

With rst
.MoveFirst
Do Until .EOF
For Each ctl In frm.Controls
'Here enter the code that sets the label caption.
.MoveNext
Next ctl
Loop
End With

End Sub

I am trying to set the Caption sequentially by running through the Parameter
recordset and the Control object in Form2. Is there a better way of achieving
this. Also, am not sure how to hide the controls that were not used in the
above sub.

Thanks,
Anand

I'm not sure if that what you are looking for, but to change a caption of a
lable use:

Me.[LableName].Caption = SomeValue

============================
To hide text box on a certain condition

If Condition = True Then
Me.[TextBoxName].Visible = True
Else
Me.[TextBoxName].Visible = False
End If
=============================
Or. to make it shorter

Me.[TextBoxName].Visible = (Condition = True)


--
Good Luck
BS"D

Hello,
I have a table which contains Parameter values for different components. The
components are stored in another table. Something like this:
Component #1: Param #1, Param #2
Component #2: Param #1, Param #3

I have two forms Form1 and Form2. Form1 takes user input in selecting the
Component. Form2 is an unbound form with a set of TextBox controls.

Here is what I want to do: Everytime the user selects a Component in Form1 I
want Access to open Form2 and fill the parameter values of that component in
the Caption of the Label controls. I also want to hide the TextBox controls
that were not used for that component.

I know how to call the data from the underlying tables from VBA. I want help
with setting the value of the Captions and hiding the unused TextBox controls.

Thanks in advance
Anand

 

[aclelland]

Each control in a Form can be accessed in the same way you access an
array element eg.
me(1).visible = false
me(2).visible = true

What I think you would do is:

for i = 0 to me.count-1
if rs.eof <> true then
me(i).caption = rs(i)
rs.moveNext
else
me(i).visible = false
end if
next x

I dont have access on this PC or I would wirite the correct code .
The code above will loop through each control on the form and set the
value to what even the value of the current rescordset field is. It
will then hide the controls which are not needed. This will also not
cause an error if there are 10 values and only 5 textboxes so you
might want to do a check yourself.

I hope that helps,
Alan

 

[Anand]

Hello Alan,
Thanks for the response. I am not very good at arrays. I did not understand
how
Me(i).Visible works.

I tried using the logic you gave by toggling the .Visible property and the
code works fine. If I try to set the Caption property I get a 'Object does
not support Property or Method' error. I feel this could be because of my
ignorance about the properties of Controls on Forms.

When I say Me(1).Caption = "" is it referring to the TextBox Control or the
label attached to it?

Could you please help?
TIA
Anand

 

[aclelland]

Sorry I miss read your original question and I though you were setting
the values of labels. The code I gave you in the above post doesnt
check if the control is a label or a textbox so the 'me(i).controlType
= 109' will make data is put into textboxes. The other problem was
using .caption rather than .value

for i = 0 to me.count-1
if rs.eof <> true and me(i).controlType = 109 then
me(i).value= rs(i)
rs.moveNext
else
me(i).visible = false
end if
next x

 

[Anand]

Thanks Alan. You had not mis-read my original question. I did want to set the
label caption. I think somewhere in my 2nd and 3rd questions I must have
given the wrong impression of my requirement.

Any way the code written by you works like a charm. I did have to change the
text box constant to a Label constant.

Thanks again,
Anand