locked
Combining 2 Subforms with a Main Form RRS feed

  • Question

  • I'm using Access 2010 and sql 2008.

    I have a form that I need to select a client, enter appointment data and doctor’s data.

    I have a Main form and 2 subforms Appointments and Doctors.

    The Main form's data source is tblClients. On the Main form I have a dropdown box with ClientNames. And Data Entry = No

    The Appointments subform’s data source is tblAppointmnets. The table has the basic appointment data like DrNum, date, time and other fields.

    The Main form and Appointment subform have a master and child links using SSN.

    This part works great. I enter data and the data’s entered into the Appointment table with the clients SSN.

    The Doctors subform’s data source is the tblDoctors. This table has DrNum, doctor's name, address and on. With DrNum being the Primary Key of the tblDoctors table.

    I've tried to make the Doctors subform a subform of the Appointment subform and linking the DrNum fields. I have the Appointment subform set to Data Entry = Yes and the Doctors subform set to Data Entry = No.

    When I try this I notice the DrNum field on the Doctors subform form is displaying (new).

    When I make these 2 subforms a subform of the Main form the data is entered into the tblAppointments table but the DrNum field is null.

    I think the problem is the Doctors subform is trying to create a new record instead of selecting an existing record.

    Anyone have any idea how I can get these 3 form to work together?

    Thanks,

    Paul
    Tuesday, March 19, 2013 5:16 PM

Answers

  • PJ

    So only the the Appointment subform actually creates new records. Also to clarify things, I want to assume that the Doctors subform only gives the user opportunity to select a doctor to assign the new appointment. If this assumptionn is correct, then you probably need to use a Combo box in the Appointment subform and have it display necessary fields to identify the right Doctor.

    Also of importance is setting the Data Entry of the Main form to No. This applies to all controls on the main form which include both subforms. Instead, you can set the "Limit to List" property of the dropdown box to "Yes" or set it "Locked" property to "Yes" to disallow user from entering non-existing client. You can do this same for all other controls on the Main form except the two subforms.


    IbrahimBadaru

    • Marked as answer by Dummy yoyo Monday, April 1, 2013 5:25 AM
    Tuesday, March 19, 2013 7:14 PM
  • I do quite a bit of data entry with subforms. There are usually 2 problems that cause most of my subforms not to work. The first is my query that drives the subform. If you are direclty referencing a table that is usually not the best idea because it is hard to reference two forms together. I always base my forms off a query. Then in your query criteria you can add the Dr selection as the criteria in your query that drives the appointment subform. Then the second item that usually causes problems is you need to either through VBA or a macro create a requery on the forms data set which will refresh when you select a new Dr or add a new Dr. I hope I am not misunderstanding your question, but these two items almost always fix my linking issues with subforms.
    • Marked as answer by Dummy yoyo Monday, April 1, 2013 5:25 AM
    Friday, March 29, 2013 2:36 AM

All replies

  • I offer just a few suggestions -- first -- it sounds like you are doing some editing in your subform(s).  One rule of thum is subforms work best for displaying data (like  a datagridview control in .Net).  For editing -- you should use a standalone form with textboxes that is not bound to any tables.  You submit the edits to you data table from this form. 

    One other suggestion (which you didn't really ask for this one - but you will) if you are using ODBC tables for your project -- you might consider adapting the disconnected recordset paradigm (used in .Net) where you pull data from the sql server to your local project (to local tables) using ADO.  You can then manipulate/edit the data locally and then resubmit any changes back to the server DB (wtih ADO).  This will provide the most reliability and performance (over a continuous ODBC coonection -- which may be part of your problem).


    Rich P

    Tuesday, March 19, 2013 5:53 PM
  • This form is used to enter data not update data.

    I use the Main form to select the client from the dropdown box.

    The Appointment subform is used to enter data for doctor appointments.

    And the Doctors subform is used to select a doctor from a dropdown box.

    I'm using a ODBC connection and ADO I'm not sure how to use it.

    Thanks,

    Paul

    Tuesday, March 19, 2013 6:41 PM
  • PJ

    So only the the Appointment subform actually creates new records. Also to clarify things, I want to assume that the Doctors subform only gives the user opportunity to select a doctor to assign the new appointment. If this assumptionn is correct, then you probably need to use a Combo box in the Appointment subform and have it display necessary fields to identify the right Doctor.

    Also of importance is setting the Data Entry of the Main form to No. This applies to all controls on the main form which include both subforms. Instead, you can set the "Limit to List" property of the dropdown box to "Yes" or set it "Locked" property to "Yes" to disallow user from entering non-existing client. You can do this same for all other controls on the Main form except the two subforms.


    IbrahimBadaru

    • Marked as answer by Dummy yoyo Monday, April 1, 2013 5:25 AM
    Tuesday, March 19, 2013 7:14 PM
  • When I try this I notice the DrNum field on the Doctors subform form is displaying (new).

    I think the problem is the Doctors subform is trying to create a new record instead of selecting an existing record.

    Just an idea - but try setting AllowAdditions in the doctors form to No.

    Wednesday, March 20, 2013 7:26 AM
  • I do quite a bit of data entry with subforms. There are usually 2 problems that cause most of my subforms not to work. The first is my query that drives the subform. If you are direclty referencing a table that is usually not the best idea because it is hard to reference two forms together. I always base my forms off a query. Then in your query criteria you can add the Dr selection as the criteria in your query that drives the appointment subform. Then the second item that usually causes problems is you need to either through VBA or a macro create a requery on the forms data set which will refresh when you select a new Dr or add a new Dr. I hope I am not misunderstanding your question, but these two items almost always fix my linking issues with subforms.
    • Marked as answer by Dummy yoyo Monday, April 1, 2013 5:25 AM
    Friday, March 29, 2013 2:36 AM