VBA Open Record Set multiple tables

[dbarmer]

Dear All:

I am trying to open two tables and read the data in VBA. One is a master
table that contains key info, needed to grab data from the second. I am
having trouble with the OpenRecordSet Commands.

Set rst = db.OpenRecordset("Eweek", dbOpenTable)

I want to Loop each record from "Eweek" - [ENO] and compare it to another
table that has a same field [ENO]. (loop all records in the second table and
calculate a function) if the [ENO] field matches. IF the data matches, I
want to post the final results in a third table for reporting purposes.

I am geting errors when trying to create two recordsets. How do you "set
rst" for more than one table? I tried rst1 and rst2.

Any examples would be most helpful.

P.S. table2 will contain many [ENO] with other fields to caculate data. SO
Creating a query would be a nightmare.

 

[Allen Browne]

Try declaring the recordsets like this:
Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Set db = CurrentDb()
Set rst1 = db.OpenRecordset("Eweek")
Set rst2 = db.OpenRecordset("SomeOtherTable")
'etc.

Opening dbOpenTable will fail if this is an attached table. The above code
will default to dbOpenTable type for a local table.

For an example of looping through a recordset, see:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

 

[dbarmer]

Allen:

Thanks for your reply. I have learned a few things from you. After looking
at some examples on your site, I tried the following, ultimately failing with
a run time error. Can you see what is up ---- I'm certain that this is NOT
the best way to do this.

Option Compare Database

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Set db = CurrentDb()
Set rst1 = db.OpenRecordset("FNLDATA")
Set rst2 = db.OpenRecordset("Eweek")

rst1.MoveFirst

Do While Not rs1.EOF
' rs1.Eno is a field I need to look for in the other table
Do While Not rs2.EOF
If rs2!Eno = rs1.Eno Then
MsgBox "Found Employee " & rs1!Eno ' test to see if the program
got here
If rs2.GL = "501101" Then ec1 = ec1 + 1
Else
End If
rs2.MoveNext
Loop
rs1.MoveNext
Loop

rst1.Close
rst2.Close
Set rs1 = Nothing


End Sub

Try declaring the recordsets like this:
Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Set db = CurrentDb()
Set rst1 = db.OpenRecordset("Eweek")
Set rst2 = db.OpenRecordset("SomeOtherTable")
'etc.

Opening dbOpenTable will fail if this is an attached table. The above code
will default to dbOpenTable type for a local table.

For an example of looping through a recordset, see:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Dear All:

I am trying to open two tables and read the data in VBA. One is a master
table that contains key info, needed to grab data from the second. I am
having trouble with the OpenRecordSet Commands.

Set rst = db.OpenRecordset("Eweek", dbOpenTable)

I want to Loop each record from "Eweek" - [ENO] and compare it to another
table that has a same field [ENO]. (loop all records in the second table
and
calculate a function) if the [ENO] field matches. IF the data matches, I
want to post the final results in a third table for reporting purposes.

I am geting errors when trying to create two recordsets. How do you "set
rst" for more than one table? I tried rst1 and rst2.

Any examples would be most helpful.

P.S. table2 will contain many [ENO] with other fields to caculate data.
SO
Creating a query would be a nightmare.

 

[Allen Browne]

Which line generates the error?

What is the error number and the error message?

There are several things that could fail here, e.g.:
- the MoveFirst would fail if there are no records,
- ec1 is not a declared variable,
- you walk every records of rst2 for every record of rst1 (not sure why),
- you don't rst2.MoveFirst again before the next loop through of rst1,
- you set rs1 to Nothing, but it's not a declared variable.

Would it be possible to achieve this with the Unmatched Query Wizard (first
dialog when you create a new query)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

Thanks for your reply. I have learned a few things from you. After
looking
at some examples on your site, I tried the following, ultimately failing
with
a run time error. Can you see what is up ---- I'm certain that this is
NOT
the best way to do this.

Option Compare Database

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Set db = CurrentDb()
Set rst1 = db.OpenRecordset("FNLDATA")
Set rst2 = db.OpenRecordset("Eweek")

rst1.MoveFirst

Do While Not rs1.EOF
' rs1.Eno is a field I need to look for in the other table
Do While Not rs2.EOF
If rs2!Eno = rs1.Eno Then
MsgBox "Found Employee " & rs1!Eno ' test to see if the
program
got here
If rs2.GL = "501101" Then ec1 = ec1 + 1
Else
End If
rs2.MoveNext
Loop
rs1.MoveNext
Loop

rst1.Close
rst2.Close
Set rs1 = Nothing


End Sub

Try declaring the recordsets like this:
Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Set db = CurrentDb()
Set rst1 = db.OpenRecordset("Eweek")
Set rst2 = db.OpenRecordset("SomeOtherTable")
'etc.

Opening dbOpenTable will fail if this is an attached table. The above
code
will default to dbOpenTable type for a local table.

For an example of looping through a recordset, see:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

Dear All:

I am trying to open two tables and read the data in VBA. One is a
master
table that contains key info, needed to grab data from the second. I
am
having trouble with the OpenRecordSet Commands.

Set rst = db.OpenRecordset("Eweek", dbOpenTable)

I want to Loop each record from "Eweek" - [ENO] and compare it to
another
table that has a same field [ENO]. (loop all records in the second
table
and
calculate a function) if the [ENO] field matches. IF the data
matches, I
want to post the final results in a third table for reporting purposes.

I am geting errors when trying to create two recordsets. How do you
"set
rst" for more than one table? I tried rst1 and rst2.

Any examples would be most helpful.

P.S. table2 will contain many [ENO] with other fields to caculate
data.
SO
Creating a query would be a nightmare.

 

[dbarmer]

Allen:

Thanks! You hit it, I had some typos.
I got all that to work. Now when I have everything program'd in, It does
not seem to work the way I need it to -- Can't get the data to the third
table. To answer the question of walking thru records, in Table 1 - (FNLDATA)
is the master set. It contains only one record for each employee. Table 2 -
(EWEEK) contains many employee no's (the key field) but with one or more of
different GL's and amt's. I need to caculate each of the 16 GL's. When I
tried this thru the query, it would duplicate the emp info several times
over. SO, I tought I would have VBA read each employee, give a sum total for
each GL. and count it to a NEW TABLE. I tried a cross-tab query as well.
The problem here is, each employee may or may not have the same GL's. The
ultimate goal is to have one line of employee data with a SUM of gl
information.

Please don't laugh when you see this code..... I know it is really aweful.
Any suggestions.

Private Sub Form_Load()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset

Dim GLis As String
Dim MonthIs As Date
Dim Employee As String
Dim Week As String
Dim Tcount As Double
Dim Tamt As Double
Dim X9 As String


Dim GL1a As Double 'DCTCLN (Duct Cleaning)
Dim GL1c As Double
Dim GL2a As Double '501101 (Res Diagnostic)
Dim GL2c As Double
Dim GL3a As Double '501201 (Res CTS)
Dim GL3c As Double
Dim GL4a As Double 'NEWPOA (New Res POA) 501302
Dim GL4c As Double
Dim GL5a As Double '501301 (SA Mt)
Dim GL5c As Double
Dim GL6a As Double '501304 (SA Rep)
Dim GL6c As Double
Dim GL7a As Double '501404 (C+ Wrn)
Dim GL7c As Double
Dim GL8a As Double '501401 (C+ Mt)
Dim GL8c As Double
Dim GL9a As Double 'ACCESS (Res or Plu Accessories)
Dim GL9c As Double
Dim GL10a As Double 'NEWEQP (Res or Plu New Equip)
Dim GL10c As Double
Dim GL11a As Double '504101 (Plu Diag)
Dim GL11c As Double
Dim GL12a As Double '504201 (Plu Cts)
Dim GL12c As Double
Dim GL13a As Double '504202 (Plu SA Mt or Ren)
Dim GL13c As Double
Dim GL14a As Double '504204 (Plu SA Rep)
Dim GL14c As Double
Dim GL15a As Double '504109 (Plu Qte)
Dim GL15c As Double
Dim GL16a As Double 'PLUPOA (New Plu POA)
Dim GL16c As Double


Set db = CurrentDb()
Set rs1 = db.OpenRecordset("FNLDATA")
Set rs2 = db.OpenRecordset("Eweek")
Set rs3 = db.OpenRecordset("EweekFinal")


If rs1.RecordCount = 0 Then Exit Sub

rs1.MoveFirst
' loop through each record in the first recordset
Do Until rs1.EOF
X9 = "F"
GL1a = 0: GL1c = 0: GL2a = 0: GL2c = 0

Tcount = 0: Tamt = 0

' If matching record is found then find gl's for this emp

If rs2.RecordCount = 0 Then Exit Sub
rs2.MoveFirst
Do Until rs2.EOF
If rs1![Eno] = rs2!Eno Then
GLis = rs2!GL
' just test two of the 16 gl's for now
If GLis = "DCTCLN" Then GL1c = GL1c + 1 And GL1a = GL1a +
rs2!Points And X9 = "T"
If GLis = "504202" Then GL2c = GL2c + 1 And GL2a = GL2a +
rs2!Points And X9 = "T"
If X9 = "T" Then
rs3.AddNew
rs3![Eno] = rs2!Eno
rs3![GL1a] = GL1a
rs3![GL1c] = GL1c
rs3![GL2a] = GL2a
rs3.Update
X9 = "F"
MsgBox "rs3![Eno]=" & rs3![Eno]
End


Else
End If



End If

rs2.MoveNext
Loop

rs1.MoveNext
Loop

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing



End Sub

Which line generates the error?

What is the error number and the error message?

There are several things that could fail here, e.g.:
- the MoveFirst would fail if there are no records,
- ec1 is not a declared variable,
- you walk every records of rst2 for every record of rst1 (not sure why),
- you don't rst2.MoveFirst again before the next loop through of rst1,
- you set rs1 to Nothing, but it's not a declared variable.

Would it be possible to achieve this with the Unmatched Query Wizard (first
dialog when you create a new query)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

Thanks for your reply. I have learned a few things from you. After
looking
at some examples on your site, I tried the following, ultimately failing
with
a run time error. Can you see what is up ---- I'm certain that this is
NOT
the best way to do this.

Option Compare Database

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Set db = CurrentDb()
Set rst1 = db.OpenRecordset("FNLDATA")
Set rst2 = db.OpenRecordset("Eweek")

rst1.MoveFirst

Do While Not rs1.EOF
' rs1.Eno is a field I need to look for in the other table
Do While Not rs2.EOF
If rs2!Eno = rs1.Eno Then
MsgBox "Found Employee " & rs1!Eno ' test to see if the
program
got here
If rs2.GL = "501101" Then ec1 = ec1 + 1
Else
End If
rs2.MoveNext
Loop
rs1.MoveNext
Loop

rst1.Close
rst2.Close
Set rs1 = Nothing


End Sub

Try declaring the recordsets like this:
Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Set db = CurrentDb()
Set rst1 = db.OpenRecordset("Eweek")
Set rst2 = db.OpenRecordset("SomeOtherTable")
'etc.

Opening dbOpenTable will fail if this is an attached table. The above
code
will default to dbOpenTable type for a local table.

For an example of looping through a recordset, see:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

Dear All:

I am trying to open two tables and read the data in VBA. One is a
master
table that contains key info, needed to grab data from the second. I
am
having trouble with the OpenRecordSet Commands.

Set rst = db.OpenRecordset("Eweek", dbOpenTable)

I want to Loop each record from "Eweek" - [ENO] and compare it to
another
table that has a same field [ENO]. (loop all records in the second
table
and
calculate a function) if the [ENO] field matches. IF the data
matches, I
want to post the final results in a third table for reporting purposes.

I am geting errors when trying to create two recordsets. How do you
"set
rst" for more than one table? I tried rst1 and rst2.

Any examples would be most helpful.

P.S. table2 will contain many [ENO] with other fields to caculate
data.
SO
Creating a query would be a nightmare.

 

[Allen Browne]

Crosstab should work:
- Group by EWEEK.ENO as Row Heading
- Group by EWEEK.GL as Column Heading
- Sum EWEEK.AMOUNT as Value

If you are not getting every GL shown, presumably you have a little lookup
table of GL codes as well. You could create a query using just the FNLDATA
and GLCODE tables. There will be no join between the 2 tables in the upper
pane of query design. This gives every possible combination of ENO and GL.
Drag these 2 fields into the output grid, and save the query. (To read up
more about this, search for Cartesian Product.)

Now Create a new query using the query you just created as an input "table."
Add EWEEK table as well. Join on both ENO and GL. Double-click the line
joining the 2 tables. Access pops up a dialog offering 2 options. Choose the
one that says:
All records from Query1, and any matches from EWEEK
Repeat for the 2nd join line.
This gives you an outer join. More info about that here:
http://allenbrowne.com/casu-02.html
You can now turn this into a crosstab, as above.

More suggestions for crosstab queries:
Crosstab query techniques
at:
http://allenbrowne.com/ser-67.html

As a general guide, doing stuff in queries is better than writing VBA code
where possible.

HTH.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

Thanks! You hit it, I had some typos.
I got all that to work. Now when I have everything program'd in, It does
not seem to work the way I need it to -- Can't get the data to the third
table. To answer the question of walking thru records, in Table 1 -
(FNLDATA)
is the master set. It contains only one record for each employee. Table
2 -
(EWEEK) contains many employee no's (the key field) but with one or more
of
different GL's and amt's. I need to caculate each of the 16 GL's. When I
tried this thru the query, it would duplicate the emp info several times
over. SO, I tought I would have VBA read each employee, give a sum total
for
each GL. and count it to a NEW TABLE. I tried a cross-tab query as well.
The problem here is, each employee may or may not have the same GL's. The
ultimate goal is to have one line of employee data with a SUM of gl
information.

Please don't laugh when you see this code..... I know it is really
aweful.
Any suggestions.

Private Sub Form_Load()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset

Dim GLis As String
Dim MonthIs As Date
Dim Employee As String
Dim Week As String
Dim Tcount As Double
Dim Tamt As Double
Dim X9 As String


Dim GL1a As Double 'DCTCLN (Duct Cleaning)
Dim GL1c As Double
Dim GL2a As Double '501101 (Res Diagnostic)
Dim GL2c As Double
Dim GL3a As Double '501201 (Res CTS)
Dim GL3c As Double
Dim GL4a As Double 'NEWPOA (New Res POA) 501302
Dim GL4c As Double
Dim GL5a As Double '501301 (SA Mt)
Dim GL5c As Double
Dim GL6a As Double '501304 (SA Rep)
Dim GL6c As Double
Dim GL7a As Double '501404 (C+ Wrn)
Dim GL7c As Double
Dim GL8a As Double '501401 (C+ Mt)
Dim GL8c As Double
Dim GL9a As Double 'ACCESS (Res or Plu Accessories)
Dim GL9c As Double
Dim GL10a As Double 'NEWEQP (Res or Plu New Equip)
Dim GL10c As Double
Dim GL11a As Double '504101 (Plu Diag)
Dim GL11c As Double
Dim GL12a As Double '504201 (Plu Cts)
Dim GL12c As Double
Dim GL13a As Double '504202 (Plu SA Mt or Ren)
Dim GL13c As Double
Dim GL14a As Double '504204 (Plu SA Rep)
Dim GL14c As Double
Dim GL15a As Double '504109 (Plu Qte)
Dim GL15c As Double
Dim GL16a As Double 'PLUPOA (New Plu POA)
Dim GL16c As Double


Set db = CurrentDb()
Set rs1 = db.OpenRecordset("FNLDATA")
Set rs2 = db.OpenRecordset("Eweek")
Set rs3 = db.OpenRecordset("EweekFinal")


If rs1.RecordCount = 0 Then Exit Sub

rs1.MoveFirst
' loop through each record in the first recordset
Do Until rs1.EOF
X9 = "F"
GL1a = 0: GL1c = 0: GL2a = 0: GL2c = 0

Tcount = 0: Tamt = 0

' If matching record is found then find gl's for this emp

If rs2.RecordCount = 0 Then Exit Sub
rs2.MoveFirst
Do Until rs2.EOF
If rs1![Eno] = rs2!Eno Then
GLis = rs2!GL
' just test two of the 16 gl's for now
If GLis = "DCTCLN" Then GL1c = GL1c + 1 And GL1a = GL1a +
rs2!Points And X9 = "T"
If GLis = "504202" Then GL2c = GL2c + 1 And GL2a = GL2a +
rs2!Points And X9 = "T"
If X9 = "T" Then
rs3.AddNew
rs3![Eno] = rs2!Eno
rs3![GL1a] = GL1a
rs3![GL1c] = GL1c
rs3![GL2a] = GL2a
rs3.Update
X9 = "F"
MsgBox "rs3![Eno]=" & rs3![Eno]
End


Else
End If



End If

rs2.MoveNext
Loop

rs1.MoveNext
Loop

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing



End Sub

Which line generates the error?

What is the error number and the error message?

There are several things that could fail here, e.g.:
- the MoveFirst would fail if there are no records,
- ec1 is not a declared variable,
- you walk every records of rst2 for every record of rst1 (not sure why),
- you don't rst2.MoveFirst again before the next loop through of rst1,
- you set rs1 to Nothing, but it's not a declared variable.

Would it be possible to achieve this with the Unmatched Query Wizard
(first
dialog when you create a new query)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

Thanks for your reply. I have learned a few things from you. After
looking
at some examples on your site, I tried the following, ultimately
failing
with
a run time error. Can you see what is up ---- I'm certain that this is
NOT
the best way to do this.

Option Compare Database

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Set db = CurrentDb()
Set rst1 = db.OpenRecordset("FNLDATA")
Set rst2 = db.OpenRecordset("Eweek")

rst1.MoveFirst

Do While Not rs1.EOF
' rs1.Eno is a field I need to look for in the other table
Do While Not rs2.EOF
If rs2!Eno = rs1.Eno Then
MsgBox "Found Employee " & rs1!Eno ' test to see if the
program
got here
If rs2.GL = "501101" Then ec1 = ec1 + 1
Else
End If
rs2.MoveNext
Loop
rs1.MoveNext
Loop

rst1.Close
rst2.Close
Set rs1 = Nothing


End Sub

:

Try declaring the recordsets like this:
Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Set db = CurrentDb()
Set rst1 = db.OpenRecordset("Eweek")
Set rst2 = db.OpenRecordset("SomeOtherTable")
'etc.

Opening dbOpenTable will fail if this is an attached table. The above
code
will default to dbOpenTable type for a local table.

For an example of looping through a recordset, see:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

Dear All:

I am trying to open two tables and read the data in VBA. One is a
master
table that contains key info, needed to grab data from the second.
I
am
having trouble with the OpenRecordSet Commands.

Set rst = db.OpenRecordset("Eweek", dbOpenTable)

I want to Loop each record from "Eweek" - [ENO] and compare it to
another
table that has a same field [ENO]. (loop all records in the second
table
and
calculate a function) if the [ENO] field matches. IF the data
matches, I
want to post the final results in a third table for reporting
purposes.

I am geting errors when trying to create two recordsets. How do you
"set
rst" for more than one table? I tried rst1 and rst2.

Any examples would be most helpful.

P.S. table2 will contain many [ENO] with other fields to caculate
data.
SO
Creating a query would be a nightmare.

 

[dbarmer]

Allen:

The crosstab would work except I have two values, one is a count of each gl
and one is a sum of each gl. I can do one or the other. Then there is
other data I need to go along, crosstab's aren't friendly in that way.

Should I do two crosstabs (one for the count, then one for sum) then merge
them into a new query?......

.....I have done something like this before, and all was good until I needed
that "other data" besides the counts and sums, and it will duplicate -
defeating the purpose. This is why I thought vba would be the best route.

What do you think?

Crosstab should work:
- Group by EWEEK.ENO as Row Heading
- Group by EWEEK.GL as Column Heading
- Sum EWEEK.AMOUNT as Value

If you are not getting every GL shown, presumably you have a little lookup
table of GL codes as well. You could create a query using just the FNLDATA
and GLCODE tables. There will be no join between the 2 tables in the upper
pane of query design. This gives every possible combination of ENO and GL.
Drag these 2 fields into the output grid, and save the query. (To read up
more about this, search for Cartesian Product.)

Now Create a new query using the query you just created as an input "table."
Add EWEEK table as well. Join on both ENO and GL. Double-click the line
joining the 2 tables. Access pops up a dialog offering 2 options. Choose the
one that says:
All records from Query1, and any matches from EWEEK
Repeat for the 2nd join line.
This gives you an outer join. More info about that here:
http://allenbrowne.com/casu-02.html
You can now turn this into a crosstab, as above.

More suggestions for crosstab queries:
Crosstab query techniques
at:
http://allenbrowne.com/ser-67.html

As a general guide, doing stuff in queries is better than writing VBA code
where possible.

HTH.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

Thanks! You hit it, I had some typos.
I got all that to work. Now when I have everything program'd in, It does
not seem to work the way I need it to -- Can't get the data to the third
table. To answer the question of walking thru records, in Table 1 -
(FNLDATA)
is the master set. It contains only one record for each employee. Table
2 -
(EWEEK) contains many employee no's (the key field) but with one or more
of
different GL's and amt's. I need to caculate each of the 16 GL's. When I
tried this thru the query, it would duplicate the emp info several times
over. SO, I tought I would have VBA read each employee, give a sum total
for
each GL. and count it to a NEW TABLE. I tried a cross-tab query as well.
The problem here is, each employee may or may not have the same GL's. The
ultimate goal is to have one line of employee data with a SUM of gl
information.

Please don't laugh when you see this code..... I know it is really
aweful.
Any suggestions.

Private Sub Form_Load()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset

Dim GLis As String
Dim MonthIs As Date
Dim Employee As String
Dim Week As String
Dim Tcount As Double
Dim Tamt As Double
Dim X9 As String


Dim GL1a As Double 'DCTCLN (Duct Cleaning)
Dim GL1c As Double
Dim GL2a As Double '501101 (Res Diagnostic)
Dim GL2c As Double
Dim GL3a As Double '501201 (Res CTS)
Dim GL3c As Double
Dim GL4a As Double 'NEWPOA (New Res POA) 501302
Dim GL4c As Double
Dim GL5a As Double '501301 (SA Mt)
Dim GL5c As Double
Dim GL6a As Double '501304 (SA Rep)
Dim GL6c As Double
Dim GL7a As Double '501404 (C+ Wrn)
Dim GL7c As Double
Dim GL8a As Double '501401 (C+ Mt)
Dim GL8c As Double
Dim GL9a As Double 'ACCESS (Res or Plu Accessories)
Dim GL9c As Double
Dim GL10a As Double 'NEWEQP (Res or Plu New Equip)
Dim GL10c As Double
Dim GL11a As Double '504101 (Plu Diag)
Dim GL11c As Double
Dim GL12a As Double '504201 (Plu Cts)
Dim GL12c As Double
Dim GL13a As Double '504202 (Plu SA Mt or Ren)
Dim GL13c As Double
Dim GL14a As Double '504204 (Plu SA Rep)
Dim GL14c As Double
Dim GL15a As Double '504109 (Plu Qte)
Dim GL15c As Double
Dim GL16a As Double 'PLUPOA (New Plu POA)
Dim GL16c As Double


Set db = CurrentDb()
Set rs1 = db.OpenRecordset("FNLDATA")
Set rs2 = db.OpenRecordset("Eweek")
Set rs3 = db.OpenRecordset("EweekFinal")


If rs1.RecordCount = 0 Then Exit Sub

rs1.MoveFirst
' loop through each record in the first recordset
Do Until rs1.EOF
X9 = "F"
GL1a = 0: GL1c = 0: GL2a = 0: GL2c = 0

Tcount = 0: Tamt = 0

' If matching record is found then find gl's for this emp

If rs2.RecordCount = 0 Then Exit Sub
rs2.MoveFirst
Do Until rs2.EOF
If rs1![Eno] = rs2!Eno Then
GLis = rs2!GL
' just test two of the 16 gl's for now
If GLis = "DCTCLN" Then GL1c = GL1c + 1 And GL1a = GL1a +
rs2!Points And X9 = "T"
If GLis = "504202" Then GL2c = GL2c + 1 And GL2a = GL2a +
rs2!Points And X9 = "T"
If X9 = "T" Then
rs3.AddNew
rs3![Eno] = rs2!Eno
rs3![GL1a] = GL1a
rs3![GL1c] = GL1c
rs3![GL2a] = GL2a
rs3.Update
X9 = "F"
MsgBox "rs3![Eno]=" & rs3![Eno]
End


Else
End If



End If

rs2.MoveNext
Loop

rs1.MoveNext
Loop

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing



End Sub

Which line generates the error?

What is the error number and the error message?

There are several things that could fail here, e.g.:
- the MoveFirst would fail if there are no records,
- ec1 is not a declared variable,
- you walk every records of rst2 for every record of rst1 (not sure why),
- you don't rst2.MoveFirst again before the next loop through of rst1,
- you set rs1 to Nothing, but it's not a declared variable.

Would it be possible to achieve this with the Unmatched Query Wizard
(first
dialog when you create a new query)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

Thanks for your reply. I have learned a few things from you. After
looking
at some examples on your site, I tried the following, ultimately
failing
with
a run time error. Can you see what is up ---- I'm certain that this is
NOT
the best way to do this.

Option Compare Database

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Set db = CurrentDb()
Set rst1 = db.OpenRecordset("FNLDATA")
Set rst2 = db.OpenRecordset("Eweek")

rst1.MoveFirst

Do While Not rs1.EOF
' rs1.Eno is a field I need to look for in the other table
Do While Not rs2.EOF
If rs2!Eno = rs1.Eno Then
MsgBox "Found Employee " & rs1!Eno ' test to see if the
program
got here
If rs2.GL = "501101" Then ec1 = ec1 + 1
Else
End If
rs2.MoveNext
Loop
rs1.MoveNext
Loop

rst1.Close
rst2.Close
Set rs1 = Nothing


End Sub

:

Try declaring the recordsets like this:
Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Set db = CurrentDb()
Set rst1 = db.OpenRecordset("Eweek")
Set rst2 = db.OpenRecordset("SomeOtherTable")
'etc.

Opening dbOpenTable will fail if this is an attached table. The above
code
will default to dbOpenTable type for a local table.

For an example of looping through a recordset, see:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

Dear All:

I am trying to open two tables and read the data in VBA. One is a
master
table that contains key info, needed to grab data from the second.
I
am
having trouble with the OpenRecordSet Commands.

Set rst = db.OpenRecordset("Eweek", dbOpenTable)

I want to Loop each record from "Eweek" - [ENO] and compare it to
another
table that has a same field [ENO]. (loop all records in the second
table
and
calculate a function) if the [ENO] field matches. IF the data
matches, I
want to post the final results in a third table for reporting
purposes.

I am geting errors when trying to create two recordsets. How do you
"set
rst" for more than one table? I tried rst1 and rst2.

Any examples would be most helpful.

P.S. table2 will contain many [ENO] with other fields to caculate
data.
SO
Creating a query would be a nightmare.

 

[Allen Browne]

There's a couple of possible approaches here.

Microsoft has a suggestion here:
http://support.microsoft.com/kb/209143

My preferred approach is to create a table with a text field. Enter a record
for each column prefix you need, e.g. "Amt" and "Qty". Then use a Cartesian
product in the crosstab.

This example works with the old Northwind sample database:

TRANSFORM Sum(IIf([FieldName]="Qty",
[Quantity],[Quantity]*[Order Details]![UnitPrice])) AS DaValue
SELECT Products.ProductName
FROM tblXtabColumns, Products
INNER JOIN (Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID)
ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.OrderDate) Between #1/1/1998# And #3/31/1998#))
GROUP BY Products.ProductName
PIVOT [FieldName] & Month([OrderDate]);

 

[dbarmer]

Allen

Thanks for all your help..... U deserve an award of some-kind. I'm going
to attempt to make two crosstabs with the sums and then combine them, as
suggested in the microsoft article. If I get in a pinch -- I may yell at you
for a Q. or two. Again Thanks so much!

There's a couple of possible approaches here.

Microsoft has a suggestion here:
http://support.microsoft.com/kb/209143

My preferred approach is to create a table with a text field. Enter a record
for each column prefix you need, e.g. "Amt" and "Qty". Then use a Cartesian
product in the crosstab.

This example works with the old Northwind sample database:

TRANSFORM Sum(IIf([FieldName]="Qty",
[Quantity],[Quantity]*[Order Details]![UnitPrice])) AS DaValue
SELECT Products.ProductName
FROM tblXtabColumns, Products
INNER JOIN (Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID)
ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.OrderDate) Between #1/1/1998# And #3/31/1998#))
GROUP BY Products.ProductName
PIVOT [FieldName] & Month([OrderDate]);


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

The crosstab would work except I have two values, one is a count of each
gl
and one is a sum of each gl. I can do one or the other. Then there is
other data I need to go along, crosstab's aren't friendly in that way.

Should I do two crosstabs (one for the count, then one for sum) then merge
them into a new query?......

....I have done something like this before, and all was good until I
needed
that "other data" besides the counts and sums, and it will duplicate -
defeating the purpose. This is why I thought vba would be the best
route.

What do you think?