MS Access - Assigning a variable to add fixed Values to VBA/SQL INSERT/SELECT statement-CodePudding

I'm trying to append records from one table to another, the code I have works but I want to change one of the values being inserted so it populated from the main form not the table where the records are being copied from

Is it possible to populate the [Job Number] Feild from the JbNum variable??

Dim db        As DAO.Database
  Dim strSQL    As String
  Dim PrtNbrGt  As String
  Dim JbNum     As String
  
  JbNum = Me.[tblManufactured.Job Number]
  PrtNbrGt = Me.SCSPartNumb

Set db = CurrentDb
strSQL = "INSERT INTO tblMaterialRequiremnt ( [Customer Part Number], [Job Number] ) " & vbCrLf & _
"SELECT tbl_BOM_Requirments.RequiredMaterialPrtNum, tbl_BOM_Requirments.ID " & vbCrLf & _
"FROM tbl_BOM_Requirments " & vbCrLf & _
"WHERE (tbl_BOM_Requirments.PrtNmber_LinkField) = """ & PrtNbrGt & """"

[Job Number] = JbNum

db.Execute (strSQL)

CodePudding user response：

Try this:

Dim db        As DAO.Database
Dim strSQL    As String
Dim PrtNbrGt  As String
Dim JbNum     As String
  
JbNum = Me.[tblManufactured.Job Number]
PrtNbrGt = Me.SCSPartNumb

Set db = CurrentDb
strSQL = "INSERT INTO tblMaterialRequiremnt ( [Customer Part Number], [Job Number] ) " & vbCrLf & _
"SELECT tbl_BOM_Requirments.RequiredMaterialPrtNum, tbl_BOM_Requirments.ID, '" & JbNum & "' & vbCrLf & _
"FROM tbl_BOM_Requirments " & vbCrLf & _
"WHERE (tbl_BOM_Requirments.PrtNmber_LinkField) = """ & PrtNbrGt & """

db.Execute strSQL