set unbound text box data source

[yatesm]

I have a main form (frmMaintenance) with a subform (frmMaintenance_Subform).
The subform has only one unbound control (text box) that I want to use to display records from different tables. I have been able to set the data source of the subform using VBA (see code below)

Private Sub optValueLists_AfterUpdate()
Dim strInput As String

Select Case Me.optValueLists
Case 1
strInput = "vlStatus"
Me.frmMaintenance_subform.Form.RecordSource = "Select * from " & strInput & ""
Case 2
strInput = "vlPosition"
Me.frmMaintenance_subform.Form.RecordSource = "Select * from " & strInput & ""
Case 3
strInput = "vlDepartment"
Me.frmMaintenance_subform.Form.RecordSource = "Select * from " & strInput & ""
Case 4
strInput = "vlClass"
Me.frmMaintenance_subform.Form.RecordSource = "Select * from " & strInput & ""
End Select
End Sub

I know this works because I see the record count change in the subform. However, what I can't seem to do is set the record source for the unbound text box after VBA sets the record source for the subform.

I have attached an image of the form in the hope that it will give you an idea of what I am working with. Any suggestions would be greatly appreciated.

 

[yatesm]

Thanks for the help? I found a solution (it's not elegant but it works)! if anyone is interest you can post a reply to this thread.

 

[boblarson]

What is your solution? One way is to simply use a DLookup. Another would be to set the control source of the text box to the particular field from the query once you have set the subform's record source.

 

[Galaxiom]

Thanks for the help?

Solutions on this site are provided by the good will of volunteers. I would suggest in future you avoid this kind of sarcasm, which is gives the impression that you presume a right to have your question answered when it suits you. Six hours is not a long wait.

You wouldn't get far trying to define a RecordSource for a control. You would need the ControlSource property.

However the odds are that the reason you are trying to do this is because your data structure is not ideal.

BTW The code you posted is unecessarily repetitive. The Select Case should simply set strInput. The line that sets the RecordSource needs only be included once, after the End Select line.

 

[yatesm]

My solution is not at all that creative but after eight hours of giving my monitor blank stares I decided on a brute force solution.

I have ten tables that I use to hold different value lists (basically used as look up tables) and in my main form (frmMaintenance) I created an option group so that when the user clicked on a "radio button" the values for a specific table would be displayed. My problem was that I could see the record source for the subform (frmMaintenance_subform) cause a change in the number of records to be displayed in the subform but no data was displayed in the text box.

What I could not get to change was the record source for the unbound text box in the subform. As I mentioned above the subform would display changing number of records but there would not be any actual data displayed.

My solution was to name the one and only field in each of the ten tables to the same name. I renamed each field to "Value" and then changed the unbound field to a bound text box with it's record source as "Value". So every time I changed the record source for the subform the "Values" displayed properly. And now the user can edit the values if desired.

And as Galaxiom assumed I was being sarcastic I guess I must offer an apology to everyone. It's continues to amaze me at how much power a question mark has.

 

[yatesm]

Oh I forgot.. Galaxiom was quite correct about the redundant code and I really do appreciate his suggestion to simplify the code. See Below:

Private Sub optValueLists_AfterUpdate()

Select Case Me.optValueLists
Case 1
strInput = "vlStatus"
Case 2
strInput = "vlPosition"
Case 3
strInput = "vlDepartment"
Case 4
strInput = "vlClass"
Case 5
strInput = "vlTrainingType"
Case 6
strInput = "vlAttendanceType"
Case 7
strInput = "vlEvalType"
Case 8
strInput = "vlLicenseType"
Case 9
strInput = "vlCorrectiveAction"
End Select
Me.frmMaintenance_subform.Form.RecordSource = "Select * from " & strInput & " order by Value asc"
End Sub

A nice solution..Thanks!

 

[Galaxiom]

Changing the ControlSource should be straightforward.
Me.controlname.ControlSource = "fieldname"

If you can post a sample of the form and code with a few records we could probably work out what is wrong.

 

[yatesm]

I have attached a zip file that contains the two mdb files. I have configured the subform back to the condition I had when I first posted. Again, thank you for your time and your willingness to help. I am getting an education in Access for sure...

 

[boblarson]

Okay, that was a quick and easy fix. And I didn't need to change the field name.

Take a look at the code to see the extra brackets and the extra code to assign the control source to the control. (and I changed the name of the unbound text box to txtValue)


EDIT: Oh, and remember you'll have to relink the frontend to your backend.

 

[yatesm]

Bob.. one question? What would I have had to have done if each of the vl tables had a different field name? Instead of "= [Value]" in your line of code, how would I have referenced each table field name? Just curious because I attempted all types of "Public" statements, a lot of different "Me." options and I couldn't get anything to work. Would it even be possible in that type of situation? Thank you and Galaxiom so much for your expertise and assistance.

 

[boblarson]

If each had a different field name you would have simply added a variable to your code (my example uses imaginary field names just from your table name):

Private Sub optValueLists_AfterUpdate()
Dim strInput As String
[B][COLOR=red]Dim strField As String[/COLOR][/B]
 
Select Case Me.optValueLists
Case 1
   strInput = "vlStatus"
[B][COLOR=red]  strField = "Status"[/COLOR][/B]
Case 2
   strInput = "vlPosition"
[B][COLOR=red]  strField = "Position"[/COLOR][/B]   
Case 3
strInput = "vlDepartment"
[B][COLOR=#ff0000]  strField = "Department"[/COLOR][/B]
Case 4
   strInput = "vlClass"
[B][COLOR=#ff0000]  strField = "Class"[/COLOR][/B]
Case 5
   strInput = "vlTrainingType"
[B][COLOR=#ff0000]  strField = "TrainingType"[/COLOR][/B]
Case 6
   strInput = "vlAttendanceType"
[B][COLOR=#ff0000]  strField = "AttendanceType"[/COLOR][/B]
Case 7
   strInput = "vlEvalType"
[B][COLOR=#ff0000]  strField = "EvalType"[/COLOR][/B]
Case 8
   strInput = "vlLicenseType"
[B][COLOR=#ff0000]  strField = "LicenseType"[/COLOR][/B]
Case 9
   strInput = "vlCorrectiveAction"
[B][COLOR=#ff0000]  strField = "CorrectiveAction"[/COLOR][/B]
End Select
 
Me.frmMaintenance_subform.Form.RecordSource = "Select * from [B][COLOR=red][[/COLOR][/B]" & strInput & "[B][COLOR=red]][/COLOR][/B] order by [B][COLOR=red]" & strField & " [/COLOR][/B]ASC"
[B][COLOR=red]Me.frmMaintenance_subform.Form.txtValue.ControlSource = "[" & strField & "]"[/COLOR][/B]
End Sub

 

[yatesm]

Bob.. you make it look so easy...

And after looking at your code it looks so logical. No wonder I don't make a living at this.... I could starve to death if I did.

You rock! Thank you very much!