I have a table called lotContents that has two fields in it; mrn(primaryKey) and partNum both are a short-text datatype. I have created a form called workOrderCreationForm that has a text box that auto-generates a work order number. Then the two other text-boxes on the form are called firstMrn and lastMrn respectively. I'm trying to find a way to query the results between two values and add the Work Order Number to each record and then place the new data in the workOrderContents table(mrn,partNum, workOrderNumber(foreign Key)).I have written a sql statement to query the results from lotContents between the two textboxes(firstMrn and lastMrn). The Sql statement looks like this:
I have two questions, how can I get this sql statment to actually work and send the queried data of this query to the table(workOrderContents)and second how can I appended the variable workOrderNumber to each record so when viewed from the workOrderContents table you will see mrn,partNum,workOrderNumber.
Code:
Private Sub Command8_Click()
'set the work order number to the "woNum" variable
woNum = Me.workOrderNumber_TextBOx
'select all mrn's between the first and last mrn values
'set firstMrn_TextBox to the variable "firstMrn"
firstMrn = Me.firstMrn_TextBox
'set lastMrn_TextBox to the variable "lastMrn"
lastMrn = Me.lastMrn_TextBox
'set the sql statement
SQL = "INSERT INTO workOrderContents" _
& "SELECT lotContent.mrn, lotContent.partNumber" _
& "FROM lotContent" _
& "WHERE lotContent.mrn BETWEEN '" & firstMrn & "' AND '" & lastMrn & "'" _
& "ORDER BY lotContent.mrn"
End Sub