How do you change the control source of a text box?

[tomric]

I have two forms, one with two command buttons, the other is a testing critera input form. Both cmd bottons open the same form. The first opens it to input test criteria, and is attached to a table. The other cmd button I want to open the same form, but I want it attached to a query to select the test criteria for a specific part which I will use is to test data from another table to pull out the good parts. I'm thinking I need to change the control source for several text boxes to display the proper test criteria from the query. Any ideas on how to do this.

[Delerna]

the general syntax for changing control properties on another form is
[code=vba]
Forms!FormName. ControlName.Pro perty=value
[/code]

[thevbist]

If I've understood your question correctly, do you want to change the "record source" of a form? Or do you only want to change the "control source" of the text box.
However, I will provide you with both solutions...
To change the "Record Source" of a form.

Here's some code...

Assuming you have a command button on the form called "cmdTestCriteri a".
On the cllick event of "cmdTestCriteri a".


Private Sub cmdTestCriteria _Click()

Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim frmTheSameForm As TheSameForm

Set dbs = CurrentDb()
Set rs = Me.RecordsetClo ne or Me.Recordset

DoCmd.OpenForm "frmTheSameForm "
TheSameForm.Rec ordset = "qryTestCriteri a"
End Sub

If you want to change the "ControlSou rce" of one or several textboxes, then add as many tables and table fields to the query as needed.


Best Regards,

Lamar Dixon, Jr.
President
Sallient Technologies, LLC
MobileMessageMe dia(MCubed)

[tomric]

Yes, I want to change the record source. I've tried the code and I keep comming up with a compile error, user-defined type not defined, on the statement Dim frmTestingcrite ria as Testingcriteria . The form does exist and that's the name of it, any sugestions as to why this is occuring?

[thevbist]

Hello,
The statement "Dim frmTestingcrite ria as Testingcriteria " is a euphemism.

The "Compile Error" you are getting is because the form does not exist. Hence the "User-Defined Type". You're not going to open a new form, you're changing the recordset for the EXISTING form.

So here's some code...

Private Sub cmdTestCriteria _Click()

Dim dbs As DAO.Database
Dim rs As DAO.Recordset


Set dbs = CurrentDb()
Set rs = Me.RecordsetClo ne or Me.Recordset

Me.Recordset = "qryTestCriteri a"
End Sub

[tomric]

Will that allow to open a second form and change the record set of that form at the same time?

[TheSmileyCoder]

This code will change the subform's source object (form) and then alter the recordsource.

I have used this code along with a treeview where a user can click on several nodes, representing different things. Based on what tag the node has, different forms will be opened.

The frm_Container is the name of the control holding the subform. This is just example code and you will have to modify it of course.

Select Case strTag
                Case "System"
                    'Open the system Documents form
                    If Not Me.frm_Container.SourceObject = "frm_System" Then Me.frm_Container.SourceObject = "frm_System"
                    If Not Me.lbl_FormContainer.Caption = "System" Then Me.lbl_FormContainer.Caption = "System"
                    'Set recordsource
                    Me.frm_Container.Form.RecordSource = "SELECT tbl_EvalNodes.* " & _
                                                    " FROM tbl_EvalNodes " & _
                                                    " WHERE KEY_EvalNode=" & getKeyItem(node.Key)
End Select

To change a single textbox to use another controlsource (I have not tested the implications of this or if it is possible at runtime)

me.tb_BidderID.ControlSource="[tx_Bidder]"

I think you will need to try it out, to see what happens when changing this at runtime. For instance, if user has allready entered a value in textbox, and you change the controlsource, what then?

[tomric]

Thank you very much, I got it to work.