Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2008
    Posts
    5

    Unanswered: Form fields to different table

    I hope someone can help me out, what I want to do is adding a record to a table based on the fields on a form
    for example the following situation, which I later can modify for my database

    tabel = tbl2
    form = frm1
    fields are fld1, fld2, fld3, fld4, fld5

    Thanks in advance

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Have a look in the help files for AddNew.... it should give you a code sample that will answer your question.

    It's a simple matter of opening a Recordset, doing an AddNew, populating the fields and then running an Update.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Sep 2008
    Posts
    5
    Quote Originally Posted by StarTrekker
    Have a look in the help files for AddNew.... it should give you a code sample that will answer your question.

    It's a simple matter of opening a Recordset, doing an AddNew, populating the fields and then running an Update.
    What I have now is this, but i can't get it right, what is wrong here ?

    Dim write_record As String
    SQL_write = "Insert into tabel2([veld1], [veld2], [veld3], [veld4])" & _
    "values ('" & Forms!frm1!field1 & "'," & Forms!frm1!field2 & "," & Forms!frm1!field3 & "," & Forms!frm1!field4)'")
    DoCmd.RunSQL write_record
    Last edited by densanan; 09-04-08 at 06:57.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, so first, there is no space before the word VALUES.
    Secondly, You declare one variable (write_record) and then assign your SQL to a variable called SQL_write and finally you run the RunSQL command with the variable write_record again. You should have Option Explicit in your modules to avoid problems like this... and ones that are harder to find too.

    Lastly, I'd use a recordset, but the SQL gurus here might yell at me there
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and
    currentdb.execute YourSQLgoesHere
    is faster and doesn't send you stupid warning messages

    ...and
    with
    "values ('" & Forms!frm1!field1 & "'
    you are treating field1 as string data (OK if it is string)
    with
    ," & Forms!frm1!field2 & "," & Forms!frm1!field3 & ",
    you are treating both as numeric. are they?
    with
    " & Forms!frm1!field4)'"
    you lost the plot: the trailing ' is fine for string, but where is the opening '. or if it is numeric, kill the trailing '

    honestly - it is not difficult, but it is exacting: precisely zero casual errors permitted.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Sep 2008
    Posts
    5
    Quote Originally Posted by izyrider
    ...and
    currentdb.execute YourSQLgoesHere
    is faster and doesn't send you stupid warning messages

    ...and
    with
    "values ('" & Forms!frm1!field1 & "'
    you are treating field1 as string data (OK if it is string)
    with
    ," & Forms!frm1!field2 & "," & Forms!frm1!field3 & ",
    you are treating both as numeric. are they?
    with
    " & Forms!frm1!field4)'"
    you lost the plot: the trailing ' is fine for string, but where is the opening '. or if it is numeric, kill the trailing '

    honestly - it is not difficult, but it is exacting: precisely zero casual errors permitted.

    izy
    Thanx, got it working now

Posting Permissions

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