Program Vibi: Membuat NEW, ADD, UPDATE, EDIT dan DELET

Public cn As ADODB.Connection
Dim rsTblMhs As ADODB.Recordset
Dim rs As ADODB.Recordset
Dim strsql As String

Public Sub koneksi()
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB.1;Password=123456;Persist Security Info=True;User ID=sa;Initial Catalog=DatabaseMhs;Data Source=TOSHIBA-PC"
End Sub

Set rs = New ADODB.Recordset
strsql = "Select max(NIM)as NIM from TblMhs"
rs.CursorLocation = adUseClient
rs.Open strsql, cn, adOpenDynamic, adLockOptimistic

If Not rs!NIM = "" Then
KODE = Mid(rs!NIM, 6, 9)
NoUrut = CLng(KODE)
NoUrut = NoUrut + 1
TxtNIM.Text = "080010" + Format(NoUrut, "000")
Else
NoUrut = 1
TxtNIM.Text = "080010" + Format(NoUrut, "000")
End If
End Sub

Private Sub CmdAdd_Click()
If TxtNIM.Text = "" Then
MsgBox "NIM belum diisi", vbInformation, "PESAN"
TxtNIM.SetFocus
ElseIf txtNama.Text = "" Then
MsgBox "NAMA belum diisi", vbInformation, "PESAN"
txtNama.SetFocus
ElseIf TxtAlamat.Text = "" Then
MsgBox "ALAMAT belum diisi", vbInformation, "PESAN"
TxtAlamat.SetFocus
Else
rsTblMhs.AddNew
rsTblMhs("NIM") = TxtNIM.Text
rsTblMhs("NAMA") = txtNama.Text
rsTblMhs("ALAMAT") = TxtAlamat.Text
rsTblMhs.Update
End If
Set DataGrid1.DataSource = rsTblMhs
TxtNIM.Text = ""
txtNama.Text = ""
TxtAlamat.Text = ""
CmdAdd.Enabled = False
CmdUpdate.Enabled = True
CmdNew.Enabled = True
CmdDelete.Enabled = True
End Sub

Private Sub CmdDelete_Click()
TxtNIM.Locked = False
txtNama.Locked = False
TxtAlamat.Locked = False
TxtNIM.Text = rsTblMhs("NIM")
txtNama.Text = rsTblMhs("NAMA")
TxtAlamat.Text = rsTblMhs("ALAMAT")
If MsgBox("apakah yakin ingin dihapus?", vbYesNo, "KONFIRMASI") = vbYes Then
Set rsTblMhs = cn.Execute("Delete from TblMhs where NIM='" & TxtNIM.Text & " ' ")
MsgBox "Data telah dihapus", vbInformation, "PESAN"
End If
'untuk me-refresh datagrid setelah delete data
If cn.State = adStateOpen Then
setrsTblMhs = New ADODB.Recordset
rsTblMhs.CursorLocation = adUseClient
strsql = "select * from TblMhs"
rsTblMhs.CursorLocation = adUseClient
rsTblMhs.Open strsql, cn, adOpenDynamic, adLockOptimistic
Set DataGrid1.DataSource = rsTblMhs
End If
End Sub

Private Sub CmdEdit_Click()
DataGrid1.Enabled = True
txtNama.Locked = False
TxtAlamat.Locked = False

TxtNIM.Text = rsTblMhs("NIM")
txtNama.Text = rsTblMhs("NAMA")
TxtAlamat.Text = rsTblMhs("ALAMAT")
CmdUpdate.Enabled = True
End Sub

Private Sub CmdNew_Click()
CmdAdd.Enabled = True
CmdNew.Enabled = False
TxtNIM.Locked = False
txtNama.Locked = False
TxtAlamat.Locked = False
kode_otomatis
End Sub

Private Sub CmdUpdate_Click()
If rsTblMhs.RecordCount > 0 Then
rsTblMhs.MoveFirst
rsTblMhs.Find "NIM='" & TxtNIM.Text & "'"
If Not rsTblMhs.EOF Then
Set rsTblMhs = cn.Execute("update TblMhs SET NAMA ='" & Trim(txtNama.Text) & "' Where NIM='" & TxtNIM.Text & "'")
Set rsTblMhs = cn.Execute("update TblMhs SET ALAMAT ='" & Trim(TxtAlamat.Text) & "' Where NIM='" & TxtNIM.Text & "'")
Else
MsgBox "Tidak Ada NIM tersebut", vbInformation, "Pesan"
End If
End If
MsgBox "Data Sudah Di ubah", vbInformation, "Pesan"
rsTblMhs.CursorLocation = adUseClient
strsql = "select*from TblMhs"
rsTblMhs.Open strsql, cn, adOpenDynamic, adLockOptimistic
Set DataGrid1.DataSource = rsTblMhs
End Sub

Private Sub Form_Load()
TxtNIM.Text = ""
txtNama.Text = ""
TxtAlamat.Text = ""
koneksi
Set rsTblMhs = New ADODB.Recordset
rsTblMhs.CursorLocation = adUseClient
strsql = "select*from TblMhs"
rsTblMhs.Open strsql, cn, adOpenDynamic, adLockOptimistic
Set DataGrid1.DataSource = rsTblMhs

CmdAdd.Enabled = False
CmdUpdate.Enabled = False
CmdEdit.Enabled = True

CmdDelete.Enabled = True
TxtNIM.Locked = True
txtNama.Locked = True
TxtAlamat.Locked = True
End Sub

Private Sub Refresh_Click()
Set DataGrid1.DataSource = Nothing
DataGrid1.Refresh
End Sub