URGENT!!! Subform needs query record source

[Immanuel_Black]

I was wondering if there was a way to set the recordsource of my subform to
a back-end query. I have been trying to put the information from the query
into the textboxes on my subform. i have a way to do it with a list box. but
i need to have it in the datasheet view of the subform. Any help on this
topic would be useful

 

[Nicholas Scarpinato]

If I'm understanding this correctly, you're wanting to view the query's
results in datasheet form in a subform. You really don't need to go through
the extra steps of creating a form for the query, you can just display the
query itself. To do this, you need to set the subform's SourceObject property
to access the query. You can do this in VB code:

Me.SubformWindow.SourceObject = "Query.qryName"

Or just set it in the Properties window if you don't need to change what
query is being viewed in the subform.

 

[Immanuel_Black]

I can't set the query to the form because the queries are stored in the back
end databse (I used the database splitter). So i'm connecting to that back
end and running the queries like this:

Dim strConnect As String
Dim cnnBE As New ADODB.Connection
Dim openQuery As New ADODB.Command

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
strConnect = strConnect & CurrentProject.Path & "\MYDB_be.mdb;"
cnnBE.Open strConnect
openQuery.ActiveConnection = cnnBE.ConnectionString
openQuery.CommandText = "exec qrySelectEquipment"


I'm trying to link this query up to the subform on my page. I want to try
your method, but i don't know how to implement it with the way i'm going.

 

[Dirk Goldgar]

I was wondering if there was a way to set the recordsource of my subform
to
a back-end query. I have been trying to put the information from the query
into the textboxes on my subform. i have a way to do it with a list box.
but
i need to have it in the datasheet view of the subform. Any help on this
topic would be useful

Do you mean you are trying to set the recordsource of a form in database A
to a stored query in database B? You should be able to do that with a
syntax similar to this:

SELECT BEQueryName.* FROM BEQueryName
IN "" "MS Access;Database=C:\Your Path To\YourBE.mdb";

An alternative syntax that I believe will work equally well is:

SELECT BEQueryName.*
FROM [C:\Your Path To\YourBE.mdb].BEQueryName

I should point out that it is usual to have all the queries in the
front-end, not the back-end, and have only the tables in the back-end.

 

[Dirk Goldgar]

I can't set the query to the form because the queries are stored in the
back
end databse (I used the database splitter). So i'm connecting to that back
end and running the queries like this:

Dim strConnect As String
Dim cnnBE As New ADODB.Connection
Dim openQuery As New ADODB.Command

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
strConnect = strConnect & CurrentProject.Path & "\MYDB_be.mdb;"
cnnBE.Open strConnect
openQuery.ActiveConnection = cnnBE.ConnectionString
openQuery.CommandText = "exec qrySelectEquipment"

This seems like way too much programming for me. If you *must* store your
queries in the back-end, see my other reply for a way to set the form's
recordsource to select from the back-end query.

 

[Nicholas Scarpinato]

I didn't realize exactly what you meant by back-end... you'll have to try
Dirk's suggestion for that one, I've never stored queries in a back-end
database before.