Need help with an If then statement before a loop

[Guest]

I have a loop in Access that is working, but what I would like to do is have
the loop only work on tables that have a specific alpha character in the
table name. I think the best way to accomplish this is to do an If - then
statement before the loop, but I can't figure out the correct
function/property to use. I tried using "Source" with wildcards and it didn't
work. I'm a little out of my league on this one and would appreciate any help.

Thanks.

 

[John Spencer]

Try the Instr function.

IF Instr(1,SomeVariable, "A",vbTextCompare) > 0 then
'Do stuff here
End If

 

[Guest]

Not enough info to gave a complete answer, but basically, you can use the
Instr() function to determine whether the table name has the character in it:

If Instr(strTableName, "x") Then
'Do the loop
End If

strTableName is the name of the table and x is the character you want to
check for. If you need more detail, post your code.

 

[Guest]

Here is my code. I copied it from one of my other db's and modified it. Right
now it works when I input an existing table name. What I would like to do is
have it loop through all the tables with a "G" in the table name and put
those records in an existing table. I think the problem is in defining the
recset. I can't figure out how to use the InStr function to define the recset.
I'm still pretty new to coding and know that I'm not great at it so please
be kind when you look at it

Thanks.

Sub GFileCompile()
Dim db As Database
Dim recset1 As New ADODB.Recordset 'The input table
Dim recset2 As New ADODB.Recordset 'The product table (create ahead to
time)

Dim specs As String
Dim count As Integer
Dim thefieldcnt As Integer


recset1.Open "SELECT [604013G].*FROM [604013G];", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic 'name of the source table
recset2.Open "SELECT [GFileCompile].*FROM [GFileCompile];",
CurrentProject.Connection, adOpenKeyset, adLockOptimistic 'name of
destination table

thefieldcnt = recset1.Fields.count 'This is a count of the fields in the
table.

Do loop
**rest of loop here
End If
count = count + 1

Loop Until count = thefieldcnt 'Stops after the last field
recset1.MoveNext
Loop Until recset1.EOF

End Sub

 

[Guest]

I am having trouble understanding what it is you are trying to do. You say
you want to select tables with a G in the name; however, you are opening
recset1 hard coded and outside both your loops. Can you be a little more
specific?

recset1.Open "SELECT [604013G].*FROM [604013G];", CurrentProject.Connection,

Here is my code. I copied it from one of my other db's and modified it. Right
now it works when I input an existing table name. What I would like to do is
have it loop through all the tables with a "G" in the table name and put
those records in an existing table. I think the problem is in defining the
recset. I can't figure out how to use the InStr function to define the recset.
I'm still pretty new to coding and know that I'm not great at it so please
be kind when you look at it

Thanks.

Sub GFileCompile()
Dim db As Database
Dim recset1 As New ADODB.Recordset 'The input table
Dim recset2 As New ADODB.Recordset 'The product table (create ahead to
time)

Dim specs As String
Dim count As Integer
Dim thefieldcnt As Integer


recset1.Open "SELECT [604013G].*FROM [604013G];", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic 'name of the source table
recset2.Open "SELECT [GFileCompile].*FROM [GFileCompile];",
CurrentProject.Connection, adOpenKeyset, adLockOptimistic 'name of
destination table

thefieldcnt = recset1.Fields.count 'This is a count of the fields in the
table.

Do loop
**rest of loop here
End If
count = count + 1

Loop Until count = thefieldcnt 'Stops after the last field
recset1.MoveNext
Loop Until recset1.EOF

End Sub

Not enough info to gave a complete answer, but basically, you can use the
Instr() function to determine whether the table name has the character in it:

If Instr(strTableName, "x") Then
'Do the loop
End If

strTableName is the name of the table and x is the character you want to
check for. If you need more detail, post your code.

 

[Guest]

I guess you found one problem. I think the major problem is I am trying to
cheat and use existing code. I should probably go back and re-think and re-do
from the beginning. Ugh.
I have inherited a database with 250 tables in it. Each table name has a
alpha suffix denoting the type of data in the table. The G tables each
contain one record. What I am trying to accomplish is appending records from
all tables with a G suffix to a GCompile table, all the records with an E
suffix to ECompile table, etc. It doesn't seem to me like it should be that
difficult; however I can't figure out the words Access wants to hear.
Just to be sure you are saying that I need to include the InStr in the first
loop when I define the recordset?

Thanks

I am having trouble understanding what it is you are trying to do. You say
you want to select tables with a G in the name; however, you are opening
recset1 hard coded and outside both your loops. Can you be a little more
specific?

recset1.Open "SELECT [604013G].*FROM [604013G];", CurrentProject.Connection,

Here is my code. I copied it from one of my other db's and modified it. Right
now it works when I input an existing table name. What I would like to do is
have it loop through all the tables with a "G" in the table name and put
those records in an existing table. I think the problem is in defining the
recset. I can't figure out how to use the InStr function to define the recset.
I'm still pretty new to coding and know that I'm not great at it so please
be kind when you look at it

Thanks.

Sub GFileCompile()
Dim db As Database
Dim recset1 As New ADODB.Recordset 'The input table
Dim recset2 As New ADODB.Recordset 'The product table (create ahead to
time)

Dim specs As String
Dim count As Integer
Dim thefieldcnt As Integer


recset1.Open "SELECT [604013G].*FROM [604013G];", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic 'name of the source table
recset2.Open "SELECT [GFileCompile].*FROM [GFileCompile];",
CurrentProject.Connection, adOpenKeyset, adLockOptimistic 'name of
destination table

thefieldcnt = recset1.Fields.count 'This is a count of the fields in the
table.

Do loop
**rest of loop here
End If
count = count + 1

Loop Until count = thefieldcnt 'Stops after the last field
recset1.MoveNext
Loop Until recset1.EOF

End Sub

Not enough info to gave a complete answer, but basically, you can use the
Instr() function to determine whether the table name has the character in it:

If Instr(strTableName, "x") Then
'Do the loop
End If

strTableName is the name of the table and x is the character you want to
check for. If you need more detail, post your code.

:

I have a loop in Access that is working, but what I would like to do is have
the loop only work on tables that have a specific alpha character in the
table name. I think the best way to accomplish this is to do an If - then
statement before the loop, but I can't figure out the correct
function/property to use. I tried using "Source" with wildcards and it didn't
work. I'm a little out of my league on this one and would appreciate any help.

Thanks.

 

[Guest]

Okay, that explains it. The problem is I don't know enough about ADODB to
know how to cycle through the table names in a database. My experience is
all with DAO, so the part where you can get a list of table names you will
have to figure out, but as far as the looping goes, you need the list of
table names to loop through and add a loop to your process.

So, the outer loop would look at each table name, determine whether it has a
G, and if it does, then do your inner loops. Then, look at the next table
name, etc.

Sorry I can't be more specific here, but my lack of ADO experience is a
problem.

Best of Luck

I guess you found one problem. I think the major problem is I am trying to
cheat and use existing code. I should probably go back and re-think and re-do
from the beginning. Ugh.
I have inherited a database with 250 tables in it. Each table name has a
alpha suffix denoting the type of data in the table. The G tables each
contain one record. What I am trying to accomplish is appending records from
all tables with a G suffix to a GCompile table, all the records with an E
suffix to ECompile table, etc. It doesn't seem to me like it should be that
difficult; however I can't figure out the words Access wants to hear.
Just to be sure you are saying that I need to include the InStr in the first
loop when I define the recordset?

Thanks

I am having trouble understanding what it is you are trying to do. You say
you want to select tables with a G in the name; however, you are opening
recset1 hard coded and outside both your loops. Can you be a little more
specific?

recset1.Open "SELECT [604013G].*FROM [604013G];", CurrentProject.Connection,

Here is my code. I copied it from one of my other db's and modified it. Right
now it works when I input an existing table name. What I would like to do is
have it loop through all the tables with a "G" in the table name and put
those records in an existing table. I think the problem is in defining the
recset. I can't figure out how to use the InStr function to define the recset.
I'm still pretty new to coding and know that I'm not great at it so please
be kind when you look at it

Thanks.

Sub GFileCompile()
Dim db As Database
Dim recset1 As New ADODB.Recordset 'The input table
Dim recset2 As New ADODB.Recordset 'The product table (create ahead to
time)

Dim specs As String
Dim count As Integer
Dim thefieldcnt As Integer


recset1.Open "SELECT [604013G].*FROM [604013G];", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic 'name of the source table
recset2.Open "SELECT [GFileCompile].*FROM [GFileCompile];",
CurrentProject.Connection, adOpenKeyset, adLockOptimistic 'name of
destination table

thefieldcnt = recset1.Fields.count 'This is a count of the fields in the
table.

Do loop
**rest of loop here
End If
count = count + 1

Loop Until count = thefieldcnt 'Stops after the last field
recset1.MoveNext
Loop Until recset1.EOF

End Sub



:

Not enough info to gave a complete answer, but basically, you can use the
Instr() function to determine whether the table name has the character in it:

If Instr(strTableName, "x") Then
'Do the loop
End If

strTableName is the name of the table and x is the character you want to
check for. If you need more detail, post your code.

:

I have a loop in Access that is working, but what I would like to do is have
the loop only work on tables that have a specific alpha character in the
table name. I think the best way to accomplish this is to do an If - then
statement before the loop, but I can't figure out the correct
function/property to use. I tried using "Source" with wildcards and it didn't
work. I'm a little out of my league on this one and would appreciate any help.

Thanks.

 

[Guest]

I am able to get a list of table names through querying the MSysObjects table
(Name, Type Criteria=1, Flags=0). If I did a make table query of the results
would that work?

Okay, that explains it. The problem is I don't know enough about ADODB to
know how to cycle through the table names in a database. My experience is
all with DAO, so the part where you can get a list of table names you will
have to figure out, but as far as the looping goes, you need the list of
table names to loop through and add a loop to your process.

So, the outer loop would look at each table name, determine whether it has a
G, and if it does, then do your inner loops. Then, look at the next table
name, etc.

Sorry I can't be more specific here, but my lack of ADO experience is a
problem.

Best of Luck

I guess you found one problem. I think the major problem is I am trying to
cheat and use existing code. I should probably go back and re-think and re-do
from the beginning. Ugh.
I have inherited a database with 250 tables in it. Each table name has a
alpha suffix denoting the type of data in the table. The G tables each
contain one record. What I am trying to accomplish is appending records from
all tables with a G suffix to a GCompile table, all the records with an E
suffix to ECompile table, etc. It doesn't seem to me like it should be that
difficult; however I can't figure out the words Access wants to hear.
Just to be sure you are saying that I need to include the InStr in the first
loop when I define the recordset?

Thanks

I am having trouble understanding what it is you are trying to do. You say
you want to select tables with a G in the name; however, you are opening
recset1 hard coded and outside both your loops. Can you be a little more
specific?

recset1.Open "SELECT [604013G].*FROM [604013G];", CurrentProject.Connection,

:

Here is my code. I copied it from one of my other db's and modified it. Right
now it works when I input an existing table name. What I would like to do is
have it loop through all the tables with a "G" in the table name and put
those records in an existing table. I think the problem is in defining the
recset. I can't figure out how to use the InStr function to define the recset.
I'm still pretty new to coding and know that I'm not great at it so please
be kind when you look at it

Thanks.

Sub GFileCompile()
Dim db As Database
Dim recset1 As New ADODB.Recordset 'The input table
Dim recset2 As New ADODB.Recordset 'The product table (create ahead to
time)

Dim specs As String
Dim count As Integer
Dim thefieldcnt As Integer


recset1.Open "SELECT [604013G].*FROM [604013G];", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic 'name of the source table
recset2.Open "SELECT [GFileCompile].*FROM [GFileCompile];",
CurrentProject.Connection, adOpenKeyset, adLockOptimistic 'name of
destination table

thefieldcnt = recset1.Fields.count 'This is a count of the fields in the
table.

Do loop
**rest of loop here
End If
count = count + 1

Loop Until count = thefieldcnt 'Stops after the last field
recset1.MoveNext
Loop Until recset1.EOF

End Sub



:

Not enough info to gave a complete answer, but basically, you can use the
Instr() function to determine whether the table name has the character in it:

If Instr(strTableName, "x") Then
'Do the loop
End If

strTableName is the name of the table and x is the character you want to
check for. If you need more detail, post your code.

:

I have a loop in Access that is working, but what I would like to do is have
the loop only work on tables that have a specific alpha character in the
table name. I think the best way to accomplish this is to do an If - then
statement before the loop, but I can't figure out the correct
function/property to use. I tried using "Source" with wildcards and it didn't
work. I'm a little out of my league on this one and would appreciate any help.

Thanks.

 

[Guest]

Yes it would. That is exactly what you would want to do. Create the query,
use it as a recordset then cycle throught that recordset checking the table
name. That would be the outer loop. Then when you find a table you want to
process, you do the inner loops. Below is untested air code, but it will
give the the basic idea.

Sub GFileCompile()
Dim db As Database
Dim recset1 As New ADODB.Recordset 'The input table
Dim recset2 As New ADODB.Recordset 'The product table (create ahead to
time)
Dim rstTableNames as NewADODB.Recordset 'Table Names to process
Dim specs As String
Dim intCount As Integer
Dim thefieldcnt As Integer
Dim strTable as String

rstTableNames.Open "SELECT [QueryName].TableName FROM [QueryName];", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic

recset2.Open "SELECT [GFileCompile].*FROM [GFileCompile];",
CurrentProject.Connection, adOpenKeyset, adLockOptimistic 'name of
destination table

Do While Not rstTableNames.EOF
strTable = rstTableName.TableName
If Instr(strTable, "G") > 0 Then
recset1.Open "SELECT [" & strTable.TableName & _
"].*FROM [604013G];", _
CurrentProject.Connection,
adOpenKeyset, adLockOptimistic 'name of the source table

thefieldcnt = recset1.Fields.count 'This is a count of the
fields in the
'table.

Do
**rest of loop here
count = count + 1
Loop Until count = thefieldcnt 'Stops after the last field
recset1.MoveNext
Loop Until recset1.EOF
recset1.Close
End If
Loop
recset2.Close
rstTableName.Close
End Sub

I am able to get a list of table names through querying the MSysObjects table
(Name, Type Criteria=1, Flags=0). If I did a make table query of the results
would that work?

Okay, that explains it. The problem is I don't know enough about ADODB to
know how to cycle through the table names in a database. My experience is
all with DAO, so the part where you can get a list of table names you will
have to figure out, but as far as the looping goes, you need the list of
table names to loop through and add a loop to your process.

So, the outer loop would look at each table name, determine whether it has a
G, and if it does, then do your inner loops. Then, look at the next table
name, etc.

Sorry I can't be more specific here, but my lack of ADO experience is a
problem.

Best of Luck

I guess you found one problem. I think the major problem is I am trying to
cheat and use existing code. I should probably go back and re-think and re-do
from the beginning. Ugh.
I have inherited a database with 250 tables in it. Each table name has a
alpha suffix denoting the type of data in the table. The G tables each
contain one record. What I am trying to accomplish is appending records from
all tables with a G suffix to a GCompile table, all the records with an E
suffix to ECompile table, etc. It doesn't seem to me like it should be that
difficult; however I can't figure out the words Access wants to hear.
Just to be sure you are saying that I need to include the InStr in the first
loop when I define the recordset?

Thanks

:

I am having trouble understanding what it is you are trying to do. You say
you want to select tables with a G in the name; however, you are opening
recset1 hard coded and outside both your loops. Can you be a little more
specific?

recset1.Open "SELECT [604013G].*FROM [604013G];", CurrentProject.Connection,

:

Here is my code. I copied it from one of my other db's and modified it. Right
now it works when I input an existing table name. What I would like to do is
have it loop through all the tables with a "G" in the table name and put
those records in an existing table. I think the problem is in defining the
recset. I can't figure out how to use the InStr function to define the recset.
I'm still pretty new to coding and know that I'm not great at it so please
be kind when you look at it

Thanks.

Sub GFileCompile()
Dim db As Database
Dim recset1 As New ADODB.Recordset 'The input table
Dim recset2 As New ADODB.Recordset 'The product table (create ahead to
time)

Dim specs As String
Dim count As Integer
Dim thefieldcnt As Integer


recset1.Open "SELECT [604013G].*FROM [604013G];", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic 'name of the source table
recset2.Open "SELECT [GFileCompile].*FROM [GFileCompile];",
CurrentProject.Connection, adOpenKeyset, adLockOptimistic 'name of
destination table

thefieldcnt = recset1.Fields.count 'This is a count of the fields in the
table.

Do loop
**rest of loop here
End If
count = count + 1

Loop Until count = thefieldcnt 'Stops after the last field
recset1.MoveNext
Loop Until recset1.EOF

End Sub



:

Not enough info to gave a complete answer, but basically, you can use the
Instr() function to determine whether the table name has the character in it:

If Instr(strTableName, "x") Then
'Do the loop
End If

strTableName is the name of the table and x is the character you want to
check for. If you need more detail, post your code.

:

I have a loop in Access that is working, but what I would like to do is have
the loop only work on tables that have a specific alpha character in the
table name. I think the best way to accomplish this is to do an If - then
statement before the loop, but I can't figure out the correct
function/property to use. I tried using "Source" with wildcards and it didn't
work. I'm a little out of my league on this one and would appreciate any help.

Thanks.

 

[Guest]

Thanks for your help. I decided to take a different path and do an If, Then,
Else statement (the loop was giving me too much trouble) Such as (abbreviated
code):
If right(tablename, 1)= G Then
Append to GFileCompile
Else if
right(tablename, 1) =E then
Append to EFileCompile

The only part I'm having a little trouble with is the right way to say
append all records of the soure table into the destination table.
Thanks again for your help.

Yes it would. That is exactly what you would want to do. Create the query,
use it as a recordset then cycle throught that recordset checking the table
name. That would be the outer loop. Then when you find a table you want to
process, you do the inner loops. Below is untested air code, but it will
give the the basic idea.

Sub GFileCompile()
Dim db As Database
Dim recset1 As New ADODB.Recordset 'The input table
Dim recset2 As New ADODB.Recordset 'The product table (create ahead to
time)
Dim rstTableNames as NewADODB.Recordset 'Table Names to process
Dim specs As String
Dim intCount As Integer
Dim thefieldcnt As Integer
Dim strTable as String

rstTableNames.Open "SELECT [QueryName].TableName FROM [QueryName];", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic

recset2.Open "SELECT [GFileCompile].*FROM [GFileCompile];",
CurrentProject.Connection, adOpenKeyset, adLockOptimistic 'name of
destination table

Do While Not rstTableNames.EOF
strTable = rstTableName.TableName
If Instr(strTable, "G") > 0 Then
recset1.Open "SELECT [" & strTable.TableName & _
"].*FROM [604013G];", _
CurrentProject.Connection,
adOpenKeyset, adLockOptimistic 'name of the source table

thefieldcnt = recset1.Fields.count 'This is a count of the
fields in the
'table.

Do
**rest of loop here
count = count + 1
Loop Until count = thefieldcnt 'Stops after the last field
recset1.MoveNext
Loop Until recset1.EOF
recset1.Close
End If
Loop
recset2.Close
rstTableName.Close
End Sub

I am able to get a list of table names through querying the MSysObjects table
(Name, Type Criteria=1, Flags=0). If I did a make table query of the results
would that work?

Okay, that explains it. The problem is I don't know enough about ADODB to
know how to cycle through the table names in a database. My experience is
all with DAO, so the part where you can get a list of table names you will
have to figure out, but as far as the looping goes, you need the list of
table names to loop through and add a loop to your process.

So, the outer loop would look at each table name, determine whether it has a
G, and if it does, then do your inner loops. Then, look at the next table
name, etc.

Sorry I can't be more specific here, but my lack of ADO experience is a
problem.

Best of Luck

:

I guess you found one problem. I think the major problem is I am trying to
cheat and use existing code. I should probably go back and re-think and re-do
from the beginning. Ugh.
I have inherited a database with 250 tables in it. Each table name has a
alpha suffix denoting the type of data in the table. The G tables each
contain one record. What I am trying to accomplish is appending records from
all tables with a G suffix to a GCompile table, all the records with an E
suffix to ECompile table, etc. It doesn't seem to me like it should be that
difficult; however I can't figure out the words Access wants to hear.
Just to be sure you are saying that I need to include the InStr in the first
loop when I define the recordset?

Thanks

:

I am having trouble understanding what it is you are trying to do. You say
you want to select tables with a G in the name; however, you are opening
recset1 hard coded and outside both your loops. Can you be a little more
specific?

recset1.Open "SELECT [604013G].*FROM [604013G];", CurrentProject.Connection,

:

Here is my code. I copied it from one of my other db's and modified it. Right
now it works when I input an existing table name. What I would like to do is
have it loop through all the tables with a "G" in the table name and put
those records in an existing table. I think the problem is in defining the
recset. I can't figure out how to use the InStr function to define the recset.
I'm still pretty new to coding and know that I'm not great at it so please
be kind when you look at it

Thanks.

Sub GFileCompile()
Dim db As Database
Dim recset1 As New ADODB.Recordset 'The input table
Dim recset2 As New ADODB.Recordset 'The product table (create ahead to
time)

Dim specs As String
Dim count As Integer
Dim thefieldcnt As Integer


recset1.Open "SELECT [604013G].*FROM [604013G];", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic 'name of the source table
recset2.Open "SELECT [GFileCompile].*FROM [GFileCompile];",
CurrentProject.Connection, adOpenKeyset, adLockOptimistic 'name of
destination table

thefieldcnt = recset1.Fields.count 'This is a count of the fields in the
table.

Do loop
**rest of loop here
End If
count = count + 1

Loop Until count = thefieldcnt 'Stops after the last field
recset1.MoveNext
Loop Until recset1.EOF

End Sub



:

Not enough info to gave a complete answer, but basically, you can use the
Instr() function to determine whether the table name has the character in it:

If Instr(strTableName, "x") Then
'Do the loop
End If

strTableName is the name of the table and x is the character you want to
check for. If you need more detail, post your code.

:

I have a loop in Access that is working, but what I would like to do is have
the loop only work on tables that have a specific alpha character in the
table name. I think the best way to accomplish this is to do an If - then
statement before the loop, but I can't figure out the correct
function/property to use. I tried using "Source" with wildcards and it didn't
work. I'm a little out of my league on this one and would appreciate any help.

Thanks.