Multiple Subforms - Can't assign value to object

[kenznpunky]

Hello,

Can someone please give me some ideas?

I have a form with 3 subforms. My main form is based on a table of patient data, and the subforms are based on this data plus different tables connected to that main table. I have patient & followup, patient & diagnosis, and patient & doctor.

It opens to the Patient (main) and followup (subform). Here, I can assign the patient id in the subform to match the patient id of the main form. However, if I go to the other 2 subforms, I can only enter data by manually entering that patient id - if I assign it in the form I get the error msg, "you can't assign a value to this object ..."

Any ideas on how to fix this or what I am doing wrong?

Thanks for your help!

 

[MajP]

can you provide the query of the main form, and the query for the subforms? Then for the subforms provide the values of the subform control's: "Link Master Fields", "Link Child Fields".

 

[kenznpunky]

Hi, Thank you!

The record source for the main form is just a table, 'dbo_Patient'

Then I have sub_followup, SELECT [dbo_Pt_Followup].[Pt_Followup_ID], [dbo_Pt_Followup].[Pt_Followup_Date], [dbo_Pt_Followup].[Pt_Followup_Notes], [dbo_Pt_Followup].[Followup_ID] AS dbo_Pt_Followup_Followup_ID, [dbo_Pt_Followup].[Pt_ID], [dbo_Pt_Followup].[Doctor_ID], [dbo_Followup].[Followup_ID] AS dbo_Followup_Followup_ID, [dbo_Followup].[Followup] FROM dbo_Followup INNER JOIN dbo_Pt_Followup ON dbo_Followup.Followup_ID=dbo_Pt_Followup.Followup_ID;

sub_diagnosis, SELECT [dbo_Pt_Diagnosis].[Pt_ID], [dbo_Pt_Diagnosis].[Diagnosis_ID] AS dbo_Pt_Diagnosis_Diagnosis_ID, [dbo_Pt_Diagnosis].[Doctor_ID], [dbo_DiagnosisCode].[Diagnosis_ID] AS dbo_DiagnosisCode_Diagnosis_ID, dbo_DiagnosisCode.[Diagnosis] FROM dbo_DiagnosisCode INNER JOIN dbo_Pt_Diagnosis ON dbo_DiagnosisCode.Diagnosis_ID=dbo_Pt_Diagnosis.Diagnosis_ID;

sub_doctors, SELECT dbo_Pt_Doctor.Pt_ID, dbo_Pt_Doctor.Doctor_ID AS dbo_Pt_Doctor_Doctor_ID, dbo_Pt_Doctor.PCP, dbo_Doctor.Doctor_FN, dbo_Doctor.Doctor_Initial, dbo_Doctor.Doctor_LN, dbo_Doctor.Doctor_ID AS dbo_Doctor_Doctor_ID, dbo_DoctorType.DoctorType_Specialty FROM (dbo_DoctorType RIGHT JOIN dbo_Doctor ON dbo_DoctorType.DoctorType_ID=dbo_Doctor.DoctorType_ID) LEFT JOIN dbo_Pt_Doctor ON dbo_Doctor.Doctor_ID=dbo_Pt_Doctor.Doctor_ID;

The LinkMaster & LinkChild are the same for each one: Pt_ID and Pt_ID. This is where if I type in the pt_id on diagnosis or doctors it works, but using the pt_id from the main form (automatically filling it in) give me the can't assign error.

Thanks for your help!!

 

[TheAceMan1]

To All . . .

For viewing:

[blue]Main Form
*********
table dbo_Patient

sub_followup
************ 
SELECT dpf.Pt_Followup_ID, 
       dpf.Pt_Followup_Date, 
       dpf.Pt_Followup_Notes, 
       dpf.Followup_ID AS dbo_Pt_Followup_Followup_ID, 
       dpf.Pt_ID, 
       dpf.Doctor_ID, 
       df.Followup_ID AS dbo_Followup_Followup_ID, 
       df.Followup 
FROM dbo_Followup AS df
INNER JOIN dbo_Pt_Followup AS dpf
ON df.Followup_ID=dpf.Followup_ID; 

sub_diagnosis
*************
SELECT dpd.Pt_ID, 
       dpd.Diagnosis_ID AS dbo_Pt_Diagnosis_Diagnosis_ID, 
       dpd.Doctor_ID, 
       ddc.Diagnosis_ID AS dbo_DiagnosisCode_Diagnosis_ID, 
       ddc.Diagnosis 
FROM dbo_DiagnosisCode AS ddc
INNER JOIN dbo_Pt_Diagnosis AS dpd
ON ddc.Diagnosis_ID=dpd.Diagnosis_ID; 

sub_doctors
***********
SELECT dpd.Pt_ID, 
       dpd.Doctor_ID AS dbo_Pt_Doctor_Doctor_ID, 
       dpd.PCP, 
       dd.Doctor_FN, 
       dd.Doctor_Initial, 
       dd.Doctor_LN, 
       dd.Doctor_ID AS dbo_Doctor_Doctor_ID, 
       ddt.DoctorType_Specialty 
FROM (dbo_DoctorType AS ddt
RIGHT JOIN dbo_Doctor AS dd
ON ddt.DoctorType_ID=dd.DoctorType_ID) 
LEFT JOIN dbo_Pt_Doctor AS dpd
ON dd.Doctor_ID=dpd.Doctor_ID;[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

Without seeing this, I cannot think of a reason. My first guess would be to double check the child link on the problematic subforms. My guess is it is not putting the value into your foreign key, but you have it assigned to the primary key (and possibly that is an autonumber). If that was the key you would get that error message.

 

[kenznpunky]

Thanks everyone! I have tried many different things, and it hasn't worked. I am now working on using a module with a global variable instead, this seems to work.

What is being assigned is part of a primary key, but not an auto-number. But I removed this, giving the table a different primary key and it still didn't work. I'm not sure what I am doing wrong, but seems like the variable solution will work.

Thanks for all your help!

 

[kenznpunky]

Wow, I figured it out. First I used a function to assign the values; this worked, but it also got me thinking about my relationships. I had it a table between tables that I was trying to update. Oops! (It's been too long since I've used access).

Anyway, now that I have that figured out I can just bind the controls to the data and remove my function.

Wow!

Thanks for your help everyone -