[Last Call] Learn how to a build a cloud-first strategyRegister Now


Access: Report: Controls with control source not on table

Posted on 2007-10-12
Medium Priority
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
  • 3
  • 2
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20068165
what's a combo box doing in your report?

and what does 72 suppose to be?
LVL 27

Accepted Solution

jjafferr earned 2000 total points
ID: 20070151
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

ID: 20077668
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 :(
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 20077718
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

jjafferr earned 2000 total points
ID: 20079217
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

ID: 20131022
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

831 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