ACCESS FEED: RE: [MS_AccessPros] started with preventing layout changes to datasheet

Kamis, 03 Januari 2013

RE: [MS_AccessPros] started with preventing layout changes to datasheet - now cant export to excel

Terence,

I'm not entirely sure what you are attempting to do but I use queries in a subform control regularly in my apps. My subform control name "SubForm" (a carry over from earlier app building). When I open the main form, I send the name of the query in the OpenArgs. When I close the form using the exit command button, there are no messages. I often use code to modify the SQL property of saved queries which are then displayed in the subform control.

The key sections of code are:
'=== the code in the main form Open event ===
Private Sub Form_Open(Cancel As Integer)
   On Error Resume Next
   If Len(Me.OpenArgs & "") > 0 Then
      strQueryName = Me.OpenArgs
    Else
      MsgBox "This feature expects a query name as an OpenArg.", vbOKOnly + vbInformation, "Can't procede"
      DoCmd.Close acForm, Me.Name
   End If
   Me.SubForm.SourceObject = "QUERY." & Me.OpenArgs 'my query names don't have spaces
End Sub

'=== The code associated with the command button to close the form ===
Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click
    DoCmd.SetWarnings False
    Me.SubForm.SourceObject = ""
    DoCmd.Close

Exit_cmdExit_Click:
    DoCmd.SetWarnings True
    Exit Sub

Err_cmdExit_Click:
    MsgBox Err.Description
    Resume Exit_cmdExit_Click

End Sub

'=== bonus code in the Resize event to take advantage of the screen size and resolution ===
Private Sub Form_Resize()
   On Error GoTo Form_Resize_Error
   'code may need to be modified to match your form
   Me.SubForm.Height = Me.InsideHeight - (Me.Section(1).Height + Me.SubForm.Top + 100)
   Me.SubForm.Width = Me.InsideWidth - 200

   On Error GoTo 0
   Exit Sub

Form_Resize_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Resize of VBA Document Form_frmMyQueryResults"
End Sub

The main form has an option group that allows the user to "send" the data to a number of destinations including Excel. This is the snippet of code used to export to Excel and open the file. This assumes your query name is in the variable strQuery, the file name is in txtFileName, and there are check boxes for including headings and auto starting.

'=== section of the send command button ===

         DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQuery, _
                Me.txtFileName, Me.chkIncludeHeadings
         If Me.chkAutoStart Then
            FollowHyperlink Me.txtFileName
         End If

Duane Hookom MVP
MS Access

----------------------------------------
> To: MS_Access_Professionals@yahoogroups.com
> From: buffalome000@yahoo.com
> Date: Wed, 2 Jan 2013 22:28:03 +0000
> Subject: [MS_AccessPros] started with preventing layout changes to datasheet - now cant export to excel
>
> This on has me stumped. Using Access 2007, windows xp, shared rived, linked tables and .accde.
>
> I have several forms which display in datsheet view, they are used by medical reaserch staff. We have lots of people using these forms, and they could change the column widths, and move columns around. THAT WAS A BAD THING. I tried to find a way to prevent these column changes from being saved. I want the datasheet to revert to it's original layout when the form is opened.
>
> I looked for a ssolution online from MS. Found a solution at http://support.microsoft.com/kb/173517
>
> I used the solution and it worked. Solution was to place the datasheet as a Sub Form on another form which was SINGLE FORM View. Resized the sub form so it was not apparent the datasheet was a sub form of Single Form.
>
> IT DID correct the problem. BUUT ! The customers can no longer EXPORT the datasheet to excel.
>
> I presume the Export function realizes this is NOT a datasheet, becasue it's actually a Single Form with a subForm as a datasheet, hence, will not export.
>
> 1. Is my assumption correct ?
> and is there a solution to the excel Export problem ?
>
> OR
>
> Is there a better way to PREVENT coulumn changes from being saved thent he solution provided in the MS article ?
>
> P.S.
>
> Sorry I have been away so long, Work Calls :)
> Terence
>
> Hello to John V, Bill M, A.D., Crystal & and the "gang" !
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>