Creating INSERT INTO sql statement to append records to a table using a form's combo-boxes-CodePudding

I have just begun building a new database to store medical records. I'm trying to set up a form that will handle all the end user functions, which for now is simply to assign treatments to a patient from a list. I have three combo-boxes on the main form: Category, Treatment, and Patient. The Category combo-box pulls from the table tblCategories, and the Patient combo-box pulls from the table tblPatients. When a Category is selected, it updates the Query qryTreatmentsByCategory, which has criteria to limit the treatment records shown to those in the selected category. This Query is the source of the Treatment combo-box on the main form.

The goal is for the data in each combo-box to be appended to the table tblPatientReport, which has an autonumber primary key field and then a field for each of these three values. The PatientID field and the CategoryID field both append without issue; however, the TreatmentID field does not. Rather than append the Treatment's ID (the primary key of the table tblTreatments), it instead appends the selected treatment's position in the query.

For example, when choosing "Bladder Scan,' the Treatment ID should be 106. However, it is the 15th record in its' category when sorted alphabetically. Because the source data for the Treatment combo-box is the query qryTreatmentByCategory, the Treatment ID is instead listed as 15. However, the query's first field is the TreatmentID from tblTreatments, so I'm not sure why it instead chooses the record's position on the query.

The code for the INSERT INTO statement is as follows:

Private Sub btnAddTreatment_Click()
    DoCmd.RunSQL "INSERT INTO tblPatientReport (ptrPatientID, ptrTreatmentID, ptrCategoryID) values (cmbPatientName.Value, cmbTreatment.Value, cmbCategory.Value)"
End Sub

The code for the query qryTreatmentsByCategory, the source for the Treatment combo-box, is as follows:

SELECT tblTreatment.TreatmentID, tblTreatment.trItem, tblCategory.CategoryID
FROM tblCategory INNER JOIN tblTreatment ON tblCategory.CategoryID = tblTreatment.trCategory
WHERE (((tblCategory.CategoryID)=[Forms]![frmMain]![cmbCategory]));

How do I get the INSERT INTO statement to use the TreatmentID field that serves as the primary key for the tblTreatments, while still using qryTreatmentsByCategory as the source for the Treatment Combo-box. Again, the first column in qryTreatmentsByCategory is the TreatmentID field from tblTreatments, so I'm not sure why that data is not being pulled.

This is my first time submitting a question, so if I have bee unclear on anything just let me know and I'll try to explain further.

CodePudding user response：

The bound column of the treatment combo box must be 1.

The SQL string must be constructed with the combo box values.

DoCmd.RunSQL "INSERT INTO tblPatientReport (ptrPatientID, ptrTreatmentID, ptrCategoryID) values (" _
  & cmbPatientName.Value & ", " & cmbTreatment.Value & ", " & cmbCategory.Value & ")"

RunSQL will not automatically replace the combo box references inside the string by their values.

CodePudding user response：

Problem has been solved! I had coded the row source for the Treatment combo-box as SELECT [qryTreatmentByCategory].[trItem] FROM qryTreatmentByCategory ORDER BY [trItem];. I changed this to just read qryTreatmentByCategory and just hid the first column with the TreatmentID.

Thank you so much for the help, I've replaced the old SQL INSERT INTO statement with the one you've provided and it works great.