Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Anderson, Indiana, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert Into problems (2002 SP3)

    I am still new at using code and I am having trouble with the following code. I am trying to take three fields from the main form and one field from another table and insert them together into a repair table.
    The results should look like this when a button on the main form is clicked so all they have to do is fill in the qty field: see attachment

    The Repairs coming from the [tb_repair_list_mstr].[ repair_list_id_mstr] and the Line, Date, Shift coming from the main form [Forms]![frm_1_prod_line_input]![prod_line_date].

    Private Sub prod_line_shift_Exit(Cancel As Integer)
    Dim strSQL As String

    strSQL = "INSERT INTO 2_tb_prod_repair_input" _
    & " ([prod_repair_line], [prod_repair_date], [prod_repair_shift], [prod_repair_id])" _
    & " SELECT [Forms]![frm_1_prod_line_input]![c_prod_line]," _
    & " & [Forms]![frm_1_prod_line_input]![prod_line_date]," _
    & " & [Forms]![frm_1_prod_line_input]![c_prod_shift], " _
    & " [tb_repair_list_mstr].[repair_list_id_mstr]" _
    & " FROM [tb_repair_list_mstr] WHERE" _
    & " [tb_repair_list_mstr].[repair_list_line_mstr] = ([Forms]![frm_1_prod_line_input]![c_prod_line])"
    DoCmd.RunSQL strSQL
    End Sub

    Am I even on the right track or am I way off base. Please any help would be great.

    Thank you,

    Winston

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert Into problems (2002 SP3)

    You've got a couple of superfluous ampersands & within the string. Try

    strSQL = "INSERT INTO 2_tb_prod_repair_input" _
    & " ([prod_repair_line], [prod_repair_date], [prod_repair_shift], [prod_repair_id])" _
    & " SELECT [Forms]![frm_1_prod_line_input]![c_prod_line]," _
    & " [Forms]![frm_1_prod_line_input]![prod_line_date]," _
    & " [Forms]![frm_1_prod_line_input]![c_prod_shift], " _
    & " [tb_repair_list_mstr].[repair_list_id_mstr]" _
    & " FROM [tb_repair_list_mstr] WHERE" _
    & " [tb_repair_list_mstr].[repair_list_line_mstr] = ([Forms]![frm_1_prod_line_input]![c_prod_line])"

  3. #3
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Anderson, Indiana, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Into problems (2002 SP3)

    Hans,

    Before it would tell me incorrect syntax, now I get a Run Time Error '170' Line 1: incorrect syntax near '2'.
    I will take the database home tonight and make it small engough to post as I am sure having it would help.

    Thank You,

    Winston

  4. #4
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Anderson, Indiana, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Into problems (2002 SP3)

    While watching a recording of Monday Night Raw at home I sat down and recreated the database to attach to this post. I discovered at home what Hans had posted worked perfect. I went to work the next day thinking it worked at home, so it should do the trick. As I probably just have a spelling error, but it did not work. The only difference is I have 2000 at home and 2002 at work. Not seeing how that could make a difference, I tried a little different approach. I finally got it to run properly at work. Thanks for the initial help Hans, as it showed that I was trying to do something that could be done. Here is a posting of it just incase it can help someone else. Note that Hans

Posting Permissions

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