I need help resolving a data entry problem

[DPROMICS]

I need help resolving a data entry problem.

I have a main form (ECMR_Main), where on the Patient's tab of the main form, allows selecting a patient from a list box that is associated with the table Tbl_PT_Demographics that contains patient demographic data.

On the Patient's tab of the main form there is a command button for entering a new patient in to the table Tbl_PT_Demographics. When the command button is clicked on, its "On-Click" command contains the code to:

1) Changes a subform SubFrm_PT_Demographic_Data from hidden to visible

2) Creates the new patient record in the table Tbl_PT_Demographics.

This is the code:
- - - - - - - - - - - - - - - - - - - - - - - - - -
Private Sub Cmd_Add_New_Patient_Click()
On Error GoTo Err_Cmd_Add_New_Patient_Click

' On Add New Patient insure that the Patient Data subform is visible
SubFrm_PT_Demographic_Data= True

' Create New Record for the New Patient to be entered in to the Database
DoCmd.GoToRecord , , acNewRec

Exit_Cmd_Add_New_Patient_Click:
Exit Sub

Err_Cmd_Add_New_Patient_Click:
MsgBox Err.Description
Resume Exit_Cmd_Add_New_Patient_Click

End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - -

OK. The problem is that when one enters the first character of a new patient's name I get the error message:

"You tried to assign the Null value to a variable that is not a variant data type."

If one clicks OK on the error message window it disapears and the character entered appears in the text box. One can then continue to add data and save the new patient's demographic data.

Placing this line of code

' Set Focus to the Patient Demographic Data Subform
[SubFrm_PT_Demographic_Data].SetFocus

before the DoCmd resolves the above error message.

However, . . . when one goes to save the new patient's demographic data the error message:

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

appears. And just won't go away. No matter how many times one clicks on its "OK".

No there are no duplicate index, key, etc. entries. It appears to be something specifically related to and where the Set Focus line of code is placed. If it is placed any where else one gets the first error message.

If one is merely changing the demographic data of an exising patient every thing works quite nicely. This uses the same subform and the Save New/Changed Patient Data command button's code as the Add New Patient.

Here is the Save New/Changed Patient Data command button code.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Private Sub Cmd_Save_Patient_Data_Click()
On Error GoTo Err_Cmd_Save_Patient_Data_Click

' Save New Patient record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Forms![Frm_ECMR_Main].Requery

' After Save New/Changed Patient insure that the Patient Data subform is invisible
SubFrm_PT_Demographic_Pacemaker_ICD_Device_Data.Visible = False

[ListBox_Display_Patients].SetFocus
[ListBox_Display_Patients].Requery
[ListBox_Display_Patients].Selected(0) = True
[ListBox_Display_Patients].SetFocus

' Display All Patients in the Database
ButtonLetter = "*"

' Now search for all patient last names that start with this letter
Call Find_A_Letter

' Refresh the form so that the list of patients are displayed in the ListBox
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Cmd_Save_Patient_Data_Click:
Exit Sub

Err_Cmd_Save_Patient_Data_Click:
MsgBox Err.Description
Resume Exit_Cmd_Save_Patient_Data_Click

End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

The specific Details:

The main form ECMR_Main is associated with the table Tbl_PT_Demographics. The table has a primary index/key. There are other tables that are linked to it in a one to many relationship.

The subform SubFrm_PT_Demographic_Data on the main form ECMR_Main has the parent-child relationship setup. The parent is the table Tbl_PT_Demographics. The subform uses the same table name for the child because its data is saved to that table.

Why is this set up this way? My thinking was that the subform would not be visible unless a new patient was being added or there were changes that needed to be made to an existing patient's demographic data. Once a new patient was added or an existing patient's data changed, on saving the data, the subform is set to invisible again. This same philosophy is used on other tab pages of the main form where data is entered through a subform and saved in to another table that is related to the main table in a one to many relationship.

Now:

1) Is there an inheirent problem with a subform on a mainform where the subform's Parent and Child definition are the same table name?

2) Is there something else that needs to be added to the Add New Patient or the Save New Patient command button code?

3) Should I be doing this, code wise, differently?

4) Or, I could just put the new patient data entry text/combo boxes, etc. back on to the main form instead of being on a subform. That did work quite nicely before I decided to change it to a main-subform format.

Anyone have a solution or any ideas on how to resolve this problem?

Best Regards,

 

[jebry]

Hi!

You are getting the first message because anytime you move from the main form to a subform Access automatically saves the current record. This is done because usually the subform is based on a table that requires a record in the main table before you can put a record in the sub table.

For what you are trying to do you could leave out the sub form and just set up a routine you could call that would make the controls visible or invisible individually. Alternatively you can just make the tab that the controls are on visible and invisible since each tab has its own visisble property.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[DPROMICS]

Thanks for the reply Jebry.

Quote from jebry's reply: "You are getting the first message because anytime you move from the main form to a subform Access automatically saves the current record."

Duh...on my part. I missed that simple fact. OK, I have just "dope slapped" my self. Now on to more productive actions.

Yes, the simple but not as "eloquant" solution is to put all of the content of the subform back on to the main form. That was the way it was. Which worked quite nicely.

Quote from jebry's reply: "For what you are trying to do you could leave out the sub form and just set up a routine you could call that would make the controls visible or invisible individually. Alternatively you can just make the tab that the controls are on visible and invisible since each tab has its own visisble property."

Ahh, you have given me a very good possible solution to accomplish the effect I want without resorting to a subform and additional complicated VB code or whatever to accomplish it.

The concept of hiding data fields that can be edited was and still is to try and prevent data from being changed when it shouldn't. If a data field isn't seen by the user they are less likely to muck with it overtly or inadvertly.

The other part of this philosophy is that only the pertinent "data" that the user needs to immidiately deal with is visible to them. In this instance a list of the patients that are seen by the medical practice. Most of what the user will do in this Access app is based on selecting a patient and then entering clinic visit data, in a different Tab on the main form, for that patient and/or print reports for the patient's current and/or previous clinic visits.

You mention that a Tab control can be made visible or invisible. I had forgotten about that capability. Turns out that the subform has two Tabs on it. One Tab is for the Patient's Demographic data and the other tab for the Patient's Pacemaker or ICD data - Device manufacturer, model number, type, lead information, etc. if the patient has a device implanted. The default of course is "None". Basically, pacemaker or ICD device data that will not change unless one is implanted, removed, or changed to a different device manufacturer or type, etc. This data is also part of a patient's demographic data along with their Date Of Birth, address, telephone number(s), etc. But needs to be seperated from the patient's demographic data.

Again, thanks for the very helpful reply. It jogged my memory and helped me realize the other possible solutions to what I want to accomplish and how to go about it.

That is what I really like about the Tek-Tips forum. While one my not get the exact answer wanted or what one thought they wanted, quite often, the response(s) either point out an alternative solution and/or jog one's memory to remember/realize that there are other possible solutions one hadn't yet thought of or had missed.

Best Regards.

 

[jebry]

Hi again!

Glad to help! If you need help with the particulars just post, but it sounds like you are going the right direction.



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[DPROMICS]

Thanks for the reply Jebry.

Well, I tried some other ways of laying out the patient data Edit/Add New patient. None of which satisfies what I need to do and how I want to be able to have the user do it. Plus, because of the amount of data fields that need to be filled in, it just doesn't fit on the main form as well as it does when setup as a main form with a subform on it.

Well, I'll go out on a limb here. I'm not trying to advertise, just enable anyone who can help see what the pertinent Access app's Form I am working on looks like. God knows I really don't want to get in to any more programming projects ever again. Especially freebee projects like this one. I'll subcontract any of that kind of work out to someone else.

There is an old saying, "A picture is worth a thousand words". Therefore, if one were to go to Google and enter PROMICS Corporation and then click on the first entry, then on teh left column click on Access Database Developement, one would see an image of the main form and patient data edit/add subform on it, of the Access App I need help with.

It seems to me that since one is adding a new record to the same table in the database, that it shouldn't be a problem doing it the way I want to do it. Especially since it works the way I have it set up now, other than the annoying error message.

OK. I would really rather keep the current main and subform layout. Therefore, does anyone know what code needs to be added or changes to teh existing code to make the Add New Patient function work without any error messages and of course saves the New Patient's data. Seems to me there must be a way to do it. It might not be pretty or considered elequant, but as long as it works reliability I'm quite willing to live with it.

OK. Anybody have any ideas or suggestions?

Best Regards,