Problem in A form

[yrsoullover]

hi every one,

i have a problem when im trying to create a button in a form, that button should insert data into a table using the values in the form opened. i.e. the form which has the command button

im doing the code like this

docmd.runsql "insert into table (tableName) Values(forms![FormName]![FieldName]


but it does not work


can any one help me pls


thanks

 

[edtab]

The syntax for insert statement is:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

with target being the destination table.

Example:

Docmd.RunSql "INSERT INTO tblInvoices ([CustID]) VALUES (forms!FormName!textboxname) ... where the value is coming from a form.

Make sure you provide the same number of parameters (ie. no. of fields equal the number of values).

Try restructuring your statement as in above example.

 

[yrsoullover]

Thanks for your help

but suppose i wanna the user to insert the the first field in the table then the other fields will be taken from the form


how can i do this??

 

[yrsoullover]

pls im waiting for your help

thanks for all of you

 

[Mike375]

It has only been 3 hours since your last post and it will be early morning in Canada

 

[yrsoullover]

ok thanks

im gonna be wait

dont u have a solution for that??

 

[yrsoullover]

well...

there is no reply til now


any one has an idea or any thing to help me??


please help


thanks

 

[gemma-the-husky]

your syntax isnt quite right - the stament should look somrthing like this when its finished

insert into mytable (myfield1, myfield2) values 12, "johnson"

=========================
if you declare a string variable

dim sqlstrg as string

and then do

sqlstrg = "insert into table (tableName) Values(forms![FormName]![FieldName]
msgbox(sqlstrg)

and only then

docmd.runsql sqlstrg

you will be able to see what your string really looks like

in your case what you realy need is more like

sqlstrg = "insert into tableName (myfield) Values " & (forms![FormName]![FieldName]

note that the string ends at values, and then there is an expresiion to retrieve the value from your form, which is quite different when its OUTSIDE the quote marks. Note also that the sysntax needs to be slightly different for numbers, text and dates

 

[yrsoullover]

thanks for yr help

i put this code

but til now it is not workin


pls have a look and tell me if it is correct or not

this is the code:


Dim sqlString, RefNo As String


RefNo = InputBox("Enter Ref No")


sqlString = "insert into table Quotation_Out values(" & RefNo & Forms![Quotation_In]![Item_no] & Forms![Quotation_In]![Source_Name] & Forms![Quotation_In]![Quatation_Way] & Forms![Quotation_In]![Title] & Forms![Quotation_In]![Customer_Name] & Forms![Quotation_In]![Attention] & Forms![Quotation_In]![Tel] & Forms![Quotation_In]![Mob] & Forms![Quotation_In]![E-mail] & Forms![Quotation_In]![RefDate] & Forms![Quotation_In]![Warrantly] & Forms![Quotation_In]![Brand_Name] & Forms![Quotation_In]![Model_Name] & Forms![Quotation_In]![ItemDesc] & Forms![Quotation_In]![Qty] & Forms![Quotation_In]![PurchasePrice] & Forms![Quotation_In]![Notes] & Forms![Quotation_In]![Deal] & Forms![Quotation_In]![Delivery time] & Forms![Quotation_In]![Payment terms] & Forms![Quotation_In]![Validity] & Forms![Quotation_In]![Delivery Place] & Forms![Quotation_In]![Purchase_Invoice] & Forms![Quotation_In]![Purchase_Date] & Forms![Quotation_In]![Purchase_way] & ")"

 

[DJNickWatt]

thanks for yr help

i put this code

but til now it is not workin


pls have a look and tell me if it is correct or not

this is the code:


Dim sqlString, RefNo As String


RefNo = InputBox("Enter Ref No")


sqlString = "insert into table Quotation_Out values(" & RefNo & Forms![Quotation_In]![Item_no] & Forms![Quotation_In]![Source_Name] & Forms![Quotation_In]![Quatation_Way] & Forms![Quotation_In]![Title] & Forms![Quotation_In]![Customer_Name] & Forms![Quotation_In]![Attention] & Forms![Quotation_In]![Tel] & Forms![Quotation_In]![Mob] & Forms![Quotation_In]![E-mail] & Forms![Quotation_In]![RefDate] & Forms![Quotation_In]![Warrantly] & Forms![Quotation_In]![Brand_Name] & Forms![Quotation_In]![Model_Name] & Forms![Quotation_In]![ItemDesc] & Forms![Quotation_In]![Qty] & Forms![Quotation_In]![PurchasePrice] & Forms![Quotation_In]![Notes] & Forms![Quotation_In]![Deal] & Forms![Quotation_In]![Delivery time] & Forms![Quotation_In]![Payment terms] & Forms![Quotation_In]![Validity] & Forms![Quotation_In]![Delivery Place] & Forms![Quotation_In]![Purchase_Invoice] & Forms![Quotation_In]![Purchase_Date] & Forms![Quotation_In]![Purchase_way] & ")"

You need to seperate the fields you are trying to insert into with "," plus I am assuming the number of fields in the table is equal to the number of values you are passing?? If not then declare your field names. I always do this anyways. Also if you are inserting text you need to add quotes and # around dates. Also don't write "table".

Example:

Field1: Number
Field2: Text
Field3: Text
Field4: Date
Field5: Text

sSQL = "INSERT INTO tblTable (Field1, Field2, Field3, Field4, Field5) VALUES(" & Forms![Form1]![txtField1] & ", """ & Forms![Form1]![txtField2] & """, """ & Forms![Form1]![txtField3] & """, #" & Forms![Form1]![txtField4] & "#, """ & Forms![Form1]![txtField5] & """);"

Note that putting double quotes inserts quotes into a string.