Unbound Text Boxes in Subform

[jwessner]

I have a form (Form1) which contains basic Project data and a subform listing the personnel assigned to the Project as a continuous form. Selecting a person on that project and clicking on a command button will open a new form (Form2).

Form2 has two subforms. Both are embedded in the main form. (Subform2 is NOT embedded in subform1.) Subform1 displays records as a continuous form based on the Primary ID of the main form and lists the projects a person is assigned to. Subform2 is unbound and displays data as a regular form based on the record (a person's project) selected in Subform1. In the On Current event of Subform1 I build an SQL statement to filter the data from the table supporting Subform2. I have confirmed that the SQL statement is properly filtering the data.

Subform2 has numerous unbound text boxes for displaying data relevant to the project selected in Subform1, and the recordset developed by the SQL statement. The data is properly passed to the unbound text boxes on Subform2.

The problem is that if there is no data for the selected project in Subform1 (the recordset is empty), the data from the previously displayed project remains displayed in the unbound text boxes, rather than the text boxes being blanked out.

This is the code I am using to populate the unbound text boxes on Subform2. The complete SQL string is not shown to save space, but it does work properly.

Private Sub Form_Current()
 
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rsClone As DAO.Recordset
    Dim x As Integer
    Dim intxxx As Integer
    Dim ctl As Control
 
‘ this code places the ID number of another form in an unbound text box 
' when a command button on that form opens this form
 
    If IsLoaded("frmMain") Then
        Forms![frmMain]![txtLinkID] = Me![FK-ID]
    End If
 
' Open a recordset with of all the items in the table tblxxx,
' but filtered by the two current conditions.
 
strSQL = "SELECT …;
 
Set rsClone = CurrentDb.OpenRecordset(strSQL)
 
' Populate the unbound text boxes on the Subform2 based on the SQL 
' statement above and the resulting recordset.
 
With rsClone
    If Not .EOF And Not .BOF Then
        .MoveFirst
            Do While Not .EOF
 
'loop through all the controls to find the correct text boxes to enter data into
‘NOTE: the tag values of the text boxes are 1,2,3…
 
                For Each ctl In Forms!frmMain![subform1 control].Controls
                    If ctl.ControlType = acTextBox Then
 
                       If CLng(ctl.Tag) = rsClone!FKxxxID Then
 
                            ctl.Value = rsClone!intxxx.Value
 
'exit the loop to save time once it is found
                            Exit For
                        End If
                    End If
                Next ctl
            .MoveNext
            Loop
    End If
'always Close Open recordsets...
    .Close
End With
 
'and set them to Nothing when you are completely through with them
    Set rsClone = Nothing
 
End Sub
 

How should I modify this code to clear data from the unbound text boxes if the recordset is empty?

Should I make Subform2 a subform of Subform1? I have tried this but have been unsuccessful at populating them with the data.

In case additional information is needed, I've attached a zipped file with the database and a Word document that explains in more detail what I am trying to accomplish.

Thanks in advance for any help and recommendations.

[ChipR]

If Not .EOF And Not .BOF Then
.MoveFirst
Do While Not .EOF

'loop through all the controls to find the correct text boxes to enter data into
‘NOTE: the tag values of the text boxes are 1,2,3…

For Each ctl In Forms!frmMain![subform1 control].Controls
If ctl.ControlType = acTextBox Then

If CLng(ctl.Tag) = rsClone!FKxxxID Then

ctl.Value = rsClone!intxxx.Value

'exit the loop to save time once it is found
Exit For
End If
End If
Next ctl
.MoveNext
Loop
Else
'set your unbound fields = "" either individually or with a loop
End If

[jwessner]

Chip,

Thank you for your review and comments. I've tried various ways of what you suggested, all with futility. Here's what I just tried adding where you put the self statement...

Else 
For Each ctl In Forms!frmPersonnel![Monthly Allocations Subform].Controls
If ctl.ControlType = acTextBox Then
ctl.Value = ""
End If
Next ctl 
 

Now I get the following error...
Run-time error '2455': You entered an expression that has an invalid reference to the property form/report.

Two things I do not understand...
1) This happens even if the recordset is not empty. So if there is data in the recordset, why is this jumping to the "else" section without puttng the data in the unbound text boxes?
2) The statement drawing the error (For Each ctl In Forms!frmPersonnel![Monthly Allocations Subform].Controls) is exactly the same as above, and works there. Why does it draw the error here?

Thanks again for your input.

Jim

[ChipR]

Make sure you are referencing the subform control's name rather than it's source in Monthly Allocations Subform.

[jwessner]

Chip,

I am. It's the exact same statement that is used about ten lines above at the beginning of the "Do While" segment and works there. That's what has been puzzling me for the last two weeks. Why does it work in one instance and not the other?

The subform itself is fsubAllocations, where as the subform control/container is "Monthly Allocation Subform".

Thanks,
Jim

[ChipR]

nevermind, stupid security settings.

[ChipR]

For Each ctl In Forms!frmpersonnel![monthly allocations subform].Form.Controls

We forgot the .Form I guess.
I had to look it up at
Forms: Refer to Form and Subform properties and controls

[jwessner]

Chip,

I can't tell you how many times I've been there and done that in the past two weeks. I've even tried .forms!Controls all to no avail. It works above without the .form, so why the hang up here? And why is it jumping to the Else portion if the recordset is not empty?

I've probably put in about 20 hours just on this one little thing.

Jim

[ChipR]

I used that line in an else clause and it worked fine. Have you changed something from the .zip you posted above?

[jwessner]

I was using my "working" db, but I just put the same code for the else clause in the file I zipped and attached here. I tried .controls, .form.controls, and .form!controls, and I get the same error for all three as I mentioned above. Is it possible for you to zip what you've done and post here?

Also, my brainstorm of the morning as I awoke, but have not had a chance to look at yet is...should I consider hiding fsubAllocations if the recordset is empty, and make it visible when there are records?

[ChipR]

Haven't tried uploading anything here, but I took your zip and started over and this is literally all I did, in addition to deleting the code from frmProjectStatus and frmPersonnelPositions that was giving me an error:

Else
    For Each ctl In Forms!frmPersonnel![Monthly Allocations Subform].Form.Controls
        If ctl.ControlType = acTextBox Then
            ctl.Value = ""
        End If
    Next ctl
End If

It would be good to indicate when there are no records, so I would either make the whole subform hidden and show a text box saying No Data, or loop through and hide all the text boxes in the form, then show the No Data text box.

[jwessner]

I actually thought of hiding the subform fsubAllocations before if there was no data in the recordset, but I just remembered that I abandoned that tactic before because I am also using the subform to input the allocation data. I haven't written that part of the code yet. I'm trying to fiox this before I attack that. I input the current data directly in the table so that I could work on the code to read the data.I do not understand how the code in the else clause can be working for you but not for me. I just tried one more time to copy and paste your code into code and I continue to get Runtime 2455 error.