In VBA how do I link the fields of a form to an on-the-fly SQL que

[garywed]

Hi.
I have a form whose fields are linked to an underlying table of
constellations and their variable stars. When the form opens it is initially
linked to the entire table-- all the stars in all the constellations. On the
form, I create a SQL query by searching on a single constellation, and I want
to be able to do this over and over again as I work through the various
constellations. I don't want to save the query-- just a temporary one in
memory while I am adding new/updating old records for the selected stars. I
can create the query OK-- the SQL works fine. But once a new query is
created, how do I link its fields up to the fields on the form, essentially
replacing the original links directly to the table. This would be like
updating the Record Source property, I guess, except that I want to do it
with VBA on the fly.

My forms and tables are all done, except for this one little thing...and I'm
new enough to VBA that I don't even know what to search for online. Any help
would be greatly appreciated!!

 

[Duane Hookom]

Why don't you just update the Record Source property with a little code?

 

[garywed]

Hi Duane.
Thanks for your reply. I guess that is what I want to do, but I am clueless
as to how to establish the connection.

Let's say in my tblStars table I have two cols, "Starname" and "Brightness",
which are linked to two fields in my form, "txtStarname" and "txtBrightness".
Now I create a SQL query for just those stars in Orion. How do I un-link
those two fields from the original table, and replace with links to the same
cols in my SQL query? In effect, what is the code to go from:

Dim db as Database, rst as Recordset, strSQL as String
strSQL = <<SQL query string>>
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
....
to
....
<< Me!txtStarname.??RecordSource = [Query???].Starname>>
<< Me!txtBrightness.??RecordSource = [Query???].Brightname>>

How do I get a handle on the new recordset, and then make the links
described above? It is probably very simple code, but I am just in the dark
on how to code it.

Thanks to all in advance!

 

[Dale Fye]

I think the easiest way to implement what you want is to use the forms
Filter and FilterOn properties. Assuming that the Constellation, StarName,
and Brightness fields are all in the original query for the form, you can
just change set the forms Filter property and turn the filter on. Lets also
assume that you have an unbound combo box on your form that contains the
names of all the constellations. In that controls after update event, you
would need some code similiar to:

Private sub cbo_Constellation_AfterUpdate()

'if the bound column of the Constellation combo box is text use this
line
me.Filter = "[Constellation] = " & chr$(34) & me.cbo_Constellation &
chr$(34)

'if the bound column of the Constellation combo box is numeric, use this
me.filter = "[Constellation] = " & me.cbo_Constellation

me.FilterOn = True

End sub

This should filter your form for you.

If you really want to use the SQL method, you could just change the Froms
RecordSource property:

Private sub cbo_Constellation_AfterUpdate()

Dim strSQL as String
strSQL = something

me.RecordSource = strSQL

End sub

HTH
Dale

Hi Duane.
Thanks for your reply. I guess that is what I want to do, but I am
clueless
as to how to establish the connection.

Let's say in my tblStars table I have two cols, "Starname" and
"Brightness",
which are linked to two fields in my form, "txtStarname" and
"txtBrightness".
Now I create a SQL query for just those stars in Orion. How do I un-link
those two fields from the original table, and replace with links to the
same
cols in my SQL query? In effect, what is the code to go from:

Dim db as Database, rst as Recordset, strSQL as String
strSQL = <<SQL query string>>
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
...
to
...
<< Me!txtStarname.??RecordSource = [Query???].Starname>>
<< Me!txtBrightness.??RecordSource = [Query???].Brightname>>

How do I get a handle on the new recordset, and then make the links
described above? It is probably very simple code, but I am just in the
dark
on how to code it.

Thanks to all in advance!

Why don't you just update the Record Source property with a little code?

 

[garywed]

Thanks for both of your replies. I was able to get my code working by using
the "Me.RecordSource" method.

All the best,
garywed

I think the easiest way to implement what you want is to use the forms
Filter and FilterOn properties. Assuming that the Constellation, StarName,
and Brightness fields are all in the original query for the form, you can
just change set the forms Filter property and turn the filter on. Lets also
assume that you have an unbound combo box on your form that contains the
names of all the constellations. In that controls after update event, you
would need some code similiar to:

Private sub cbo_Constellation_AfterUpdate()

'if the bound column of the Constellation combo box is text use this
line
me.Filter = "[Constellation] = " & chr$(34) & me.cbo_Constellation &
chr$(34)

'if the bound column of the Constellation combo box is numeric, use this
me.filter = "[Constellation] = " & me.cbo_Constellation

me.FilterOn = True

End sub

This should filter your form for you.

If you really want to use the SQL method, you could just change the Froms
RecordSource property:

Private sub cbo_Constellation_AfterUpdate()

Dim strSQL as String
strSQL = something

me.RecordSource = strSQL

End sub

HTH
Dale

Hi Duane.
Thanks for your reply. I guess that is what I want to do, but I am
clueless
as to how to establish the connection.

Let's say in my tblStars table I have two cols, "Starname" and
"Brightness",
which are linked to two fields in my form, "txtStarname" and
"txtBrightness".
Now I create a SQL query for just those stars in Orion. How do I un-link
those two fields from the original table, and replace with links to the
same
cols in my SQL query? In effect, what is the code to go from:

Dim db as Database, rst as Recordset, strSQL as String
strSQL = <<SQL query string>>
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
...
to
...
<< Me!txtStarname.??RecordSource = [Query???].Starname>>
<< Me!txtBrightness.??RecordSource = [Query???].Brightname>>

How do I get a handle on the new recordset, and then make the links
described above? It is probably very simple code, but I am just in the
dark
on how to code it.

Thanks to all in advance!

Why don't you just update the Record Source property with a little code?

--
Duane Hookom
Microsoft Access MVP


:

Hi.
I have a form whose fields are linked to an underlying table of
constellations and their variable stars. When the form opens it is
initially
linked to the entire table-- all the stars in all the constellations.
On the
form, I create a SQL query by searching on a single constellation, and
I want
to be able to do this over and over again as I work through the various
constellations. I don't want to save the query-- just a temporary one
in
memory while I am adding new/updating old records for the selected
stars. I
can create the query OK-- the SQL works fine. But once a new query is
created, how do I link its fields up to the fields on the form,
essentially
replacing the original links directly to the table. This would be like
updating the Record Source property, I guess, except that I want to do
it
with VBA on the fly.

My forms and tables are all done, except for this one little
thing...and I'm
new enough to VBA that I don't even know what to search for online.
Any help
would be greatly appreciated!!