Xtab Controled with VBA (1 Viewer)

[94Sport5sp]

Hi:

For the application I am developing I have created a X Tab Query to produce a listing in a form (use is unlikely to print this) showing the results of the X Tab Query. Now I need to find some way to automate the query to limit the information based on date. So I am looking for suggestions. I am using A2003 to develop the this application

The first issue is to allow the user to specify the date (start and end) period. I found the Date Picker Control and added it to the form. I tried to preset the date but Access tells me the control is read-only. Is there a better way to do this? How do I tell the query what dates to list?

Secondly, in the query I created I use the IN clause to control the output order of the columns. Can I use code to change this order? I am thinking if a new value occurs in the future then the X Tab would not list the new column until I updated the query. Would I be better off to put the query in code and then run the SQL statement from within code?

Thanks for your thoughts

 

[vbaInet]

I was just about to ask you what a X tab query is but it dawned on me that you mean a Crosstab query.

What code did you use to try to set a default date?

The only way to change the order is to change the SQL of the query definition. So why don't you leave it as a dynamic list?

 

[94Sport5sp]

Hi:

Thanks for the reply.

I have two Date Picker controls: one called FromDate and one called ToDate. I have tried various ways to change the date. For test purposed the code is Me.FromDate = DateValue(Now()-1) to set the FromDate to yesterday produces the run time error DatePicker is read only. I have also tried Me.FromDate.Value = DateValue(Now()-1) and FromDate.Value = DateValue(Now()-1) and others options. All produce the same error DatePicker is read only.

The output of the X Tab without column headings does not produce a meaningful report. The output column data is text and is therefor sorted alphabetically by the query. The user would like the data grouped based on how the data is used and so I put in column headings into the query which works well. The data is now in the order the user wants. However, I know that when new data is added or names get changed or something then my list will not show the new, or modified data. If I can change the column headings with VBA then I could control the changes to the data without having the change the query.

The date is a similar problem. The user would like to see the data for a specific period, hence the FromDate and the ToDate controls. For now it is not a big deal to click on the Date Picker and select a date and then I can check if FromDate is older than ToDate and respond as needed.

Thanks

 

[vbaInet]

What version of Access do you have?

It would be easier if you upload a cut down version of your db with only the relevant bits.

 

[94Sport5sp]

Hi:

Access 2003 with windows XP.

Thanks

 

[vbaInet]

So can you upload a stripped down version of your db?

 

[94Sport5sp]

So can you upload a stripped down version of your db?

Hi:

Had to check on that and sorry the answer was no. However if it helps, the Date Picker if from the toolbox and is MS Date Picker 6.0 SP4 which is standard with Access 2003. Also I note in the Others property tab there is a Value property which allows me to set a default date. I just need to figure out how to access that with VBA code.

Thanks

 

[94Sport5sp]

Hi:

Back on the net to do more searching. I found http://social.msdn.microsoft.com/Forums/lv/accessdev/thread/589cff8b-8536-4794-a9b5-5a106ef74c8d that describes my problem with Date Picker with the exception that they are using Access 2007. They were, however, using the same version of Date Picker. So I went back to my form and implemented there change, and, IT DID NOT WORK and I got the same error back.

After thinking on this problem I decided to create a new form and load the Date Picker control anew. The form was unbound and only had three controls, DTPicker0, Text1 and Text2 and nothing else. Text1 has as a control source =NOW() - [Text2] so I could change the date at will. I set up a double click event on Text2 to set DTPicker0 to Text1 with me.DTPicker0 = Text1. Worked like a charm. Ok, something wrong on my form. So I recreated my form ran the me.DTPicker0 = Text1 test and it failed. After some more tests and lots of thinking I determined the only real difference between the working form and the non-working form was where the me.DTPicker0 = Text1 was placed. On the working form the double click event was on the Text2 control and on the non working form it was on the FormOpen event. Moved the me.DTPicker0 = Text1 to a non form event and works like a charm. Old form, new form, even copied control to different form. Works as it should.

So for anyone else who finds the message that is how I fixed that problem. Now I just have to decide on how to handle the X Tab column headings

Thanks

 

[vbaInet]

Here's an alternative to the date picker. It's a non ActiveX solution:

http://www.lebans.com/monthcalendar.htm

For the column headings, as already mentioned, you will need to use VBA to change the SQL of the query if you want to make it to be dynamic and sorted in your preferred order. I'll start you of with some aircode:

dim strSQL as string
dim qdf as dao.querydef

set qdf = currentdb.querydefs("Crosstab Query Name")
strSQL = qdf.sql

' get the SQL statement without the IN ( ) part
strsql = left(strsql, instrrev(strSQL, "In (", 1)) - 1)

' continue the code from here to set the new IN () part

' set the SQL of the querydef to the newly built statement
qdf.sql = strSQL
set qdf = nothing

 

[94Sport5sp]

Hi:

Thanks for the link and the aircode. I had read earlier about change the sql for the query but have not had a chance to test it. Is the change to the query permanent (not a problem, just curious), and it this better than doing a DoCmd.RunSQL statement?

Thanks

 

[vbaInet]

Yes it will be permanent.

Where does the DoCmd.RunSQL come in?

 

[94Sport5sp]

Yes it will be permanent.

Thanks. That works great.

Where does the DoCmd.RunSQL come in?

One of my original ideas was to create/copy the sql statement into vba code and then run the code with the DoCmd.RunSQL from within vba code. But, then I was also wondering which use less resources and is easier to maintain. So, it really does not affect your suggestion to change the query definition.

Thanks

 

[vbaInet]

Good to hear.

The code I gave you doesn't execute the SQL and DoCmd.RunSQL executes action queries, i.e. Update, Delete and Insert. It doesn't work for SELECT queries.

 

[94Sport5sp]

Hi:

Thanks for the clarification.

 

[vbaInet]

No problem!