Type mismatch in report

[Kinger43]

What is wrong with this code

Private Sub Report_Open(Cancel As Integer)
On Error GoTo err_Report_Open
    
    If Form_Turtle_Meter_Form!Multiplier = "F3" Then
        Me!Text8.ControlSource = F3
    ElseIf Form_Turtle_Meter_Form!Multiplier = "F4" Then
        Me!Text8.ControlSource = F4
    End If
    
err_Report_Open:
    MsgBox Err.Description
    Exit Sub
    
End Sub

Whenever I open the report it gives me a Type mismatch error. Fields F3 and F4 are both plain text fields.

 

[pbaldy]

How about

Me!Text8.Value = "F3"

 

[Kinger43]

It says you cannot assign a value to this object

Anyway, I don't want the box to say "F3" or "F4". These are two fields in the table that the report is based on. I want to be able to specify which field goes into this spot in the detail section of the report. I've tried using the On Format event of the detail section as well with the same results as I was getting previously.

 

[pbaldy]

Ah; I don't think you can change the controlsource from record to record. I'd use a control source like:

=IIf(Multiplier="F3",[F3],[F4])

 

[Kinger43]

That would be fine if those were the only two fields to choose from. I am must testing code using the two fields. The final application will have about 15 fields or so. The control source wouldn't change from record to record anyhow. It would merely change from report instance to report instance.

 

[KeithG]

You can use the Switch function then

 

[pbaldy]

If it doesn't change from record to record, you can set the control source in the open event of the report.

 

[Kinger43]

Explain how it would change from record to record. Form_Turtle_Meter_Form!Multiplier is a control box on a form, unless this changes the control source won't change. For every record in the report, this box should have the field F3 for Text8 if thats the field that was chosen or F4 if that's what was chosen. It won't switch from record to record.

 

[KeithG]

Then set which field the control will display on the Open event as Paul suggested.

 

[Kinger43]

That is what I am attempting to do unless I am not understanding you correctly. I want to select a field in a control box, Form_Turtle_Meter_Form!Multiplier, on the form and run the report. When the report opens the control source of an unbound text boxt, Text8, in the report should get set to whatever field I selected in the form.
The switch function worked and that is what I will use, but I am just getting started learning VB and I am just curious about what I am doing wrong. It seems to me that the original code that I posted should work; but it tells me that there is a type mismatch.

 

[pbaldy]

No, I originally thought you wanted it to change record to record. Now that I know it doesn't, I'm saying you can set the control source of the textbox in the open event of the report.

 

[KeithG]

you needs quotes like below

Me!Text8.ControlSource = "F3"

 

[Kinger43]

you needs quotes like below

Me!Text8.ControlSource = "F3"

I thought the quotes made it a string and it would put F3 literally into the control source as a string. I see now why it was giving me a type mismatch; little syntax qwerk. Thanks Keith

 

[Kinger43]

Private Sub Report_Open(Cancel As Integer)
On Error GoTo err_Report_Open
    
    Dim controlbox                        'Box that controls which control source gets used
    Dim controlledbox                     'Box that gets its control source changed per user request
    Dim Field5                      'Field to choose from
    Dim Field6                      'Field to choose from add more as necessary
    controlbox = Form_Turtle_Meter_Form!Multiplier      'Define the control box (user input box)
    controlledbox = Me!Text8.ControlSource              'Define text box in report to be controlled
    Field5 = F5                      'Define your fields here
    Field6 = F6
    
    If controlbox = "F5" Then
        controlledbox = "Field5"
    ElseIf controlbox = "F6" Then
        controlledbox = "Field6"
    End If
    
err_Report_Open:
    MsgBox Err.Description
    Exit Sub
    
End Sub

Now I am trying to make this code more universal. It doesn't give me an error when it runs but it doesn't set the control source either. I'm guessing that the variables need to be defined "As something" but I don't know what.

 

[pbaldy]

It should be as simple as this in the open event:

Me.ControlName.ControlSource = "FieldName"

If that isn't working, double check both names. If that doesn't work, can you post a sample.

 

[Kinger43]

I understand that, but now I am trying to learn more about VB. I know there is a simpler way of doing it but that doesn't help me in the long run.

 

[Kinger43]

=[Me].[Text8].[ControlSource]="Forms!Turtle_Meter_Form!Multiplier"

I placed the above directly into the on open event of the report and it gave me the error "The object doesn't contain the Automation object 'Me.'

So I tried it without the Me. and it didn't recognize Forms!Turtle_Meter_Form!Multiplier as a control; it asked me for the parameter.

 

[KeithG]

is the form open?

 

[pbaldy]

Try

Dim strField as String
strField = Forms!Turtle_Meter_Form.Multiplier
Me.Text8.ControlSource = strField

 

[Kinger43]

Try

Dim strField as String
strField = Forms!Turtle_Meter_Form.Multiplier
Me.Text8.ControlSource = strField

You mean put this in VB code as an [Event Procedure] in On Open? I thought you had meant to put it in as an expression, which seemed very odd to me.
I tried that code earlier without luck, but I was trying to make Me.Text8.ControlSource a Dim as well.