Insert a record into table in ms access

I need to create a small "application" using ms access 2007. All I need is to create a form that will take care of input/output related to a few db tables. I created the tables: patients, treatments and labresults.

Primary key in patients table is ID, and it serves as a foreign key in treatments and labresults tables, where it is named patientID.

I also created a form that I mentioned in the beginning of this question. It has multiple tabs. 1st tab opens/creates the patient, and the second one is used to enter data that is to be entered into the labresults table. Same applies to treatments table.

My problem: I added a button to the 2nd tab, and I want to attach an 'action' that will set an INSERT query with values taken from fields (controls) in tab 2, together with ID field taken from tab1 (that corresponds with patient ID from patients table), and then execute a query.

Right now I'm trying to achieve this myself, but with little success. Also, searchin MS site for solution(s) is kind of hard, since it always show results that have 'query' in it :)... And query isn't smt I want to use. (However, I'll accept any solution).

Thx

Tables:

patients

ID - primary key,开发者_如何学Python autogenerated
patientID - internal number of the patient record. I could've used this, but it would complicate my life later on :)
gender
age
dateOfDiagnose

- field names are actually in Serbian, but field names aren't that much important

labtests

ID - primary key
patientID - foreign key, from patients table
... bunch of numerical data :)

There are 2 more tables, but they basically reflect some additional info and are not as important.

My form needs to enable user to enter data about the patient, and then enter several rows in labtests table, as treatment progresses. There are 2 types of treatment, and there is a table related to that, but they only have few fields in it, containint info about the start of the treatment, and the rest is info about lab tests.

It is quite possible to run SQL under VBA, but I suspect that it may be easier to simply set up the form properly. For example, if you set up the ID as the Link Child & Master fields for the lab results subform (which I hope you are using), then it will be filled in automatically. Furthermore, it is also possible to set the default value of a control to the value that was previously entered with very little code. I therefore suggest that you add some notes on what you wish to achieve.

Some further notes based on subsequent comment

From your comments, it appears that you have a minimum of three relevant tables:

Patients

PatientID PK, Counter

Treatments

TreatmentID PK, Counter
PatientID FK
TreatmentTypeID FK

LabResults

LabResultID PK, Counter
TreatmentID FK 
PatientID FK <-- Optional, can be got through TreatmentID
LabResultTypeID FK

In addition, you will need a TreatmentTypes table that list the two current treatments and allows for further treatment types:

TreatmentTypes

TreatmentTypeID PK, Counter 
TreatmentDescription 

You will also need:

LabResultTypes

LabResultTypeID PK, Counter <-- This can be referenced in the LabResults table
TreatmentTypeID FK

There are arguments for PKs other than those suggested, in that you have natural keys, but I think it is easier when working with Access to use autonumbers, however, you will need indexes for the natural keys.

I strongly recommend that all tables also include date created, date modified/updated and a user ID for both. Access does not keep this data, you must do it yourself. Date created can be set by a default value, and if you are using the 2010 version, the other fields can have 'triggers' (data-level macros), otherwise, you will need code, but it is not too difficult. Note also that these same 'triggers' could be used to insert relevant records : Meet the Access 2010 macro designer.

Your form will contain records for each patient and two subforms, Treatments and LabResults. The Treatments subform is bound to the Treatments table having PatientID for LinkChild and Master fields and a combobox for TreatmentTypeID:

Rowsource: SELECT TreatmentTypeID, TreatmentDescription FROM TreatmentTypes
ColumnCount: 2
BoundColumn: 1

After a treatment type is added to the Treatments subform, you can run a query, or run SQL under VBA. You can either use the After Insert event for the form contained by the treatments subform or a button, the After Insert event has advantages, but it is only triggered when the user saves the record or moves from the record, which is the same thing. Working from the treatments subform, and the After Insert event, the SQL would be on the lines of:

''Reference Microsoft DAO x.x Object Library
Set db = CurrentDB
sSQL = "INSERT INTO LabResults (TreatmentID, PatientID, LabResultTypeID) " _
     & "SELECT " _
     & Me.TreatmentID & ", " _
     & Me.PatientID & ", " _
     & "LabResultTypeID FROM LabResultTypes " _
     & "WHERE TreatmentTypeID " = & Me.TreatmentTypeID
db.Execute sSQL

MsgBox "Records inserted: " & db.RecordsAffected

Me.Parent.LabResults_Subform.Form.Requery

The query should also include the date and username, as suggested above, if your version is less than 2010 and you have not set up triggers.

You will be tempted to use datasheets, I suspect, for the subforms. I suggest you resist the temptation and use either single forms of continuous forms, they are more powerful. For an interesting approach, you may wish to look at the Northwind sample database Customer Orders form.