• Recent Post

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

    • JPMorgan hackers altered, deleted bank records, says report
       The scope of yesterday's computer attack against JPMorgan Chase and at least one other bank appears to be much larger than initially reported. In addition to possibly affecting seven financial organizations, instead of two as originally reported, some bank records at JPMorgan were altered and possibly deleted, reported CNN, citing unnamed sources. The […]
    • Inside Google's Secret Drone-Delivery Program
      A zipping comes across the sky.Tags: GoogleIndustry News […]
    • Chef engineer leaves the company after receiving death threats from its open source community
      A release engineer from Chef, the company providing commercial support for the open-source Chef configuration management tool, said in a blog post Wednesday that he is leaving the company after being harassed by members of the Chef community for his contributions to the open source project.Tags: Industry News […]
    • Bitcoin’s Earliest Adopter Is Cryonically Freezing His Body To See The Future
      Some bitcoin enthusiasts have used their cryptocurrency to travel around the world. Others have spent it on a trip to space. But the very earliest user of bitcoin (after its inventor Satoshi Nakamoto himself) has now spent his crypto coins on the most ambitious mission yet: to visit the future.Tags: BitCoinTechnologyIndustry News […]
    • Why physical security (and InfoSec!) still matter
      In the current era of mega-(should I say giga-?) breaches with tens to hundreds of millions of lost customer records and the hacking-of-everything, it is safe to assume that the logical security of devices becomes almost more important than the physical protection around those assets. While it is true that the logical (in-)security of devices renders “remote […]
    • IEEE promotes security skills to software developers
      The Institute of Electrical and Electronics Engineers (IEEE) has formed a new advisory group with the private sector that aims to advise software developers on how to ensure that their applications are secure. The IEEE has linked with ten IT and security organisations - including Google, Twitter, Cigital and RSA - to form the IEEE Centre for Secure Design (C […]
    • Microsoft rolls out DLP to SharePoint Online and OneDrive for Business
      Microsoft has extended the data loss prevention features in Office 365 so that they are available not only for its email tools but also for data in SharePoint Online and OneDrive for Business. Office 365 already had DLP capabilities for Exchange Online and Outlook, so that compliance officers could monitor email communications and enforce corporate and regul […]
    • JPMorgan bank could be hackers' latest victim
      While hackers have been targeting retail stores over the past few months, it appears they haven't given up on banks. The FBI is investigating a data breach into JPMorgan and possibly several other banks, according to The Wall Street Journal. JPMorgan is the largest bank in the US and the sixth largest in the world, according to Forbes. […]
    • Twitter Opens Its Nifty Analytics Dashboard To Everyone
      Back in July, Twitter launched a really nifty analytics dashboard. A bit like Google Analytics for tweets, it allows you to gauge the performance of each and every tweet you sent. How many people saw it? How many of those actually clicked your links? There was one catch, though: it was only open to advertisers and verified users. No longer! Now you too can o […]
    • Google and VMware team with Nvidia to bring workstation graphics to Chromebooks
      Google and VMWare have teamed up with Nvidia to bring virtual desktops and workstation grade graphics to Google Chromebooks. The project uses the next generation of VMware's Blast and Nvidia's Grid virtual GPU technology to offer cutomers high power performance from the safety of a Google Chromebook. VMware Blast is the firm's protocol for del […]

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