Code
Here are some code tips for your add-in.
Open a menu form
To open a form, specify the function name
to run in the USysRegInfo table.
In this case, Expression would be:
=LaunchMenu()
Function LaunchMenu()
'200429 strive4peace
DoCmd.OpenForm "MyMenuFormname"
End Function
Refer to database
Anything you can do with a database object,
like use a TableDef or OpenRecordset,
will be done in whatever database you refer to.
— current database
To refer to the current database, use
Dim db As DAO.Database
Set db = CurrentDb
'do stuff
Set db = Nothing
— code database
To refer to the code database, use
Dim db As DAO.Database
Set db = CodeDb
'do stuff
Set db = Nothing
Make a blank database
'make a blank database
DBEngine.CreateDatabase sPathFileDatabase,dbLangGeneral
Link to table in another database
Function Link2TableOtherDatabase(psPathFileDatabase As String _
,psTablename As String)
'strive4peace
'When you are using Make Table and Append queries,
'use the optional IN clause to specify the path and filename of an external database.
'Then use this procedure to link to the table
'CALLS
' DropTheTable
Dim db As DAO.Database _
,tdf As DAO.TableDef
'set db to be the current database
Set db = CurrentDb
'if table is already in the current database, delete it
Call DropTheTable(psTablename, db)
'link to table
With db
Set tdf = .CreateTableDef(psTablename)
tdf.Connect = ";Database=" & psPathFileDatabase
tdf.SourceTableName = psTablename
.TableDefs.Append tdf
.TableDefs.Refresh
End With
'release object variables
Set tdf = Nothing
Set db = Nothing
End Function
Here for code to
DropTheTable,
which is called by Link2TableOtherDatabase.
It deletes a table if it is
in the passed or current database.
Use CurrentProject to loop forms or reports
in the Current database
Dim AccessObject As AccessObject _
,Obj As Object
Dim i As Integer _
,iCount As Integer _
' --- Forms
iCount = CurrentProject.AllForms.Count
For i = 0 To iCount-1
Set AccessObject = CurrentProject.AllForms(i)
DoCmd.OpenForm AccessObject.Name,acViewDesign
Set Obj = Forms(AccessObject.Name)
'do stuff
DoCmd.Close acForm,AccessObject.Name,acSaveNo
Next i
' --- Reports
iCount = CurrentProject.AllReports.Count
For i = 0 To iCount-1
Set AccessObject CurrentProject.AllReports(i)
DoCmd.OpenReport AccessObject.Name,acViewDesign
Set Obj = Reports(AccessObject.Name)
'do stuff
DoCmd.Close acReport,AccessObject.Name,acSaveNo
Next i
'release object variables
Set AccessObject = Nothing
Set Obj = Nothing
