>I have a customer entry form that displays typical customer information. I am using a view for the control source of the textboxes displaying the customer information. The view is parameterized on the customer name so I can load a subset of all the records in the underlying table. This all works fine. But how do I now find a record based on customer number? In order to change the where clause from UPPER(CustName) BETWEEN UPPER(?cCustNameL) AND UPPER(?cCustNameU) to use the customer number, I would have to recreate the view. I don't really want to do this. My end goal is to be able to have the user use a find screen to select customers by a range of names, numbers, states, etc... The subset of records could now be navigated through and changed as required. Any suggestions as to general approaches to this problem?
>
> thanks,
>
> - bob -

I think you are going to have to either create parameterized views for each option based on the user options. I have resorted to using SQLConnect(), and SQLExec() functions to send programatically built SQL statements to the server. I have a form that has a generic grid with RecordSource="", some option groups, checkboxes, etc. Once the user hits the "GO!" command button, the click event of the command button builds the variables needed by the SQL statement template. This also works with SQL queries on local tables to create temporary cursors.

cSQL="Select fieldlist from tablelist where field1 = variable1 and ";
    +"field2 IN "+variable2+" and ";
    +"field3 > "+variable3+" ";
    +"Order by whatever"
nRetVal=SQLExec(gnHandle,cSQL,"CursorName")

gnHandle is the connection handle returned by SQLEXEC() ( >0 if successful).

My form builds the values for variable1, 2 and 3. If nRetVal>0, I set the RecordSource of the grid to ="CursorName" and do a ThisForm.Refresh() at the end of the "GO!" click method. The grid is also ReadOnly.

I also set ThisForm.Grid1.RecordSource="" at the beginning of the "GO!" button click method, otherwise you lose all current grid formatting.

If you are interested in more of the intricacies of building the SQL "on-the-fly", let me know. I will e-mail you some examples.