A18.3 Call to Access Macro - LUPMISManual

A18.3 Call to MS Access Macro

Level of expertise required for this Chapter: Expert / Programmer; general Map Maker training

This approach  is more flexible and can also load reports, but also functional buttons, macros, modules etc. It is more resource hungry, i.e. slower.

In general: Map Maker: Send macro name and variable (UPN) --> Access: Run macro --> Call module (reading variable) --> Report (with condition)

1. Security settings must allow access (see Annex 1.1.12).

- - - -

2. Open (or create) an Access database (MDB file), as explained in step 2 of  Annex 18.2.

- - - -

3. Main table (tbl_parcel) must have the same structure as explained under step 3 of  Annex 18.2.

• ‘UPN’ (text, field size:13)
  

• ‘ID’ (autonumber, as primary key)
  

• Additional fields are possible.
  

Make sure, that the field ‘UPN’ contains the same UPN numbers as the field ‘ID’ of the related DRA file. (See also Annex 10 on data standards).

- - - -

4. Main output report (rpt_parcel) must have ‘tbl_parcel’ as the main data source: Reports: rpt_parcel (might have to be created first) > right-mouse: Design View > top left: Right-mouse > Properties > Property sheet window: Data > Record source: tbl_parcel > Close it

All other operations are standard Access operations.

- - - -

5. Create module (mdl_parcel) using standard Access commands (Database Tools > Visual Basic >>>).

Modules: mdl_parcel (as just created) > right-mouse: Design View > Insert following 2 functions

Public Function GetCmdLine()                                                         
   GetCmdLine = Command                                                             
End Function                                                                                       

Public Function MainParcel1()                                                          
   Dim GetCmdLine2 As String                                                        
   Dim Identifier, stLinkCriteria As String                                         
   GetCmdLine2 = GetCmdLine()                                                      
   stDocName = "rpt_parcel"                                                              
   stLinkCriteria = "[tbl_parcel]![UPN]='" & [GetCmdLine2] & "'"    
   DoCmd.OpenReport stDocName, 2, , stLinkCriteria                   
End Function                                                                                           

Close and save it.

- - - -

6. Create macro (mac_parcel) using Access commands (Create > Macro > Macro): Macros: mac_parcel (as just created) > right-mouse: Design View > Under action select RunCode > Function Name: Expression Builder window: Functions > mdf file > mdl_parcel > MainParcel1 > OK > Close and save it

- - - -

7. The Access database (MDB file) should not have a startup function (see step 1 of  Annex 18.2: Disabled).

- - - -

8. Map Maker must have following data link command line (see Chapter 6.9):
<access> “C:\LUPMIS\Permits\Area\Area_permits.mdb” /x mac_parcel /cmd “<label>”
where Area is the name of the community

For example:
<access> “C:\LUPMIS\Permits\Kasoa\Kasoa_permits.mdb” /x mac_parcel /cmd “<label>”