Data entry form with linked tables

Question

Hi.  Hope this is the appropriate forum for this question.  I need help getting started with probably a routine application.  I have two SQL 2005 tables -- let's say Doctor & Patient -- which could be joined as 1-to-many.    I linked the two SQL tables in Access 2010 and set up a relationship between them.  I created a third table in Access -- call it Patient2 -- which will have additional patient information columns that are not in the SQL tables and which are to be filled out in Access.   I need an Access data entry form which could be a form/subform layout with Doctor as the master and Multiple patients in the child form.  The patient records consist of fields from the linked table, but also an empty field -- Release_Date -- from the Patient2 table.  The Doctor and Patient fields should be locked, and only Release_Date should be editable.

Main Table:

Doctor 

---

Smith

Williams

Child Table:

Doctor    Patient    Release_Date

--------   --------    --------------

Smith      Jones      11/13/2013

Smith      Doe        11/10/2013

Williams  Owens    11/12/2103

How would you go about setting this up?  Are lookups involved?  Queries?  Events with code behind them?  Thanks.

Answer 1

The DoctorID indicates who the Patient's doctor is. The PatientID indicates who the patient is. You can get the DoctorID from the Patient table, but for ease of linking I included it in this table. The Release Date is just that. The relationship between these three pieces of info should be obvious. Again, including the DoctorID jaut make it easier to link to the Doctor on the main form.

Answer 2

Sounds like what I want to do, but I'm lost on the exact steps. Can you clarify how the three elements on the subform are related to each other and to the master form. Thanks.

I think this article may also be relevant for me...

http://www.fmsinc.com/microsoftaccess/forms/synchronize/linkedsubforms.asp

Answer 3

If I'm following you your SQL tables should not be updated. The only data you need to enter is Release Date, which is in your local Access table. 

If that is the case, your Patient2 table should look like this:

Patient2ID (PK Autonumber)

PaitientID (FK from Patient SQL table)

DoctorID (FK)

ReleaseDate

All you need then is a main form bound to the Doctor table and a subform bound to Patient2. On the Subform you have 3 controls; a combobox to display the patient name and a textbox to enter the Release Date and the DoctorID to link to the main form.

The relevant properties of the Combo would be:

RowSource: SELECT PatientID, First name & " " & Lastname AS Fullname FROM Patients;

Bound column: 1

Locked: True

Enabled: False

Column Count: 2

Column Widths: 0";2"

This will display the patient name for that record but not allow it to be changed. The DoctorID would be hidden

So all the user can do is enter the Release Date.

Answer 4

Access queries do indeed behave like a view; updating a Query updates the underlying Table (of course not all queries are updateable).

However, a Query joining two tables on different platforms (a SQL 2005 table joined to an Access table) will not be updateable, unless I am remembering wrong. You can certainly import the data from SQL into local Access tables, and create a Form with Subforms to update all three tables. No code should be required, though you can use code to make the form fancier if you wish. Naturally you'll be updating the local copy not the SQL instance. However (if you have appropriate permissions) you could export the Access data back to SQL.

Answer 5

After doing some research and thinking through this, I think it would be best to take the data from the linked SQL tables and bring it into Access tables.  So if the links are broken, or I need to move the database, at least it would not have a dependency on the linked tables.  So maybe use a query to populate all the Access tables.  But are the query results dynamic (like a view)?  Hope that mases sense.