Error '3270', Property not found, when setting Properties!ColumnWidth

[tetsuo2030]

Greetings.

I have VBA code running from a combobox's AfterUpdate event which sets a querydef to a particular SQL statement based on the selection. Reason: rather than saving a bunch of queries, I just have one called "qry_reporting_ all" and simply change the QueryDef through VBA. Works like a charm.

Now, for aesthetic reasons, I wanted to set the query's columns for each field to 2 inches (2880 twips) wide--per my data, 2 inches looks good on the screen. So, I found some code to loop through all the fields in the query (which vary from SQL statement to SQL statement), and set those column widths. See below.

    Dim dbo As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim fld As DAO.Field
    Dim wid As DAO.Property
    Dim strSQL As String
    Dim myField As String


strSQL = "SELECT tbl_calendar.SiteName, tbl_calendar.EventName, Count(tbl_calendar.FullName) AS EventCount " & _
                        "From tbl_calendar " & _
                        "GROUP BY tbl_calendar.SiteName, tbl_calendar.EventName;"    
    Set dbo = CurrentDb()
    Set qdf = dbo.QueryDefs("qry_reporting_all")
    qdf.sql = strSQL
    'set column widths
    For Each fld In qdf.Fields
        myField = fld.Name
        Set fld = qdf.Fields(myField)
        Set wid = fld.Properties!ColumnWidth  '<--Errors
        wid = 2880
    Next

This works for fields "SiteName" and "EventName" , but when it gets to field name "EventCount ", it throws an Error 3270, "Property not found" on the fld.Properties! ColumnWidth line.

While testing, it looks like this code works for all my SQL statements that aren't Summary Queries...it seems whenever I hit a calculated field, this fails. I've tried aliasing other, non-caculated fields like so:

 strSQL = "SELECT tbl_calendar.SiteName as Test...

...and get no errors, so it's not about the name.

I added a watch to myField to be safe and it's returning "EventCount ", which is what the SQL defines. When I open qry_reporting_a ll, the fields are labeled as expected and the data is correct.

I'm guessing it has to do with Count(). Any ideas?

[NeoPa]

Firstly, but as an aside, I would suggest you ask yourself why you need a QueryDef object with these different sets of SQL. Bear in mind that such a change to the design of objects in the database make it less flexible as a project. For instance, you won't be able to use it in an MDE or ACCDE project. I'll leave that with you to consider.

As for your problem, I suspect that, for reasons I don't particularly follow, an aggregated field in a QueryDef doesn't have a ColumnWidth property set by default. Seems strange I know, but it appears to be the case nevertheless.

In such a situation I would suggest an error handler that captures this error and handles it by creating the property itself with the required value. I expect that would work. Database Properties has some code that you should be able to use to help with that.

[tetsuo2030]

Thanks, NeoPa; as always, your advice is enlightening. Tell me more about "...you won't be able to use it in an MDE or ACCDE project". I've redefined the SQL behind queries in this way before and made them .accde files.

I imagine there isn't necessarily a "need" for the QueryDef object, but with my limited knowledge, I find something that works and just sort of stick to it. Having only a handful of actual "temp" queries in my object view is visually cleaner to me. When the user says: "Okay, now we want to see this data", I just add a new item to my dropdown, a new SQL statement to the VBA module, and open the same query.

[NeoPa]

Tetsuo:
I've redefined the SQL behind queries in this way before and made them .accde files.

Interesting. I'm talking more from theory than practice, but I thought the idea of an ACCDE was that the design was unable to be changed, thus guaranteeing users never f^Hmuck up the database. If you find that such an update is possible even in an ACCDE then that's very interesting. It's actually quite a clever approach.

Tetsuo:
I imagine there isn't necessarily a "need" for the QueryDef object

Actually, though you may be correct technically, the QueryDef does give you some nice-to-have control over how the data is displayed. You could even store the field sizes for your list of SQLs.

This brings me on to a suggestion for you :
I've been where you are with arrays of information stored in code. Then I remembered I was working in a database system (Doh!) What I realised then was that it makes much more sense to store the data in a table (or set of tables) and to code around that data, rather than to have the data hard-coded into your modules. I believe this idea can make a very useful and clever setup, even more so (and save you time eventually when setting up new queries in your list).

[tetsuo2030]

I think I'm following: rather than coding a new Select case statement / SQL into the module, store it and the dropdown value in a table, set my combobox to the table, and maybe run a DLookup against the associated SQL statement? That'd be accessible from...everywhe re.

[NeoPa]

DLookup() calls are restricted in as much as you cannot use a SQL string value for the Domain parameter. In that sense a QueryDef is preferable, although you could do something very similar with a Recordset object, which would support a SQL string. That said, it seems you have a fairly sound grasp of what I was on about. It's a far more flexible approach than forever redesigning the code.