title: pass vba variable to sql
body: <p>I have implement a command in access that once its clicked, it will insert new row to the corresponding table. The problem is its not passing variables to SQL, it just insert rows with ACTUAL string..like myDate...myId....Can some one help me to fix that...</p>
  
  <pre><code>Private Sub Command34_Click()
  On Error GoTo Err_Command34_Click
  
      Dim myDate As Date
      Dim myQty As Long
      Dim myAdj As Long
      Dim myReason As String
      Dim myId As Long
  
  
      Dim sqlMyDate As String
      Dim sqlMyQty As String
      Dim sqlMyAdj As String
      Dim sqlMyReason As String
      Dim sqlMyID As String
  
  
      Dim mySqlInsert As String
  
      sqlMyDate = "SELECT TOP 1 [DATE OF PHYSICAL INVENTORY] FROM [PHYSICAL INVENTORY FINISHED GOODS] WHERE [JOB #]=""2188"" ORDER BY [PHYSICAL INVENTORY FINISHED GOODS].[ID] DESC;"
      sqlMyQty = "SELECT TOP 1 [REQ'D ADJUSTMENT] FROM [PHYSICAL INVENTORY FINISHED GOODS] WHERE [JOB #]=""2188"" ORDER BY [PHYSICAL INVENTORY FINISHED GOODS].[ID] DESC;"
      sqlMyAdj = "SELECT TOP 1 [REQ'D ADJUSTMENT] FROM [PHYSICAL INVENTORY FINISHED GOODS] WHERE [JOB #]=""2188"" ORDER BY [PHYSICAL INVENTORY FINISHED GOODS].[ID] DESC;"
      sqlMyReason = "SELECT TOP 1 [REASON ADJUSTMENT REQ'D] FROM [PHYSICAL INVENTORY FINISHED GOODS] WHERE [JOB #]=""2188"" ORDER BY [PHYSICAL INVENTORY FINISHED GOODS].[ID] DESC;"
      sqlMyID = "SELECT TOP 1 [ID] FROM [PHYSICAL INVENTORY FINISHED GOODS] ORDER BY [PHYSICAL INVENTORY FINISHED GOODS].[ID] DESC;"
  
       myDate = CurrentDb.OpenRecordset(sqlMyDate).Fields(0).Value
       myQty = CurrentDb.OpenRecordset(sqlMyQty).Fields(0).Value
       myAdj = 0 - CurrentDb.OpenRecordset(sqlMyAdj).Fields(0).Value
       myId = CurrentDb.OpenRecordset(sqlMyID).Fields(0).Value + 1
  
  
       If CurrentDb.OpenRecordset(sqlMyReason).Fields(0).Value = Null Then
       myReason = "No Reason"
       Else
       myReason = CurrentDb.OpenRecordset(sqlMyReason).Fields(0).Value
       End If
  
      DoCmd.RunSQL "INSERT INTO [PHYSICAL INVENTORY FINISHED GOODS] ([ID],[JOB #],[DATE OF PHYSICAL INVENTORY],[QTY FORMED],[QTY WIP],[QTY ON HAND],[REQ'D ADJUSTMENT],[REASON ADJUSTMENT REQ'D],[DATE POSTED]) VALUES (""&amp;myId&amp;"",""2180"",""&amp;myDate&amp;"",""0"",""0"",""&amp;myQty&amp;"",""&amp;myAdj&amp;"",""&amp;myReason&amp;"");"
  
  Exit_Command34_Click:
      Exit Sub
  Err_Command34_Click:
      MsgBox Err.Description
      Resume Exit_Command34_Click
  </code></pre>