run function that is specified in a variable

  • Thread starter Thread starter PiB311
  • Start date Start date
P

PiB311

Hello all,

Hope someone can help me. I created a table with different procedures that
run during a nightly update process. I wanted to be able to skip one part if
something failed.

To do this, I created a table that specifies a process number and the script
that is run to complete that process.

I need to be able to loop through records and run specific scripts that
should be completed. When I try the call method of the variable, I get
"Compile error: expected: . or (".

Here is my code.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim str_sql As String


Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * " & _
"FROM tbl_processes " & _
"INNER JOIN tbl_scripts " & _
"WHERE tbl_processes.task_id = " & int_task &
"")

rst.MoveFirst

Do Until rst.EOF

str_sql = "INSERT INTO tbl_process_log ( process_id , status_id ,
error_message , error_action ) " & _
"SELECT " & rst!process_id & ", 1, '','' "

DoCmd.SetWarnings False
DoCmd.RunSQL str_sql
DoCmd.SetWarnings True

call rst!script

Please assist. Any help is appreciated!
 
Set rst = db.OpenRecordset("SELECT * " & _
"FROM tbl_processes " & _
"INNER JOIN tbl_scripts " & _
"WHERE tbl_processes.task_id = " & int_task & "")
That does not compute. You have a JOIN with no ON clause.

Use alternative insert syntax for this.

str_sql = "INSERT INTO tbl_process_log ( process_id , status_id ,
error_message , error_action ) " & _
"Values(" & rst!process_id & ", 1, '','' )"

This could fail if error_Message and Error_action are not set up to accept
zero-lenght strings. In that case try null or not inserting anything into
those fields
str_sql = "INSERT INTO tbl_process_log ( process_id , status_id ) " & _
"Values(" & rst!process_id & ", 1 )"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
John,

Thanks for your help. For some reason, the whole Select statement did not
come through.

I used the Eval function to run the prodedure stored in my variable. This
has so far produced the results I wanted. This is depicted below:

str_sql = Eval(rst!script)

Where rst!script is created in a recordset and holds the procedure that is
to be run.

Is this the way you would accomplish this? Please advise.

Again thanks for any help.
 
If it works then it works.

I'm not sure what you are doing so I don't know if I would do it that way or not.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top