Recordset Code Help

  • Thread starter Thread starter mattc66 via AccessMonster.com
  • Start date Start date
M

mattc66 via AccessMonster.com

Below is my code that I am having trouble with. Both Tables have a related
fld called CustomerPO. What I need is RS1 to grab the first record and then
get the related RS2 records before it goes and gets the next PO.

Can someone read my code and help me write in order to do this.

Thanks


CODE>>>>>>>>>>>>>>>>>>>>>


Function copyOrder()

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs5 As DAO.Recordset

Dim varReturn As Variant

Set db = CurrentDb()

Set rs1 = db.OpenRecordset("850_OrderRecord")
Set rs2 = db.OpenRecordset("850_DetailRecord")
Set rs5 = db.OpenRecordset("EDI_850_Export")

rs1.MoveFirst
While Not rs1.EOF
With rs5
rs5.AddNew
rs5.Fields("R_TYPE") = rs1.Fields("R_TYPE")
rs5.Fields("FLD1") = Nz(rs1.Fields("CUSTOMER_ID"), "") ' helps to
deal with nulls in bundle table
rs5.Fields("FLD2") = Nz(rs1.Fields("CUSTOMER_PO"), "") ' it says
if there's a null, use this
rs5.Fields("FLD3") = Nz(rs1.Fields("ORDER_DATE"), "") '<--in this
case, a blank
rs5.Update
End With
rs1.MoveNext

Wend
Set rs1 = Nothing

rs2.MoveFirst
While Not rs2.EOF
With rs5
rs5.AddNew
rs5.Fields("R_TYPE") = Nz(rs1.Fields("R_TYPE"), "")
rs5.Fields("FLD1") = Nz(rs1.Fields("CUSTOMER_ID"), "") ' helps to
deal with nulls in bundle table
rs5.Fields("FLD2") = Nz(rs1.Fields("CUSTOMER_PO"), "") ' it says
if there's a null, use this
rs5.Fields("FLD3") = Nz(rs1.Fields("ITEM"), "") '<--in this case,
a blank
rs5.Update
End With
rs2.MoveNext

Wend
Set rs1 = Nothing
Set rs2 = Nothing
Set rs5 = Nothing
End Function
 
Hi -

I don't know what you are trying to accomplish here - you are putting
data from two different tables into the same Export table; rs5!FLD3 is
therefore either a date or an item, depending I assume on the value of
rs5!R_TYPE. If the ORDER_DATE is of type date/time, you are going to be
in a mess.

It is **bad** practice to have one field contain different data types
depending on the value of another field. I suggest (strongly!) that you
stop right there and rethink the structure of the export table.

As I said, I don't know what you are trying to do, but if you create a
query linking the orders and details tables, and then use that query as
as the source of one recordset, it will be a lot easier. In fact, if
you make that query into an append or make-table query, you won't need
code at all.

Hope this helps

John
 
Each fld in the export table is Text. The Data Flds are text.

I am building an EDI Translator. Once I have the data in the export Table I
will be able to export it out in a deliminated format.

I don't like it ether, but I am forced to assemble the data in this order
based on the requirments of the EDI 850 format required by my ERP software.

Final Export file needed.
"O","STEVE","060802","PO1","060831","UPS GRND","","","","0005555","","JOE"
"D","STEVE","PO1","WAGON",10,25.00,"N","N","060831","","","","",0
"D","STEVE","PO1","CYCLE",5,15.00,"N","N","060831","","","","",0
"O","FRED","060802","PO2","060825","UPS GRND","","","","0005555","","JOE"
"D","FRED","PO2","WAGON",8,25.00,"N","N","060825","","","","",0
"D","FRED","PO2","CYCLE",8,15.00,"N","N","060825","","","","",0

I am note sure how I'd do this with a query.

Matt


J. Goddard said:
Hi -

I don't know what you are trying to accomplish here - you are putting
data from two different tables into the same Export table; rs5!FLD3 is
therefore either a date or an item, depending I assume on the value of
rs5!R_TYPE. If the ORDER_DATE is of type date/time, you are going to be
in a mess.

It is **bad** practice to have one field contain different data types
depending on the value of another field. I suggest (strongly!) that you
stop right there and rethink the structure of the export table.

As I said, I don't know what you are trying to do, but if you create a
query linking the orders and details tables, and then use that query as
as the source of one recordset, it will be a lot easier. In fact, if
you make that query into an append or make-table query, you won't need
code at all.

Hope this helps

John
Below is my code that I am having trouble with. Both Tables have a related
fld called CustomerPO. What I need is RS1 to grab the first record and then
Click to expand...
[quoted text clipped - 59 lines]
Set rs5 = Nothing
End Function
Click to expand...
Click to expand...
 
Final Export file needed.
"O","STEVE","060802","PO1","060831","UPS GRND","","","","0005555","","JOE"
"D","STEVE","PO1","WAGON",10,25.00,"N","N","060831","","","","",0
"D","STEVE","PO1","CYCLE",5,15.00,"N","N","060831","","","","",0
"O","FRED","060802","PO2","060825","UPS GRND","","","","0005555","","JOE"
"D","FRED","PO2","WAGON",8,25.00,"N","N","060825","","","","",0
"D","FRED","PO2","CYCLE",8,15.00,"N","N","060825","","","","",0

I am note sure how I'd do this with a query.
Click to expand...

Well, I wouldn't. I'd use a Query to assemble the desired field
values, and then use VBA code using the TransferText method to
construct the string with delimiters and commas.

John W. Vinson[MVP]
 
I open to any help. I have been trying to figure out the best way to handle
this... The trouble is it must be formatted with the Order header, then the
order detail for each order.

Matt

John said:
Final Export file needed.
"O","STEVE","060802","PO1","060831","UPS GRND","","","","0005555","","JOE"
Click to expand...
[quoted text clipped - 5 lines]
I am note sure how I'd do this with a query.
Click to expand...

Well, I wouldn't. I'd use a Query to assemble the desired field
values, and then use VBA code using the TransferText method to
construct the string with delimiters and commas.

John W. Vinson[MVP]
Click to expand...
 
Create A select query that joins the two tables on the field or fields that
relates the two.

I don't think you need any VBA code to output the data in the required
format. I would suggest building an export specification for the query that
will provide the formatting you need, then export it with the TransferText
method using the export spec.

mattc66 via AccessMonster.com said:
I open to any help. I have been trying to figure out the best way to handle
this... The trouble is it must be formatted with the Order header, then the
order detail for each order.

Matt

John said:
Final Export file needed.
"O","STEVE","060802","PO1","060831","UPS GRND","","","","0005555","","JOE"
Click to expand...
[quoted text clipped - 5 lines]
I am note sure how I'd do this with a query.
Click to expand...

Well, I wouldn't. I'd use a Query to assemble the desired field
values, and then use VBA code using the TransferText method to
construct the string with delimiters and commas.

John W. Vinson[MVP]
Click to expand...

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com
Click to expand...
 
Hi -

Given your requirement, then VBA code is probably the best way.
However, I would not put the data into another table - you can write to
the text file directly.

In the first post, you looped through the orders table, and then through
the details table. That won't work - what you have to do is loop
through the orders table, but inside that loop you:

1) write the data for the order to the text file
2) select the details records for that order
3) write the data for each detail line to the text file.

I have done that sort of thing; the biggest headache is to get the
syntax on the output file right. Your code might look something like this:

sub copyOrder()

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

dim SQL as string



Set db = CurrentDb()
open "FileName" for output as #1
Set rs1 = db.OpenRecordset("850_OrderRecord")
rs1.MoveFirst
'
' Loop through the orders
'
While Not rs1.EOF
'
' Write the data for this order
'
write #1, rs1![r_type],nz(rs1![customer_ID],"").... etc
'
' Get the details for this order
'
SQL = "Select * from 850_DetailRecord where CUstomer_ID = ' &
rs1![customer_ID] & "'"
Set rs2 = db.OpenRecordset(SQL)
while not rs2.EOF
'
' Write the data for this order details record
'
Write #1,rs2![r_type],nz(rs2![customer_ID],"").... etc
rs2.movenext
wend
rs2.close
'
' Move to next order record
'
rs1.movenext
wend
rs1.close
close #1
set db=nothing
End sub

The use of the VBA Write statement make coding simpler - it puts in
quotes and commas for you on the text file. Check the VBA help entry
for Write#. Replace "Filename" with the name of the text file,
including full path if needed.

Hope this helps - happy programming!
(Trick or treat?)

John



I open to any help. I have been trying to figure out the best way to handle
this... The trouble is it must be formatted with the Order header, then the
order detail for each order.

Matt

John said:
Final Export file needed.
"O","STEVE","060802","PO1","060831","UPS GRND","","","","0005555","","JOE"
Click to expand...

[quoted text clipped - 5 lines]
I am note sure how I'd do this with a query.
Click to expand...

Well, I wouldn't. I'd use a Query to assemble the desired field
values, and then use VBA code using the TransferText method to
construct the string with delimiters and commas.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Thank you very much - I will give this a shot.

J. Goddard said:
Hi -

Given your requirement, then VBA code is probably the best way.
However, I would not put the data into another table - you can write to
the text file directly.

In the first post, you looped through the orders table, and then through
the details table. That won't work - what you have to do is loop
through the orders table, but inside that loop you:

1) write the data for the order to the text file
2) select the details records for that order
3) write the data for each detail line to the text file.

I have done that sort of thing; the biggest headache is to get the
syntax on the output file right. Your code might look something like this:

sub copyOrder()

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

dim SQL as string

Set db = CurrentDb()
open "FileName" for output as #1
Set rs1 = db.OpenRecordset("850_OrderRecord")
rs1.MoveFirst
'
' Loop through the orders
'
While Not rs1.EOF
'
' Write the data for this order
'
write #1, rs1![r_type],nz(rs1![customer_ID],"").... etc
'
' Get the details for this order
'
SQL = "Select * from 850_DetailRecord where CUstomer_ID = ' &
rs1![customer_ID] & "'"
Set rs2 = db.OpenRecordset(SQL)
while not rs2.EOF
'
' Write the data for this order details record
'
Write #1,rs2![r_type],nz(rs2![customer_ID],"").... etc
rs2.movenext
wend
rs2.close
'
' Move to next order record
'
rs1.movenext
wend
rs1.close
close #1
set db=nothing
End sub

The use of the VBA Write statement make coding simpler - it puts in
quotes and commas for you on the text file. Check the VBA help entry
for Write#. Replace "Filename" with the name of the text file,
including full path if needed.

Hope this helps - happy programming!
(Trick or treat?)

John
I open to any help. I have been trying to figure out the best way to handle
this... The trouble is it must be formatted with the Order header, then the
Click to expand...
[quoted text clipped - 14 lines]
Click to expand...
 
Part of this is working. It writes the rs1 data to the text file, but it
doesn't write the rs2 data. It appears to loop past it and it doesn't fail.
Not sure why..

See below code.

Matt

CODE>>>>>>>>>>>>>>>>>>

Private Sub exp850_Click()

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Dim SQL As String

Set db = CurrentDb()
Open "c:\Files\exp850.txt" For Output As #1
Set rs1 = db.OpenRecordset("850_OrderRecord")

rs1.MoveFirst
'
' Loop through the orders
'
While Not rs1.EOF
'
' Write the data for this order
'
Write #1, rs1![R_TYPE], rs1![CUSTOMER_PO], rs1![ORDER_DATE]
'
' Get the details for this order
'
SQL = "Select * from 850_DetailRecord where CUSTOMER_PO = ' & " _
& rs1![CUSTOMER_PO] & "'"

Set rs2 = db.OpenRecordset(SQL)

While Not rs2.EOF
'
' Write the data for this order details record
'
Write #1, rs2![R_TYPE], rs2![CUSTOMER_ID], rs2![CUSTOMER_PO]

'Write #1, rs2
rs2.MoveNext
Wend
rs2.Close
'
' Move to next order record
'
rs1.MoveNext
Wend
rs1.Close

Close #1
Set db = Nothing

End Sub


J. Goddard said:
Hi -

Given your requirement, then VBA code is probably the best way.
However, I would not put the data into another table - you can write to
the text file directly.

In the first post, you looped through the orders table, and then through
the details table. That won't work - what you have to do is loop
through the orders table, but inside that loop you:

1) write the data for the order to the text file
2) select the details records for that order
3) write the data for each detail line to the text file.

I have done that sort of thing; the biggest headache is to get the
syntax on the output file right. Your code might look something like this:

sub copyOrder()

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

dim SQL as string

Set db = CurrentDb()
open "FileName" for output as #1
Set rs1 = db.OpenRecordset("850_OrderRecord")
rs1.MoveFirst
'
' Loop through the orders
'
While Not rs1.EOF
'
' Write the data for this order
'
write #1, rs1![r_type],nz(rs1![customer_ID],"").... etc
'
' Get the details for this order
'
SQL = "Select * from 850_DetailRecord where CUstomer_ID = ' &
rs1![customer_ID] & "'"
Set rs2 = db.OpenRecordset(SQL)
while not rs2.EOF
'
' Write the data for this order details record
'
Write #1,rs2![r_type],nz(rs2![customer_ID],"").... etc
rs2.movenext
wend
rs2.close
'
' Move to next order record
'
rs1.movenext
wend
rs1.close
close #1
set db=nothing
End sub

The use of the VBA Write statement make coding simpler - it puts in
quotes and commas for you on the text file. Check the VBA help entry
for Write#. Replace "Filename" with the name of the text file,
including full path if needed.

Hope this helps - happy programming!
(Trick or treat?)

John
I open to any help. I have been trying to figure out the best way to handle
this... The trouble is it must be formatted with the Order header, then the
Click to expand...
[quoted text clipped - 14 lines]
Click to expand...
 
I got it to work. It was a syntax issue with the SQL statment.

SQL = "Select * From 850_DetailRecord Where CUSTOMER_PO = '" & rs1!
[CUSTOMER_PO] & " '"

If I need to add a 3rd record set that also needed to be grouped with the
same Customer order. Were would I place the While statement? Would it go
inside the rs2 statement?

Thanks - So far this is working great....



Part of this is working. It writes the rs1 data to the text file, but it
doesn't write the rs2 data. It appears to loop past it and it doesn't fail.
Not sure why..

See below code.

Matt

CODE>>>>>>>>>>>>>>>>>>

Private Sub exp850_Click()

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Dim SQL As String

Set db = CurrentDb()
Open "c:\Files\exp850.txt" For Output As #1
Set rs1 = db.OpenRecordset("850_OrderRecord")

rs1.MoveFirst
'
' Loop through the orders
'
While Not rs1.EOF
'
' Write the data for this order
'
Write #1, rs1![R_TYPE], rs1![CUSTOMER_PO], rs1![ORDER_DATE]
'
' Get the details for this order
'
SQL = "Select * from 850_DetailRecord where CUSTOMER_PO = ' & " _
& rs1![CUSTOMER_PO] & "'"

Set rs2 = db.OpenRecordset(SQL)

While Not rs2.EOF
'
' Write the data for this order details record
'
Write #1, rs2![R_TYPE], rs2![CUSTOMER_ID], rs2![CUSTOMER_PO]

'Write #1, rs2
rs2.MoveNext
Wend
rs2.Close
'
' Move to next order record
'
rs1.MoveNext
Wend
rs1.Close

Close #1
Set db = Nothing

End Sub
Hi -
Click to expand...
[quoted text clipped - 72 lines]
Click to expand...
 
You have a double quote in the wrong place in the SQL = statement.

Instead of .....where CUSTOMER_PO = ' & " _
You should have .....where CUSTOMER_PO = '" & _

You did not get an error because the SQL string was valid (i.e. syntax
was OK), but wrong.

John


Part of this is working. It writes the rs1 data to the text file, but it
doesn't write the rs2 data. It appears to loop past it and it doesn't fail.
Not sure why..

See below code.

Matt

CODE>>>>>>>>>>>>>>>>>>

Private Sub exp850_Click()

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Dim SQL As String

Set db = CurrentDb()
Open "c:\Files\exp850.txt" For Output As #1
Set rs1 = db.OpenRecordset("850_OrderRecord")

rs1.MoveFirst
'
' Loop through the orders
'
While Not rs1.EOF
'
' Write the data for this order
'
Write #1, rs1![R_TYPE], rs1![CUSTOMER_PO], rs1![ORDER_DATE]
'
' Get the details for this order
'
SQL = "Select * from 850_DetailRecord where CUSTOMER_PO = ' & " _
& rs1![CUSTOMER_PO] & "'"

Set rs2 = db.OpenRecordset(SQL)

While Not rs2.EOF
'
' Write the data for this order details record
'
Write #1, rs2![R_TYPE], rs2![CUSTOMER_ID], rs2![CUSTOMER_PO]

'Write #1, rs2
rs2.MoveNext
Wend
rs2.Close
'
' Move to next order record
'
rs1.MoveNext
Wend
rs1.Close

Close #1
Set db = Nothing

End Sub


J. Goddard said:
Hi -

Given your requirement, then VBA code is probably the best way.
However, I would not put the data into another table - you can write to
the text file directly.

In the first post, you looped through the orders table, and then through
the details table. That won't work - what you have to do is loop
through the orders table, but inside that loop you:

1) write the data for the order to the text file
2) select the details records for that order
3) write the data for each detail line to the text file.

I have done that sort of thing; the biggest headache is to get the
syntax on the output file right. Your code might look something like this:

sub copyOrder()

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

dim SQL as string

Set db = CurrentDb()
open "FileName" for output as #1
Set rs1 = db.OpenRecordset("850_OrderRecord")
rs1.MoveFirst
'
' Loop through the orders
'
While Not rs1.EOF
'
' Write the data for this order
'
write #1, rs1![r_type],nz(rs1![customer_ID],"").... etc
'
' Get the details for this order
'
SQL = "Select * from 850_DetailRecord where CUstomer_ID = ' &
rs1![customer_ID] & "'"
Set rs2 = db.OpenRecordset(SQL)
while not rs2.EOF
'
' Write the data for this order details record
'
Write #1,rs2![r_type],nz(rs2![customer_ID],"").... etc
rs2.movenext
wend
rs2.close
'
' Move to next order record
'
rs1.movenext
wend
rs1.close
close #1
set db=nothing
End sub

The use of the VBA Write statement make coding simpler - it puts in
quotes and commas for you on the text file. Check the VBA help entry
for Write#. Replace "Filename" with the name of the text file,
including full path if needed.

Hope this helps - happy programming!
(Trick or treat?)

John

I open to any help. I have been trying to figure out the best way to handle
this... The trouble is it must be formatted with the Order header, then the
Click to expand...

[quoted text clipped - 14 lines]
John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
Hi -

It would depend on what the data was. If the third record set was a
further breakdown of the order details (i.e. for each rs2 record there
are several rs3 records), then it would go inside the rs2 While ... Wend
loop. If the rs3 data is dependent on the customer order data (i.e. for
each rs1 record there are several rs3 records), then the rs3 loop goes
inside the rs1 loop, but outside the rs2 loop.

For the first situation:

While not rs1.eof
while not rs2.eof
while not rs3.eof
wend
wend
wend

and for the second:

While not rs1.eof
while not rs2.eof
wend

while not rs3.eof
wend
wend

Can you see the difference?

John


I got it to work. It was a syntax issue with the SQL statment.

SQL = "Select * From 850_DetailRecord Where CUSTOMER_PO = '" & rs1!
[CUSTOMER_PO] & " '"

If I need to add a 3rd record set that also needed to be grouped with the
same Customer order. Were would I place the While statement? Would it go
inside the rs2 statement?

Thanks - So far this is working great....



Part of this is working. It writes the rs1 data to the text file, but it
doesn't write the rs2 data. It appears to loop past it and it doesn't fail.
Not sure why..

See below code.

Matt

CODE>>>>>>>>>>>>>>>>>>

Private Sub exp850_Click()

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Dim SQL As String

Set db = CurrentDb()
Open "c:\Files\exp850.txt" For Output As #1
Set rs1 = db.OpenRecordset("850_OrderRecord")

rs1.MoveFirst
'
' Loop through the orders
'
While Not rs1.EOF
'
' Write the data for this order
'
Write #1, rs1![R_TYPE], rs1![CUSTOMER_PO], rs1![ORDER_DATE]
'
' Get the details for this order
'
SQL = "Select * from 850_DetailRecord where CUSTOMER_PO = ' & " _
& rs1![CUSTOMER_PO] & "'"

Set rs2 = db.OpenRecordset(SQL)

While Not rs2.EOF
'
' Write the data for this order details record
'
Write #1, rs2![R_TYPE], rs2![CUSTOMER_ID], rs2![CUSTOMER_PO]

'Write #1, rs2
rs2.MoveNext
Wend
rs2.Close
'
' Move to next order record
'
rs1.MoveNext
Wend
rs1.Close

Close #1
Set db = Nothing

End Sub

Hi -
Click to expand...

[quoted text clipped - 72 lines]
John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
I got it to work.. Thank you so much for all your help...

If you do this for a living send me an email with your contact info to: mattc
[at] saunatec [dot] com

J. Goddard said:
Hi -

It would depend on what the data was. If the third record set was a
further breakdown of the order details (i.e. for each rs2 record there
are several rs3 records), then it would go inside the rs2 While ... Wend
loop. If the rs3 data is dependent on the customer order data (i.e. for
each rs1 record there are several rs3 records), then the rs3 loop goes
inside the rs1 loop, but outside the rs2 loop.

For the first situation:

While not rs1.eof
while not rs2.eof
while not rs3.eof
wend
wend
wend

and for the second:

While not rs1.eof
while not rs2.eof
wend

while not rs3.eof
wend
wend

Can you see the difference?

John
I got it to work. It was a syntax issue with the SQL statment.
Click to expand...
[quoted text clipped - 73 lines]
Click to expand...
 
Back
Top