Solved Can't assign a value to this object, subform within a parent form that also has another subform (1 Viewer)

[HalloweenWeed]

Hi,
I am working on a mass-record maintenance/modification form, within a FE, to enhance old records to add field information and correct the current information on past entries with typos (hundreds of records) for an institution. The main form does not have any associated "source," and all controls are "unbound." It contains two subforms: one that retrieves the person records, and another that retrieves the main records, both are Access standard queries source. The former source is a query of a table, linked to the BE, and the latter subform is sourced via a query of a query of a table of the main records.

Everything works fine until it hits the vba code:

LN_FN = Me.Parent![LN_FN_input]

"Can't assign a value to this object"
Odd because the code has already assigned values to other controls on the subform with the same source query at that point. LN_FN is not a calculated field, in either query, it is a field in the source master table, same as the other fields (even same data type, 'short text', 256 length max) that have already had values assigned at that point. When debugging in break mode, I can hover over both input and present values and read them, and they are correct (type 'text' strings) and not long. I have been careful not to name any controls on the main parent form the same as a subform control (thus the "_input" suffix). Basically, what this routine is doing is writing info from the main parent form, unbound controls, to the subform current record. Doing this via vba code from a button on the record list, within the subform "detail" (the code is huge so I will refrain from posting it all unless absolutely needed, I suspect it is not).

At first, with this control within the subform detail panel, I created the control by copying the "LN" field, then modifying it (selecting a different field, and renaming the control same as field, same as I always do). That field that I copied is one of the assignments that is presently working (in vba) before the code gets to this point. The subform is set to the following properties:
Recordset type: Dynaset
Data Entry: No
Allow additions: No (I do not want to be able to add records)
Allow deletions: No
Allow edits: Yes
Allow filters: Yes
Record locks: No locks
Filter on load: No

The control "LN_FN" properties:
Format:
Visible: Yes
Control source: LN_FN
Text format: Plain text
Input mask:
Default value:
Validation rule:
Filter lookup: Database default
Enabled: Yes (have tried no)
Locked: No (have tried yes)

This control was the last control added by the way.


What troubleshooting I have already done:
Checked the queries carefully for multiples of the same field, calculated field, and source. The thing is, there is not even any conditionals on these fields in either query, and it is a field in the linked table, same table that other forms routinely modify (even the same field). I have tried renaming the control, same result. I have checked both the parent form and it's other subform for the same control name/field, the other subform had the same field and field name, so I tried renaming both the field and control on the other subform and other source query, just in case that was causing an issue, no joy.

I can open the source query (with the forms open, they are needed for parameters of the query and LN_FN is not one of those), and manually modify LN_FN there! This would mean that the problem does not reside in the underlying query, correct?

Edit: FYI there are no comboboxes on any of these forms.


I would appreciate any ideas you may have. Thank you.

 

[bob fitz]

LN_FN = Me.Parent![LN_FN_input]

Where is this code. Is it in the Main form's code module or in the Subform's code module?

 

[CJ_London]

Also confirm the LN_FN control is actually updateable.

 

[HalloweenWeed]

Where is this code. Is it in the Main form's code module or in the Subform's code module?

Subform, under "Update_But_Click()"

Doing this via vba code from a button on the record list, within the subform "detail".

 

[HalloweenWeed]

Actually this is the entire procedure, attached, but I don't think it will help anyone answer this question.
This is the module header:

Option Compare Database
Dim Error2185cnt As Integer
Dim DB As DAO.Database
Dim GrvtInfoTbl As DAO.Recordset ', GrvcTbl As DAO.Recordset
Dim LogTbl As DAO.Recordset
Dim intFileNo As Integer

Private Sub Form_Load()

'    Me.Parent![stDoc2Name] = "MaintEditTable"
    Set DB = CurrentDb

End Sub

FYI: This sub also records (logs) the changes made (and the time and username) in a text file and a table in the back-end, but that's not related to the problem.

 

[CJ_London]

I don't see your function Update_But_Click in your attachment

 

[HalloweenWeed]

It's actaully UpdateGrvnc_But_Click() I wanted to minimize disclosure (privacy issues).

 

[CJ_London]

what's in a name....

comes back to my question in post #3

 

[HalloweenWeed]

I really thought that I had covered those bases with my OP. If I missed something, can you be a little more specific with your Q?
Because I have already answered this to the best of my knowledge. I don't know what else to tell you.

 

[CJ_London]

you say you can manually modify the field in the underlying query - but can you manually modify it in the subform? It's just crossing off the possibilities.

When the code stops - what is the current value of LN_FN? what is the value you are trying to assign?

 

[HalloweenWeed]

Oh that's great, that's the sort of thinking that I am looking for - I didn't intend to allow editing directly from the control.
But yes, I tried it and it worked. I verified that after closing the form the new edit was saved. But of course it still doesn't help fix the subroutine, just reduces the possibilities. Thank you.

 

[HalloweenWeed]

I just tested the entire subroutine, with the values: Me.Parent![LN_FN_input] = LN_FN (same as), causing the problem line to be skipped, and the rest worked fine (except for one tiny logging typo). So this one hang-up is the only thing stopping this "(write) update" button from working fine to update a single record. Arrrgggh!

 

[CJ_London]

what other fields in the subform are updated? - I'm seeing lines like

![Grievant_DOB] = Me.Parent![Grievant_DOB_input]

but none with the initial ! (but there is a lot of code, so may have missed it)

 

[HalloweenWeed]

what other fields in the subform are updated? - I'm seeing lines like

![Grievant_DOB] = Me.Parent![Grievant_DOB_input]

but none with the initial ! (but there is a lot of code, so may have missed it)

Ah, you missed the "With Me.Parent.GrvcTbl". That is a field in the current record in that table, which is also the source table of this subform, which begs the question: can I edit the value that way? I love it. I will try...

 

[CJ_London]

no, I didn't - that was going to be my next question - does it impact on your subform? Sounds like it does.

Your reference to LN_FN can only relate to the current record in the subform - but you are looping through the GrcvTbl

 

[HalloweenWeed]

Yes, I forgot that the parent form opens GrcvTbl in the vba, and leaves it open, therefore yes there is a "loop" condition.
I tried changing the offensive code to:

![LN_FN] = Me.Parent![LN_FN_input]

and viola! It works great! Thank you both for helping me through that. FYI: I also changed the subsequent code line to:

LogTbl![New_LN_FN] = ![LN_FN]

The control doesn't update instantly when you make changes to GrcvTbl.

Problem solved. I'm very glad to be a part of this community!

 

[CJ_London]

glad you got it working

 

[HalloweenWeed]

Retrospective analysis

Parent form header:

Option Compare Database
Dim Error2185cnt As Integer
Dim DB As DAO.Database
Public GrvcTbl As DAO.Recordset, GrvtInfoTbl As DAO.Recordset

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Handler

    If Not ZeroLength(Me.OpenArgs) Then ParntForm.Value = Me.OpenArgs
    Error2185cnt = 0

'open the Grievance Table for search
    Set DB = CurrentDb
    Set GrvcTbl = DB.OpenRecordset(GrvcTblDocName, dbOpenDynaset, dbInconsistent)


ExitSub:
    Exit Sub

The parent form opened the table designated as GrvcTbl.
Then the offending subroutine in the subtable accessed it and indexes to the same record:

    With Me.Parent.GrvcTbl
        .MoveFirst
        Do While Not .EOF

'check for matches
            If ![Grievance_ID] = Grievance_ID Then

...
    (Loop)

Inside that loop, this code then locks the record (for editing):

                    If CorrDOB_CheckBox Or ![Grievant_DOB] = Me.Parent![Grievant_DOB_input] Then

                        .Edit
...

Thus at this point you no longer can make changes to any of the controls on the subform.
That's why you could edit the control manually, because the record had not been locked for editing.
Error solved.

 

[CJ_London]

useful feedback for others experiencing the same problem