does anyone know how i can loop through all the records and insert these into the tblTestresults table
You have two options: An Append query or VBA code.
But first, be aware that "Max" and "Min" are reserved words in Access and shouldn't be used as object names.
*** OK, the following might not work right immediately because I don't know your table structures or field types. You'll probably have to do some tweaking.
So, the query option.
You can create an append query that adds the records all at once.
It would look something like:
Code:
INSERT INTO tblTestsResults ( ReelNumber, PartNumberID, PartNumber, Area, Test, [Max], [Min], Nom )
SELECT tblTestsRequired.ReelNumber, tblTestsRequired.PartNumberID, tblTestsRequired.PartNumber, tblTestsRequired.Area, tblTestsRequired.Test, tblTestsRequired.Max, tblTestsRequired.Min, tblTestsRequired.Nom
FROM tblTestsRequired
WHERE (((tblTestsRequired.PartNumber) = FORMS!frmPartNumber!PartNumberID));
"FORMS!frmPartNumber!PartNumberID" is the control on the form that limits (or selects) the records that will be appended to the table "tblTestsResults". (I may have picked the wrong control)
The query can be executed from a button click using something like:
Code:
Private MyButtonName_Click()
CurrentDb.Execute "MyAppendQuery", dbfailonerror.
End Sub
VBA Code option
I think you are getting forms and tables/recordsets a little mixed up.
Forms display data. They do not "have" data the way tables do.
In your example code, the data on the form does not change even though record set "rs" moves from record to record.
The record source of the form is independent of the record set in code.... so the data in the form doesn't change. And since you are referencing the controls that are on the form that don't change, all records appended/inserted have the same values.
Here is the code (UNTETSTED) I came up with. For the text type fields, you will need to add delimiters to the code.
I named the button "cmdAddTests"
Code:
Private Sub cmdAddTests_Click()
Dim db As DAO.database
Dim rs As DAO.Recordset
Dim sSQL As String
Set db = CurrentDb
'Set rs = db.OpenRecordset("tblTestsRequired") 'myTable is a MS-Access table created previously
'open a record set of the records to be added to the other table
' limited by "PartNumberID"
sSQL = "SELECT ReelNumber, PartNumberID, PartNumber, Area, Test, Max, Min, Nom FROM tblTestsRequired"
sSQL = sSQL & " WHERE PartNumberID = " & Me.PartNumberID 'Me.PartNumberID is the control on the form
' Debug.Print sSQL
'create the recordset
Set rs = db.OpenRecordset(sSQL)
'
'check to see if there are records in the recordset
If Not rs.bof And Not rs.EOF Then
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
sSQL = "INSERT INTO tblTestsResults(ReelNumber, PartNumberID, PartNumber, Area, Test, Max, Min, Nom) "
sSQL = sSQL & " VALUES( " & rs!reelNumber & ", " & rs!PartNumberID & ", " & rs!PartNumber & ", " & rs![Area] & ", "
sSQL = sSQL & rs!TestName & ", " & rs![MAX] & ", " & rs![Min] & ", " & rs![Nom] & ")"
' Debug.Print sSQL
'insert the record
db.Execute sSQL, dbfailonerror
rs.MoveNext 'press Ctrl+G to see debuG window beneath
Loop
End If
'clean up - close recordsets and destroy objects created
rs.Close
Set rs = Nothing
Set db = Nothing
MsgBox ("Done")
End Sub