423,319 Members | 2,562 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,319 IT Pros & Developers. It's quick & easy.

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

P: 31
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.

Expand|Select|Wrap|Line Numbers
  1.     Dim dbo As DAO.Database
  2.     Dim qdf As DAO.QueryDef
  3.     Dim fld As DAO.Field
  4.     Dim wid As DAO.Property
  5.     Dim strSQL As String
  6.     Dim myField As String
  7.  
  8.  
  9. strSQL = "SELECT tbl_calendar.SiteName, tbl_calendar.EventName, Count(tbl_calendar.FullName) AS EventCount " & _
  10.                         "From tbl_calendar " & _
  11.                         "GROUP BY tbl_calendar.SiteName, tbl_calendar.EventName;"    
  12.     Set dbo = CurrentDb()
  13.     Set qdf = dbo.QueryDefs("qry_reporting_all")
  14.     qdf.sql = strSQL
  15.     'set column widths
  16.     For Each fld In qdf.Fields
  17.         myField = fld.Name
  18.         Set fld = qdf.Fields(myField)
  19.         Set wid = fld.Properties!ColumnWidth  '<--Errors
  20.         wid = 2880
  21.     Next
  22.  
  23.  
  24.  
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:
Expand|Select|Wrap|Line Numbers
  1.  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_all, the fields are labeled as expected and the data is correct.

I'm guessing it has to do with Count(). Any ideas?
Jan 10 '13 #1

✓ answered by 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.

Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 30,996
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.
Jan 10 '13 #2

P: 31
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.
Jan 11 '13 #3

NeoPa
Expert Mod 15k+
P: 30,996
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).
Jan 11 '13 #4

P: 31
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...everywhere.
Jan 15 '13 #5

NeoPa
Expert Mod 15k+
P: 30,996
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.
Jan 15 '13 #6

Post your reply

Sign in to post your reply or Sign up for a free account.