Maaf baru bisa ol lagi..
Btw Terima kasih banyak Mr.. Kid atas petunjuknya... kemarin saya coba2 di
script SQL nya tetep ga bisa pake replace tapi mungkin karena tanpa karakter
"$" setelah "replace", akhirnya saya ngutak-atik di script VBA nya ketemulah
script kayak gini...
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Private Sub cmd_simpan_Click()
Application.ScreenUpdating = False
Dim petik As String, garingpetik As String, namapemohon As String, namakuasa As
String
Dim badanusaha As String, namausaha As String, jenisusaha As String
petik = "'"
garingpetik = "\'"
namapemohon = Replace(Range("nama_pemohon_2"), petik, garingpetik)
namakuasa = Replace(Range("nama_kuasa_2"), petik, garingpetik)
badanusaha = Replace(Range("badan_usaha_2"), petik, garingpetik)
namausaha = Replace(Range("nama_usaha_2"), petik, garingpetik)
jenisusaha = Replace(Range("jenis_usaha_2"), petik, garingpetik)
konekdb
conn.CursorLocation = adUseClient
Set rs = New ADODB.Recordset
sQuery = "SELECT * FROM data_ho WHERE no_reg='" & Range("no_reg_2").Value & "'"
rs.Open sQuery, conn, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
conn.Execute "UPDATE data_ho SET nama_pemohon='" & namapemohon & "',
alamat_pemohon='" & Range("alamat_pemohon_2").Value & "', telp_pemohon='" &
Range("telp_pemohon_2").Value & _
"', lokasi_permohonan='" &
Range("lokasi_permohonan_2").Value & "', telp_lokasi='" &
Range("telp_lokasi_2").Value & "', badan_usaha='" & badanusaha & "',
nama_usaha='" & namausaha & _
"', jenis_usaha='" & jenisusaha & "',
nama_indeks1='" & Range("nama_indeks1_2").Value & _
"', jenis_usaha1='" & Range("jenis_usaha1_2").Value
& "', lrtu1=REPLACE('" & Range("lrtu1_2").Value & "',',','.'), il1='" &
Range("il1_2").Value & _
"', igt_permukiman1='" &
Range("igt_Permukiman1_2").Value & "', igt_nonpermukiman1='" &
Range("igt_nonpermukiman1_2").Value & "', igt_terminal1='" &
Range("igt_terminal1_2").Value & _
"', igt_pertanian1='" &
Range("igt_pertanian1_2").Value & "', igtt_produk1='" &
Range("igtt_produk1_2").Value & "', igtt_jenis1='" &
Range("igtt_jenis1_2").Value & _
"', ret1=REPLACE('" & Range("ret1_2").Value &
"',',','.'), nama_indeks2='" & Range("nama_indeks2_2").Value & "',
jenis_usaha2='" & Range("jenis_usaha2_2").Value & "', lrtu2=REPLACE('" &
Range("lrtu2_2").Value & _
"',',','.'), il2='" & Range("il2_2").Value & "',
igt_permukiman2='" & Range("igt_permukiman2_2").Value & "',
igt_nonpermukiman2='" & Range("igt_nonpermukiman2_2").Value & _
"', igt_terminal2='" &
Range("igt_terminal2_2").Value & "', igt_pertanian2='" &
Range("igt_pertanian2_2").Value & "', igtt_produk2='" &
Range("igtt_produk2_2").Value & _
"', igtt_jenis2='" & Range("igtt_jenis2_2").Value &
"', ret2=REPLACE('" & Range("ret2_2").Value & "',',','.'), nama_kuasa='" &
namakuasa & "', telp_kuasa='" & Range("telp_kuasa_2").Value & _
"', lama_terhutang='" &
Range("lama_terhutang_2").Value & "', besar_denda=REPLACE('" &
Range("besar_denda_2").Value & "',',','.'), cat1='" & Range("cat1_2").Value &
"', cat2='" & Range("cat2_2").Value & _
"', cat3='" & Range("cat3_2").Value & "',
tgl_masuk='" & Range("tgl_masuk_c_2").Value & "', tgl_tinjau='" &
Range("tgl_tinjau_c_2").Value & "', tgl_skrd1='" & Range("tgl_skrd1_c_2").Value
& _
"', tgl_skrd2='" & Range("tgl_skrd2_c_2").Value &
"', alasan_skrd2='" & Range("alasan_skrd2_2").Value & "', tgl_skrd3='" &
Range("tgl_skrd3_c_2").Value & "', alasan_skrd3='" &
Range("alasan_skrd3_2").Value & _
"', tgl_dibayarkan='" &
Range("tgl_dibayarkan_c_2").Value & "', tgl_terbit_sk='" &
Range("tgl_terbit_sk_c_2").Value & "', no_kwitansi='" &
Range("no_kwitansi_2").Value & _
"', status_cetak_sk='" &
Range("status_cetak_sk_2").Value & "', syarat1='" & Range("syarat1_2").Value &
"', tgl_syarat1='" & Range("tgl_syarat1_c_2").Value & "', syarat2='" &
Range("syarat2_2").Value & _
"', tgl_syarat2='" & Range("tgl_syarat2_c_2").Value
& "', syarat3='" & Range("syarat3_2").Value & "', tgl_syarat3='" &
Range("tgl_syarat3_c_2").Value & "', syarat4='" & Range("syarat4_2").Value & _
"', tgl_syarat4='" & Range("tgl_syarat4_c_2").Value
& "', retribusi=REPLACE('" &
Application.WorksheetFunction.Sum(Range("ret1_2").Value, Range("ret2_2").Value)
& "',',','.'), pk='" & Range("pk_2").Value & _
"', ket_pk='" & Range("ket_pk_2").Value & "',
luas_usaha=REPLACE('" &
Application.WorksheetFunction.Sum(Range("lrtu1_2").Value,
Range("lrtu2_2").Value) & _
"',',','.') WHERE no_reg='" &
Range("no_reg_2").Value & "'"
MsgBox "Data berhasil di UPDATE", vbInformation
Range("no_reg_2").Select
Else
MsgBox "Data tidak ada di DATABASE HO, Silahkan dimasukkan dari Sheet
BeritaAcaraTinjau", vbInformation
Range("no_reg_2").Select
End If
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Jadi pake Replacenya ada yg di script VBA nya, ada yang di Script SQL nya....
