Can you sent the Record Source properties Through VB

[Muddman]

I am running a query and then displaying the results on a form. This works fine. I set the Record source properties of the form to my query and it displays nicely. Now, I would like to have about 7 more queries that would display on a form. The only way that I can think of getting around this is to create about 7 more forms and attach the correct query to each one individually. This sounds like a lot of extra work.

Is there any way through VB that I could set the record source property of ,say a Master form, just before I ran the query on it so that it displays on the Master Form. That way, I would only need one form instead of 7 copies of the first.

Any help would be great.

Thanks

 

[MichaelRed]

Yes, but I wouldn't advise it. To dynamically set the recordsource, you need to open the form in design mode, change the property and then shift to formview. You can open the fome "hidden', and save your users the trauma of seeing the design view, but it is still significantly slower than the alternative in most instances.

Here, I'm assuming that you are going to "open " the form from another form, where the 'recordsource' is selected. I will arbitrairly refer to these as qryRecSrc1 ... qryReecSrc9. Creat a new query, which I will (again) arbitrairly refer to as MyRecSrc. Assign the Forms' recordSource Property to this new query ("MyRecSrc&quot. On hte form used to select the recordsource, decide HOW you want to select the variable recordsource (qryRecSrc1 ... qryReecSrc9), such that you will always know which recordsource to use. Here, I will assume that you will just have a series of command buttons for the recordsources (again -) arbitrairly nammed cmdRecSrc1 ... cmdRecSrc9.

In the OnClick event of the individual cmd buttons, call a general procedure (Sub OpenReport?), using the name of the associated recordsource as the argument. It will look something like:

Public Sub OpenReport (rptRecSrc as String)

[tab]Dim dbs as Databse
[tab]Dim qdf as QueyDef

[tab]Set dbs = Currentdb
[tab]set qdf = dbs.Querydefs("MyRecSrc&quot

[tab]qdf.Sql = "Select * from " & rptRecSrc & ";"

[tab]DoCmd.OpenReport "MyReport" ...

End Sub


Dome entirely in the Tek Tips "Your Reply", so probably something is awry, in addition to not knowing which ver of Accss.



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[scking]

Setting the recordsource property using VB is a common practice. The RecordSource property setting can be a table name, a query name, or an SQL statement.

To set the recordsource merely ---

Table:
Me.RecordSource = "tblEmployee"

Query:
Me.RecordSource="qryEmployee"

SQL using textual information from a text box on the form:
Me.RecordSource = "SELECT * FROM tblEmployee WHERE tblEmployee.LastName=" & """" & Me.txtLastName & """" & ";"

SQL using numeric information from a text box on the form:
Me.RecordSource = "SELECT * FROM tblEmployee WHERE tblEmployee.LastName=" & Val(Me.txtEmpNumber) & ";"

Steve King Growth follows a healthy professional curiosity

 

[gny]

Here comes some example code right from the Access97 help file:

Sub cmboCompanyName_AfterUpdate()
	Dim strNewRecord As String
	strNewRecord = "SELECT * FROM Customers " _
		& " WHERE CustomerID = '" _
		& Me!cmboCompanyName.Value & "'"
	Me.RecordSource = strNewRecord
End Sub

Me refers to the form whose code module the code is written in. It can of course be replaced with the name of any open form.

/gny