Making Table using plain VBA

One of the fundamental tasks that Access users frequently perform is creating tables. By using the techniques in this column, you can add a menu item to Access that lets you create a simple table with just two mouse clicks.
http://office.microsoft.com/en-us/assistance/HA010428181033.aspx

Creating a Clock on your Form

Microsoft Access Database Solutions: Create a working clock on your forms

Looking for that extra special effect to give your Microsoft Access database project a professional flair...Thought about adding a working clock to your Access forms?

To put a simple text clock on a form, create a Label called lblClock on the form, set the form's TimerInterval to 1000, and the following code behind the Timer Event.

You can also create two command buttons called cmdClockStart and cmdClockEnd and attach respective code to each to have the clock run on demand.

'***************** Code Start ***************
Private Sub Form_Timer()
Me!lblClock.Caption = Format(Now, "dddd, mmm d yyyy, hh:mm:ss AMPM")
End Sub

Private Sub cmdClockStart_Click()
Me.TimerInterval = 1000
End Sub

Private Sub cmdClockEnd_Click()
Me.TimerInterval = 0
End Sub
'***************** Code End ***************

How to eliminate white space in reports with CanShrink and code in Access 2002

View products that this article applies to.

Article ID	:	299011
Last Review	:	June 23, 2005
Revision	:	5.0

This article was previously published under Q299011

For a Microsoft Access 97 version of this article, see 95390.

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

For a Microsoft Access 2000 version of this article, see 210589.

On this page

	SUMMARY
	MORE INFORMATION
	REFERENCES
	APPLIES TO

SUMMARY

Before you print a form or a report, you can save space by conditionally reducing the size of the controls on the form or report. For example, you might want to reduce the size of text boxes that contain varied-length text, or reduce the size of text boxes bound to fields that could be empty.

Before you try to reduce the size of controls in reports, however, consider the following:

•	Overlapping controls will not shrink, even when the CanShrink property is set to Yes.
•	Controls shrink line by line (vertically). This means, for example, that if there is a group of controls placed on the left side of the page and a large control (for example, an OLE picture) on the right side of the page, the controls on the left side of the page will not shrink, unless the picture is blank and hidden.
•	Space between controls is not affected by the CanShrink or CanGrow property.
•	Controls located in the page header or page footer can shrink and grow. However, the sections do not have CanGrow and CanShrink properties. This means, you can still have blank space in a page header or page footer section (even though its controls may shrink).

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Back to the top

MORE INFORMATION

A control containing null information automatically shrinks to nothing and disappears when its CanShrink property is set to Yes, but the space between the controls is not affected by the CanShrink property.

For example, say you have 11 controls in a column of a report, and there is .1 inch of space between each control. Even if all 11 controls contain null information and all 11 have their CanShrink property set to Yes, the space between the controls adds up to 1 inch. When you print the report, even though the 11 controls are not printed, the first control that does contain text will be printed 1 inch lower than you might expect because of all the accumulated space.

A good way to manage the size of your controls is to have as few controls as possible. This will minimize the white space between the controls.

The following example demonstrates how to print mailing labels by using a Visual Basic function to minimize the number of controls and to give you even spacing. Say you want to create mailing labels containing a name, business name, address, city, state, and zip code. You could use several different fields, but it would be better to use a single text box, as in the following step-by-step example.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

More info: http://support.microsoft.com/?kbid=299011

Goto Record Command in a Form

This command comes very handy when you want to process the record one by one or test single records against another table.

Private Sub Form_Open(Cancel As Integer)
Dim intNPWCount As Integer

Dim strNPWGID As String
Dim FName As String

FName = Me.Form.Name
intNPWCount = Nz(DCount("[MPGID]", "MPW_Weekly_TimeTracker", "[DeptID]='" & Forms![ManpowerOTPopulationF].[ComboDeptID] & "' And [WKNO]=" & Forms![ManpowerOTPopulationF].[TxtWKNO]), 0)

If intNPWCount <> 0 Then

For A = 1 To intNPWCount

DoCmd.GoToRecord acDataForm, FName, acGoTo, A

‘Other Commands

Next A

Else

Exit Sub

End If

End Sub

How do I log users off a database and keep them off?

The following provides a methodology to allow the developer to schedule and enforce periods of downtime for any number of databases.

Define a database, ie, TimeCtl. All users should have read access to the database, but must not be able to change/move/delete the database or anything within the database.
Define a table, tblLogOut on the TimeCtl database having the following fields per row.
--ApplicationName text(50) primary key
--LogoffStart date/time
--LogoffEnd datetime
--Inactive Boolean

you will have one row for every active database. The application name is the database name only without the mdb. It is not the full path name. For example, if you database is DrugCourt.mdb the applicationname on tblLogOut is DrugCourt.
LogoffStart is the date and time you want to insure that all users cannot access applicationname. LogoffEnd is the date and time the users can begin to use applicationname again.
If the inactive flag is true, the record will never be checked again. This allows you to keep a historical track of your scheduled down time.

In TimeCtl, create a module called gfunctions (global functions). Cut and paste the following function into gfunctions.



Public Function funShutDown(DatabaseName As String) As Boolean: funShutDown = False

Dim db As Database
Dim rs As Recordset
Dim strSQL As String

DatabaseName = UCase(DatabaseName)
Dim myDate As Date: myDate = Now()
strSQL = "SELECT * FROM TBLLOGOUT WHERE APPLICATIONNAME = '" _
& DatabaseName & "' AND INACTIVE = 0 AND #" & myDate & "# BETWEEN LOGOFFSTART AND LOGOFFEND"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount = 0 Then
GoTo OutShutDown
Else
funShutDown = True
End If

OutShutDown:
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

End Function


WATCH OUT FOR WORD WRAP.

On each database that is now represented on tblogout, establish a reference to the timecontrol database. This will allow you to call funShutDown from that database.
Link tbllogout to your database. Again, we don’t care if the user can see this table as long as he can’t do anything else but see information.

In your application database create an unbound form with one textbox. Place the following code on the open form event.


Private Sub Form_Open(Cancel As Integer)

If funShutdown(UCase("drug court")) = True Then
Application.Quit
End If

End Sub

This insures that a user who attempts to log on during the scheduled down time will fail to do so.

Set the forms timer interval to 300000 (5 minutes). The following is the forms timer event.

Private Sub Form_Timer()

If funShutdown("drug court") = True Then
Application.Quit
End If

End Sub

In the startup routine of your database, open this form hidden.

Every 5 minutes, your database will test if it should shut down. If down time is scheduled it will shut down within 5 minutes of the downtime start time.

Reference: http://www.tek-tips.com/faqs.cfm?fid=2145

Hide a table in Database Window

(Q) Can I hide a table so that it doesn't appear in the Database window?

(A) If you want to hide a table in the database windows so that other users can't select it, preface the table's name with 'Usys'. For example, if your table's name is employee, change it to UsysEmployee. This makes your table into a system object which access does not display. To see this table later, choose view, options, show system objects.

More Reference: The Access Web

Upper Case Function in access: The upper case function of MS Access is done by issuing the prefix command UCase before the field you want to change into upper case. 

Removing Filter of a Form through VBA

Procedure for Preset Filters:

Private Sub CMDRemoveFilter_Click()
Me.Filter = ""
End Sub

Explanation:
Double quote removes the filter.

Filtering a Form through VBA

Procedure for Preset Filters:

Private Sub CMDNullSalaries_Click()

Dim strNull As String

strNull = "(([Salary Query Form].[HR Salary] Is Null))"

Me.Form.Filter = strNull

Me.FilterOn = True

End Sub

Explanation:

Form “Salary Query Form” will display only the Record of whose “HR Salary” field contains nothing.

Procedure for filters passed from a Combo List:

Private Sub CMDFilter_Click()

On Error GoTo Err_CMDFilter_Click

Dim vFilter As String

vFilter = Forms![Salary Query Form]![DeptFilterCombo]

Me.Filter = "DeptID = '" & vFilter & "'"

Me.FilterOn = True

Exit_CMDFilter_Click:

Exit Sub

Err_CMDFilter_Click:

MsgBox Err.Description

Resume Exit_CMDFilter_Click

End Sub

Explanation:

1. vFilter variable is declared.

2. vFilter variable takes the value from Forms![Salary Query Form]![DeptFilterCombo]

3. DeptID Field will then be filtered with the value from vFilter variable.

NOTE: If the variable contains string, then vFilter should be enclosed by single quote.

"DeptID = '" & vFilter & "'"

4. Declare Me.FilterOn as True. This is to activate filter.

Passing Value from Access to Excel Range. The following blog will share to you how the File location directory will be passed to a Range in an Excel Spreadsheet. 

Passing Value from Access to Excel Range

Here is a simple way of passing a value from MS Access to an Excel File that is being opened by MS Access.

Situation: I have an MS Access Database form(A) which exports a query(B) to a certain directory as an excel file. This query is going to be used by an Excel Spreadsheet(C) which is preformatted for calculation and reporting. Now, as I open the preformatted Excel Spreadsheet(C) from Access(A) I would like to pass the directory location where I saved the query as xls (B) to SpreadSheet(C). This directory will be then used by Excel to know where to automatically pick-up the xls query(B) so that it can create pivot tables and other reports from (B).

(A) MS Access Form: This form has fields with directory location assigned as Me.DirLoc
(B) Query exported as XLS
(C) Preformatted XLS with loaded Macro to auto-fetch (B)

Private Sub CmdRunRegenerator_Click()
Dim DistDir
Dim ReportCreator As String

ReportCreator = Me.Regenerator
DestDir = Me.DIRLoc
Dim objXL As Object, x
On Error Resume Next
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
'Open the Workbook
.Workbooks.Open DestDir & "\" & ReportCreator
'Include CARMA in menu, run AutoOpen
.ActiveWorkbook.RunAutoMacros xlAutoOpen
.ActiveWorkbook.Sheets("Sheet1").Range("D9").Value = Me.DIRLoc
x = .Run("RegenerateEmpFile")
.ActiveWorkbook.Save
.ActiveWorkbook.Close
objXL.Quit
'AccountsViewEngine
'Auto_Open
End With
Set objXL = Nothing
UpdateSource
ClearInputOutputFiles
MsgBox ("Update completed and imported xls file cleared.")
End Sub

NOTE: The line highlighted in red is the command to pass the value of the Directory location in the MS Access form to the spreadsheet being opened.

Access Data Definition Language - Creating and Altering Tables On The Fly

One of the first things an Access developer learns is how to write SQL Queries. A simple request for data might look something like this:
SELECT FullName, Address, Phone
FROM tblEmployee ORDER BY FullName
This type of query uses Data Manipulation Language (DML). While this example does not perform any great manipulation of the data, some very clever calculations are possible. The SQL language is powerful indeed.
However, once you have gotten a handle on DML, you will want to explore the world of DDL, Data Definition Language. This too is part of the SQL language and can be run from an Access query, or through VBA code, but unlike DML, these commands do not return a result set. DDL is used to create and alter database objects, such as tables. First, let's see how this is done and then consider some scenarios where this technique will save you time and in some cases, a lot of work.

The process to create a DDL query is a little different from what you might be used to. Begin in the normal way, by choosing New Query from the Query window, but when prompted, do not add any tables. You are then presented with the QBE (query by example) grid with no tables. Select Data Definition from the Query | SQL Specific menu to continue.

Selecting Data Definition will take you to a very plain, Notepad-like interface where you can paste or type in your DDL SQL script. Save the query and select Run from the Query menu to execute the script. When you run this kind of query, Microsoft Access displays the following warning:

PROBLEM:An employee table needs to have their salary updated based from an imported excel file containing the Global ID of the Employees with matching Salary changes. When importing the Excel spreadsheet it automatically set the data type of the Global ID field into numbers. This creates an error when doing an update because the Access table to be updated contains a Global ID field that is preset to data type text.

SUGGESTION:Change the Global ID Data type of the imported Excel Spreadsheet into Text on the fly, before executing the update command.

SOLUTION:
1. Invoke a query. From Object Window click Queries, Click New, Design View, OK, Close, Click Query Grid. From the Main Menu click Query, SQL Specific, Data Definition.
2. Inside the query window Type: ALTER TABLE [Source Table] ALTER COLUMN GID Text;
3. Save the query. With my example I saved it as AlterSourceTableQ
4. Call the query inside your procedure:

Private Sub CMDImportEmpFile_Click()
Dim SQL As String
DoCmd.SetWarnings False

SQL = "DELETE [Source Table].* " & _
"FROM [Source Table];"

DoCmd.RunSQL SQL


DoCmd.OpenQuery "AlterSourceTableQ"
DoCmd.OpenQuery "UpdateSourceFromImportedFileQ"
DoCmd.SetWarnings True
End Sub

To find out more....Click here

Opening Excel from MS Access thru VBA

This procedure opens up the excel sheet from a given directory and run a macro from within excel.

Private Sub CmdOpenframedetailedCombiner_Click()
Dim strPath As String
Dim strFileName As String
Dim strSourceDir As String
Dim strSheetName As String
Dim xlApp As Object
Dim xlSheet As Object

strSourceDir = "\\zwnwb080\Groups3\CDMAProd\PRODUCTION SERVICE SHEETS\daily web plan\"
strFileName = "frame and detailed combiner v7 with GSM Update.xls"
strPath = strSourceDir & strFileName
strSheetName = "detailed"

Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.Workbooks.Open strPath
.Run "UpdateDetailedFromForXlsdetailedQ"
End With
End Sub

Exporting Query to Excel using VBA

Before you perform the export operation you have to follow simple steps to produce the right output you want:

1. IDENTIFY Source Query.
The Query is called : ExpenseSummarySelectQ
We will assign a variable called: stDocName for ExpenseSummarySelectQ

2. IDENTIFY Output File and Directory.
The output Excel file will be called: ExpenseSummarySelect 04-11-05.xls
The directory this file will be exported is: \\zwnwb080\Expense
3. SET VARIABLES and their types.
"ExpenseSummarySelectQ" = stDocName type String
"ExpenseSummary" = FileName type String
"04-11-05" = tDate As String
"\\zwnwb080\Expense\" = FileDir As String

To combine the filename and Directory like this:
\\zwnwb080\Expense\ExpenseSummary 04-11-05.xls
You need to combine variables too:
XFile = FileDir & FileName & tDate & " .xls"

6. Compose your Procedure.
Private Sub CMDRunExpSummarySelectQ_Click()
Dim stDocName As String
Dim FileName As String
Dim XFile As String
Dim tDate As String
Dim FileDir As String

stDocName = "ExpenseSummarySelectQ"
FileName = "ExpenseSummary"
tDate = Format(Now(), "mm-dd-yy")
FileDir = "\\zwnwb080\"
xFile = FileDir & FileName & tDate & " .xls"

DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.OutputTo acOutputQuery, "ExpenseSummarySelectQ", _
acFormatXLS, xFile, False
DoCmd.Close acQuery, "ExpenseSummarySelectQ", acSaveNo
MsgBox (xFile &amp; " in " & FileDir & " had been created. Excel Report will now be created.")

End Sub