|
|
I have an excel export button that works fine, i would like to add a
prompt that when I click the export to excel a yes/no prompt comes up,
yes to export no to exit prompt, this way i can eliminate accidental
clicks. Thanks in advance.
Ryan
|
|
<ryan.fitzpatrick3[ at ]safeway.com> wrote in message
news:c4d5a34f-63b3-4c35-bcbf-59c1e3574fd6[ at ]p2g2000prf.googlegroups.com...
[Quoted Text] >I have an excel export button that works fine, i would like to add a
> prompt that when I click the export to excel a yes/no prompt comes up,
> yes to export no to exit prompt, this way i can eliminate accidental
> clicks. Thanks in advance.
Check out the MsgBox function in the online help. You can call it with
arguments set to ask a yes/no question, then examine the result to see which
of the answers was chosen and act accordingly. Here's an example:
If MsgBox( _
"Do you want to export to Excel?", _
vbQuestion+vbYesNo, _
"Export to Excel?") _
= vbYes _
Then
' continue with the export ...
Else
' don't do it.
End If
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
Would the code look like this with mine?
Private Sub Export_Click()
On Error GoTo Err_Export_Click
Dim dbCurr As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim lngOrderBy As Long
Dim strQueryName As String
Dim strSQL As String
[Quoted Text] > If MsgBox( _
> "Do you want to export to Excel?", _
> vbQuestion+vbYesNo, _
> "Export to Excel?") _
> = vbYes _
> Then
> ' continue with the export ...
> Else
> ' don't do it.
> End If
' You only need to go to this effort if there's a filter
If Len(Me.Filter) > 0 Then
Set dbCurr = CurrentDb
' Get the SQL for the existing query
strSQL = dbCurr.QueryDefs("QryAdageVolumeSpend").SQL
' Check whether there's an ORDER BY clause in the SQL.
' If there is, we need to put the WHERE clause in front of it.
lngOrderBy = InStr(strSQL, "ORDER BY")
If lngOrderBy > 0 Then
strSQL = Left(strSQL, lngOrderBy - 1) & _
" WHERE " & Me.Filter & " " & _
Mid(strSQL, lngOrderBy)
Else
' There's no ORDER BY in the SQL.
' Remove the semi-colon from the end, then append the WHERE clause
strSQL = Left(strSQL, InStr(strSQL, ";") - 1) & _
" WHERE " & Me.Filter
End If
' By using the current date and time, hopefully that means
' a query by that name won't already exist
strQueryName = "qryTemp" & Format(Now, "yyyymmddhhnnss")
' Create the temporary query
Set qdfTemp = dbCurr.CreateQueryDef(strQueryName, strSQL)
' Export the temporary query
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:=strQueryName, FileName:= _
"C:\Documents and Settings\All Users\Desktop\Adage
Downloaded On" & Format(Now, "mm" & "-" & "dd" & "-" & "yyyy" & "[ at ]" &
"hh" & "nn") & ".xls", _
hasfieldnames:=True
' Delete the temporary query
dbCurr.QueryDefs.Delete strQueryName
Else
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:=strQueryName, FileName:= _
"C:\Documents and Settings\All Users\Desktop\Adage
Downloaded On" & Format(Now, "mm" & "-" & "dd" & "-" & "yyyy" & "[ at ]" &
"hh" & "nn") & ".xls", _
hasfieldnames:=True
End If
Exit_Export_Click:
Set dbCurr = Nothing
Exit Sub
Err_Export_Click:
MsgBox Err.Description
Resume Exit_Export_Click
End Sub
On Dec 8, 2:16 pm, "Dirk Goldgar"
<d...[ at ]NOdataSPAMgnostics.com.invalid> wrote:
> <ryan.fitzpatri...[ at ]safeway.com> wrote in message
>
> news:c4d5a34f-63b3-4c35-bcbf-59c1e3574fd6[ at ]p2g2000prf.googlegroups.com...
>
> >I have an excel export button that works fine, i would like to add a
> > prompt that when I click the export to excel a yes/no prompt comes up,
> > yes to export no to exit prompt, this way i can eliminate accidental
> > clicks. Thanks in advance.
>
> Check out the MsgBox function in the online help. You can call it with
> arguments set to ask a yes/no question, then examine the result to see which
> of the answers was chosen and act accordingly. Here's an example:
>
> If MsgBox( _
> "Do you want to export to Excel?", _
> vbQuestion+vbYesNo, _
> "Export to Excel?") _
> = vbYes _
> Then
> ' continue with the export ...
> Else
> ' don't do it.
> End If
>
> --
> Dirk Goldgar, MS Access MVPwww.datagnostics.com
>
> (please reply to the newsgroup)
|
|
<ryan.fitzpatrick3[ at ]safeway.com> wrote in message
news:40d13413-190f-49ea-b4b1-4269f28fc721[ at ]w39g2000prb.googlegroups.com...
[Quoted Text] > Would the code look like this with mine?
You need to modify the code I posted to bypass the rest of the processing if
the user answers No. In this particular case, as I understand it, you just
want to exit the procedure in that case, so try it like this:
'----- start of revised code -----
Private Sub Export_Click()
On Error GoTo Err_Export_Click
Dim dbCurr As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim lngOrderBy As Long
Dim strQueryName As String
Dim strSQL As String
' Get confirmation from user.
If MsgBox( _
"Do you want to export to Excel?", _
vbQuestion+vbYesNo, _
"Export to Excel?") _
= vbNo _
Then
Exit Sub
End If
' ... continue with rest of original procedure ...
'----- end of revised code -----
I haven't looked at the rest of your procedure code; I'll just assume that
works.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
Thanks that worked!
On Dec 8, 5:23 pm, "Dirk Goldgar"
<d...[ at ]NOdataSPAMgnostics.com.invalid> wrote:
[Quoted Text] > <ryan.fitzpatri...[ at ]safeway.com> wrote in message
>
> news:40d13413-190f-49ea-b4b1-4269f28fc721[ at ]w39g2000prb.googlegroups.com...
>
> > Would the code look like this with mine?
>
> You need to modify the code I posted to bypass the rest of the processing if
> the user answers No. In this particular case, as I understand it, you just
> want to exit the procedure in that case, so try it like this:
>
> '----- start of revised code -----
> Private Sub Export_Click()
>
> On Error GoTo Err_Export_Click
>
> Dim dbCurr As DAO.Database
> Dim qdfTemp As DAO.QueryDef
> Dim lngOrderBy As Long
> Dim strQueryName As String
> Dim strSQL As String
>
> ' Get confirmation from user.
> If MsgBox( _
> "Do you want to export to Excel?", _
> vbQuestion+vbYesNo, _
> "Export to Excel?") _
> = vbNo _
> Then
> Exit Sub
> End If
>
> ' ... continue with rest of original procedure ...
>
> '----- end of revised code -----
>
> I haven't looked at the rest of your procedure code; I'll just assume that
> works.
>
> --
> Dirk Goldgar, MS Access MVPwww.datagnostics.com
>
> (please reply to the newsgroup)
|
|
|