don't want to "Enter Parameter Value"

[jguscs]

When I create a report with a bound control set to a field in a record source (table) that doesn't have a column corresponding to the bound control in the report, a pop-up box shows up (preceeding report generation) entitled, "Enter Parameter Value" and lists the name of the bound control that was supposed to be in the table.

What I want to know is if it's possible to avoid this pop-up "Enter Parameter Value" box.
Possibly by pre-defining a default value (of an empty string) for all unfound bound columns.
Possibly on a case-by-case basis (though this would be less preferrable).

Any thoughts/experience?

 

[Fornatian]

I'm confused, how can you have a control bound to field(column) that does not exist in the datasource, by definition the field will be unbound won't it? That's why you are being asked to provide the parameter(field) value. What are you trying to do?

 

[SforSoftware]

Keep in mind that you also can leave the 'controlsource'-property blank...

 

[jguscs]

Well, it's a control that "trying" to be bound to a field (in a table). "Trying" means that there has already been a control source designated for the control (thus, making it bound).

See, the report uses a recordsource that can vary from one table to another. The thing is that MOST of the time, EVERY bound control in the report matches EVERY column/field in the table.
But SOME tables are missing a field or two. And in those cases, an annoying "Enter Parameter Value" pop-up box comes up when the report generates because it's missing sources for those controls. I just want to get rid of the pop-up box.

 

[SforSoftware]

Ok,

How do you change the recordsource of the report?
At that time you can check (or know) which fields are in it and you can set the controlsource to a zero-lenth string.

 

[jguscs]

I don't know, SforSoftware, maybe Access thinks the control source for the control IS being left blank, but in fact it's not blank-- it's just pointing to a field that's not available in the table.

 

[SforSoftware]

You assign different recordsources to the report (you're speaking about different tables, how's that?)
If Yes, how do you do that?

 

[jguscs]

I was going to copy the code when Access crashed.
It'll take a sec.

The user can select the table to use for the report's source from a list box in the Switchboard form. Then, when the Report opens the following code is run:

Private Sub Report_Open(Cancel As Integer)
Dim varItem As Variant
For Each varItem In Forms!Switchboard.LstTables.ItemsSelected
Me.RecordSource = Forms!Switchboard.LstTables.ItemData(varItem)
Next varItem

Then the report goes ahead and uses the designated record source and... generates. The only problem is on the rare occasion that the user selected a table with 1 or 2 fields missing and then the "Enter Parameter Value" box pops up before the report generates. I want to get rid of that pop-up box.

 

[jguscs]

OK, what you suggested did work.

I'm checking the table one column at a time with a DLookup and if there is an error (because DLookup can't find the column that it was supposed to be able to find), I have some error-checking code that sets the control that was SUPPOSED to be linked to the column (that is missing) to an empty string.
This has to be done in Report_Open, though, because after that it's too late.

This is kind of inefficient, however.
If anyone has a way of just disabling the "Enter Parameter Value" pop-up box, that would be preferred.
Thanks.

 

[SforSoftware]

While you was doing, I did something like that

Private Sub Report_Open(Cancel As Integer)
  On Error Resume Next
  
  Dim ctl As Control
  Dim var As Variant
  Dim rst As DAO.Recordset
  
  Set rst = CurrentDb.OpenRecordset(Me.RecordSource)
  
  For Each ctl In Me.Controls
    var = rst(ctl.ControlSource)
    
    If Err = 438 Then   ' Control has no Controlsource-property
      Err.Clear
      Resume Next
    End If
    If Err = 3265 Then  ' Controlsource doesn't exist in recordsource
      ctl.ControlSource = ""
      Err.Clear
      Resume Next
    End If
  Next ctl
End Sub

There will be no other way, because usually you don't assign such different recordsources...

PS. Might be quicker than the (awfully slow) DLookUp