Loop or For..Next questions…

[Rob]

Hi ….I have this Code that connects to our AS/400 to capture data using
hidden forms with users input. It works well but I need to enhance it a
little bit.

Current process: User has to select individual month to download their data
(one month at a time). Very tedious process especially when you’re
downloading 18 months worth of data.

Preferred process: What I want to do is to replace the strFRM string ("FROM
" & strFRM & " (see code below)) line to look at my
tblTABLE_NAMES.TABLE_NAME1 where Selected=YES , so the users can download and
process multiple months in one shot.

tblTABLE_NAMES fields (TBL_NO, TABLE_NAME1, Selected)

I want it to do a loop until all Selected=YES are processed. Can any of you
experts help me out?? Sorry, I’m a newbie to VBA ïŒ, so any help is very much
appreciated!

As always, you guys are wonderful !

Thanks,
Rob

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim strFRM As String
strFRM = Me.cboF10.Value
strFRM2 = Forms!FRMTABLENAMES.cboTableName2.Value
strFRM3 = Forms!FRMTABLENAMES.cboTableName3.Value
Set db = CurrentDb
Set qdf = db.QueryDefs("001-qextract_query")

Debug.Print "Default ODBCTimeout of QueryDef: " & _
qdf.ODBCTimeout
qdf.ODBCTimeout = 100

DoCmd.SetWarnings False
DoCmd.OpenForm "frmtablenames", , , , , acHidden
DoCmd.OpenForm "frmProcessing"
DoCmd.RepaintObject acForm, "frmprocessing"

DoCmd.Echo False, "Downloading data...."
strSQL = "INSERT INTO TEMPMAINDATA_OD_Temp ( …….. )" & _

"SELECT ………. " & _

"FROM " & strFRM & " " & _
"where ………"

qdf.SQL = strSQL
DoCmd.OpenQuery "001-qextract_query"

MsgBox "Appended " & strFRM & " to the Table", vbOKOnly
DoCmd.OpenQuery "qAppend_tempmaindata_od_temp"
DoCmd.Close acForm, "frmprocessing"
DoCmd.Echo True
End Sub

 

[Sergey Poberezovskiy]

You could try somethins similar to the following:

Dim rs As DAO.Recordset

strSQL = "INSERT INTO TEMPMAINDATA_OD_Temp ( …….. )" & _

"SELECT ………. " & _

"FROM #my_table_name_to_replace# " & _
"where ………"

' If the "Selected" field of Yes/No type change the SQL to Where Selected=True
Set rs = db.OpenRecordset("Select TABLE_NAME1 From tblTABLE_NAMES Where
Selected='Yes'", dbOpenForwardOnly)

Do While Not rs.EOF
qdf.SQL = Replace(strSQL, "#my_table_name_to_replace#", rs!TABLE_NAME1)
qdf.Execute dbFailOnError
rs.MoveNext
Loop
rs.Close

HTH

 

[Rob]

Hi Sergey

Thanks for your prompt response!

I tried it and I got an error saying "Data Type mismatch..."

Here's my amended code:

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim strFRM As String
Dim rs As DAO.Recordset
strFRM = Me.cboF10.Value
strFRM2 = Forms!FRMTABLENAMES.cboTableName2.Value
strFRM3 = Forms!FRMTABLENAMES.cboTableName3.Value
Set db = CurrentDb
Set qdf = db.QueryDefs("001-qextract_query")

Debug.Print "Default ODBCTimeout of QueryDef: " & _
qdf.ODBCTimeout
qdf.ODBCTimeout = 100

DoCmd.SetWarnings False
DoCmd.OpenForm "frmtablenames", , , , , acHidden
DoCmd.OpenForm "frmProcessing"
DoCmd.RepaintObject acForm, "frmprocessing"

DoCmd.Echo False, "Downloading data...."

strSQL = "INSERT INTO TEMPMAINDATA_OD_Temp ( ……)" & _
"SELECT …. " & _
"FROM #my_table_name_to_replace# " & _
"WHERE …….;"

' If the "Selected" field of Yes/No type change the SQL to Where Selected=True
Set rs = db.OpenRecordset("Select TABLE_NAME1 From tblTABLE_NAMES Where
Selected='Yes'", dbOpenForwardOnly)

Do While Not rs.EOF
qdf.SQL = Replace(strSQL, "#my_table_name_to_replace#", rs!TABLE_NAME1)
qdf.Execute dbFailOnError
rs.MoveNext
Loop
rs.Close

qdf.SQL = strSQL
DoCmd.OpenQuery "001-qextract_query"

MsgBox "Appended " & strFRM & " to the Table", vbOKOnly
DoCmd.Close acForm, "frmprocessing"
DoCmd.Echo True

End Sub

I forgot to mention that I have Access2000. Im currently using DAO 3.6 is
that okay? Also, I'm not sure what to do with "#my_table_name_to_replace#" so
I left it as it is in my FROM statement.

I appreciate you bearing with me for being a newbie on VBA.

Hope to hear from you or anyone soon.

Thanks,
Rob

 

[SteveS]

Rob,

I think the problem is that you are comparing a boolean field [Selected] to a
string ('Yes'):

..... Where Selected='Yes' ......

Try changing this line in your code:

Set rs = db.OpenRecordset("Select TABLE_NAME1 From tblTABLE_NAMES Where
Selected='Yes'", dbOpenForwardOnly)


to

Set rs = db.OpenRecordset("Select TABLE_NAME1 From tblTABLE_NAMES Where
Selected= Yes", dbOpenForwardOnly)


or

Set rs = db.OpenRecordset("Select TABLE_NAME1 From tblTABLE_NAMES Where
Selected= True", dbOpenForwardOnly)


HTH

 

[Rob]

Hi SteveS

Thanks for catching that error !

However, when I tried running it, I got "Syntax error in From clause"
Could it be the way I wrote the FROM clause in my strSQL line?

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim strFRM As String
Dim rs As DAO.Recordset
strFRM = Me.cboF10.Value
strFRM2 = Forms!FRMTABLENAMES.cboTableName2.Value
strFRM3 = Forms!FRMTABLENAMES.cboTableName3.Value
Set db = CurrentDb
Set qdf = db.QueryDefs("001-qextract_query")

Debug.Print "Default ODBCTimeout of QueryDef: " & _
qdf.ODBCTimeout
qdf.ODBCTimeout = 100

DoCmd.SetWarnings False
DoCmd.OpenForm "frmtablenames", , , , , acHidden
DoCmd.OpenForm "frmProcessing"
DoCmd.RepaintObject acForm, "frmprocessing"

DoCmd.Echo False, "Downloading data...."


strSQL = "INSERT INTO TEMPMAINDATA_OD_Temp (......)" & _
"SELECT ......." & _
"FROM &table_name&" & _
"WHERE .....;"

Set rs = db.OpenRecordset("Select TABLE_NAME1 From tblTABLE_NAMES Where
Selected=Yes", dbOpenForwardOnly)

Do While Not rs.EOF
qdf.SQL = Replace(strSQL, "Table_Name", rs!table_name1)
qdf.Execute dbFailOnError
rs.MoveNext
Loop
rs.Close

qdf.SQL = strSQL
DoCmd.OpenQuery "001-qextract_query"

MsgBox "Appended " & strFRM & " to the Table", vbOKOnly
DoCmd.Close acForm, "frmprocessing"
DoCmd.Echo True

End Sub

Your guidance is very much appreciated.

Thanks a bunch in advance!
Rob

 

[TC]

Rob wrote:

strSQL = "INSERT INTO TEMPMAINDATA_OD_Temp (......)" & _
"SELECT ......." & _
"FROM &table_name&" & _
"WHERE .....;"

I see where you replace "table_name" in that string, further down,
before you execute this sql. But what's with the dots? They are not
acceptable, in any of the three places that you've used them - and you
don't replace them with anything acceptable, before you run that sql.

HTH,
TC [MVP Access]