Open an excel before opening a form

[vrk1219]

HI,
I have a button on a form, which on click need to open an excel sheet and then need to ask a question to user, weather to open the form or not and then if the anwer is Yes, then open the form, else do not open the form.

I have written the code for generating and opneing excel in the click event of the button, followed by code to open the form. Then in the open event of the form, written the following code.


Private Sub Form_Open(Cancel As Integer)
If MsgBox("Are you sure you want to open?", vbYesNo) = vbYes Then
Me.Form.Visible = True
Else
Me.Form.Visible = False
End If
End Sub

But bofore the excel gets generated, this dialog is asking weather to open the form or not.

Please help me.


Regards
RK Veluvali

 

[Dennisk]

how can you click the button on your form without opening the form? the form HAS to be opened in order to execute the on open event code.

I sugest you move the button to say a switchboard or menu, then you can ask two question

Open the spreadsheet y/n
open the form y/n
and take the appropriate action

 

[vrk1219]

Hi Dennisk,
I think my explanation was not clear. The first form has a button, on clicking that will give me an excel sheet, followed by a Yes/No dialog box weather or not to open another form.

Is that possible?

 

[Dennisk]

yes but place the code in the button's on click event not the form's on open event.

 

[CEH]

I would not use the code you are using..........
Seems a little odd using the visible property....... You are stopping the on open event from happening... correct?
So............ in message box if vbNo then ......... cancel event..... else........ do nothing, and form will open.
If the Excel sheet is going to open no matter if "Yes" or "No" then that code needs written into both actions.......All I see here is setting the visible property of a form. Or, execute the code to open the excel sheet before the If...Then.. statement.

 

[vrk1219]

Hi,
I coded the Button's click event as follows:


---A Message box asking user to check the sheet---
---Code that opens excel sheet---
---if ..elese statement as follows---
If MsgBox("Are you sure you want to open?", vbYesNo) = vbYes Then
stDocName = "FormName"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

But, even this is not solving my problem, as the excel sheet is opening only after I click on Yes/No in the dialog box, which is not the intended way it has to function.

The intended way is:
After the user checks the dialog box, clicks ok, the excel should open, then the user should get a dialog box; upon clicking Yes in the dialog box , the form should open; if he/she clicks on No, nothing should happen.

Please let me know where I'm going wrong.

Regards
RK Veluvali

 

[CEH]

I think your best bet is to copy and paste your code here showing the whole process you are trying to do..... Little easier for anyone to help you if they can see the actual coding.

 

[vrk1219]

Hi,
Here is the code I'm using.

Private Sub cmdCreateRole_Click()
On Error GoTo Err_cmdCreateRole_Click
Dim stDocName As String
Dim stLinkCriteria As String

Dim sql As String, outputPath, HasFieldNames As String
Dim HyperlinkAddress
Dim Timestamp, filename, path As String

DoCmd.SetWarnings False

Timestamp = Day(Now) & Month(Now) & Year(Now) & " " & Hour(Now) & Minute(Now) & Second(Now)
path = "C:\Documents and Settings\All Users\Documents\"
filename = "Existing Roles Report_" & Timestamp & ".csv"
outputPath = "C:\Documents and Settings\All Users\Documents\"




DoCmd.TransferText TransferType:=acExportDelim, _
tableName:="RoleMaster", _
filename:=outputPath & filename, HasFieldNames:=-1

MsgBox "The File name is: " & filename & vbCrLf & _
"Please check under" & outputPath
Me.cmdCreateRole.HyperlinkAddress = path & filename

MsgBox " Please review the list of exisitng roles" & vbCrLf & _
"Create a new role only if the intended role does not exist in the list" & vbCrLf & _
"Check for role definition if roles look similar"

If MsgBox("Are you sure you want to open?", vbYesNo) = vbYes Then
stDocName = "frmCreateRole"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Exit_cmdCreateRole_Click:
Exit Sub
Err_cmdCreateRole_Click:
MsgBox Err.Description
Resume Exit_cmdCreateRole_Click

End Sub


Regards
RK Veluvali

 

[vrk1219]

can anyone help me.

Regards
RK Veluvali

 

[vrk1219]

Hi All,
The above requirement is very much needed, and I desperately need your help.
Please help me solving the above said issue.


Regards
RK Veluvali