D
DoveArrow
This is a question that I've been asking myself for years. I've seen
some answers on various websites. However, I have yet to see one that
is: 1) Easy enough for novice programmers like myself to understand,
and 2) Works.
Below is a solution that I worked out for myself, using a button on a
form and a text box. Basically, it allows users to open the
spreadsheet on their own desktop using a Saved Export. I don't know if
it's the most elegant of solutions, but it seems to work pretty well.
Private Sub OK_Click()
Dim str As String
str = Me.TextBox 'Set string equal to text in TextBox
On Error GoTo Err_OK_Click
If Len(Trim(Nz(Me.TextBox))) = 0 Then 'If there is nothing in TextBox.
MsgBox "Please enter your name.
Me.TextBox.Set Focus
Else
DoCmd.SetWarnings False 'Turn warnings off.
DoCmd.OpenQuery "qdelTable" 'Delete all entries from Table.
DoCmd.OpenQuery "qappTable" 'Append all entries from your query to
Table.
DoCmd.SetWarnings True 'Turn warnings on.
DoCmd.OpenTable "Table" 'Open Table.
DoCmd.RunSavedImportExport str 'Run the saved export for the person
whose name was entered in TextBox.
End If
Close_OK_Click:
DoCmd.Close acForm, "Form" 'Close Form.
Exit Sub
Err_OK_Click:
Select Case Err.Number
Case 2302 'This error will pop up if the Excel Spreadsheet towards
which you are trying to export data is open.
MsgBox "Error 2302: Microsoft Access can't export the query you've
selected to Excel." & vbCr & _
"Please make sure that the Excel file towards which you are trying to
export data is closed."
Case 3349 'This error will pop up if someone has entered text in a
date or number field on the New Admits Excel Spreadsheet.
MsgBox "Error 3349: Data mismatch. Please check the New Admits Excel
Spreadsheet" & vbCr & _
"to make sure that text has not been entered into a number or date
field for your students."
Case 31602 'This error will pop up if there are no saved exports for
the APS name entered.
Select Case MsgBox("No export subroutines exist for " & str & ". Do
you wish to create one?", vbQuestion + vbYesNo)
Case vbYes
DoCmd.RunCommand acCmdExportExcel 'Runs the Export to Excel wizard.
GoTo Close_OK_Click
Case vbNo
MsgBox "You will need to create an export subroutine before you can
export data to Excel."
GoTo Close_OK_Click
End Select
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description
End Select
End Sub
some answers on various websites. However, I have yet to see one that
is: 1) Easy enough for novice programmers like myself to understand,
and 2) Works.
Below is a solution that I worked out for myself, using a button on a
form and a text box. Basically, it allows users to open the
spreadsheet on their own desktop using a Saved Export. I don't know if
it's the most elegant of solutions, but it seems to work pretty well.
Private Sub OK_Click()
Dim str As String
str = Me.TextBox 'Set string equal to text in TextBox
On Error GoTo Err_OK_Click
If Len(Trim(Nz(Me.TextBox))) = 0 Then 'If there is nothing in TextBox.
MsgBox "Please enter your name.
Me.TextBox.Set Focus
Else
DoCmd.SetWarnings False 'Turn warnings off.
DoCmd.OpenQuery "qdelTable" 'Delete all entries from Table.
DoCmd.OpenQuery "qappTable" 'Append all entries from your query to
Table.
DoCmd.SetWarnings True 'Turn warnings on.
DoCmd.OpenTable "Table" 'Open Table.
DoCmd.RunSavedImportExport str 'Run the saved export for the person
whose name was entered in TextBox.
End If
Close_OK_Click:
DoCmd.Close acForm, "Form" 'Close Form.
Exit Sub
Err_OK_Click:
Select Case Err.Number
Case 2302 'This error will pop up if the Excel Spreadsheet towards
which you are trying to export data is open.
MsgBox "Error 2302: Microsoft Access can't export the query you've
selected to Excel." & vbCr & _
"Please make sure that the Excel file towards which you are trying to
export data is closed."
Case 3349 'This error will pop up if someone has entered text in a
date or number field on the New Admits Excel Spreadsheet.
MsgBox "Error 3349: Data mismatch. Please check the New Admits Excel
Spreadsheet" & vbCr & _
"to make sure that text has not been entered into a number or date
field for your students."
Case 31602 'This error will pop up if there are no saved exports for
the APS name entered.
Select Case MsgBox("No export subroutines exist for " & str & ". Do
you wish to create one?", vbQuestion + vbYesNo)
Case vbYes
DoCmd.RunCommand acCmdExportExcel 'Runs the Export to Excel wizard.
GoTo Close_OK_Click
Case vbNo
MsgBox "You will need to create an export subroutine before you can
export data to Excel."
GoTo Close_OK_Click
End Select
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description
End Select
End Sub