???Using SQL after declaring a database and rs

pnrjack
05-30-2002, 11:49 AM
I am unsure of how to reset the recordsource after I have created a database and rs in code. Here is what I have so far but it is not working with .recordsource error: Method or data member not found.

strSQL = "SELECT * FROM Products ORDER BY Brand, Category" 'Selects all records from the product table
Data1.RecordSource = strSQL
Data1.Refresh

The initializations look like this:
Private Sub Form_Load()
Set Data1 = OpenDatabase(App.Path & "\TechHeadsDb1.mdb")
Set rs = Data1.OpenRecordset("Products", dbOpenDynaset)
End Sub

PLEASE HELP

ScotK
05-30-2002, 12:45 PM
I'm not exactly sure what your trying to do, it looks like your code is trying to make a recordset out of a database but anyway... why not just change your init to look like:

Set Data1 = OpenDatabase(App.Path & "\TechHeadsDb1.mdb")
Set rs = Data1.OpenRecordset("SELECT * FROM Products ORDER BY Brand, Category", dbOpenDynaset)

pnrjack
05-30-2002, 01:08 PM
What is I want to put a where in the statement after reading in from a text field? How would I do that? Thanks for your help.

ScotK
05-30-2002, 01:58 PM
Well, there's more than 1 way to skin a cat. My neighbor used to have 3 and.. well never mind that, for your problem you could just close the recordset and reopen it with an appropriate SQL statement or you could use a datacontrol where you can just change the recordsource and refresh ( I think that's about the same as a close and reopen) or you could do something like:


Dim dbsNorthwind As Database
Dim qdfTemp As QueryDef
Dim rstView As Recordset

Set dbsNorthwind = OpenDatabase("C:\temp\Northwind.mdb")
Set qdfTemp = dbsNorthwind.CreateQueryDef("", _
"PARAMETERS ViewCountry Text; " & _
"SELECT FirstName, LastName, Country FROM " & _
"Employees WHERE Country = [ViewCountry] " & _
"ORDER BY LastName")

qdfTemp.Parameters!ViewCountry = "USA"
Debug.Print "Data after initial query, " & "[ViewCountry] = USA; "

Set rstView = qdfTemp.OpenRecordset
Do While Not rstView.EOF
Debug.Print " " & rstView!FirstName & " " & _
rstView!LastName & ", " & rstView!Country
rstView.MoveNext
Loop

qdfTemp.Parameters!ViewCountry = "UK"
rstView.Requery qdfTemp

Debug.Print "Requery after changing underlying data"
Set rstView = qdfTemp.OpenRecordset
Do While Not rstView.EOF
Debug.Print " " & rstView!FirstName & " " & _
rstView!LastName & ", " & rstView!Country
rstView.MoveNext
Loop


rstView.Close
dbsNorthwind.Close




I'm sure there are other ways too

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum