Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2014
    Posts
    12

    Unanswered: Send data to table from a form

    Hi All

    I have an issue with my form where i have a submit button
    My table name is tblReverse
    field are as per attached

    When i click on the submit button to send the data to a table, it gives me an error as per attached

    Is there a problem with my code:
    CurrentDb.Execute "insert into tblreverse(txtRequestedBy,dtRequestedDate,txtPrevB illTo,txtOrderNo,txtInvoiceNo,txtLicenceNo,txtInst ruction,attAttachment,mmRemarks)" & _
    "values(""," & Me.ccReq & ",'" & Me.ccDate & "','" & _
    Me.ccPrev & ",'" & Me.ccOrder & ",'" & Me.ccInvoice & ",'" & Me.ccLic & ",'" & Me.ccInst & ",'" & Me.ccAtt & ",'" & Me.ccRem & "')"

    Im new in VBA and i cant understand these code which i get from google

    Can you please help me ,

    Thanking you
    Attached Thumbnails Attached Thumbnails Data Error.jpg   field Data.jpg  
    Last edited by ludovic_44; 09-07-14 at 09:50.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    string columsn should be delimited with a pair of either single or double quotes
    date columns should be delimited with a pair of hash symbols #, and be in either ISO (yyyy/mm/dd) OR US format mm/dd/yyyy, but NPOT resot of the world format dd/mm/yyyy
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2014
    Posts
    12
    HI
    I did what you told me, i google, but still i have another error now:

    Run-time error '3134':
    Syntax error in INSERT INTO statement


    I changed my code to :
    CurrentDb.Execute "insert into tblreverse(txtRequestedBy,dtRequestedDate,txtPrevB illTo,txtOrderNo,txtInvoiceNo,txtLicenceNo,txtInst ruction,attAttachment,mmRemarks)" & _
    "values(" & Me.ccReq & ", # & Me.ccDate & #, " & _
    Me.ccPrev & ", " & Me.ccOrder & ", " & Me.ccInvoice & ", " & Me.ccLic & ", " & Me.ccInst & ", " & Me.ccAtt & ", " & Me.ccRem & ")"

    I really cant figure it out where lies the issue
    Can you please have a look

    Thanking you

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Change your code to:
    Code:
        Dim strSQL As String
        
        strSQL = "insert into tblreverse(txtRequestedBy,dtRequestedDate,txtPrevB illTo,txtOrderNo,txtInvoiceNo, " & _
                 "txtLicenceNo,txtInst ruction,attAttachment,mmRemarks) " & _
                 "values(" & Me.ccReq & ",#" & Me.ccDate & "#," & Me.ccPrev & ", " & Me.ccOrder & ", " & Me.ccInvoice & "," & _
                 Me.ccLic & ", " & Me.ccInst & ", " & Me.ccAtt & ", " & Me.ccRem & ")"
        CurrentDb.Execute strSQL, dbFailOnError
    That way, if you still get an error, you can open the debug window (Ctrl+G), print the value of the SQL expression (? strSQL), open a new query in SQL view and copy the value of the SQL expression from the debug window into it. When you try to execute the query, any syntax error will be highlighted.

    Note: Dont forget the space between the closing parenthese of the columns list and the VALUES keyword.
    Have a nice day!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You are not delimiting text values with ' or "
    nor ate you delimiting date values with #
    I dont know what country you are from so I dont know if your default date vale us in iso or us format.

    There is no space between the closing bracket of the column list and the word values... that may not br an issue.

    values ('" & atextcontrol & "', #" & format(adatecontrol, "yyyy/mm/dd") &"#, " & .....
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2014
    Posts
    12
    Hi All,

    Thanking very much for the prompt reply, but really, im so silly, I cant figure it out, im still facing some issue, I attached it, can you please have a look where I make the mistake
    thank yu for the help
    Attached Files Attached Files

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and the problem is in what form?
    what is stopping you positing the code here
    AND the full error message
    AND the full SQL...
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Apr 2014
    Posts
    12
    Hi healdem,

    Thanks for your message ,
    The error is on the form "frmReversal3, when clicking on the button "Command25"
    I got the errors, go to the VBA, I have written the code as it were said here, but I cant figure where comes the error
    Can you please have a look onto please

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The reason why I ask is that it seem to work, but not knowing what the form should do, nor whcuih form has the problem nor sample data which would indicate the issue
    let alone no SQL... frankly its impossible to diagnose a problem


    why you've ignored Sinndho's exccellent advice toassignt he fvalue of the SQL to a variable before using it mystifies me... his reason being it makes debugging your SQL a breeeze.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Apr 2014
    Posts
    12
    I really appreciate the help of Sinndho, but I don't know how to do it, despite, he explaine it to me clearly,
    I don't know how to proceed, im sorry,
    The form reversal, when you inpu data on it, and you click on the button, all datas are supposed to be transferred on the table tblReverse,
    But I got the error
    Run time error 3075
    I don't know this error
    when you clock on the button, which error displayed to you

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no when if fill in the data, click the button it works....
    ..the only case where it trips up is if there is no value in the date control. you need to decide how you handle that
    do you put in a default
    do you reject the update till there is a valid date and so on

    but then again I don't know what data you tested
    you haven't given any indication of what the error is beyond its 3075 which is a missing operator. usually the words following the generic messgae point to where the SQL engine triped up, and thats usually where the error is. iin very rare cases the error message may not help, but it shoudl always be your first point of call

    because you haven't shown the sql thats failing its impossible to tell if its a data error, a design error a code error or whatever

    you need to be more specific about what the problem is
    ive opened up your db here changed the code to:
    Code:
    strSQL = "INSERT INTO tblreverse(txtRequestedBy,dtRequestedDate,txtPrevBillTo,txtOrderNo,txtInvoiceNo,txtLicenceNo,txtInstruction) " & _
    "values('" & Me.ccReq & "', #" & Format(Me.ccDate, "yyyy/mm/dd") & "#, '" & _
    Me.ccPrev & "', '" & Me.ccOrder & "', '" & Me.ccInvoice & "', '" & Me.ccLic & "', '" & Me.ccInst & "')"
    
    CurrentDb.Execute strSQL
    and it works for data in all controls
    one thing I did notice is that the column named mmremark doesn't seem to be picking up the right value from the recordset

    incidentally saying "it doesn't" work isn't helpful, and is highly likely to put possibel respondents off.
    failing to supply information as requested suggests that you value your time, and don't give a fig for volunteers time, those who are prepared to look at your problem and help you get it working.

    by assigning the SQL to a variable it measn its easy for you to look at the SQL and you to post it here. you can see the sql by placing your mouse cursor over the varaible or in the immediate window ?strSQL
    Last edited by healdem; 09-07-14 at 16:32.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Apr 2014
    Posts
    12
    healdem

    You are my Hero, you solved my problem, it seems that the code work now,
    Thank you very much dear, god bless you for having help me, I was really in trouble, as I have had to deliver this tomorrow,
    Thank you very much,

    But I don't know how to tag it as solved

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    but the issue is I DID NOT CHANGE anything in your code.... except to assign the SQL to a variable. so there was no error to fix........ Aside fromthe error in your dependant form
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •