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.
Expand|Select|Wrap|Line Numbers
- 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.