Access: Report: Controls with control source not on table

Posted on 2007-10-12
Last Modified: 2013-11-28
I have a report thats control source = table1. Table1 has the following fields: (1,2,3,4,5).
A combo box on this report has a control source = 72. Because 72 is not a field on table1, when I try to print this report it asks me what the value of 72 is before printing.

Is there a way to get my Access database to NOT ASK me what the value of 72 is before printing or showing print preview?. I just want it to print and ignore controls with a control source that does not exist on table1.
Question by:ouestque
    LVL 119

    Expert Comment

    by:Rey Obrero
    what's a combo box doing in your report?

    and what does 72 suppose to be?
    LVL 27

    Accepted Solution

    Hi ouestque,

    Although capricorn1's question still stands, whic would be interesting to know the answer,
    here is the solution to your question:
    we have to check the fields of Table1 looking for the field 72,
    if it doesn't exist, then we tell the Report to "not assign" a control source for the combobox.

    place this code in your Report, after correcting the combobox name:

    Private Sub Report_Open(Cancel As Integer)
        Dim varTable As DAO.TableDef
        Dim varField As DAO.Field
        dim FieldExists
        For Each varTable In CurrentDb.TableDefs
            If varTable.Name = "table1" Then
                For Each varField In varTable.Fields
                    If varField.Name = Me.COMBOBOX999.ControlSource Then
                        FieldExists = -1
                    End If
            End If
        'now that we know if the field exists/doesn't exist in the table,
        'if it doesn't, then don't assign a control source to this combobox
        If FieldExists = 0 Then
            Me.COMBOBOX999.ControlSource = ""
        End If

        Set varTable = Nothing
        Set varField = Nothing
    End Sub


    Author Comment

    Answer1: There are combo boxes in my report  because 'I am assuming'  the previous programmer had to convert all 102 forms in the database to a report format. It's kind of a long story, but it was much faster to cut and paste controls from each form to a report, then resize all controls as a textbox.

    Answer2: There are about 200-700 controls on the report. 72 represents the 72nd control on the report. (They were labled this way before I was told to 'fix' the thing :(

    Author Comment

    Although jjafer's code answers the question above, I have 102 forms with up to 250 controls with control sources. All 102 reports  have different control names. Many of which have control sources that don't exist on the table.

    QUESTION: Is there a way to tweak jjafferr's code above so that it will check all controls on my report and if the control source for that control doesn't exist on the table, it doesn't assign a control source to that control.

    I could cut and paste this code to all 102 reports (Instead of checking all 250 controls on each form for control source validity)
    LVL 27

    Assisted Solution

    Now that we know better,
    I would suggest to use DeleteReportControl, to delete those controls from the Reports.

    If that option is sccepted, then what we can do is:
    Make a backup of your mdb, and work on a copy,
    make a code to go through ALL the Reports (in design view),
    if the control (comboboxes in your case) doesn't exit, then delete it, save the Report,
    move on to the next Report.

    This way, one code (from a Form) can clean all controls in your 102 Reports.


    Author Comment

    I suppose there is no other way than to get rid of the problem controls or change them. Nonetheless I really appreacate ya'lls help THANKS!!!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now