Terima kasih Master... . Luar Biasa. Sukses. Sekali lagi, terima kasih.
Best Regards, MANSUR AZIZ [email protected] --- Pada Kam, 24/6/10, [email protected] <[email protected]> menulis: Dari: [email protected] <[email protected]> Judul: Bls: [belajar-access] Re: Split data Kepada: [email protected] Tanggal: Kamis, 24 Juni, 2010, 10:35 AM Tentu saja tidak ada efeknya karena commandbutton tidak memanggil procedure. 1. pastikan isi command button seperti di bawah ini. Private Sub Command0_Click() On Error GoTo Err_Command0_Click FormatTable1ToTable2 Exit_Command0_Click: Exit Sub Err_Command0_Click: MsgBox Err.Description Resume Exit_Command0_Click End Sub 2. Tambahkan satu Field / Kolom bernama RecID dengan tipe data Long Integer pada Table2. Jadikan kolom / field RecID ini sebagai Primary Key. Kalau anda ingin memanggil dari macro, anda harus mengganti kata Public Sub menjadi Public Function. Jangan lupa poin nomor 2. --- In [email protected], mansur aziz <mansur4...@...> wrote: > > > Saya kesulitan memahami alur dari sintak tersebut. Saya coba pastekan ke > module1, kemudian saya buat command button di form1, tapi tidak ada efeknya. > Ketika saya running tidak ada efeknya. > > Terlampir file mdb nya. > > Mohon penjelasan lagi > > > Best Regards, > MANSUR AZIZ > mansur4...@... > > > --- Pada Kam, 24/6/10, [email protected] > <[email protected]> menulis: > > Dari: [email protected] > <[email protected]> > Judul: [belajar-access] Re: Split data > Kepada: [email protected] > Tanggal: Kamis, 24 Juni, 2010, 8:04 AM > > > > > > > > Â > > > > > > > > > > Copy Paste Fungsi dan Prosedur di bawah ini ke modul baru. > > Jangan Lupa sesuaikan nama tabelnya. > > Asumsi, Tabel1 berisi kolom Job (teks), Customer (text), Jumlah (Currency) > > Tabel2 berisi kolom RecID(LongInt-PrimaryKey), Job (teks), Customer > (text(255)), Jumlah (Currency) > > > > Anda bisa memanggil Prosedur FormatTable1ToTable2 dari command button / macro. > > > > Function ReadJob(ByVal Job As String) As String > > On Error GoTo ReadJob_Err > > > > Dim vRC As String 'vRC = variable Rantai Customer > > vRC = "" > > Dim rst As New ADODB.Recordset > > strSQL = "SELECT CUSTOMER FROM TABLE1 WHERE JOB='" & Job & "'" > > rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, > adLockPessimistic, adCmdText > > > > If Not (rst.BOF Or rst.EOF) Then > > rst.MoveLast > > rst.MoveFirst > > For i = 1 To rst.RecordCount > > vRC = IIf(i = 1, rst!Customer, vRC & "~~" & rst!Customer) > > rst.MoveNext > > Next > > End If > > > > rst.Close > > Set rst = Nothing > > ReadJob = vRC > > > > ReadJob_Keluar: > > Exit Function > > > > ReadJob_Err: > > Debug.Print "Read Job Error Is : " & Err.Number & vbCrLf & Err.Description > > Resume ReadJob_Keluar > > > > > > End Function > > > > Public Sub FormatTable1toTable2() > > On Error GoTo Cara2_Err > > > > 'Pembersihan Table2 > > strSQL = "DELETE * FROM TABLE2" > > CurrentDb.Execute strSQL, dbFailOnError > > > > 'Mengisi Table2 dgn data dari table1 + function ReadJob untuk mengisi > Kolom Customer. > > Dim rst As New ADODB.Recordset > > strSQL = "SELECT Table1.Job, Sum(Table1.Jumlah) AS Jumlah FROM Table1 > GROUP BY Table1.Job;" > > rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, > adLockPessimistic, adCmdText > > > > If Not (rst.BOF Or rst.EOF) Then > > rst.MoveLast > > rst.MoveFirst > > For vBarisKe = 1 To rst.RecordCount > > strSQL = "INSERT INTO Table2(RecID,Job,Jumlah,Customer) VALUES(" > & vBarisKe & ",'" & rst!Job & "'," & rst!Jumlah & ",'" & ReadJob(rst!Job) & > "')" > > CurrentDb.Execute strSQL, dbFailOnError > > rst.MoveNext > > Next > > End If > > > > rst.Close > > Set rst = Nothing > > > > > > Cara2_Keluar: > > Exit Sub > > > > Cara2_Err: > > Debug.Print "Read Job Error Is : " & Err.Number & vbCrLf & Err.Description > > Resume Cara2_Keluar > > > > End Sub >
