Combo box as control source

[Guest]

I am using a combo box on a form as the control source for a text box on my
report. This works when I put the name of the combo in the report's control
source, but I am trying to do it programmatically in the report open event
because I have several of these to manage.

This is what I have been trying thus far:
Me.txtItem01.ControlSource = Forms!frmItemTester5!cboItem01.Column(1)

Can someone help?

 

[Marshall Barton]

I am using a combo box on a form as the control source for a text box on my
report. This works when I put the name of the combo in the report's control
source, but I am trying to do it programmatically in the report open event
because I have several of these to manage.

This is what I have been trying thus far:
Me.txtItem01.ControlSource = Forms!frmItemTester5!cboItem01.Column(1)

Does the combo box have the name of a field in the report's
record source table/query?

If it does, then your code should work.

If it just has a value that you want to display, then you
must start with an = sign:

For a numeric value:
Me.txtItem01.ControlSource = "=" _
& Forms!frmItemTester5!cboItem01.Column(1)

For a text value:
Me.txtItem01.ControlSource = "=""" _
& Forms!frmItemTester5!cboItem01.Column(1) * """"

For a date value:
Me.txtItem01.ControlSource = "=" _
& Format(Forms!frmItemTester5!cboItem01.Column(1),
"\#,\/d\/yyyy\#")

OTOH, if it's just a value, why mess with the record source
property? Just copy the value in the report header's Format
event:
Me.txtItem01 = Forms!frmItemTester5!cboItem01.Column(1)

 

[Guest]

Marsh- thanks for the quick reply. I've been trying your suggestions, but...

Does the combo box have the name of a field in the report's
record source table/query?

If it does, then your code should work.

Right now, I'm using the combo boxes from my form as the record source, but
I may want to have an option to use a query in the near future.

If it just has a value that you want to display, then you
must start with an = sign:

For a numeric value:
Me.txtItem01.ControlSource = "=" _
& Forms!frmItemTester5!cboItem01.Column(1)

I tried this and get a syntax error, but not sure why.

OTOH, if it's just a value, why mess with the record source
property? Just copy the value in the report header's Format
event:
Me.txtItem01 = Forms!frmItemTester5!cboItem01.Column(1)

This worked, but I couldn't incorporate the following loop.

Dim intCounter As Integer
Private Const strSQL_1 = "Forms!frmItemTester5!cboItem0"
Private strSQL_A As String

For intCounter = 1 To 5
strSQL_A = (strSQL_1 & intCounter & ".Column(1)")
Me("txtItem0" & intCounter) = strSQL_A
Next intCounter

Thanks,
darin

 

[Marshall Barton]

I still have no idea what you are trying to do with all
this. All I can say is that your loop would have to be:

Dim intCounter As Integer
For intCounter = 1 To 5
Me("txtItem0" & intCounter) = _
Forms!frmItemTester5("cboItem0" & intCounter).Column(1)
Next intCounter

 

[Guest]

Thanks- it worked. BTW, from a bank of test questions, instructors select
specific questions through combo boxes and, ultimately, make evaluations
through this report that prints on those pesky 'fill in the bubble' sheets.

Is there a brief explanation of when/why to use the header Format event
instead of the detail record source?

I still have no idea what you are trying to do with all
this. All I can say is that your loop would have to be:

Dim intCounter As Integer
For intCounter = 1 To 5
Me("txtItem0" & intCounter) = _
Forms!frmItemTester5("cboItem0" & intCounter).Column(1)
Next intCounter
--
Marsh
MVP [MS Access]

Marsh- thanks for the quick reply. I've been trying your suggestions, but...


Right now, I'm using the combo boxes from my form as the record source, but
I may want to have an option to use a query in the near future.


I tried this and get a syntax error, but not sure why.

This worked, but I couldn't incorporate the following loop.

Dim intCounter As Integer
Private Const strSQL_1 = "Forms!frmItemTester5!cboItem0"
Private strSQL_A As String

For intCounter = 1 To 5
strSQL_A = (strSQL_1 & intCounter & ".Column(1)")
Me("txtItem0" & intCounter) = strSQL_A
Next intCounter

Thanks,
darin

 

[Marshall Barton]

I think you meant to say Control Source, not record source.

Generally the control source is used to specify the name of
a field in the record source table/query (the control is
then called a "bound control"). Alternatively, the control
source can contain an expression (preceeded by an = sign)
that refers to one or more fields and/or other controls. To
just set a value from code, you should use the Value
property (as we ebded up doing). The complicating factor
here is that the Value property has not yet been established
at the time of the Open event, so you have to wait until a
Format event. On the other hand, unlike forms, report
control's ControlSource can not be set after the Open event,
so there may be a tradeoff in some unusual dituations.

 

[Guest]

Marsh- thanks for taking the time for the explanation. The why is as valuable
as the how for my learning process.

I think you meant to say Control Source, not record source.

Generally the control source is used to specify the name of
a field in the record source table/query (the control is
then called a "bound control"). Alternatively, the control
source can contain an expression (preceeded by an = sign)
that refers to one or more fields and/or other controls. To
just set a value from code, you should use the Value
property (as we ebded up doing). The complicating factor
here is that the Value property has not yet been established
at the time of the Open event, so you have to wait until a
Format event. On the other hand, unlike forms, report
control's ControlSource can not be set after the Open event,
so there may be a tradeoff in some unusual dituations.
--
Marsh
MVP [MS Access]

Thanks- it worked. BTW, from a bank of test questions, instructors select
specific questions through combo boxes and, ultimately, make evaluations
through this report that prints on those pesky 'fill in the bubble' sheets.

Is there a brief explanation of when/why to use the header Format event
instead of the detail record source?