Form Transaksi with SQL

Kali ini kita akan membuat lanjutan dari project PRA_UAS nya... yang akan kita buat adalah form transaksi.. nah transaksi kali ini menggunakan gabungan data picker, dan list box seperti tampilan berikut ini :

Buat Tampilannya menjadi sebagai berikut :

dengan ketentuan logikanya sebagai berikut :

1. Buat sub  tidak_aktif, bersih, koneksi_refresh, hapus, bersih_brg,dan no_baru
2. Ketika di jalankan semua tombol dalam keadaan non aktif. Tombol yang aktif hanya tambah dan Keluar. Fokus di tombol tambah
3. Tombol Tambah di klik maka Nota muncul otomatis.  Tanggal transaksi menggunakan data picker. Pilih Kode user yang akan otomatis memunculkan nama user. Tombol simpan dan batal aktif. Tombol tambah dan keluar tidak aktif
4.  Pilih Kode barang yang ada di listbrg, Secara otomatis akan menampilkan nama barang dan harga. Posisi kursor focus di qty.
5. Input jumlah QTY. jika > dari stok barang akan muncul pesan “Stok Tidak Cukup” , dan posisi kursor masih di QTY. jika tidak maka akan melakukan perhitungan subtotal = harga * qty dan kemudian memasukkan data barang tersebut kedalam grid sementara.
6. Kemudian muncul message box  “Ingin beli lagi ?” jika ya maka akan mengulang proses No 4. Jika tidak akan dilakukan perhitungan Total bayar = subtotal + total bayar. Dan posisi kursor ada di uang bayar.
7. Uang bayar di input maka akan menghitung uang kembali = uang bayar – total bayar
8. Tombol simpan di klik maka akan menyimpan data transaksi kedalam tabel transaksi dan detail transaksi. Kemudian mengedit stok barang di data barang = stok barang – qty
9. Tombol batal untuk membatalkan transaksi
10. Tombol keluar untuk menutup program dengan message box
    

Kemudian Ketikkan Listing sebagai berikut :

Sub bersih( )

txtnota.Text = " "

cbuser.Text = "Pilih"

txtnama_user.Text = " "

listbrg.ListIndex = -1

txtnabar.Text = " "

txthrg.Text = " "

txtqty.Text = " "

txtsub.Text = " "

txttotal.Text = " "

txtbayar.Text = " "

txtkembali.Text = " "

End Sub

Sub koneksi_refresh( )

conn.CursorLocation = adUseClient

rstmp.Open "sementara", conn

With rstmp

If Not (.BOF And .EOF) Then

a = .Bookmark

End If

End With

Set gridtmp.DataSource = rstmp.DataSource

End Sub

Sub bersih_brg( )

listbrg.ListIndex = -1

txtnabar.Text = " "

txthrg.Text = " "

txtqty.Text = " "

txtsub.Text = " "

End Sub

Sub tidak_aktif( )

txtnota.Enabled = False

cbuser.Enabled = False

txtnama_user.Enabled = False

listbrg.Enabled = False

txtnabar.Enabled = False

txthrg.Enabled = False

txtqty.Enabled = False

txtsub.Enabled = False

txttotal.Enabled = False

txtbayar.Enabled = False

txtkembali.Enabled = False

End Sub

 Sub No_baru( )

Call koneksi

rstran.Open "select * from transaksi Where Nota In(Select Max(Nota)From Transaksi)Order By Nota Desc", conn

rstran.Requery

    Dim Urutan As String * 10

    Dim Hitung As Long

    With rstran

        If .EOF Then

            Urutan = Right(Date, 2) + Mid(Date, 4, 2) + Left(Date, 2) + "0001"

            txtnota.Text = Urutan

        Else

            If Left(!nota, 6) <> Right(Date, 2) + Mid(Date, 4, 2) + Left(Date, 2) Then

                Urutan = Right(Date, 2) + Mid(Date, 4, 2) + Left(Date, 2) + "0001"

            Else

                Hitung = (!nota) + 1

                Urutan = (Right(Date, 2) + Mid(Date, 4, 2) + Left(Date, 2)) + Right("0000" & Hitung, 4)

            End If

        End If

        txtnota.Text = Urutan

    End With

End Sub

Function hapus( )

Call koneksi

Dim SQLHapus As String

SQLHapus = "Delete From sementara "

conn.Execute SQLHapus

Set gridtmp.DataSource = rstmp.DataSource

gridtmp.Refresh

End Function

Private Sub cmdbatal_Click( )

bersih

tidak_aktif

cmdbatal.Enabled = False

cmdtambah.Enabled = True

cmdtambah.SetFocus

cmdsimpan.Enabled = False

cmdkeluar.Enabled = True

hapus

End Sub

Private Sub cmdkeluar_Click( )

pesan = MsgBox("Yakin Mau Keluar?!!", vbYesNo, "Konfirmasi")

If pesan = vbYes Then

Unload Me

Else  

Exit Sub

End If

End Sub

Private Sub cbuser_Click()

Call koneksi

rsuser.Open "select * from user where kd_user='" & cbuser & "'", conn

If Not rsuser.EOF Then

txtnama_user.Text = rsuser!nama_user

End If

listbrg.Enabled = True

listbrg.SetFocus

End Sub

Private Sub cmdsimpan_Click()

On Error Resume Next

Call koneksi

Dim simpan_trans As String

sqlfak = "select * from transaksi"

Set rstran = conn.Execute(sqlfak, , adCmdText)

simpan_trans = "insert into transaksi(nota,tgl,kd_user,total) values(' " & txtnota.Text & " ', ' " & tgl.Value & " ',' " & cbuser.Text & " ',' " & txttotal.Text & " ')"

Set rstran = conn.Execute(simpan_trans, , adCmdText)

sqlsem = "select * from sementara"

Set rstmp = conn.Execute(sqlsem, , adCmdText)

rstmp.MoveFirst

    Do While Not rstmp.EOF

        If rstmp!kd_brg <> vbNullString Then

            Dim InputDetail As String

          InputDetail = "Insert Into Detail_trans(nota,kd_brg,qty,subtotal)values (' " & txtnota.Text & " ', ' " & rstmp!kd_brg & " ',' " & rstmp!qty & " ',' " & rstmp!subtotal & " ')"

            conn.Execute InputDetail

        End If

    rstmp.MoveNext

    Loop

       

sqlbrg = "select * from barang"

conn.Execute sqlbrg

       

    'kurangi jumlah stok

    rstmp.MoveFirst

    Do While Not rstmp.EOF

        If rstmp!kd_brg <> vbNullString Then

        rsbarang.Open "Select * from barang where kd_brg=' " & rstmp!kd_brg & " ' ", conn

           If Not rsbarang.EOF Then

                Dim Kurangi As String

                Kurangi = "update barang set stok=' " & rsbarang!stok - rstmp!qty & " ' where kd_brg=' " & rstmp!kd_brg & " ' "

                conn.Execute Kurangi

            End If

        End If

    rstmp.MoveNext

    Loop

bersih

tidak_aktif

cmdtambah.Enabled = True

cmdsimpan.Enabled = False

cmdbatal.Enabled = False

cmdkeluar.Enabled = True

koneksi_refresh

Call hapus

End Sub

Private Sub cmdtambah_Click()

cbuser.Enabled = True

No_baru

txtnota.Enabled = True

txtnota.SetFocus

Call hapus

cbuser.SetFocus

cmdtambah.Enabled = False

cmdsimpan.Enabled = True

cmdbatal.Enabled = True

cmdkeluar.Enabled = False

End Sub

 

Private Sub Form_Activate()

Call koneksi

rsbarang.Open "select * from barang", conn

Do Until rsbarang.EOF

listbrg.AddItem rsbarang!kd_brg

rsbarang.MoveNext

Loop

rsuser.Open "select * from user", conn

Do Until rsuser.EOF

cbuser.AddItem rsuser!kd_user

rsuser.MoveNext

Loop

rstran.Open "select * from transaksi", conn

rsdetail.Open "select * from detail_trans", conn

tidak_aktif

cmdsimpan.Enabled = False

cmdbatal.Enabled = False

End Sub

Private Sub Form_Load()

tgl.Format = dtpCustom

tgl.CustomFormat = Format(Now, "dd MM yyyy")

End Sub

Private Sub listbrg_Click()

Call koneksi

rsbarang.Open "Select * from barang where kd_brg=' " & listbrg & " ' ", conn

If Not rsbarang.EOF Then

txtnabar = rsbarang!nama_brg

txthrg = rsbarang!hrgjual

End If

txtqty.Enabled = True

txtqty.SetFocus

End Sub

Private Sub txtqty_KeyPress(KeyAscii As Integer)

Dim simpan As String

Dim simpan2 As String

Dim stok As Integer

Dim ubah As String

Dim ubah2 As String

 

If KeyAscii = 13 Then

If Len(Trim(txtqty.Text)) = 0 Then

MsgBox "isi qty dulu...", vbOKOnly, "Pesan"

txtqty.SetFocus

ElseIf Val(txtqty.Text) > rsbarang!stok Then

MsgBox "Stok Tidak Cukup", vbOKOnly, "Pesan"

txtqty.Text = ""

txtqty.SetFocus

Else

sqlsem = "select * from sementara"

Set rstmp = conn.Execute(sqlsem, , adCmdText)

sqlbrg = "select * from barang where kd_brg='" & listbrg.Text & "'"

Set rsbarang = conn.Execute(sqlbrg, , adCmdText)

        txtsub.Text = Val(txtqty.Text) * Val(txthrg.Text)

        tanya = MsgBox("Mo tambah barang lagi?", 32 + 4, "Lagi")

        If tanya = vbYes Then 'tambah data barang

            simpan = "insert into sementara values('" & listbrg.Text & "','" & txtnabar.Text & "','" & txthrg.Text & "','" & txtqty.Text & "','" & txtsub.Text & "')"

            Set rstmp = conn.Execute(simpan, , adCmdText)

           

            txttotal.Text = Val(txttotal.Text) + Val(txtsub.Text)

            listbrg.Enabled = True

            listbrg.SetFocus

            bersih_brg

            txtqty.Enabled = False

            txtsub.Text = ""

        Else

            simpan2 = "insert into sementara values('" & listbrg.Text & "','" & txtnabar.Text & "','" & txthrg.Text & "','" & txtqty.Text & "','" & txtsub.Text & "')"

            Set rstmp = conn.Execute(simpan2, , adCmdText)

           

          

            txttotal.Text = Val(txttotal.Text) + Val(txtsub.Text)

            listbrg.Enabled = False

            txtqty.Enabled = False

            txtbayar.Enabled = True

            txtbayar.SetFocus

        End If

    koneksi_refresh

End If

End If

End Sub

Private Sub txtbayar_KeyPress(KeyAscii As Integer)

If KeyAscii = 13 Then

txtkembali.Text = Val(txtbayar.Text) - Val(txttotal.Text)

End If

End Sub