Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Stop button code if condition is met in Module.

Posted on 2010-08-25
4
Medium Priority
?
431 Views
Last Modified: 2013-11-27
Sorry about the title, wasn't sure how to say it. I have a module that verifies a table has data:
 
Public Sub CheckTable()
    Dim fIsTableEmpty As Boolean
    Dim db As Database
    Dim rs As Recordset
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset("Scrap", dbOpenTable)
    If rs.RecordCount = 0 Then
        fIsTableEmpty = True
        MsgBox "Nothing in the table, Please import Cognos report"
        On Error GoTo Error_Handler
    Dim fdg As FileDialog, vrtSelectedItem As Variant
    Dim strSelectedFile As String
    
    Set fdg = Application.FileDialog(msoFileDialogFilePicker)
    
    With fdg
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls"
        .Filters.Add "Excel 2007", "*.xlsx"
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewDetails
        If .Show = -1 Then
            For Each vrtSelectedItem In .SelectedItems
            strSelectedFile = vrtSelectedItem
        Next vrtSelectedItem
        Else
        End If
'        If Not IsEmpty(strSelectedFile) Then
        If Len(Trim(strSelectedFile) & "") > 0 Then
        Dim StrSQL As String
        StrSQL = "Delete * from Scrap;"
        DoCmd.SetWarnings False
        DoCmd.RunSQL StrSQL
        DoCmd.SetWarnings True
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Scrap", strSelectedFile, True, , False
        Else
        DoCmd.OpenForm "FileNotSelected", acNormal, , , , , False
        End If
        
        

        
    End With
        Set fd = Nothing
Exit_Procedure:
     Exit Sub

Open in new window


I call it from a form button:
Private Sub btnTotal_Click()
        CheckTable
        DoCmd.Hourglass True
        'Turns off the Access warning messages
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "TotalScrapQry"
        DoCmd.Hourglass False
        'Turns the Access warning messages back on
        DoCmd.SetWarnings True
        
        
        DoCmd.OpenForm "ByTotalScrap", acNormal, , , , , False
End Sub

Open in new window


What I want to happen is if the module finds that there is no data to stop running the button code on the form. Instead, it resumes back and continues to finish the button code and open the 'ByTotalScrap' form. That is fine if the user selects an Excel file from within the Module code, but if the users clicks the Cancel button when selecting an Excel file the form is still opened and there is no data. Hope that makes sense. Thanks for any help on this.
0
Comment
Question by:G Scott
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33521434
I think what you need is a global variable in a module which is set to a default value  during button processing pre form open.  If the user processes the form normally then it is set to a 'good' state as part of the processing and if the user cancels it stays in the default.

IN your button code you test teh state of this flag in order to take some action i.e. mode the normal/scrap function.

Make sense?

Chris
0
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 400 total points
ID: 33521486
You could make CheckTable a function instead of a Sub, with a Boolean return value.  Set it to False if the table is empty, and True otherwise.  Then check its value in the btnTotal_Click procedure, so you can exit if the table is empty.
0
 
LVL 10

Accepted Solution

by:
dwe761 earned 1600 total points
ID: 33521492
You could change CheckTable() to a function and return True if table has data.  Then your button click code would be something like this:
Private Sub btnTotal_Click()
      IF  CheckTable Then
        DoCmd.Hourglass True
        'Turns off the Access warning messages
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "TotalScrapQry"
        DoCmd.Hourglass False
        'Turns the Access warning messages back on
        DoCmd.SetWarnings True
        
        
        DoCmd.OpenForm "ByTotalScrap", acNormal, , , , , False
     End If
End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:G Scott
ID: 33522362
Thanks dwe761. That worked!
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question