Accessing Form Objects in SQL

w11184

Registered User.
Local time
Today, 11:22
Joined
Feb 20, 2012
Messages
41
Hi,

I am just wondering whether its possible to have an sql query that will refer to the content of an object on the form?

Basically something like this:

SELECT me!cboSearch.Text
FROM tblData
WHERE tblData.&me!cboSearch.Text = 5;


Thanks.
 
Thanks for showing that page, but I am still having a bit of trouble with it.

At the moment this is what I have and when I run it, it doesn't give me an error but neither does it give me the correct results.

SELECT [Forms]![frmMain]![lblVarName]![Caption]
FROM tblData
WHERE [Forms]![frmMain]![lblVarName]![Caption] = 5;

Basically lblVarName is a label with the field name that I would like in its caption.
 
I would suggest trying to obtain the final resulting SQL query once all of the substitutions have been completed and see what SQL you end up with.

Perhaps via VBA code ask the query object to return its .SQL property. Paste that SQL query into a new Access Query object and try running it.

Perhaps you can locate the QueryDef object in the Access UI and edit \ copy the SQL from that QueryDef object.

Also, please paste what ever SQL you receive from the .SQL property and perhaps a second set of eyes will spot an error.
 
SELECT [Forms]![frmMain]![lblVarName]![Caption]
FROM tblData
WHERE [Forms]![frmMain]![lblVarName]![Caption] = 5;

Is there a reason you're using Caption? If this is a text box, you need to drop the ![Caption] off of the end of those calls.

A cleaner option for doing this would be putting code in the afterupdate to change the SQL in your query:

Code:
Dim mySQL as String

mySQL = "SELECT " & Me.lblVarName & " FROM tblData WHERE " & Me.lblVarName & " = 5"
Currentdb.QueryDefs("YourQuery").SQL = mySQL

This way, if the query is run without the form being open it will still work.
 
Is there a reason you're using Caption? If this is a text box, you need to drop the ![Caption] off of the end of those calls.

A cleaner option for doing this would be putting code in the afterupdate to change the SQL in your query:

Code:
Dim mySQL as String

mySQL = "SELECT " & Me.lblVarName & " FROM tblData WHERE " & Me.lblVarName & " = 5"
Currentdb.QueryDefs("YourQuery").SQL = mySQL

This way, if the query is run without the form being open it will still work.

Thank you for the QueryDef idea. Do you mind elaborate how I would go about using it? I've never used it before and can't really find anything that useful on the internet.

EDIT: Maybe I should elaborate myself as well. What I mean is, what goes where? eg. What goes in "YourQuery"?
 
"Your Query" is the name of the query that you're working with right now. Or you can build a new query. Doesn't matter what's in the query because it's going to be overwritten.
 
"Your Query" is the name of the query that you're working with right now. Or you can build a new query. Doesn't matter what's in the query because it's going to be overwritten.

Thanks very much for that, I got it working!

There's one problem though, when I update the sql the content of the subform doesn't change until I close the form and reopen it again. Do you know how I can get it to refresh?
 
me.requery , or me.refresh

I've tried that but that doesn't work in my design. Because the subform is not "linked" to the main form but rather it is built using a query as its object source. I've also tried re-querying the subform as well but that doesn't seem to work either.
 
Early on with Access 2007, I discovered that I had to re-tell the form its RecordSource for a Requery to be successful, so...

Code:
  'Refire the query that populated this form
  Me.RecordSource = "frmqryclsObjProjectsTbl_RefreshLocalTmpTbl"
  Me.Requery
The call to Me.Requery was not successful otherwise... weird.

Also, if in your case it is a subform, perhaps the subform will not realize that it must fire the event... perhaps you need to notify it to fire the event after the query is run.
 
Ah, I think what you need to do here is instead of having your form based on a named query you need to put that SQL statement in the forms recordsource property.

Code:
Dim mySQL as String

mySQL = "SELECT " & Me.lblVarName & " FROM tblData WHERE " & Me.lblVarName & " = 5"
Me.Recordsource = mySQL
Me.Requery

If this doesn't work for you, post your whole function and maybe that will help me figure it out.
 
Ah, I think what you need to do here is instead of having your form based on a named query you need to put that SQL statement in the forms recordsource property.

I read years ago that Access performance is better when Form Queries are in QueryDef objects and NOT as raw SQL pasted into the Form. So I code that way until I find out otherwise. I believe I picked that up from Using Access 2000 pub by Que.

Same rule was true for Combo Box controls to be filled based on a Query... point the Combo Box at a QueryDef object.
 
Yes, querydefs are faster... but only marginally so. I work with an SQL Server backend that's 10 miles away from me with 2 million records in it and queries as simlpe as this are still pretty much instant.
 
My point remains...I found that I needed to "touch" the

Code:
Me.RecordSource
putting EXACTLY the same string back in that which was already there for a Form based on a Query to correctly refire the same query.

Sounds like the OP is bumping into the same sort of trouble.
 

Users who are viewing this thread

Back
Top Bottom