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

Question

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)

Gustav · Accepted Answer · 2022-09-26 11:31:56Z

1

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

answered Sep 26, 2022 at 11:31

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sambob_628 Over a year ago

Hi Gustav, thanks for your help.. its caused a compile error/syntax error expected end of statement on the FROM line

sambob_628 Over a year ago

Got It... its working now.... Thanks very much..."SELECT tbl_BOM_Requirments.RequiredMaterialPrtNum, '" & JbNum & "'" & vbCrLf & _

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related