Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2003
    Location
    Manchester, NH
    Posts
    57

    Question Unanswered: Inserting form data into table Help!

    I have a form based off a query and I want to print this form and then save the data on the form into another table. I'm assuming I can use an SQL insert statement like this:

    Insert into faxLog values (Combo6, FaxNumber, Combo8, page, Combo10, Combo14, Date, Time, numbers1, numbers2, numbers3)

    But I can't seem to figure out how to make that work with the On-click event. I want to print the form and then save the data to this other table and close the form all with one click. Any help would be VERY appreciated!

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I'll just assume you have a real good reason for storing the same data in your database twice....

    Your string should look something like

    Code:
    strsql="insert into faxlog values(" & combo6 & ", '" & faxnumber & "', ...)"
    *note - make sure you use ' around text fields
    Inspiration Through Fermentation

  3. #3
    Join Date
    Apr 2003
    Location
    Manchester, NH
    Posts
    57

    Question Well it doesn't give me an error....

    but it is NOT inserting the data from the form into the table. The reason behind me storing the data, is because of the backwards way I made the database. I made a form that feeds off of a query. The purpose of this was to be able to use a combo box to populate one other piece of data relevant to the combobox selection made..i.e. someone picks a company from the drop down box and that companies fax number populates on the form. At that point the user is selecting or entering new data in the rest of the fields and I want to save that data in a table to keep track of data entered through a report I made. Do I have to use the table I want to insert into as the control source for the form to make this work?

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    How are you executing the query?

    docmd.runsql strsql

    or

    cnn.execute strsql

    or

    something else?
    Inspiration Through Fermentation

  5. #5
    Join Date
    Apr 2003
    Location
    Manchester, NH
    Posts
    57

    Question Here's exactly what I have...

    DoCmd.RunSQL strsql = "INSERT INTO faxLog values(""," & Combo6 & ", '" & FaxNumber & "', " & Combo8 & ", '" & numPages & "', " & Combo10 & ", " & Combo14 & ", " & date & ", " & time & ", '" & numbers1 & "', '" & numbers2 & "', '" & numbers3 & "',)"

    the first set of open/close quotes is for the autonumber field in the table it's going into.

  6. #6
    Join Date
    Apr 2003
    Location
    Manchester, NH
    Posts
    57

    Question and the error I'm getting now is....

    Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'.

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You need to seperate the two lines:

    strsql = "INSERT INTO faxLog values(""," & Combo6 & ", '" & FaxNumber & "', " & Combo8 & ", '" & numPages & "', " & Combo10 & ", " & Combo14 & ", " & date & ", " & time & ", '" & numbers1 & "', '" & numbers2 & "', '" & numbers3 & "',)"

    DoCmd.RunSQL strSql


    Also, you don't need to put the "" in for the autonumber field, but I'm not sure it will work that way either. You may need to state the column names in the source table:

    strsql = "INSERT INTO faxLog (somefield, faxnumber, anotherfield, ...) values(" & combo6 & "...

    The autonumber field will take care of itself.
    Inspiration Through Fermentation

  8. #8
    Join Date
    Apr 2003
    Location
    Manchester, NH
    Posts
    57

    Talking It's working fine now...

    Thank you Redneck for the help!

  9. #9
    Join Date
    Apr 2003
    Location
    Manchester, NH
    Posts
    57

    Lightbulb Just a heads up though...

    I found that I had to use single quotes around EVERY field regardless of what kind i.e. text or combo box. Other than that, I had to get rid of the autonumber field (Data type mismatch issues whenever I left those double or single quotes in), it wasn't liking that at all, no real loss for me, but just a heads up.

  10. #10
    Join Date
    Jun 2009
    Posts
    1

    Inserting form data into table

    I found this posting very helpful as I'm trying to do a similar task. In my case, I have a form that is built on a table of "issues" that are posted by various members of a project team. Management would like to see a report of a subset of the fields of all the medium and high ranking issues that have not been closed. I built a query that retrieves the correct records. The user would like to edit some of the fields of the records to perhaps get only the latest text regarding status and resolutions before printing the report. I am trying to save this modified record set to another table so the report can be built off of it.

    The solution here works for me for the current record. How do I loop through and get all the records as they exist in the form.
    Thanks for your help.

Posting Permissions

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