 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / July 2007In VBA how do I link the fields of a form to an on-the-fly SQL que
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!! Why don't you just update the Record Source property with a little code?  SignatureDuane Hookom Microsoft Access MVP > Hi. > I have a form whose fields are linked to an underlying table of [quoted text clipped - 13 lines] > new enough to VBA that I don't even know what to search for online. Any help > would be greatly appreciated!! 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? > [quoted text clipped - 15 lines] > > new enough to VBA that I don't even know what to search for online. Any help > > would be greatly appreciated!! 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 [quoted text clipped - 55 lines] >> > Any help >> > would be greatly appreciated!! 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, [quoted text clipped - 94 lines] > >> > Any help > >> > would be greatly appreciated!! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.