Unable to find field when changing the controlsource

[AlexisW]

Hello



I'm currently working on a macro for a report. The report shows dates for different categories which the user decides. I'd like my report to show only the categories that the user has decided. The report runs from a query which, obviously, finds the correct date based on the user's criteria

I have the following code:

Private Sub Report_Open(Cancel As Integer)
    Dim counter As Integer
    Dim dbs As Database
    Dim rst As Recordset
    
    counter = 7
    
    ' Open the table of SwitchboardItems, and find
    ' the first item for this Switchboard Page.
    Set dbs = CurrentDb()
    strSQL = "SELECT * FROM [SwitchboardItems]"
    strSQL = strSQL & " WHERE [ItemNumber] > 1 AND [SwitchboardID]=2"
    strSQL = strSQL & " ORDER BY [ItemNumber];"
    Set rst = dbs.OpenRecordset(strSQL)
    If Not (rst.EOF) Then
        If rst![Yes/No] = True Then
            Me("Text" & counter).ControlSource = "=[All Expired Query]![Mid(rst![ItemText], 5) & ' Expiry Date']"
            Me("Label" & counter).Caption = "=[All Expired Query]![Mid(rst![ItemText], 5) & ' Expiry Date']"
            counter = counter + 1
        End If
        rst.MoveNext
    End If
    
    If counter < 14 Then
        For I = counter To 14
            Me("Text" & I).Visible = False
            Me("Label" & I).Visible = False
        Next I
    End If
    
    Set dbs = Nothing
    Set rst = Nothing
    
End Sub

'All Expired Query' is the query that runs,
'SwitchboardItems' is the table that contains the criteria the user can decide from

The problem i'm getting is the report can find the field, it gives the following error:
The report name 'All Expired Query' you entered in either the property sheet or macro is misspelled or refers to a report that doesn't exist.


Does anyone know the correct syntax I should be using?

Thanks for any help!

[June7]

Concatenate variables. Reference to recordset field is a variable:

Me("Text" & counter).ControlSource = "=[All Expired Query]![" & Mid(rst!ItemText, 5) & " Expiry Date]"

However, Caption property is not like ControlSource. It will not get values from the form RecordSource but will instead display the literal characters that are within the quotes. So maybe you want:

Me("Label" & counter).Caption = Mid(rst!ItemText, 5) & " Expiry Date"

[AlexisW]

Thanks so much for the correct syntax. I knew about the label syntax but for some odd reason I changed it
However I'm still getting the same error, it thinks that my query is a report, and so it can't find it. Is there any way to tell the macro that it's a query?
Thanks

[June7]

Just hit me you are trying to set the ControlSource property of textboxes in a report. Don't think this is possible (in forms, yes). I have tried. What I have done is set the RecordSource property of a report in its Open event. Then I manage visibility of textboxes depending on data of the RecordSource.

[AlexisW]

Alright, So I fixed the part which was getting my previous error (the query couldn't be found, I was accidentally opening a report, opps!)
However now I'm getting a new problem, The script goes all the way through, but when it gets to End Sub, it prompts for a parameter

Even if I take out the [All Expired Query] part from the changing control source, it still pops up
Is there some other mistake in my code that I can't see?

[June7]

Don't know, as I said, don't think can use VBA to set the ControlSource property of textboxes on report.

I would have to review the project if you want to provide. Follow instructions at bottom of my post. Identify the objects involved in the issue.

[AlexisW]

Here is my project, any help you can provide concerning my problem would be greatly appreciated
Thanks

[June7]

I think I understand what you are trying to do with this report. If what you want is to not show empty Expiry Date fields for each student and also compress the data, ie. 'shift' it left so no blank columns. This really doesn't even make sense for a report that will list multiple students, each with different fields completed.

Part of the issue is that your data is not normalized.

What is purpose of all the Yes/No 'Find ...' fields?

I did manage to get rid of the parameter prompt with this code change:
Me("Text" & counter).ControlSource = "[" & Mid(rst![ItemText], 6) & " Expiry Date]"

However, the code to set ControlSource and Caption just doesn't work. For some reason the switchboard recordset retrieves only one record.

There is no reason to have code 'open' a query that a form or report has as RecordSource. Just opening the form/report does that.

[AlexisW]

For this particular report, I want it to show the records that have expired dates (It won't show a record that has anything in July or later) for a particular certificate. However if the user wants to show, say, both "Health" and "CPR" expiry dates, the report will show every record that has an expired certificate. The reason I used a union was because I couldn't get the Or statements to work for me
The purpose of the Yes/No "Find" fields is so that the query know which columns to take from depending on what the user has picked to look at
It's too bad thatthe controlSource and Caption can't be changed, though it's not a huge deal, it was just an idea I had.
Thank you for looking into it!