timeout

[joe789]

Hi Folks,

I receive a timeout error when attempting to execute code from a command button that the user clicks, the code simply reads criteria from several list boxes that have been selected by the user and uses those elements in a select query to display the total units. If the user selects too many options, the query takes to long to run and therefore the timeout error is rendered. I know how to set the timeout in a typical query, but not through code. If anyone can help, I would greatly appreciate it. Here is some of the code I am using.

*** code:

Dim SQL As String

SQL = "SELECT sum(new_quantity), sum(finalnetamt) from CLAIMSUNION WHERE ((CLAIMSUNION.clmstat)='a' Or (CLAIMSUNION.clmstat)='p') AND ((CLAIMSUNION.procstat)='p' Or (CLAIMSUNION.procstat)='f') AND " & sFY & " and " & sGender & " and " & sRace & " and " & sAdultYouth & " and " & sAODMH & " and " & LOB & sProviders

Set db = CurrentDb


On Error Resume Next
db.QueryDefs.Delete "qryDataType"

On Error GoTo 0

Set qDef = db.createquerydef("qryDataType", SQL)



DoCmd.OpenQuery "qryDataType"


*** end code


Again, code works fine but if user selects too many options, the query takes too long to run and a timeout error is produced.

Thanks

 

[ajetrumpet]

can't you write a query with the builder and just use a WHERE clause? why write it in code? no need.

 

[joe789]

The only reason I have to do it like this is because the users who will use this program are not computer literate and don't want to have to do any extra steps and are very prone to making errors ... so this way, I have a dozen listboxes that force the user to click on what they want and a GO button that runs the answer for them ... this way, it is completely idiot proof and there is no way someone can accidentally spell something wrong or forget to click something or whatever ... there has to be a way to set this with code, I just don't know what it would be as what I am finding just doesn't seem to like VBA in Access ... I have wrote several useful program like this before, but never experienced a timeout error yet, prob. because of the large volume of data and options available

 

[ajetrumpet]

i would seriously recommend not doing this Joe, as in my mind it is not efficient. But that is IMO. programs now are idiot proof anyway. all of them are. It just depends how much you DON'T want them to do.

you can EASILY do this with a stored query. put all the STOP code you need behind the 'GO' button. hide the object if you want! lock it! make it invisible in the db window if you have one! heck, the possibilities are endless dude!

 

[Rabbie]

programs now are idiot proof anyway. all of them are. It just depends how much you DON'T want them to do.

Just remember that idiots are getting smarter all the time

 

[ajetrumpet]

Just remember that idiots are getting smarter all the time

not the ones that i get to control. I lock em down totally. LOL