Posts filed under ‘Ms Access’

My Amazon Store


May 7, 2009 at 3:07 pm Leave a comment

Memilih Directory dng UserForm

Kadang kita perlu mengambil file dari directory tertentu dalam aplikation Excel. Kita dapat menggunakan method GetOpenFileName untuk mendisplay sebuah user dialog yang meminta sebuah file (file name), akan tetapi kita tidak diberi pilihan untuk mendisplay sebuah dialog box that yang menunjukkan hanya directory saja. Dalam tulisan ini saya mencobah menyajikan sebuah fungsi yang diberi nama AmbilDirektori yang akan menampilkan dialog box seperti di bawah ini dan menghasilkan string yang menunjukkan directory yang terpilih. Jika user anda mengklik cancel, maka fungsi ini akan menghasilkan string kosong (empty). Fungsi AmbilDirektori memerlukan satu argument, tapi tidak wajib (optional). Argumen ini adalah variable a string yang akan ditampilkan dalam dialog box. Jika argumen tidak disertakan maka dialog box menampilkan pesan Pilih folder.

AmbilDirektori function

Fungsi AmbilDirektori adalah sebagai berikut. Untuk menggunakannya cukup copykan ke module VBA. Sebagai alternatif ambil contoh disini

hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
’32-bit API declarations
Declare Function SHGetPathFromIDList Lib “shell32.dll” _
Alias “SHGetPathFromIDListA” (ByVal pidl As Long, ByVal pszPath As String) _
As Long
Declare Function SHBrowseForFolder Lib “shell32.dll” _
Alias “SHBrowseForFolderA” (lpBrowseInfo As BROWSEINFO) As Long
Sub UjiCoba()
Dim Msg As String
Msg = “Contoh: Pilih lokasi untuk backup.”
MsgBox AmbilDirektori(Msg)
End Sub
Function AmbilDirektori(Optional Msg) As String
Dim path As String
Dim r As Long, x As Long, pos As Integer
bInfo.pidlRoot = 0&
If IsMissing(Msg) Then
bInfo.lpszTitle = “Pilih folder.”
bInfo.lpszTitle = Msg
End If
bInfo.ulFlags = &H1
x = SHBrowseForFolder(bInfo)
path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal path)
If r Then
pos = InStr(path, Chr$(0))
AmbilDirektori = Left(path, pos – 1)
AmbilDirektori = “”
End If
End Function

Cara menggunakan fungsi AmbilDirektori

Berikut subrutin sederhana untuk memakai fungsi AmbilDirektori

Sub UjiCoba()

Dim Msg As String

Msg = ” Contoh: Pilih lokasi untuk backup!!”

MsgBox AmbilDirektori(Msg)

End Sub

March 9, 2009 at 12:51 pm Leave a comment

eBook on Ms Office VBA

Advancing Your Office 2007 Power: Using Visual Basic
By Greg Perry

You can automate many routine tasks that you perform with Office 2007’s products by recording your keystrokes and storing them in a macro. You can easily assign the macro to a keystroke such as Ctrl+Shift+F12 and Word or Excel will zip through your automated commands the moment you press the keys.
Recorded macro keystrokes have been around for years and many Office users are familiar with them. The only drawback is they are simple and don’t go the extra mile when you want something really automated.  Read more on Office2007withVisualBasic.pdf size : 206.65 KB

March 6, 2009 at 7:09 pm 1 comment

Using the NotInList event (combo)

The functionality of combo boxes can be extended significantly by adding a bit of code to the NotInList event which gets triggered when the LimitToList property is set to Yes and the user enters text that is not listed in the combo.

It can be made to add new records, change existing search fields, and so on. Your first line of code should be:


You can then do what you like, because you have the new value that the user typed in to the NewData parameter and the original data is still available in the field. I generally open a modal form asking if the user wishes to add a new record or change the existing one, and then take appropriate action in the recordset.

The only trick is to end the NotInList subroutine with


This forces Access to ignore the error condition which originated the trigger for the NotInList subroutine.

This tip was supplied by (Armin Burkholder), May 16, 1995.

December 27, 2008 at 3:29 pm Leave a comment

Access Carry Over Data

Carry data over to new record

If a new record will be similar to the previous one, you may wish to automatically fill text boxes with that data for the user to edit. dBase provided a “carry” mechanism as early as the mid ’80s. Use a form’s BeforeInsert event to achieve this result in Access.

  1. Open the form in Design View.
  2. In the Properties Box, select the BeforeInsert event, and type the left square bracket. Access responds by filling in [Event Procedure].
  3. Click the “” button. Access responds by opening the code window.
  4. Between the Sub … and End Sub lines, enter:
Call CarryOver(Me)
  1. Save the form.
  2. In the Database window, select the Modules tab and choose New.
  3. Paste this code, and then save the module with a name such as basCarryOver:
Sub CarryOver(frm As Form)
On Error GoTo Err_CarryOver
' Purpose:  Carry the values over from the last record to a new one.
' Usage:   In a form's BeforeInsert event procedure, enter:
'                   Call CarryOver(Me)
' Notes:    This example limited to text boxes and combo boxes.
'           Text/combo boxes must have same Name as the fields they represent.
    Dim rst As DAO.Recordset
    Dim ctl As Control
    Dim i As Integer
    Set rst = frm.RecordsetClone
    If rst.RecordCount > 0 Then
        For i = 0 To frm.count - 1
            Set ctl = frm(i)
            If TypeOf ctl Is TextBox Then
                If Not IsNull(rst(ctl.Name)) Then
                    ctl = rst(ctl.Name)
                End If
            ElseIf TypeOf ctl Is ComboBox Then
                If Not IsNull(rst(ctl.Name)) Then
                    ctl = rst(ctl.Name)
                End If
            End If
    End If
    Set rst = Nothing
    Exit Sub
    Select Case Err
    Case 2448         'Cannot assign a value
        Debug.Print "Value cannot be assigned to " & ctl.Name
        Resume Next
    Case 3265         'Name not found in this collection.
        Debug.Print "No matching field name found for " & ctl.Name
        Resume Next
    Case Else
        MsgBox "Carry-over values were not assigned, from " & ctl.Name & _
            ". Error #" & Err.Number & ": " & Err.Description, vbExclamation, "CarryOver()"
        Resume Exit_CarryOver
    End Select
End Sub

December 26, 2008 at 6:29 am 1 comment

Gambar di Form Access

Menjawab pertanyaan Puji Astuti – tentang: Posting Menampilkan Gambar Pada Form

Menampilkan Gambar/Foto/Image/Picture tanpa harus menyimpan dalam file database maka berikut saya lampirkan file TampilGambar.ZIP

Silahkan dibuka karena sumbernya dari mana-mana yang jelas Microsoft juga membahas hal ini di

ACC: How to Display Images in a Form or in a Report Without Storing the Images in a Table

Berikut cuplikan dari Microsoft Access Help dan Support:
“Sometimes it is not practical to store images in a Microsoft Access table. If you have a large number of images, or if each of your image files is large, the size of the Microsoft Access database file can rapidly increase. The following examples show you how to display Windows bitmap images on a Microsoft Access form and on a report without storing the images in a Microsoft Access table.”

Dan beberapa pakar Access juga membahas salah satunya Microsoft® Access Software & UtilitiesHandling Images with Microsoft® Access

Jadi ada tiga macam cara mengelola gambar di Access yaitu:

1. Simpan sebagai gambar di OLE Field  – Store the image in an OLE field and use a bound object frame to display the image.

2. Simpan alamat path/dir dalam text field yang dapat digunakan sbg control image – Store the path to the image in a text field, using an image control to display the image.

3. Simpan sebagai BLOB dalam OLE Field – Store the image as a binary large object bitmap (BLOB) in an OLE field, extract the image when required and use an image control to display the image.

December 16, 2008 at 3:14 pm Leave a comment

Property Field Access

Berikut adalah penjelasan sederhada dari Inherritance Field Properties

  1. Description    akan menjadi statusbar text pada form
  2. Caption        akan menjadi label form. Pada datasheet akan menjadi judul kolom
  3. Default Value    Untuk menentukan nilai default
  4. Format        Untuk Mengatur penampilan data
  5. Input Mask    Untuk mengatur format data ketika dimasukkan
  6. Lookup        Untuk menentukan jenis control yang akan digunakan secara default
  7. Validation Rule    untuk menguji validitas data
  8. Validation Text    sebagai textmsg yang ditampilkan jika validation rule tidak terpenuhi/dilanggar.

September 13, 2008 at 6:47 am Leave a comment

Older Posts

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 13 other followers

Blog Stats

  • 104,823 hits

Twitter Updates