VB in Access Query

[susieqtotallynew]

Hi, all,
In my access database, I am using a form to allow users to enter parameters for ID, Constituency Type and Class Year. The command button on the form runs the following VB code:

Private Sub RunQuery_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant

Set db = CurrentDb()
On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query")
On Error GoTo 0

where = Null
where = where & " AND [ID]='" + Me![id] + "'"
where = where & " AND [ConstituencyType]='" + Me![ConstituencyType] + "'"
where = where & " AND [ClassYear]='" + Me![ClassYear]

DoCmd.OpenQuery "Dynamic_Query"

End Sub

However, when I click the button an error, 7874 appears, telling me that Access can't find the Object 'Dynamic_Query'. What am I doing wrong?

Thanks for your help in advance...I am new to this.

 

[dhookom]

I'm not sure how you expect to find Dynamic_Query when you have a line of code that deletes it.

I expect you would like your code to build a SQL statement and use it to update the SQL property of a saved query. If so, don't delete the query, just use code like:

Private Sub RunQuery_Click()
  Dim db As DAO.Database
  Dim qd As DAO.QueryDef
  Dim where As String
  Dim strSelect As String
  Set db = CurrentDb()
  On Error Resume Next
  Set qd = db.QueryDefs("Dynamic_Query")
  On Error GoTo 0
  strSelect = "SELECT ... FROM .... "
  where = " 1=1 "
  where = where & " AND [ID]='" + Me![id] + "'"
  where = where & " AND [ConstituencyType]='" + Me![ConstituencyType] + "'"
  where = where & " AND [ClassYear]='" + Me![ClassYear]
  qd.SQL = strSelect & strWhere
  
  DoCmd.OpenQuery "Dynamic_Query"

End Sub

Duane
Hook'D on Access
MS Access MVP

 

[susieqtotallynew]

Duane,
Sorry if I'm being obtuse, but using your code, I now get an error on the following line:
qd.SQL = strSelect & strWhere

saying, specifically, that the variant is empty.


Perhaps it would help if you could explain what these two lines do:
strSelect = "SELECT ... FROM .... "
where = " 1=1 "
Thank you again.

 

[dhookom]

This line should be the SELECT and FROM clauses from your query. The ... need to be replaced with your fields and tables/queries.
strSelect = "SELECT ... FROM .... "

This line:
where = " 1=1 "
should have been
where = " WHERE 1=1 "
It simply begins the WHERE clause of the sql statement.

I am assuming that you wish to dynamically change the SQL of a query. Is this correct? This also assumes you have the query saved in your queries so that you can change the SQL.



Duane
Hook'D on Access
MS Access MVP

 

[susieqtotallynew]

Duane,
Thanks very much. That is all working fine...

Now, if I may throw a fly in the ointment, so to speak...How would I fix up the line to accommodate a range of values? For example, I have a field in the database called "Ask Amount" and would like to find values that range between a lower and upper value, such as between 100 and 500. I was thinking of this, but am not sure it's quite right, as I can't figure out how to add in the upper range value:

Where = Where & " AND Between [AskAmount] AND [AskAmount]'" + Me![AskAmount] + "'"

Do you have any suggestions?

Thanks again...

 

[dhookom]

Just build a query in the query designer and then view the SQL to see how this might look. I don't know your field names or where your values are coming from. I assume your field(s) might be numeric so if this is the case, ditch the extra single quotes.

Duane
Hook'D on Access
MS Access MVP