469,612 Members | 1,597 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,612 developers. It's quick & easy.

Unbound Text Boxes in Subform

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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  3.     Dim strSQL As String
  4.     Dim db As DAO.Database
  5.     Dim rsClone As DAO.Recordset
  6.     Dim x As Integer
  7.     Dim intxxx As Integer
  8.     Dim ctl As Control
  10. ‘ this code places the ID number of another form in an unbound text box 
  11. ' when a command button on that form opens this form
  13.     If IsLoaded("frmMain") Then
  14.         Forms![frmMain]![txtLinkID] = Me![FK-ID]
  15.     End If
  17. ' Open a recordset with of all the items in the table tblxxx,
  18. ' but filtered by the two current conditions.
  20. strSQL = "SELECT …;
  22. Set rsClone = CurrentDb.OpenRecordset(strSQL)
  24. ' Populate the unbound text boxes on the Subform2 based on the SQL 
  25. ' statement above and the resulting recordset.
  27. With rsClone
  28.     If Not .EOF And Not .BOF Then
  29.         .MoveFirst
  30.             Do While Not .EOF
  32. 'loop through all the controls to find the correct text boxes to enter data into
  33. ‘NOTE: the tag values of the text boxes are 1,2,3…
  35.                 For Each ctl In Forms!frmMain![subform1 control].Controls
  36.                     If ctl.ControlType = acTextBox Then
  38.                        If CLng(ctl.Tag) = rsClone!FKxxxID Then
  40.                             ctl.Value = rsClone!intxxx.Value
  42. 'exit the loop to save time once it is found
  43.                             Exit For
  44.                         End If
  45.                     End If
  46.                 Next ctl
  47.             .MoveNext
  48.             Loop
  49.     End If
  50. 'always Close Open recordsets...
  51.     .Close
  52. End With
  54. 'and set them to Nothing when you are completely through with them
  55.     Set rsClone = Nothing
  57. 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.
Attached Files
File Type: zip PAMr3db.zip (197.0 KB, 174 views)
Dec 9 '08 #1
11 4867
1,287 Expert 1GB
If Not .EOF And Not .BOF Then
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
'set your unbound fields = "" either individually or with a loop
End If
Dec 10 '08 #2

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...
Expand|Select|Wrap|Line Numbers
  1. Else 
  2. For Each ctl In Forms!frmPersonnel![Monthly Allocations Subform].Controls
  3. If ctl.ControlType = acTextBox Then
  4. ctl.Value = ""
  5. End If
  6. 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.

Dec 10 '08 #3
1,287 Expert 1GB
Make sure you are referencing the subform control's name rather than it's source in Monthly Allocations Subform.
Dec 10 '08 #4

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".

Dec 10 '08 #5
1,287 Expert 1GB
nevermind, stupid security settings.
Dec 10 '08 #6
1,287 Expert 1GB
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
Dec 10 '08 #7

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.

Dec 10 '08 #8
1,287 Expert 1GB
I used that line in an else clause and it worked fine. Have you changed something from the .zip you posted above?
Dec 10 '08 #9
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?
Dec 10 '08 #10
1,287 Expert 1GB
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:

Expand|Select|Wrap|Line Numbers
  1. Else
  2.     For Each ctl In Forms!frmPersonnel![Monthly Allocations Subform].Form.Controls
  3.         If ctl.ControlType = acTextBox Then
  4.             ctl.Value = ""
  5.         End If
  6.     Next ctl
  7. 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.
Dec 10 '08 #11
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.
Dec 10 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Dos Lil | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.