how to run several different functions at form open?

[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

 

[John W. Vinson]

I really have to ask why you're running this code routinely on opening a
database! Normally one would have your tables already constructed with all its
indexes and relationships defined. They just sit there. There'd normally be no
need to change the indexes or relationship repeatedly, certainly not every
time you open the database, unless tbl_Locations is being created anew every
time you use the database (which is not evident ane would not be typical).

What's going on? why are you running this code at all?

That asked... you can simply copy the three lines starting with dbs_Execute
into the second routine (after the line Set db = CurrentDb would be fine), and
delete the first Sub (from Private Sub... through End Sub). However, my
concern is that neither Create is needed at all (or if it is, I'm certainly
not seeing WHY it's needed).

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

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com