Access Database Tips and Tricks

I was supporting this dying application along with my other work on SRGT. Since RGC was dying. they wanted to decomission it and wanted me to develop a small vba application for users to be able to view the data.

grr.. I'll be glad to work in java but VBA is totally new to me.. so I am learning and posting what I learn here

for everyones ref incuding me :D

To show another Form from the current Form
DoCmd.OpenForm "frmEmployeeMain"

Form is accessed by ME
ie., to ref any form variable in access, use ME.


Passing variable from one Form to Another

syntax of accessing: Forms![formname]![fieldname]

Dim strSun As String

strSun = Forms!CustomeSearchForm!customerName

Showing an alert msg.

Alert msgs are great way to debug an app. Here is how to show 1 in access

yourMsg = MsgBox(strSun, 1, strEmpCount)

Creating welcome screen:

1) In your design window... go to tools --> Startup --> select the form / page you want to use for startup

2) You can also choose to hide the database window here.

When you have a welcome screen, press F11 to see the database window instantly


Wild card search with parameters:

uery parameter coming from another form : [Forms]![CustomeSearchForm]![customerName]

TO add wild card search to the above parameter just add "*" & [query params] &"*"

Like "*" & [Forms]![CustomeSearchForm]![customerName] & "*"

Equi Join vs OuterJoin

Instead of using equi join use the outer join to display all the data from table 1 though the
mapping information is missing in the child table or table 2.Just rt click on the join to change the join properties.

avoiding ghostly grey pages with graceful error messages.

Catching errors on page load.

Private Sub Form_Load()
On Error GoTo Err_search_Click

strSun = Me.id_rgc_customer

Exit_search_Click:
Exit Sub

Err_search_Click:

MsgBox "No Search Results Found. Please Refine Your Search."
DoCmd.Close

End Sub

Adding other tips I have learnt on printing and executing queries from access VBA

Close all other forms from an existing Form:
Just refer them by Form Name

DoCmd.Close acForm, "OldFormName"

But there is also an efficient way of doing it. I am just lazy but saw it one of my google searches y'day.

Executing Queries in Access VBA:

Dim db As DAO.Database

When you define your Database variable.. please make sure.. you append it with DAO.I did this stupid mistake of not adding DAO. yesterday and you can see my frustration
in my prev post.

Its just that Access defaults it to ADO and not DAO.. so I kept getting type mis match errors GRRRR.. I knew I passed by the hell y'day. SO DO NOT do the same mistake I did.

So being cautious define all ur Record sets etc.,

Dim db As DAO.Database
Dim facList As DAO.Recordset
Dim sptList As DAO.Recordset
Dim qryPar As DAO.QueryDef
Dim qrySpt As DAO.QueryDef

Next step is setting ur database to current database
Set db = CurrentDb
Set qrySpt = db.QueryDefs("myDefinedQry")

printRGCFacilityQuery is the query you must have defined in ur queries tab already

If your query takes input parameter.. set them here. If ur query expects i/p parms
and if u didnt set them already you will get too few parameters error.

'Set parameters

qrySpt.Parameters(0) = facList!fi_id_facility
qrySpt.Parameters(1) = Me.id_rgc_customer

'Execute Query
Set sptList = qrySpt.OpenRecordset

If (sptList.RecordCount > 0) Then

sptList.MoveFirst

Do Until facList.EOF

'Your Biz Logic goes here

End If
sptList.MoveNext
Loop

'Its always a good practice to close your connections after ur done w/ your processing.
qrySpt.Close
Set qrySpt = Nothing


Printing Other pages from the existing page:
'Open Form
DoCmd.OpenForm "printFacSupportForm", acPreview
'Print Form
DoCmd.RunCommand acCmdPrint
'Close Form
DoCmd.Close acForm, "printFacSupportForm"

'Open the form ur printing from

Me.Form.GoToPage 1



Happy Coding :D
-Sonu