Using a query as control source on a report

[emilybartholomew]

Is there any way to use a saved query as the control source for an item on a Report? I am trying to pull in the most recent note associated with a project, and would like to just define the control source as this:
SELECT note_text FROM Note_Log WHERE note_date = [max_date] AND project_id = [form_project]
where I already have the most current note date on the form.

I tried selecting the saved query from the Control Source properties list, but it doesn't work.

Another problem- I have Access 2000 and I haven't figured out how to connect my Visual Basic to the DB. I think I just need to figure out the Provider. Any ideas on how to do this would be really helpful too.

 

[seaport]

I do not think you can use a saved query as the control source for an item on a Report.
What you can do is to assign the value to the item in the detail_format event. for example:

***
txtRecentNotes=dlookup("note_text", "Note_Log", "note_date = [max_date] AND project_id = [form_project]&quot
***

Hope this helps.

Seaport

 

[cmmrfrds]

Connect to Access 2000. Here is a function from an access program that connects to another access mdb. reference set under tools menu to ActiveX data object 2.6 library.

Function TestAdo()
Dim cn As New Connection
Dim rs As Recordset, connString As String
connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\bigtuna\Databases\MotorRepairDB.mdb;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString
Set rs = cn.OpenSchema(adSchemaTables)
While Not rs.EOF
If rs!table_type = "table" Then
Debug.Print rs!table_name
End If
rs.MoveNext
Wend
rs.Close
Set cn = Nothing
End Function

To build the connection string. Go on the desktop and create an empty notepad file and save. rename the file extention to udl. Double click on the file and it will walk you through creating the connection string.

QUERY - you can put your query in a function and have the function return the date. The datasource would =funcName()

 

[LonnieJohnson]

Or you could simply use the DMax function...

=DMax("MyField","MyTable&quot

Hope this helps. prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.

 

[emilybartholomew]

Fantastic. That worked great.