• Recent Post

  • RSS R o o t s e c u r e

    • BlackBerry to acquire German firm Secusmart
      BlackBerry is buying privately held German firm Secusmart which specialises in voice and data encryption, in a bid to improve its credentials security as part of its strategy to target specific industries and niches. Secusmart specialises in encryption and anti-eavesdropping services for public sector, enterprise and telecommunications service providers.Tags […]
    • Former NSA director will file "at least" 9 patents to detect malicious hackers
      In an interview Monday with former National Security Agency Director General Keith Alexander, Foreign Policy's Shane Harris learned that Alexander plans to file “at least” nine patent applications—“and possibly more"—pertaining to technology for detecting network intruders.Tags: SecurityIndustry News […]
    • New "Fake ID" exploit allows new types of Malware on Android?
      A new Android design error discovered by Bluebox Security allows malicious apps to grab extensive control over a user's device without asking for any special permissions at installation. The problem affects virtually all Android phones sold since 2010. Bluebox calls the flaw "Fake ID" because it allows malware apps to pass fake credentials to […]
    • Microwave hacked into weapon, used to blow up a stereo
      There are some things in this world you're better off not trying, and dismantling a microwave to make a stick-mounted weapon is probably one of them. Still, as with all things, someone somewhere will eventually try it, as was the case with the person(s) behind Kreosan. Kreosan is the YouTube handle of a young Russian man who, featured in the video below […]
    • How do hackers breach institutions like Canada's NRC?
      Cyberattacks like the one against the National Research Council of Canada are increasing around the world. But by knowing the steps hackers would use for a sophisticated attack, security experts try to gain the upper hand. "Sometimes in breaches, companies call it a 'highly sophisticated cyberattack' (as the Government of Canada's chief t […]
    • US senator seeks strong curbs on NSA metadata collection
      A new Senate proposal to curb the government's bulk collection of Americans' telephone records and increase transparency about the program has White House backing, and may get more traction with critics who have dismissed other bills as too weak. Democrat senator Patrick Leahy, chairman of the Senate Judiciary Committee, introduced the legislation […]
    • Satya Nadella sounds the alarm, literally, to kick-off Microsoft hackathon
      Satya Nadella is pushing a lot of change across the Microsoft org and a new event is kicking off this week called //OneWeek. The week is dedicated to updating the employees on the corporate mission, boosting morale and holding one massive hackathon. The hackathon kicked off today and we have gathered images from the event that offers a peek inside the world […]
    • Amazon opens 3D printing store, sells customizable stuff
      Amazon has sure come a long way from their roots as an online bookstore. They've since expanded to sell everything from music to groceries and everything in between. The company even produces their own hardware now, including the Fire Phone and the set-top Fire TV, expanding into almost every aspect of what a customer may want to purchase.Tags: AmazonIn […]
    • Chinese authorities announce Microsoft anti-monopoly investigation
      Microsoft Corp appears to be the latest U.S. company targeted by China for antitrust investigation as government officials paid sudden visits to the software firm's Chinese offices on Monday. Representatives from China's State Administration for Industry & Commerce, which is responsible for enforcing business laws, made the visits to Microsoft […]
    • About those alleged backdoors in Microsoft products...
      Scott Charney, of Microsoft's Trustworthy Computing, said the government has "never" asked for a backdoor in Microsoft products. Yet a former engineer working on BitLocker claimed the government does ask, but those requests are “informal.”Tags: MicrosoftSecurity […]

Cyrus Rico

.::Everything Must Be Share::.

Program STOK Dengan VB dan Ms. Access

Program STOK Penjualan menggunakan VB dan Database Ms Access (Episode 5B)

Episode sebelumnya (Epesiode 5A) pembahasan seputar transaksi

pembelian, nah pada episode kali ini akan dibahas transaksi penjualan, cara menampilkan data serta tabel dan query yang digunakan.

Tetap menggunakan database sebelumnya yakni STOK.MDB yang dapat di download bersama program ini, maka singung terlebih dahulu tabel-tabel dan query yang dipergunakan sebagai berikut:

1. HJual, Untuk menampung data transaksi Penjualan maka dibuatkan 2 (dua) Tabel (HJual dan DJuali), ke 2 tabel dihubungkan dengan sebuah field kunci yakni NOFAKTUR, pembahasan tabel Hjual dan Djual mirip Hbeli dan DBeli pada tulisan yang lalu.

Struktur tabel HJual sbb:

Field Name Data Type Field Properties

NOFAKTUR Text Field Size 10, (Nomor Faktur Penjualan)

TGL Date/Time Field Size 8 otomatis (Tanggal Transaksi Penjualan)

CUSTOMER Text Field Size 50, (Nama Customer)

KETERANGAN Text Field Size 100 (Mencatat Keterangan Transaksi)

Buatkan index dengan nama XNOFAKTUR dari field NOFAKTUR yang bersifat Primarykey

2. DJual, Untuk menampung data many transaksi Penjualan, Struktur

Tabel DJual sbb:

Field Name Data Type Field Properties

NOFAKTUR Text Field Size 10, (Nomor Faktur Penjualan)

KODEBRG Text Field Size 18 (Kode Barang)

JML Number Integer,(Jumlah Barang yang di jual)

HARGA Number Single (Harga Jual Barang)

Field Nofaktur dapat di index akan tetapi tidak boleh bersifat Primarykey

3.TCustomer, Untuk menampung data Master Customer, Struktur

Tabelnya sbb:

Field Name Data Type Field Properties

KODECUST Text Field Size 5, (Kode Customer)

NAMACUST Text Field Size 100 (Nama Customer)

ALAMAT Text Field Size 255,(Alamat Customer)

TELPON Text Field Size 15(Nomor Telepon Customer)

Buatkan index dengan nama XKODECUST dari field KODECUST yang bersifat Primarykey

4. QJUAL, Adalah query untuk menampilkan record data penjualan,

Adapun struktur Query ini adalah melibatkan ke 3 tabel yang sudah dibuat (HJual, DJual dan TBarang) , lihat gambar dibawah ini:

Query QJual

Bila kesulitan membaca gambar maka dapat menempuh cara membuat query dengan mengcopy isi statement SQLnya melalui menu SQL View. Adapun SLQ Stringnya seperti dibawah ini:

SELECT HJUAL.NOFAKTUR, HJUAL.TGL, HJUAL.CUSTOMER, HJUAL.KETERANGAN, DJUAL.KODEBRG, TBARANG.NAMABRG, TBARANG.SATUAN, DJUAL.JML, DJUAL.HARGA, [DJUAL]![JML]*[DJUAL] [HARGA] AS SUBJUMLAH FROM TBARANG INNER JOIN (HJUAL INNER JOIN DJUAL ON HJUAL.NOFAKTUR = DJUAL.NOFAKTUR) ON TBARANG.KODEBRG = DJUAL.KODEBRG;

Catatan:

Baris perintah diatas tidak dipisahkan dengan enter, hilangkan dahulu efek enter tersebut dengan menekan tombol del di setiap diujung baris perintah.

6. QJJual, Adalah query untuk menghitung jumlah penjualan masing-masing barang, Adapun struktur Query berasal dari query QJUAL, lihat gambar dibawah ini:

Query QJJual MegadataKeterangan:

Bila kesulitan membaca gambar maka dapat menempuh cara membuat query dengan mengcopy isi statement SQLnya melalui menu SQL View. Adapun SLQ Stringnya seperti dibawah ini:

SELECT QJUAL.KODEBRG, QJUAL.NAMABRG, Sum(QJUAL.JML) AS JMLJUAL, Sum(QJUAL.HARGA) AS HARGAJUAL, Sum(QJUAL.SUBJUMLAH) AS SUBJUMLAHJUAL FROM QJUAL GROUP BY QJUAL.KODEBRG, QJUAL.NAMABRG;

Catatan:

Baris perintah diatas tidak dipisahkan dengan enter, hilangkan dahulu efek enter tersebut dengan menekan tombol del diujung baris perintah sehingga baris ke dua naik menyambung dengan baris pertama.

PROGRAM STOK PENJUALAN

FRMJUAL (Form Penjualan)

Form ini digunakan untuk melakukan transaksi pembelian, Desain dan Layout form seperti gambar dibawah:

Form Penjualan Megadata

Isi Perintah Form Penjelasan:

‘——- BOF FrmJual ————-

Public Mtotal As Single

Public RBaru As Boolean

Public SubHLama As Single

Sub HapusTbantu()

‘Hapus Tbantu

If Adodc1.Recordset.State = 1 Then

Adodc1.Recordset.close

End If

CN.Execute “delete * from Tbantu”

Adodc1.Recordset.Open “SELECT * FROM TBANTU”, CN

Adodc1.Refresh

Me.DataGrid1.ReBind

Me.DataGrid1.Refresh

End Sub

Private Sub Adodc1_WillChangeRecord(ByVal adReason As ADODB.EventReasonEnum,

ByVal cRecords As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset

As ADODB.Recordset)

‘baris perintah Parameter sub procedure tidak dipisahkan dengan enter

If adReason = adRsnAddNew Then

RBaru = True

Else

RBaru = False

End If

End Sub

Private Sub Adodc1_WillChangeRecordset(ByVal adReason As ADODB.EventReasonEnum,

adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

‘baris perintah Parameter sub procedure tidak dipisahkan dengan enter

If adReason = adRsnAddNew Then

RBaru = True

Else

RBaru = False

End If

End Sub

Private Sub cadd_Click()

Me.Text1.Text = “”

Me.Text2.Text = “”

Me.Text3.Text = “”

Me.Text4.Text = “”

cESave.Caption = “&Save”

Call HapusTbantu

Adodc1.Recordset.AddNew

Adodc1.Recordset!KOdeBrg = “”

Adodc1.Recordset.Update

Me.Refresh

Text1.SetFocus

Mtotal = 0

End Sub

Private Sub cDelete_Click()

‘Untuk menghapus data, Pada kasus ini Data yang terhapus

‘tidak dikembalikan ke master Stok,

‘artinya perhitungan akan salah jika mengunakan

‘rumus stok sebelumnya, untuk itu pembaca dapat memodifikasi

‘sub rutin ini khususnya pada penghapusan Djual berbentuk Loop

Dim Tanya As Byte

Tanya = MsgBox(“Anda yakin menghapus Transaksi yang tampil..” & Chr(10) & _

“Jumlah Barang yang dihapus tidak dikembalikan ke Stok”,

vbQuestion + vbYesNo, “HAPUS DATA”)

If Tanya = vbYes Then

CN.Execute “Delete * from Hjual Where NOFAKTUR=’” & Trim(Text1.Text) & “‘”

CN.Execute “Delete * from Djual Where NOFAKTUR=’” & Trim(Text1.Text) & “‘”

cadd_Click

MsgBox “Data sudah terhapus..”

End If

End Sub

Private Sub cESave_Click()

If cESave.Caption = “&Save” Then

cESave.Caption = “&Edit”

Set rsHJual = New ADODB.Recordset

rsHJual.Open “select * from HJual”, CN, adOpenKeyset, adLockOptimistic,adCmdText

rsHJual.AddNew

rsHJual!NoFaktur = Trim(Text1.Text)

rsHJual!tgl = DtJUal.Value

rsHJual!Customer = Trim(Text2.Text)

rsHJual!keterangan = Trim(Text3.Text)

Set rSDJual = New ADODB.Recordset

rSDJual.Open “select * from Djual “,CN,adOpenKeyset, adLockOptimistic,adCmdText

With Adodc1.Recordset

.MoveFirst

Do Until .EOF

If Len(Trim(!KOdeBrg)) > 0 Then

rSDJual.AddNew

rSDJual!NoFaktur = Trim(Text1.Text)

rSDJual!KOdeBrg = !KOdeBrg

rSDJual!Jml = !Jml

rSDJual!Harga = !HrgJual

Set rSB = New ADODB.Recordset

rSB.Open “select * from Tbarang where KODEBRG=’” & !KOdeBrg & “‘”,

CN, adOpenKeyset, adLockOptimistic, adCmdText

If rSB.EOF And rSB.BOF Then

Else

rSB!JUMLAH = rSB!JUMLAH – !Jml

rSB.Update

End If

rSDJual.Update

End If

.MoveNext

Loop

rsHJual.Update

End With

MsgBox “Data Penjualan sudah tersimpan”

else

‘Ini untuk tombol Edit

End If

End Sub

Private Sub cexit_Click()

Unload Me

End Sub

Private Sub DataGrid1_AfterColEdit(ByVal ColIndex As Integer)

Dim Kobrg As String

If ColIndex = 0 Then

Kobrg = Trim(DataGrid1.Columns(0).Text)

Set rSB = New ADODB.Recordset

rSB.Open “select * from Tbarang where KodeBrg =’” & Kobrg & “‘”,

CN, adOpenKeyset, adLockOptimistic, adCmdText

If rSB.EOF And rSB.BOF Then

MsgBox “Kode Barang ini tidak ada”

Else

With DataGrid1

.Columns(1).Text = rSB!NamaBrg

.Columns(2).Text = rSB!Satuan

.Columns(3).Text = rSB!HargaJual

End With

SendKeys “{RIGHT 4}”

End If

End If

If ColIndex = 4 Then

With DataGrid1

.Columns(5).Text = Val(.Columns(3).Text) * Val(.Columns(4).Text)

If RBaru Then

Mtotal = Mtotal + Val(.Columns(5).Text)

Else

Mtotal = (Mtotal – SubHLama) + Val(.Columns(5).Text)

End If

SubHLama = 0

Text4.Text = Format(Mtotal, “#,##0″)

End With

SendKeys “{DOWN}”

SendKeys “{HOME}”

End If

End Sub

Private Sub DataGrid1_BeforeColEdit(ByVal ColIndex As Integer,

ByVal KeyAscii As Integer, Cancel As Integer)

If ColIndex = 4 Then

SubHLama = Val(DataGrid1.Columns(5).Text)

End If

End Sub

Private Sub DtJUal_KeyDown(KeyCode As Integer, Shift As Integer)

TekanEnter (KeyCode)

End Sub

Private Sub Form_Load()

VFrmJual = True

Me.Width = 9420

Me.Height = 5550

Me.Left = (Screen.Width – Me.Width) \ 2

Me.Top = 1000

Adodc1.Recordset.close

CN.Execute “delete * from Tbantu”

Adodc1.Recordset.Open “select * from Tbantu”

Me.Adodc1.Refresh

Me.DataGrid1.Refresh

End Sub

Private Sub Form_Terminate()

‘VFrmJual = False

End Sub

Private Sub Form_Unload(Cancel As Integer)

VFrmJual = False

End Sub

Private Sub Text1_KeyPress(KeyAscii As Integer)

KeyAscii = Asc(UCase(Chr(KeyAscii)))

TekanEnter (KeyAscii)

End Sub

Private Sub Text1_LostFocus()

Dim Tanya As Integer

Mtotal = 0

If Len(Trim(Text1.Text)) > 0 Then

Set rsHJual = New ADODB.Recordset

rsHJual.Open “select * from Qjual where NOFAKTUR =’” &

Trim(Text1.Text)& “‘”, CN, adOpenKeyset,

adLockOptimistic, adCmdText

If rsHJual.EOF And rsHJual.BOF Then

Else

cESave.Caption = “&Edit”

Tanya = MsgBox(“Faktur Sudah pernah ada, Mau ditampilkan.? “,

vbQuestion + vbYesNo, “FAKTUR GANDA”)

If Tanya = vbYes Then

Text2.Text = rsHJual!Customer

DtJUal.Value = rsHJual!tgl

Text3.Text = rsHJual!keterangan

CN.Execute “delete * from Tbantu”

Set rSBantu = New ADODB.Recordset

rSBantu.Open “select * from Tbantu”, CN, adOpenKeyset,

adLockOptimistic,adCmdText

rsHJual.MoveFirst

Do Until rsHJual.EOF

rSBantu.AddNew

rSBantu!KOdeBrg = rsHJual!KOdeBrg

rSBantu!NamaBrg = rsHJual!NamaBrg

rSBantu!Satuan = rsHJual!Satuan

rSBantu!HrgJual = rsHJual!Harga

rSBantu!Jml = rsHJual!Jml

rSBantu!Subjumlah = rsHJual!Subjumlah

Mtotal = Mtotal + rsHJual!Subjumlah

rSBantu.Update

rsHJual.MoveNext

Loop

Set rsHJual = Nothing

Set rSBantu = Nothing

Adodc1.Recordset.close

Adodc1.Recordset.Open “sELECT * FROM TBANTU”, CN

Adodc1.Recordset.Requery -1

Adodc1.Refresh

DataGrid1.ReBind

DataGrid1.Refresh

Text4.Text = Format(Mtotal, “#,##0″)

Me.Refresh

End If

End If

End If

End Sub

Private Sub Text2_KeyPress(KeyAscii As Integer)

KeyAscii = Asc(UCase(Chr(KeyAscii)))

TekanEnter (KeyAscii)

End Sub

Private Sub Text2_LostFocus()

Dim RsCust As ADODB.Recordset

Set RsCust = New ADODB.Recordset

RsCust.Open “select * from TCUSTOMER where NamaCust =’”

& Trim(Text2.Text) & “‘”, CN, adOpenForwardOnly, adLockReadOnly

If RsCust.EOF And RsCust.BOF Then

MsgBox “Maaf Nama Customer ini tidak ada”

Text2.SetFocus

Exit Sub

End If

End Sub

Private Sub Text3_KeyPress(KeyAscii As Integer)

KeyAscii = Asc(UCase(Chr(KeyAscii)))

TekanEnter (KeyAscii)

End Sub

‘——— EOF File Form FrmJual —————

FVBarang (Form View Barang)

Form ini digunakan untuk menampilkan Jumlah pembelian, jumlah penjualan dan stok barang, Desain dan Layout form seperti gambar dibawah:

Form View Barang megadata

‘ ———– BOF FVBarang ———————–

Private Sub close_Click()

Unload Me

End Sub

Private Sub cview_Click()

If Option1.Value Then

Adodc1.Recordset.close

Adodc1.Recordset.Open “select * from QJMLBRG order by KODEBRG”, CN,

adOpenKeyset, adLockReadOnly, adCmdText

Adodc1.Recordset.Requery -1

DataGrid1.ReBind

DataGrid1.Refresh

End If

If Option2.Value Then

Adodc1.Recordset.close

Adodc1.Recordset.Open “select * from QJMLBRG where NAMABRG LIKE ‘%”

& Trim(Text1.Text) & “%’ order by NAMABRG”, CN,

adOpenKeyset, adLockReadOnly, adCmdText

Adodc1.Recordset.Requery -1

DataGrid1.ReBind

DataGrid1.Refresh

End If

If Option3.Value Then

Adodc1.Recordset.close

Adodc1.Recordset.Open “select * from QJMLBRG where JUMLAH <="

& Val(Trim(Text2.Text)) & ” order by NAMABRG”, CN,

adOpenKeyset,adLockReadOnly, adCmdText

Adodc1.Recordset.Requery -1

DataGrid1.ReBind

DataGrid1.Refresh

End If

End Sub

Private Sub Form_Load()

Me.Height = 5400

Me.Width = 9360

Me.Left = (Screen.Width – Me.Width) \ 2

Me.Top = 1000

End Sub

‘————- EOF FVBarang ————–

PENJELASAN:

Pada Source Program yg di download terdapat form MDI dan beberapa komponen lain seperti icon megadata, semata-mata sebagai bahan pembelajaran, Komponen lain seperti Tabel User dan Porgram Login serta form User akan disempurnakan pada modify berikutnya, ini dilakukan agar para pembaca yang bersifat pemula tidak terlalu merasa berat dengan listing program.

Demikian pembahasan STOK dan akan disambung pada tulisan berikutnya.

Silakan meninggalkan pesan untuk sesuatu yang perlu dijelaskan..

Download Program

Categories: Kuliah - Pemrograman