To VBA or Not to VBA

[craw]

really, i've tried to search this one...

I am trying to open a form2 based on the input from the user on form1. (Literally, the user inputs an upper and lower range to search for tool diameters that meet this criteria - i.e., show all tools between .25 and .3). I can get this all to work using a static query and setting the record source of the form2 to this query. But I'm wondering if doing it in VBA would be better/quicker/smarter/whathaveyou. I have a nasty looking SQL statement that is replacing the query in the code below, i'm pretty sure no syntax errors. When I run the code, I get error that MS doesn't recognize the form in the line Set Forms!frm_DiameterSearchResults.Recordset = sql. So I tried to move this line after the Open statement... but this gives the error that an Application or object is not defined.

any help with the code or with whether i should use this route would be mucho appreciated, gracias

Private Sub Command13_Click()
On Error GoTo Err_Command13_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim sql As String
    
sql = "SELECT tbl_CToolInfo.Tool, tbl_CToolInfo.CuttingDiameterNominal, tbl_CToolInfo.TabOn, tbl_TabOnAndName.ToolDescription "
sql = sql & "FROM tbl_CToolInfo INNER JOIN tbl_TabOnAndName ON tbl_CToolInfo.TabOn = tbl_TabOnAndName.TabOn "
sql = sql & "WHERE tbl_CToolInfo.CuttingDiameterNominal Between '" & [Forms]![frm_SearchExistTool]![Text8] & "' And '" & [Forms]![frm_SearchExistTool]![Text10] & "' "
sql = sql & "AND tbl_TabOnAndName.ToolDescription Like '" & " * " & [Forms]![frm_SearchExistTool]![Text15] & " * " & "'"
sql = sql & "ORDER BY tbl_CToolInfo.CuttingDiameterNominal;"

    Set Forms!frm_DiameterSearchResults.Recordset = sql
    stDocName = "frm_DiameterSearchResults"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "frm_SearchExistTool"
    
Exit_Command13_Click:
    Exit Sub

Err_Command13_Click:
    MsgBox Err.Description
    Resume Exit_Command13_Click
    
End Sub

 

[pbaldy]

Well, your subject is "to VBA or not..", and I'd say not in this case. A saved query will run faster than one built in code. In this case, I don't see anything dynamic about the query, so there would be no reason to build it in code.

As to the errors, the first is because the form isn't open. For the second (doing it after the form is open), I think you want to set the RecordSource, not the Recordset.

 

[craw]

pbaldy ~~~thanks for the reply
so it's not considered "inexprienced" to do things using querys and not code? that's refreashing to know.

I did have a question on this...

As to the errors, the first is because the form isn't open. For the second (doing it after the form is open), I think you want to set the RecordSource, not the Recordset.

I tried the following and got an error "Invalid use of property" which highlighted .RecordSource

stDocName = "frm_DiameterSearchResults"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Set Forms!frm_DiameterSearchResults.RecordSource = sql
    DoCmd.Close acForm, "frm_SearchExistTool"

 

[pbaldy]

No, it's certainly not inexperienced to use saved queries rather than code. As I say, saved queries run faster, all things being equal. I only build them in code when they're "dynamic"; where the actual structure of the query will change based on selections the user makes on a form, or something like that.

For the error, try dropping the "Set", like:

Forms!frm_DiameterSearchResults.RecordSource = sql

 

[craw]

dynamic queries

what exactly is a dynamic query? I thought simply writing out a SQL statement using code (as in the first part of my code) instead of a physical query meant dynamic? if not, what is what I did called?
thanks again pbaldy!

 

[craw]

pbaldy,
I tried dropping the "set" but now i get an error saying i've canceled the previous operation.

oh well, I'm going to go with the queries anyway... thanks again for your advice.

 

[pbaldy]

Re: dynamic queries

what exactly is a dynamic query? I thought simply writing out a SQL statement using code (as in the first part of my code) instead of a physical query meant dynamic? if not, what is what I did called?

Perhaps this is open to debate, but I interpret 'dynamic" to mean that it can change. In your case, every time that code ran, the SQL was going to be exactly the same. By dynamic, I'm referring to situations where, based on user input, the SQL may be different every time the code runs. I often use it to build the WHERE portion of a query based on a multi-select list box, which you can't refer to directly in a query criteria. Or you may have a form where the user can pick which field they want to search on, so the SQL will be built accordingly, changing the field(s) referred to in the WHERE clause.