I want to select data from tblMedicalRecords which match a certain critera and write them to a table called tblWeights. Below is an example of what the SQL query would look like.
The records from the table tblDogs have to match the value that is in field ReproductionID with the value in variable ReproID. Those records' DogID should then match up with the value in Dogid from table tblMedicalTreatments where TreatmentTypeID in tblMedicalTreatments should equal 7.
The selected records should then be written to table tblWeights.
I know this should be done within a recordset and I'm not at all familiar with recordsets yet.
I would very much appreciate help in setting up a recordset that will give me the result as explained above.
SQLQuery:
SELECT tblDogs.PuppyNumber, tblMedicalTreatments.TreatmentDate, tblMedicalTreatments.Weight, tblMedicalTreatments.TreatmentTypeID
FROM tblMedicalTreatments INNER JOIN tblDogs ON tblMedicalTreatments.DogID = tblDogs.DogID
WHERE (((tblMedicalTreatments.TreatmentTypeID)=7))
ORDER BY tblDogs.PuppyNumber;
The records from the table tblDogs have to match the value that is in field ReproductionID with the value in variable ReproID. Those records' DogID should then match up with the value in Dogid from table tblMedicalTreatments where TreatmentTypeID in tblMedicalTreatments should equal 7.
The selected records should then be written to table tblWeights.
I know this should be done within a recordset and I'm not at all familiar with recordsets yet.
I would very much appreciate help in setting up a recordset that will give me the result as explained above.
SQLQuery:
SELECT tblDogs.PuppyNumber, tblMedicalTreatments.TreatmentDate, tblMedicalTreatments.Weight, tblMedicalTreatments.TreatmentTypeID
FROM tblMedicalTreatments INNER JOIN tblDogs ON tblMedicalTreatments.DogID = tblDogs.DogID
WHERE (((tblMedicalTreatments.TreatmentTypeID)=7))
ORDER BY tblDogs.PuppyNumber;
Code:
Dim sql As String
Dim MotherID As Long
MotherID = Me.DogID
Dim Mother As String
Mother = Me.CallName
Dim Litternumber As Long
Litternumber = InputBox("Enter Litter number")
Dim ReproID As Long
ReproID = DLookup("reproductionid", "[tblreproduction]", "[motherid]=" & [MotherID] & " and [mlittercount] = " & Litternumber & "")
DoCmd.OpenQuery "Empty tblWeights"
' Start Recordset....
DoCmd.RunSQL "INSERT INTO tblWeights (Puppynumber, Weight, TreatmentDate) " & _
"VALUES (" & PuppyNumber & ", " & Weight & ",,#" & TreatmentDate & "#)"
'.....Close Recordset