Code required to close a form & hyperlink ...

[Mikey C]

Hi all

I have a command button on a form which runs the code below. Basically
it launches and updates a spreadsheet for bulk data entry. I have also
used the command button as a hyperlink to launch a specified website.

Now ... my questions are:

1 - What code would I need to include below to close a particular form
after this code has run? (This form must be closed before I can import
the data so would be easier if automatic)

2 - Is it 'best practice' to enable the command button as a hyperlink
to launch the site or should this be incorporated within the below
code? If so how?

Thanks folks - much appreciated!

Private Sub Pokersitescout_Data_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Company",
"S:\Competitor Analysis DB\CA Database\Pokersitescout Data.xls", 0,
"Company"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Country",
"S:\Competitor Analysis DB\CA Database\Pokersitescout Data.xls", 0,
"Country"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Game
Type", "S:\Competitor Analysis DB\CA Database\Pokersitescout Data.xls",
0, "Game Type"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Segment",
"S:\Competitor Analysis DB\CA Database\Pokersitescout Data.xls", 0,
"Segment"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Period
Covering", "S:\Competitor Analysis DB\CA Database\Pokersitescout
Data.xls", 0, "Period"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Year",
"S:\Competitor Analysis DB\CA Database\Pokersitescout Data.xls", 0,
"Year"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Finance /
KPI's", "S:\Competitor Analysis DB\CA Database\Pokersitescout
Data.xls", 0, "Finance/KPI's"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Measure
Type", "S:\Competitor Analysis DB\CA Database\Pokersitescout Data.xls",
0, "Measure"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Group /
Criteria", "S:\Competitor Analysis DB\CA Database\Pokersitescout
Data.xls", 0, "Group/Criteria"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Data
Type", "S:\Competitor Analysis DB\CA Database\Pokersitescout Data.xls",
0, "Data Type"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Currency
/ Format", "S:\Competitor Analysis DB\CA Database\Pokersitescout
Data.xls", 0, "Currency/Format"

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open ("S:\Competitor Analysis DB\CA
Database\Pokersitescout Data.xls")
objExcel.Visible = True
Set objExcel = Nothing
End Sub

 

[Tom Wickerath]

Hi Mikey,

1. DoCmd.Close acForm, "NameOfForm"
Note: If you are using a bound form, you will need to ensure against any
loss of data. Usually, one does this by checking the dirty property, and
attempting to set it to false. See the following article for more details:

Losing data when you close a form
http://allenbrowne.com/bug-01.html

If you need to close this before starting your imports, you can place this
line of code just after the Private Sub Pokersitescout_Data_Click(), as in:

Private Sub Pokersitescout_Data_Click()
On Error GoTo ProcError

DoCmd.Close acForm, "NameOfForm"

DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, "Company", _
"S:\Competitor Analysis DB\CA Database\Pokersitescout Data.xls", _
0, "Company"

DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, "Country", _
"S:\Competitor Analysis DB\CA Database\Pokersitescout Data.xls", _
0, "Country"

' etc.

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Pokersitescout_Data_Click..."
Resume ExitProc
End Sub


2 - I don't understand your question...


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Mikey C]

Thanks Tom - much appreciated!

Don't worry about Q2, was a daft question, not thinking as usual ;-)

 

[Mikey C]

Sorry - me again!

I have entered the following code but get an error message - any ideas?

Error message is "Error 2455: You entered an expression that has an
invalid reference to the property Dirty"


Private Sub Pokersitescout_Data_Click()

On Error GoTo ProcError

If Me.Dirty Then
Me.Dirty = False
End If
DoCmd.Close acForm, "Financial / Stats"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Company",
"S:\Competitor Analysis DB\CA Database\Pokersitescout Data.xls", 0,
"Company"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Country",
"S:\Competitor Analysis DB\CA Database\Pokersitescout Data.xls", 0,
"Country"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Game
Type", "S:\Competitor Analysis DB\CA Database\Pokersitescout Data.xls",
0, "Game Type"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Segment",
"S:\Competitor Analysis DB\CA Database\Pokersitescout Data.xls", 0,
"Segment"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Period
Covering", "S:\Competitor Analysis DB\CA Database\Pokersitescout
Data.xls", 0, "Period"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Year",
"S:\Competitor Analysis DB\CA Database\Pokersitescout Data.xls", 0,
"Year"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Finance /
KPI's", "S:\Competitor Analysis DB\CA Database\Pokersitescout
Data.xls", 0, "Finance/KPI's"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Measure
Type", "S:\Competitor Analysis DB\CA Database\Pokersitescout Data.xls",
0, "Measure"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Group /
Criteria", "S:\Competitor Analysis DB\CA Database\Pokersitescout
Data.xls", 0, "Group/Criteria"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Data
Type", "S:\Competitor Analysis DB\CA Database\Pokersitescout Data.xls",
0, "Data Type"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Currency
/ Format", "S:\Competitor Analysis DB\CA Database\Pokersitescout
Data.xls", 0, "Currency/Format"

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open ("S:\Competitor Analysis DB\CA
Database\Pokersitescout Data.xls")
objExcel.Visible = True
Set objExcel = Nothing

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & err.Number & ": " & err.Description, _
vbCritical, "Error in procedure Pokersitescout_Data_Click..."
Resume ExitProc

End Sub

 

[Mikey C]

I should also have mentioned that my 'Financial / Stats' form also
contains a sub-form (if this matters)