SQL INSERT Syntax

  • Thread starter all21 via AccessMonster.com
  • Start date
A

all21 via AccessMonster.com

dear all......
i have a form with save button inside.....
my concept is if i click the save button, the recors is daved then add new
record in database
but if when i click the save button, it only says error message 2501 he
runcommand avtion was cancelled.....
i've try to find any solution in much forum, but i still not statisfied of
the rsult..
now i was thinking........how if i change my concept to sql.....
means : in form, i only use unbound textbox, and if i click savebutton, it
runs any sql statement like this : INSERT INTO tbpelunasanutang (field1
field2 field3........) FROM (idon't know what to write here :))
can u help me with the statement ?
this is the structure of my dtabase that i use in the form :
1. tblpembelianheader :
|Field Name |Data Type |Description |
|Notransaksi |Text | |
[quoted text clipped - 5 lines]
|TotalBeli |Number | |
|byr |Yes/No |Bayar Cash Aau Tidak |

2. tbpelunasanutang :
|Field Name |Data Type |Description |
|NoPembayaran |Text | |
[quoted text clipped - 6 lines]
|TotalUtang |Number | |
|TotalByr |Number | |

3. qryutang :
SELECT TBLPembelianHeader.NoTransaksi, TBLPembelianHeader.Tanggal,
TBLPembelianHeader.NoVendor, TBLVendor.NamaPerusahaan, TBLPembelianHeader.
TotalQty, TBLPembelianHeader.TotalBeli, TBLPembelianHeader.bayar,
TBLPembelianHeader.Status
FROM TBLVendor INNER JOIN TBLPembelianHeader ON TBLVendor.NoVendor =
TBLPembelianHeader.NoVendor
WHERE (((TBLPembelianHeader.bayar)<>True) AND ((TBLPembelianHeader.Status)
<>"Retur"));

Help me with this one please............

warm regrads,
all21
 
S

Steve Sanford

If you have 3 fields -

NoPembayaran - Text
TotalUtang - Number (integer)
TotalByr - Number (single)

and 3 unbound controls :

txtNoPembayaran
intTotalUtang
sngTotalByr

The barebones SQL to insert a new record using the unbound controls would
look like:

Public Sub cmdSaveRecord()
Dim strSQL As String

'create the SQL string
strSQL = "INSERT INTO tbpelunasanutang (NoPembayaran, TotalUtang, TotalByr)"
strSQL = strSQL & " VALUES ('" & Me.txtNoPembayaran & "'"
strSQL = strSQL & ", " & Me.intTotalUtang
strSQL = strSQL & ", " & Me.sngTotalByr & ");"

'insert the record
CurrentDb.Execute strSQL, dbFailOnError
End Sub

You will have to change the field names and control names to your names.

Validation code could/should be added to check if any of the controls are
blank/empty/Null before the record is inserted.

You could add a message box to say that the record was inserted, and maybe
add code to clear the unbound controls after a successful insert.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


all21 via AccessMonster.com said:
dear all......
i have a form with save button inside.....
my concept is if i click the save button, the recors is daved then add new
record in database
but if when i click the save button, it only says error message 2501 he
runcommand avtion was cancelled.....
i've try to find any solution in much forum, but i still not statisfied of
the rsult..
now i was thinking........how if i change my concept to sql.....
means : in form, i only use unbound textbox, and if i click savebutton, it
runs any sql statement like this : INSERT INTO tbpelunasanutang (field1
field2 field3........) FROM (idon't know what to write here :))
can u help me with the statement ?
this is the structure of my dtabase that i use in the form :
1. tblpembelianheader :
|Field Name |Data Type |Description |
|Notransaksi |Text | |
[quoted text clipped - 5 lines]
|TotalBeli |Number | |
|byr |Yes/No |Bayar Cash Aau Tidak |

2. tbpelunasanutang :
|Field Name |Data Type |Description |
|NoPembayaran |Text | |
[quoted text clipped - 6 lines]
|TotalUtang |Number | |
|TotalByr |Number | |

3. qryutang :
SELECT TBLPembelianHeader.NoTransaksi, TBLPembelianHeader.Tanggal,
TBLPembelianHeader.NoVendor, TBLVendor.NamaPerusahaan, TBLPembelianHeader.
TotalQty, TBLPembelianHeader.TotalBeli, TBLPembelianHeader.bayar,
TBLPembelianHeader.Status
FROM TBLVendor INNER JOIN TBLPembelianHeader ON TBLVendor.NoVendor =
TBLPembelianHeader.NoVendor
WHERE (((TBLPembelianHeader.bayar)<>True) AND ((TBLPembelianHeader.Status)
<>"Retur"));

Help me with this one please............

warm regrads,
all21
Click to expand...
 
A

all21 via AccessMonster.com

dear mr steve.........
thank u for your advance....
but, can u give me an example code to add a new record after insert the data??
?


regrads,


umar yusuf masyhur
 
S

Steve Sanford

You don't!! The insert IS the new record. Why do you want to enter a blank
record???

The controls on the form are unbound. You enter the data, then click the
SAVE button. The code uses SQL to insert a record into a table. Then you
should clear the unbound controls to be ready to add new data.
 
A

all21 via AccessMonster.com

Ok I understand now..........
thanks.............
i'll try it now & send a report to u............
 
A

all21 via AccessMonster.com

dear mr.
thanks for your advance
i've try it, but i still have problem
this my code for save button:
Private Sub save_click()
On Error GoTo Err_save_Click
Dim strSQL As String
If IsNull(Me.NoPembayaran) Then
MsgBox "No Pembayaran Tidak Boleh Kosong!", vbCritical, "Perhatian"
Else
If IsNull(Me.No_Pembelian) Then
MsgBox "Pilih Nomor Trnasaksi Pembelian!", vbCritical, "Perhatian"
Else
If IsNull(Me.totalbyr) Then
MsgBox "Masukkan Jumlah Uang yang Dibayarkan!", vbCritical, "Perhatian"
Else
strSQL = "INSERT INTO tbpelunasanutang (NoPembayaran, Tanggalbyr, NoTransaksi,
KodeSupplier, NamaSupplier, tangaltrx, TotalQty, Totalutg, TotalByr)"
strSQL = strSQL & " VALUES ('" & Me.NoPembayaran & "'"
strSQL = strSQL & ", " & Me.Tanggal
strSQL = strSQL & ", " & Me.No_Pembelian
strSQL = strSQL & ", " & Me.Text24
strSQL = strSQL & ", " & Me.KodeSupplier
strSQL = strSQL & ", " & Me.Text29
strSQL = strSQL & ", " & Me.TotalQty
strSQL = strSQL & ", " & Me.TotalBeli
strSQL = strSQL & ", " & Me.totalbyr & ");"

CurrentDb.Execute strSQL
End If
End If
End If
If Me.Totalutg - Me.totalbyr <= 0 Then
CurrentDb.Execute "UPDATE TBLPembelianHeader SET TBLPembelianHeader.bayar =
True WHERE (((TBLPembelianHeader.NoTransaksi)='" & Me.No_Pembelian & "'));",
dbFailOnError
End If
Exit_save_Click:
Exit Sub

Err_save_Click:
MsgBox Err.Number & vbCrLf & Err.Description
Resume Exit_save_Click
End Sub

firs time i try to click the save button, it says "to many argument -1". than
i got ill and not try it for few days. now when i click the save button, it
says error on loading dll. and the code editor point to CurrentDb
 
A

all21 via AccessMonster.com

dear Mr.
i've try your code & my code became like this:

Private Sub save_click()
On Error GoTo Err_save_Click
Dim strSQL As String
If IsNull(Me.NoPembayaran) Then
MsgBox "No Pembayaran Tidak Boleh Kosong!", vbCritical, "Perhatian"
Else
If IsNull(Me.No_Pembelian) Then
MsgBox "Pilih Nomor Trnasaksi Pembelian!", vbCritical, "Perhatian"
Else
If IsNull(Me.totalbyr) Then
MsgBox "Masukkan Jumlah Uang yang Dibayarkan!", vbCritical, "Perhatian"
Else
strSQL = "INSERT INTO tbpelunasanutang (NoPembayaran, Tanggalbyr, NoTransaksi,
KodeSupplier, NamaSupplier, tangaltrx, TotalQty, Totalutg, TotalByr)"
strSQL = strSQL & " VALUES ('" & Me.NoPembayaran & "'"
strSQL = strSQL & ", " & Me.Tanggal
strSQL = strSQL & ", " & Me.No_Pembelian
strSQL = strSQL & ", " & Me.Text24
strSQL = strSQL & ", " & Me.KodeSupplier
strSQL = strSQL & ", " & Me.Text29
strSQL = strSQL & ", " & Me.TotalQty
strSQL = strSQL & ", " & Me.TotalBeli
strSQL = strSQL & ", " & Me.totalbyr & ");"

CurrentDb.Execute strSQL
End If
End If
End If
If Me.Totalutg - Me.totalbyr <= 0 Then
CurrentDb.Execute "UPDATE TBLPembelianHeader SET TBLPembelianHeader.bayar =
True WHERE (((TBLPembelianHeader.NoTransaksi)='" & Me.No_Pembelian & "'));",
dbFailOnError
End If
Exit_save_Click:
Exit Sub

Err_save_Click:
MsgBox Err.Number & vbCrLf & Err.Description
Resume Exit_save_Click
End Sub

but when i try it, sometimes it says "error to many argument -1", sometimes
it says "syntax error in strsql". then i got ill for few days so i cant touch
the aplication anymore. when i try it again, it says "error loading dll" and
the code editor pointing to CurrentDb
can u see what's wrong with my syntax?
 
S

Steve Sanford

Everything looks OK. Some things to look at:

- Is the first two lines of the code page:

Option Compare Database
Option Explicit

- Have you compiled the code? In the IDE, Click on "DEBUG/COMPILE"

- Add a "DEBUG.PRINT strSQL" statement before the line

CurrentDb.Execute strSQL

- Single step thru the code to find the line that has the error.

- Do all of the controls on the form have values?? And, in the strSQL line,
are they properly delimited?

Is this right?

Me.NoPembayaran - String
Me.Tanggal - Number
Me.No_Pembelian - Number
Me.Text24 - Number
Me.KodeSupplier - Number
Me.Text29 - Number
Me.TotalQty - Number
Me.TotalBeli - Number
Me.totalbyr - Number
 
Top