INeed help resolving data entry problem on a form/subform 1

[DPROMICS]

I need help resolving the cause of a data entry problem on a form/subform.

The details . . .

1) This Access application is being created under Access
2000.

2) In my Access application I have a main form called
ECMR_Main.

The main form ECMR_Main allows selecting a patient from
its associated table.

The table the form is associated with is called
Tbl_PT_Demographics.

The table contains patient demographic data.

The table is the primary table of my Access
application.

There are other tables in the Access application that
are linked to this table in a "One To Many" relationship.

The table Tbl_PT_Demographics table’s index is called
PT_DB_Number.

3) The main form ECMR_Main has a subform
SubFrm_PT_Demographic_Pacemaker_ICD_Device_Data on it.

The subform on the main form is normally set to hidden.

The subform,
SubFrm_PT_Demographic_Pacemaker_ICD_Device_Data, is also
associated to the same table, Tbl_PT_Demographics, as
the main form ECMR_Main.

The subform,
SubFrm_PT_Demographic_Pacemaker_ICD_Device_Data, has its
parent-child relationship defined.

On the subform’s property sheet the Link Child Fields is
set to PT_DB_Number. The Link Master Fields is also set
to PT_DB_Number. Remember that the main and subform are
associated to the same table, Tbl_PT_Demographics.

4) On the main form, ECMR_Main, there is a command button
labeled Add New Patient, that when clicked on allows
entering a new patient in to the patient demographic
data table, Tbl_PT_Demographics.

5) When the command button is clicked on it transfers the
focus to a subform
SubFrm_PT_Demographic_Pacemaker_ICD_Device_Data that is
on the main form ECMR_Main.

When the command button is clicked on, its "On-Click"
command contains the code to:

1) Changes the Subform,
SubFrm_PT_Demographic_Pacemaker_ICD_Device_Data,
from hidden to visible.

2) Sets the focus to the subform,
SubFrm_PT_Demographic_Pacemaker_ICD_Device_Data.

And by default to the first field on the subform,
the patient’s last name field, Tbo_PT_Name_Last.

3) Creates a new patient record in the table
Tbl_PT_Demographics.

This is the VB code for the Add a New Patient command button’s “On Click”:

- - - - - - - - - - - - - - - - - - - - - - - - - -
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_Pacemaker_ICD_Device_Data.Visible = True

' Set Focus to the Patient Demographic Data Subform
SubFrm_PT_Demographic_Pacemaker_ICD_Device_Data.SetFocus

' 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
- - - - - - - - - - - - - - - - - - - - - - - - - -

When the application is running, the problem is that when one enters the first character of a new patient's last name, in the text filed Tbo_PT_Name_Last, an error message is immediately displayed. The error message is:

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

If one clicks OK, the error message window closes and the character entered appears in the text box. One can then continue to add data and then save the new patient's demographic data with no additional error messages. The new patient’s data is added to the table Tbl_PT_Demographics.

In the VB code it doesn’t matter if the focus is set to the subform before a new record is created or after it. The same error message is displayed.

OK, Is there something else that needs to be added to the Add New Patient command button’s code? May be a Me.xxx or something like that or something else?

Is there an inherent problem with a subform on a mainform where both forms are associated with the same table and the subform’s Parent and Child fields are the same table’s index?

Should I setup this code significantly differently than it is? My “gut” programmer’s feeling is that this arrangement of main and subform isn’t a problem. It’s just a matter of setting up the necessary underlying VB code correctly.

OK. Does anybody have a solution for this or any ideas on how to resolve this data entry problem?

If anyone wants to see the main-subform screen shot of this application in order to get a better feel for what and why I am doing it this way, go to:

http://www.promicscorp.com/promics-ecmr-program.htm.

The old adage, “A picture is worth a thousand words” is very, very, true. NOTE: This "orphan" web page displays only an image of the main form of my Access application. There are no advertisements for anything on it.

Best Regards,

 

[formerTexan]

In general, the use of a form-subform arrangement is called for when data from 2 or more tables needs to be displayed. Your posting doesn't indicate any reason for your unconventional approach, so I advise that you dispense with the subform. If nothing else, it will complicate your life.

The code behind your command button is creating a new record in the subform and hence a new record in the underlying table. Is this really the behavior you want?

As for the error message, it is straight forward although rather unhelpful as to exactly where the proble lies. I suggest setting a breakpoint on the click event of the command button and then stepping through the code to see what the offending variable is and where it turns up. Somewhere in the subform or parent form's code a attempt is being made to pass a Null to a variable declared as something other than a variant. Variants are the only variable type that will accept Nulls.

I'm curious as to what type of field PT_DB_Number is? Is it a primary key field? How are you creating a value for this field when a new record is created in the subform?

Cheers,
Bill

 

[DPROMICS]

This Access application and its database, I have created, is a freebee project I got myself in to a couple of years ago. It is now, thankfully, nearing completion. Its now a matter of resolving a few details and adding some additional functionality, like an audit log - a necessity for medical applications, and of course writing the User's Manual.

The overall design criteria of this application was to create an app that flows logically and is easy for a User to learn how to and use. At the heart of this concept was the desire to have the program's layout and operation consistent. Basically, a professional looking, commercial quality, Access application.

This Access app tracks patient visits to the various clinics that the cardiology practice sees its patients in. There are several of them. But I suppose non-medical people would understand what a Pacemaker is and why a cardiology practice would want to see patients in a clinic to check on how the patient is doing with the implanted Pacemaker device and make any adjustments to the device's operation should that be necessary.

In each clinic visit patient data is collected to track the patient's progress and for the necessary documentation that must be placed in to a patient's medical chart.

The Access app is setup as a main form with several subforms on the main form (see the reference to the image of the program's screen on a stand-a-lone web page.

On the main form are subforms which:

1) Displays a list of over all commands such as Exit, Logout, and About the program.

2) Displays the currently selected patient.

3) Displays a subform where patient and clinic visit data can be entered and/or edited.

The Main form has a tabbed section where the User can select a patient or one of the various clinics.

There are tabs for the patients and for the Clinics that the program tracks visits for.

In the patient's tab a list of a portion of all of the patients is displayed and a method of selecting a specific patient. Additionally, there are command buttons for adding a new patient and editing an existing patient's demographic data. The list of and method of selecting a patient is part of the tab on the main form. Adding a new patient or editing an existing patient's data is done on a subform. The subform is hidden when not being used. That just makes the screen cleaner looking.

In the Clinic visits tabs a list of a portion of all of the patient's clinic visits data is displayed. Additionally, there are command buttons for adding a new clinic visit's data and editing an previous clinic visit's data. Adding a new clinic visit's data or editing an previous clinic's data is done on a subform. The subform is hidden when not being used. That just makes the screens cleaner looking.

The general flow of the App is that a clinician selects a patient from the patient's tab and then selects the clinic that the patient was seen in to enter data from that clinic visit.

Hence, the use of a subform to capture a new patient's or edit and existing patient's demographic data, as well as, clinic visit data.

Now, when the Add New Patient command button is clicked on, the under lying code should unhide the patient demographic data capture subform, set the focus to it and create a new record in the table Tbl_PT_Demographics. Which it does. The problem is the darn error message.

OK, there must be a straight forward way to do this that satisfies Access so that it does not display the error message.

Sort of a "Rube Goldburg" method would to be create a record in a temp table called say, Tbl_New_PT_Demographics. Capture the data there for the new patient and when the User clicks on Save New Patient data the new patient's demographic data is transfered to a new record in the table Tbl_PT_Demographics. Ughhh.

Oh, the field PT_DB_Number in the table Tbl_PT_Demographics is the primary key for that table. It is setup in a One To Many relationship with the primary index field in the other patient clinic visit data tables. That is, for each patient entry in the table Tbl_PT_Demographics there are multiple patient clinic visit records for THAT patient in the various clinic visit tables.

I'll stat with your suggestion of looking at and stepping through the code for the Add New Patient command button. I was hoping that some one on the Tek Tips Access forum had already run in to this situation and had already figured out the solution to it. If not, I'll just have to spent the time to figure it out and make it work.

Any one else run in to this or have any additional suggestions?

Best Regards,

 

[formerTexan]

The solution is to change the variable type or pass the variable a non-null value. Only a person with the application will be able to sort out which variable is involved.

An alternate to hiding/unhiding subforms might be to use a stand-alone popup form. Not necessarily a better way, just a different approach.

Out of curiosity, is PT_DB_Number an autonumber?

Cheers,
Bill

 

[DPROMICS]

Bill,

"Out of curiosity, is PT_DB_Number an autonumber?"

Yes. It is the primary key (index) in the table Tbl_PT_DB_Number and is set to be an autonumber.

I created the Access app so I know it pretty well inside and out.

I'm still taking some mid-winter time off from work. But next Monday morning I'm back on to this project again. The goal is to have it wrapped up by the end of January.

Then, hopefully, its on to projects where I can bill for my time for a change.

Best Regards,
Don (former Michigander now basking in Arizona's aboundant desert sun shine and WARMTH!)

 

[DPROMICS]

Its been a few weeks. Well, actually a couple of months now. But I just got back to this project again today. I have found the solution to my problem. Duh, on my part.

The reason, as I see it, I was getting the error message about a Null value being passed to a variable that wasn't a varient data type is that I was setting the focus to a data field in the subform before I set the focus to that subform. By adding the line of VB code to set the focus to the subform first before setting the focus to the data field on that form resolved the error message problem.

This is the updated VB code for the Add a New Patient command button’s “On Click”:

- - - - - - - - - - - - - - - - - - - - - - - - - -
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_Pacemaker_ICD_Device_Data.Visible = True

' Set the Focus to the Patient Demographic Data Subform
SubFrm_PT_Demographic_Pacemaker_ICD_Device_Data.SetFocus

' Now Set the Focus to the First data field on the Patient Demographic Data Subform
[SubFrm_PT_Demographic_Pacemaker_ICD_Device_Data]![Tbo_PT_Name_Last].SetFocus

' Create New Record for the New Patient in 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
- - - - - - - - - - - - - - - - - - - - - - - - - -