none
how do i write code to have a procedure run once? i have code that creates tables used to track items when user uses accdb file. RRS feed

  • Question

  • I have procedures that create a table, a form with controls.  That must create only one table and one form.

    I have not figured out how to have my code run only once, no matter how many times the Access accdb file is opened or run.

    AutoExec i am using to run code everytime the access file starts to set options. seems that code would add a table each time accdb ran. end up with too many tables.

    1. one idea I had was from about 10 yrs ago, i think i saw a statement in vba or a function built-in to vba that would delete a module.  idea was to have code creating the table, then after table is created, code would run to delete the module with table creating code in it. I know then the calling code would have to deal with calling a procedure that no longer exists. I think I can trap for that, not sure.

     2. a differnt idea, have a "hidden" form created only if code to create the table runs. And have code that creates the table FIRST check for a value in a texbox on hidden form. If finds the value DO NOT create the table.

    3. think i can insert a text string in a module. i know i can get count of all lines, and count lines in procedures. and after run code that creates the table runs, last bit would insert 'exit sub" above code that creates the table.

    seems there must be a better way than my 3 ideas.

    i will be very greatful for any help or hints of things to try.

    i am running win 10 pro for workstations, 64 bit, ver 1809, access in office pro 2016

    Mark J


    Mark J

    Friday, June 7, 2019 1:19 AM

Answers

  • Here are two functions that will allow you to check if a table or a form has been created in the current database:

    ' Checks if a table by name exists in the current database.
    ' Returns True if it does, False if not.
    '
    ' 2011-10-10. Cactus Data ApS, Gustav Brock
    '
    Public Function IsTable(ByVal TableName As String) As Boolean
    
        Dim Table   As DAO.TableDef
        
        For Each Table In CurrentDb.TableDefs
            If Table.Name = TableName Then
                Exit For
            End If
        Next
        
        IsTable = Not Table Is Nothing
        
    End Function
    
    ' Checks if a form by name exists in the current database.
    ' Returns True if it does, False if not.
    '
    ' 2011-10-10. Cactus Data ApS, Gustav Brock
    '
    Public Function IsForm(ByVal FormName As String) As Boolean
    
        Dim Form    As Object
        
        For Each Form In CurrentProject.AllForms
            If Form.Name = FormName Then
                Exit For
            End If
        Next
        
        IsForm = Not Form Is Nothing
    
    End Function
    


    Gustav Brock

    • Marked as answer by PuzzledByWord Sunday, June 9, 2019 10:31 PM
    Sunday, June 9, 2019 6:56 AM
  • Alternatively, a little bit shorter,

    Function IsTable(strTableName As String) As Boolean
        Dim lngType As Long
        On Error Resume Next
        lngType = CurrentData.AllTables(strTableName).Type
        IsTable = (Err = 0)
    End Function

    Function IsForm(strFormName As String) As Boolean
        Dim lngType As Long
        On Error Resume Next
        lngType = CurrentProject.AllForms(strFormName).Type
        IsForm = (Err = 0)
    End Function

    Jan

    • Marked as answer by PuzzledByWord Sunday, June 9, 2019 10:33 PM
    Sunday, June 9, 2019 11:42 AM
  • still I thank you for your info. your way to check for IF EXIST is much better than my ideas.

    Hi PuzzledByWord,

    I use a function Table_exists almost the same as Jan has given in his function IsTable. But I must admit, the way Jan's function works, is a little bit sharper then mine, especially the: On Error Resume Next. My compliments.

    For forms I am hardly interested in AllForms, because I only use a few basic (generalised) forms for all my applications. But many times I need to know if a form is opened. For that purpose I use a function comparable to Jan's function IsForm, but only using the Forms-collection.

    Imb.

    • Marked as answer by PuzzledByWord Monday, June 10, 2019 11:24 PM
    Monday, June 10, 2019 8:18 AM

All replies

  • i will be very greatful for any help or hints of things to try.

    Hi Mark,

    An alternative could be to check whether table already exists.

    If not, create the table etc.

    But I am not sure if this is what you mean, because in all your options you are generating/deleting code.

    Imb.

    Friday, June 7, 2019 5:24 AM
  • You are overcomplicating a simple task.

    First, check if the table and form exist. If they do, cancel the creation of the table and form.


    Gustav Brock

    Saturday, June 8, 2019 10:28 AM
  • Gustav Brock,

    you are very correct that the ideas I had were overcomplicating the task.

    I was hoping that there was a better way than my ideas.  

    as you did point out.

    now how do I check by code not by eyeball if the table or form exist?

    this will be used by a user NOT computer savvy person.  that does not have permissions to make that call. 

    your help will solve the issues of creating the form or table once.

    that only leaves the other code that that is used in the setup. but does not create a table or form.

    Thank you for your info.

    Mark J


    Mark J

    Saturday, June 8, 2019 10:50 PM
  • Imb-hb,

    Checking if a table or form exists is a better way,  if I knew how to check if the table or form exist. 

    I am guessing I would use a IF THAN statement.    do not know how to set that IF to check for the table exist or how to do it for a form.

    I do thank you for getting back to me so soon with a better way than I had come up with.

    I STILL HAVE OTHER CODE THAT DOES     NOT      create a table or form to check for.  and is more similar to code that is to run once like in "install" code.

    still I thank you for your info. your way to check for IF EXIST is much better than my ideas.

    thanks,

    Mark J


    Mark J

    Saturday, June 8, 2019 11:00 PM
  • Here are two functions that will allow you to check if a table or a form has been created in the current database:

    ' Checks if a table by name exists in the current database.
    ' Returns True if it does, False if not.
    '
    ' 2011-10-10. Cactus Data ApS, Gustav Brock
    '
    Public Function IsTable(ByVal TableName As String) As Boolean
    
        Dim Table   As DAO.TableDef
        
        For Each Table In CurrentDb.TableDefs
            If Table.Name = TableName Then
                Exit For
            End If
        Next
        
        IsTable = Not Table Is Nothing
        
    End Function
    
    ' Checks if a form by name exists in the current database.
    ' Returns True if it does, False if not.
    '
    ' 2011-10-10. Cactus Data ApS, Gustav Brock
    '
    Public Function IsForm(ByVal FormName As String) As Boolean
    
        Dim Form    As Object
        
        For Each Form In CurrentProject.AllForms
            If Form.Name = FormName Then
                Exit For
            End If
        Next
        
        IsForm = Not Form Is Nothing
    
    End Function
    


    Gustav Brock

    • Marked as answer by PuzzledByWord Sunday, June 9, 2019 10:31 PM
    Sunday, June 9, 2019 6:56 AM
  • Alternatively, a little bit shorter,

    Function IsTable(strTableName As String) As Boolean
        Dim lngType As Long
        On Error Resume Next
        lngType = CurrentData.AllTables(strTableName).Type
        IsTable = (Err = 0)
    End Function

    Function IsForm(strFormName As String) As Boolean
        Dim lngType As Long
        On Error Resume Next
        lngType = CurrentProject.AllForms(strFormName).Type
        IsForm = (Err = 0)
    End Function

    Jan

    • Marked as answer by PuzzledByWord Sunday, June 9, 2019 10:33 PM
    Sunday, June 9, 2019 11:42 AM
  • Gustav Brock,

    thank you.

    it solves the testing for a table or form.

    it gives me a idea on how to check on the other code that does not create a table or form.

    thanks for the help.

    Mark J


    Mark J

    Sunday, June 9, 2019 10:30 PM
  • Jan,

    thanks, the less I type, the less typos I make.

    this does solve the issue of is a table or form created or not.

    thanks,

    Mark J


    Mark J

    • Marked as answer by PuzzledByWord Sunday, June 9, 2019 10:33 PM
    • Unmarked as answer by PuzzledByWord Sunday, June 9, 2019 10:33 PM
    Sunday, June 9, 2019 10:33 PM
  • still I thank you for your info. your way to check for IF EXIST is much better than my ideas.

    Hi PuzzledByWord,

    I use a function Table_exists almost the same as Jan has given in his function IsTable. But I must admit, the way Jan's function works, is a little bit sharper then mine, especially the: On Error Resume Next. My compliments.

    For forms I am hardly interested in AllForms, because I only use a few basic (generalised) forms for all my applications. But many times I need to know if a form is opened. For that purpose I use a function comparable to Jan's function IsForm, but only using the Forms-collection.

    Imb.

    • Marked as answer by PuzzledByWord Monday, June 10, 2019 11:24 PM
    Monday, June 10, 2019 8:18 AM