Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    2

    Unanswered: INSERT INTO statement having local variable and SELECT statement as parameters in VBA

    Hi,

    I am trying to write a VBA code that is an INSERT INTO statement. But the parameters I am passing are of different types. One of them is a local variable where as the other one is a SELECT statement. They are working separately. But I can't seem to figure out how to put them tgether. For example -

    Syntax for local variable that works -
    oCmd3.CommandText = "Insert Into tmsht (emp_no) Values ('" & Form_Login_TimeSheet.EmpNo & "')"

    Syntax for SELECT statemant that works -
    oCmd3.CommandText = "Insert Into tmsht (dt_id) SELECT dt_id FROM tm_dy WHERE dt = #" & Form_Login_TimeSheet.WS.Cells(16, i).Value & "#"

    What I need to know is how to put these two codes into a single one so I can update both the columns. Please help.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by Bappaditya
    Hi,

    I am trying to write a VBA code that is an INSERT INTO statement. But the parameters I am passing are of different types. One of them is a local variable where as the other one is a SELECT statement. They are working separately. But I can't seem to figure out how to put them tgether. For example -

    Syntax for local variable that works -
    oCmd3.CommandText = "Insert Into tmsht (emp_no) Values ('" & Form_Login_TimeSheet.EmpNo & "')"

    Syntax for SELECT statemant that works -
    oCmd3.CommandText = "Insert Into tmsht (dt_id) SELECT dt_id FROM tm_dy WHERE dt = #" & Form_Login_TimeSheet.WS.Cells(16, i).Value & "#"

    What I need to know is how to put these two codes into a single one so I can update both the columns. Please help.
    HI Bappaditya

    It is a little bit confusing on how many records you are trying to insert (if more than one) and I assume that both insert statements are inteneded for the same record. If so, then this may do it, but no guarantees!!

    Code:
    oCmd3.CommandText = "Insert Into tmsht (dt_id, emp_no) " & _
    "SELECT dt_id, '" &  Form_Login_TimeSheet.EmpNo & "' as EmpNo FROM tm_dy WHERE dt = #" & Form_Login_TimeSheet.WS.Cells(16, i).Value & "#"
    As I said no guarantees, but may be going in the right direction !??


    MTB

  3. #3
    Join Date
    May 2009
    Posts
    2
    Hi MTB,

    Your solution is correct. Thank you very much.

    - Bappaditya

Posting Permissions

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