how to run several different functions at form open? (1 Viewer)

[tpcolson]

I'm not any sort of programmer at all, and I managed to cobble together the following bits of code in order to create an index and a relationship attached to two buttons. I need for the two functions to somehow be merged into one function and attached to formopen? Form open allready has a good bit of vba attached to it, not sure how I'd nest additional functions in it. One of the functions is private, one is public, not sure how to change the syntax of the public one to run as "private". Thanks!

Private Sub CreateIndex_Click()
    Dim dbs As Database
    Set dbs = CurrentDb
    dbs.Execute "CREATE UNIQUE INDEX Location_ID " _
        & "ON tbl_Locations (Location_ID) " _
        & "WITH PRIMARY;"
 
    dbs.Close
 
End Sub

Option Compare Database
' =================================
' Create 1 to m relationship from tbl_Locations to tbl_Events
Public Function CreateRelationship_Click()
    Dim db As DAO.Database
    Dim tdf1 As DAO.TableDef
    Dim tdf2 As DAO.TableDef
    Dim rels As DAO.Relations
    Dim rel As DAO.Relation
 
        Set db = CurrentDb
        Set tdf1 = db.TableDefs("tbl_Locations")
        Set tdf2 = db.TableDefs("tbl_Events")
        Set rels = db.Relations
 
                For Each rel In rels
                    If rel.Name = "myRelationship" Then
                        rels.Delete ("myRelationship")
                    End If
                Next
 
                Set rel = db.CreateRelation("myRelationship", tdf1.Name, tdf2.Name, dbRelationUpdateCascade + dbRelationDeleteCascade)
 
                rel.Fields.Append rel.CreateField("Location_ID")
                rel.Fields("Location_ID").ForeignName = "Location_ID"
                rels.Append rel
 
        Set rels = Nothing
        Set tdf = Nothing
        Set tdf2 = Nothing
        Set db = Nothing
End Function

 

[Galaxiom]

Public functions and subs also work in the private context.

Generally if you have a lot of stuff happening it is best to put each procedure in a separate sub and then just call them from the event procedure. Just add a line for each call using the name of the sub of function and any arguments.

 

[tpcolson]

I tried the following, with numerous errors. Thanks for the tip though! can you clarify just how one would put multiple procedures in sepearate subs and call them from one event procedure? I'm not an expert programmer by any means, and only came up with the original code after hours of hacking. Thanks!

Private Sub cmdMakeRel_Click()
    Dim db As DAO.Database
    Dim tdf1 As DAO.TableDef
    Dim tdf2 As DAO.TableDef
    Dim rels As DAO.Relations
    Dim rel As DAO.Relation
    Dim dbs As Database
        Set dbs = CurrentDb
        Set db = CurrentDb
        Set tdf1 = db.TableDefs("tbl_Locations")
        Set tdf2 = db.TableDefs("tbl_Events")
        Set rels = db.Relations
        
                For Each rel In rels
                    If rel.Name = "myRelationship" Then
                        rels.Delete ("myRelationship")
                    End If
                Next
                
                Set rel = db.CreateRelation("myRelationship", tdf1.Name, tdf2.Name, dbRelationUpdateCascade + dbRelationDeleteCascade)
                
                rel.Fields.Append rel.CreateField("Location_ID")
                rel.Fields("Location_ID").ForeignName = "Location_ID"
                rels.Append rel
        
        Set rels = Nothing
        Set tdf = Nothing
        Set tdf2 = Nothing
        Set db = Nothing
        dbs.Execute "CREATE UNIQUE INDEX Location_ID " _
        & "ON tbl_Locations (Location_ID) " _
        & "WITH PRIMARY;"
       
    dbs.Close
End Sub

Public functions and subs also work in the private context.

Generally if you have a lot of stuff happening it is best to put each procedure in a separate sub and then just call them from the event procedure. Just add a line for each call using the name of the sub of function and any arguments.

 

[Galaxiom]

You need to Set the rels object before you try to use it.

Private Sub Form_Load()
   MyOtherSub
   AnotherSub
End Sub
 
Private Sub MyOtherSub()
   ' do stuff
End Sub
 
Private Sub AnotherSub()
   ' do other stuff
End Sub

You should also use the dbFailOnError argument with the Execute Method otherwise failures slip quietly by without telling you.

 

[tpcolson]

Hi thanks for the advice! The rels object worked fine when it was wrapped in it's own function, based on your reply, I tried the following, to no avail. Testing it with a button first.

Private Sub cmdMakeRel_Click()
Private Sub MakeIndex()
    Dim dbs As Database
        Set dbs = CurrentDb
        dbs.Execute "CREATE UNIQUE INDEX Location_ID " _
        & "ON tbl_Locations (Location_ID) " _
        & "WITH PRIMARY;"
       
    dbs.Close
End Sub
    Private Sub CreateRel()
    Dim db As DAO.Database
    Dim tdf1 As DAO.TableDef
    Dim tdf2 As DAO.TableDef
    Dim rels As DAO.Relations
    Dim rel As DAO.Relation
        Set db = CurrentDb
        Set tdf1 = db.TableDefs("tbl_Locations")
        Set tdf2 = db.TableDefs("tbl_Events")
        Set rels = db.Relations
        
                For Each rel In rels
                    If rel.Name = "myRelationship" Then
                        rels.Delete ("myRelationship")
                    End If
                Next
                
                Set rel = db.CreateRelation("myRelationship", tdf1.Name, tdf2.Name, dbRelationUpdateCascade + dbRelationDeleteCascade)
                
                rel.Fields.Append rel.CreateField("Location_ID")
                rel.Fields("Location_ID").ForeignName = "Location_ID"
                rels.Append rel
        
        Set rels = Nothing
        Set tdf = Nothing
        Set tdf2 = Nothing
        Set db = Nothing
        End Sub
        
    
End Sub

 

[Galaxiom]

You can't nest subs like that. You have to get to an End Sub before you can define another sub.

You can only call a different sub by using its name and arguments from within another sub like I did in the notional example.

My comment about the Set was from misunderstanding what you were doing after too quick a glance at the code.

I have not created a relationship using VBA so I am not really much help on that. However, intuitively this line looks suspect to me:

rel.Fields.Append rel.CreateField("Location_ID")

What errors are you getting on which lines?