Replies

I’m not sure what went wrong but have a look at the attached db to see what I mean.

you can change the report in design mode and then open for preview as follows

    Dim stDocName As String

    stDocName = "ReportName"
   'open the thing in design mode.
    DoCmd.OpenReport stDocName, acViewDesign
    ' set the chart rowsource in design mode.
        Reports!ReportName!ChanrtName.RowSource = "QueryName"
    'save the changed report
    DoCmd.close acReport, stDocName, acSaveYes
    'open the thing as per normal.
    DoCmd.OpenReport stDocName, acPreview

Altenately I believe that opening the report with a “where clause” or a saved query as a filter will also solve your problem assuming that the different queries are just variations on the original dataset.

    strDocName = "tblName" 'any table will do.
    ' Give focus to Database window; select table (first
    ' table in list is ideal).
    
      DoCmd.SelectObject acTable, strDocName, True

Then, I ran a MakeTable query using Select * from UnionQuery.
Can this be done in 1 step? Or, would I run the queries in sequence “In the autoexec macro”?

A. The union query does not need to be run at all. When the make table query is executed the union query provides the data. In effect the make table query is running the union query in the background.

1) what do you mean by “shell to access from excel” .. I was thinking about getting the data from Access using an ODBC connection with MSQuery.

A. using the following, to open access & your db where the autoexec will make the table you need and quit access.

***************** Code Start ******************
‘This code was originally written by Terry Kreft.
‘It is not to be altered or distributed,
‘except as part of an application.
‘You are free to use it in any application,
‘provided the copyright notice is left unchanged.
‘
‘Code Courtesy of
‘Terry Kreft

Private Const STARTF_USESHOWWINDOW& = &H1
Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1&

Private Type STARTUPINFO
    cb As Long
    lpReserved As String
    lpDesktop As String
    lpTitle As String
    dwX As Long
    dwY As Long
    dwXSize As Long
    dwYSize As Long
    dwXCountChars As Long
    dwYCountChars As Long
    dwFillAttribute As Long
    dwFlags As Long
    wShowWindow As Integer
    cbReserved2 As Integer
    lpReserved2 As Long
    hStdInput As Long
    hStdOutput As Long
    hStdError As Long
End Type
Private Type PROCESS_INFORMATION
    hProcess As Long
    hThread As Long
    dwProcessID As Long
    dwThreadID As Long
End Type
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
    hHandle As Long, ByVal dwMilliseconds As Long) As Long
Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
    lpApplicationName As Long, ByVal lpCommandLine As String, ByVal _
    lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
    ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
    ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, _
    lpStartupInfo As STARTUPINFO, lpProcessInformation As _
    PROCESS_INFORMATION) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal _
    hObject As Long) As Long
Public Sub ShellWait(Pathname As String, Optional WindowStyle As Long)
    Dim proc As PROCESS_INFORMATION
    Dim start As STARTUPINFO
    Dim ret As Long
    ' Initialize the STARTUPINFO structure:
    With start
        .cb = Len(start)
        If Not IsMissing(WindowStyle) Then
            .dwFlags = STARTF_USESHOWWINDOW
            .wShowWindow = WindowStyle
        End If
    End With
    ' Start the shelled application:
    ret& = CreateProcessA(0&, Pathname, 0&, 0&, 1&, _
            NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)
    ' Wait for the shelled application to finish:
    ret& = WaitForSingleObject(proc.hProcess, INFINITE)
    ret& = CloseHandle(proc.hProcess)
End Sub

‘***************** Code End ****************

2) what do you mean by “or use access to open the database, waiting for it to terminate” — for what to terminate?

A. wait for access to terminate. When access closes the new table has been created and then you can attach via ODBC.

3) RE: “In the autoexec macro, run the make table query, and then exit access” … How do I do this?

A. create new macro
Action = “OpenQuery”. Query Name = your query name.
Action = “Quit”

Save the macro as “AUTOEXEC” and that is all you have to do.

4) RE: “then fire off your processing from within excel.” Is there any way that I could control opening ACCESS and firing off the AutoExec Macro from VBA within EXCEL?

A. This is what 1) should do. You could also use automation to do this, but I’m not sure if your vba code stops when opening an automation object. you definitely want your code to open access, make the table and then return control to excel, ie executing the next line of code.

“I think that I will stay away from “Creating a Chart in a report”, for now, as I have yet to get my feet wet with ACCESS reports and “provides(ing) live data to your users” is not an issue because once these source databases are created they do not change.
I have to put “Linking this to a control or list box on a form” on my agenda. I have been putting off working with Forms for too long.”

Access reports are fairly easy but as with anything if you don’t have the experience it can take much longer than it should. Working with access forms is again very simple. if you have a grip on VBA in excel it should be fairly easy to make the move to access, just getting used to a new group of commands.

Good luck.

I assumed that the report would be based on the new query. Oh well.

If the report is only designed to return the values from the new recordset change the report datasource to the new query. Update the controlsource where appropriate to get it to use any data field names that are different.

If the count is associated with a different recordset, modify the source for the report by adding the new query to the query the report is based on. Linking on the date field (presumably) to make the count figure available as just another field. Then change the dcount controlsource to be the field name from the query.

Make any sense or just more confusing?

Your welcome.

“Is it possible for the combo box to on the main form to auto populate NEW records that are added into the subform? ”

Yes, the link child/master fields does exactly this. The default value for the child field in a new record is the master field value. You can of course link multiple fields this way.

“comboBox only contains an anthologyTitle which is also in the subform as Ttitle, but there

you can base a make table query on the union query ie

SELECT qryUnion.field[n] INTO tblNewTable
FROM qryUnion;

To me it sounds like the export to excel is making work for yourself. The ability to calculate an average for a variable on demand in an access query is there. Linking this to a control or list box on a form is also quite straight forward. Creating a Chart in a report is also fairly straightforward, not as simple as excel I admit, but the advantage is again, linked tables with the variable average based on a query provides live data to your users.

Of course if you are quite competent in excel and a little lost in access you are probably doing the right thing. You could shell to access from excel and execute the query, or use access to open the database, waiting for it to terminate. In the autoexec macro, run the make table query and then exit access, then fire off your processing from withing excel.

My patience is endless, your thanks is appreciated. Do you need more detailed explanations?

If it is the access generated switchboard the button click will call something like “=HandleButtonClick(1)”

This gets the menu, the command type and the argument. The handlebuttonclick () uses the following, check yours to make sure it is the same,

Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8

in this case to run the code just replace the 7 with 8 in tblSwitchboardItems and change the argument from the macro name to the function name.

“Does your solution here retain the result of this formula? or is it trying to multiply the formula itself by .0925?”

it multiplies the value contained in the text box by .0925. When you say stuck in a loop have you actually stepped through the code to determine that is what is happening or are you making an assumption.

The problem with domain lookups is simply that they are incredibly slow. Depending on what you are doing I’d be tempted to base your report on a query where the resultant records provide the value for Track[n] ie

SELECT tblMain.DATE, Count(tblMain.DATE) AS Track
FROM tblMain
GROUP BY tblMain.DATE
HAVING (((tblMain.DATE) Between Date()-1 And Date()-7));

will provide a recordset like

DATE Track
12/08/2001 4
13/08/2001 1
14/08/2001 2
15/08/2001 2

This will be a lot quicker.

Put a break point on the first line of the relevant report section format event and determine if the problem is the code being called multiple times or just the speed of the domain lookups.

make sure that the detail section has it’s Can Grow property set to true also.

I cam across something similar with a user developed database but the problem was that the memo was truncated at 255 characters. It turned out it was the format “>” as the user wished to view text in Uppercase. When I removed the format the data magically reappered.

It sounds like you are trying to add a new record at the main form level. Make sure that the combo that you are using to filter the sub form is unbound to prevent changing data at the top level. If this is the case you will not be able to shift the focus to the subform without undoing your entry in the combo box. Is this the case?

If you are displaying dat a in the sub form that relates to the current record in the main form this doesn’t apply of course, but in that case you wouldn’t be changing info in a combo box.

This should work with no problems. Where are you trying to execute the code? Assuming it is in the detail section of the report the following will work.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me!track1box.Width = track1 * 0.0925
End Sub

Are you getting an error message? or just not seeing the control? if an error what is it? If no error just no apparent control, try setting the back colour to red and see if it is a fine line, then try

Me!track1box.Width = track1 * 0.0925

Dont forget that the width setting here is in twips.

No worries, glad you thought the reply was useful.

1. basing a query on the union and table X is certainly a viable option. If speed is a factor then make a table from the union query and create indexes in the new table for the fields you will be joining on, and any fields that you are particularily interested in using for queries etc.

2. I’d link the tables in this case rather than import the data. The advantage is that the data is dynamic, assuming that the tables are production tables rather than manufactured for your particular exercise.

If the tables are manufactured using a make table query, you may wish to change your process to create the tables in your final database rather than in the source database and then linking them.

If the form has a combo box that lists the months, using the month number as the key, you are obviously populating this from a value list. Why not change the combo box to be populated from the query, this will show only the months where there is data. Add a second combo box bound to the date field from the query but have two columns, the second column being the sum of the amount. set the column width for column 1 to zero. This will show the Amount in the combo. When you change the selected month from the date combo, set the value of combo 2 to equal combo1 (after update event) the amount from the query will change automatically.

Alternately you can add a parameter to the query under the date field eg forms!MySummaryForm!cboSelectMonth. Then when you have changed the date in the combo box you can requery the control/subform etc that is displaying the query output to obtain the correct value.

There seems to be a problem with what you are doing. When you say you have based the form on the query, you get the records returned by the query. Changing data in a combo based on the date field attempts to change the data for that record if the combo box and the form are bound to a table or query.

Why append into a new table? if you just want a quick look at the combined data from the tables you can create a new query

select * from tbl1
union
select * from tbl2
union
etc etc

This is a bit slow when executed and will only work if the number of fields is the same, same order etc but it appears that this would be the case.

Depends on what you are doing and what you need but is an easy solution. To add a new table to the pot just add a new union statement.