Access - VBA - create link between tables / add values to complete query

1
votes

I am quite new to Access and thus I thought I might just ask before getting into too complex solutions...

What I want to do is the following:

  1. I would like to create a database for all patients undergoing a specific procedure in our clinic, which requires the implantation and explantation of a device

  2. Thus, I created a table for patient-related data (ID, Name, Date of birth, etc.) and two tables regarding implantation and explantation data (Date, time, patientID, surgeon, etc.). I could have thrown all data in one table, however, I would prefer to keep that separated, to keep the structure and data clearer. Does that make sense?

  3. I created a query, that links all data by patientID

  4. I created a form, that displays the query data. I would want to use that, to populate the tables.

The problem thereby is, that I cannot add data to the implantation / explantation fields of the form, since there is no correlating patientID given there. Thus, I created some VBA Code, to automatically fill in the patientID into the other tables, once the patientname has been entered.

var1 = Form_test2.internalPatientID.Value
StrSQL = "INSERT INTO ImplantationsDatenT PatientenID ('" & var1 & "' );"

DoCmd.SetWarnings False
DoCmd.RunSQL StrSQL
DoCmd.SetWarnings True

That works so far, however, the data is not directly updated in the form, meaning, I have to switch to another dataset and back and then I can enter the values for implantation time etc, but not earlier.

My Questions are:

  1. Does it make sense, to split the data, even if I always have data for all tables?
  2. Is there an easier way to fill in the data than described?
  3. If not, how can I "update" the query from VBA, to allow direct filling of all fields?

Thank you for your time and for reading all this, I hope I made my problem understandable...

1
ms-accessdatabase-designvba
The keyword is master, child: you can open the patients form and then drag the implantation table I to the form. Access will ask for relationship. Select patientid now both form becomes linked and you can insert into implantations without vba - Krish
Thank you both for your help! That helped a lot and reduced the complexity significantly! - Fabian

1 Answers

0
votes

Adressing your questions:

  1. It does make sense to split data if it has too many differences in data structures or attributes. Regarding your 'Implantation' and 'Explantation' tables, though I am not aware of all the fields contained, it would seem better to merge them if most of the fields are common for both tables. Creating a table named Procedures, and within designate a field that distinguishes if it is an 'Implantation' or 'Explantation', making it easy to separate them with one query and a simple where clause.

  2. You can follow krish's comment.