recordset problem

[Mike Johnson]

I providing the entire sub in case it's needed.

I've identified the problem within the code as remarks. Thanks

Public Sub Process_Monthly()
On Error GoTo Err_Ok_Bttn_Click

Dim dbsCurrent As DAO.Database
Dim rstRecords As DAO.Recordset
Dim dbsCurrent1 As DAO.Database
Dim rstRecords1 As DAO.Recordset
Dim dbsCurrent2 As DAO.Database
Dim rstRecords2 As DAO.Recordset

'Step 1. Add Qty_pd_to_dt to Qty_Instl_to_dt in Master_table
Set dbsCurrent = CurrentDb()
Set rstRecords = dbsCurrent.OpenRecordset("Master_Items_T")

If rstRecords.RecordCount <> 0 Then
rstRecords.MoveFirst
rstRecords.Bookmark = rstRecords.LastModified
End If

Do While Not rstRecords.EOF

rstRecords.Edit
rstRecords!QTY_INSTL_TO_DT = rstRecords!QTY_PD_TO_DT +
rstRecords!QTY_INSTL_TO_DT
rstRecords.Update
rstRecords.MoveNext

Loop

rstRecords.Close
dbsCurrent.Close

'Step 2. Add all Work Order Qty_pd_to_dt to Master_table
'Will need to check for match before updating item
Set dbsCurrent = CurrentDb()
Set rstRecords = dbsCurrent.OpenRecordset("WrkOdr_Names_T")
Set dbsCurrent2 = CurrentDb()
Set rstRecords2 = dbsCurrent.OpenRecordset("Master_Items_T")

If rstRecords.RecordCount <> 0 Then
rstRecords.MoveFirst
rstRecords.Bookmark = rstRecords.LastModified
End If
If rstRecords2.RecordCount <> 0 Then
rstRecords2.MoveFirst
rstRecords.Bookmark = rstRecords2.LastModified
End If
Do While Not rstRecords.EOF

'step through work orders
Set dbsCurrent1 = CurrentDb()
Set rstRecords1 =
dbsCurrent1.OpenRecordset(rstRecords!Work_Order_Name)
If rstRecords1.RecordCount <> 0 Then
rstRecords1.MoveFirst
rstRecords1.Bookmark = rstRecords1.LastModified
End If

Do While Not rstRecords.EOF
'
'Problem:
'When Work_Order_Name changes the rstRecords1
'needs to be updated to reflect the new record source
'not sure how to do this.
'
'I've tried adding the following code after the do while below.
'Set rstRecords1 =
dbsCurrent1.OpenRecordset(rstRecords!Work_Order_Name)
'If rstRecords1.RecordCount <> 0 Then
'rstRecords1.MoveFirst
'rstRecords1.Bookmark = rstRecords1.LastModified
'End If
'This seems to resolve the issue but for some reason will not
'move to the first record. When called. Label move first below
'
'Add to Qty Installed to Date to Qty Paid to Date
Do While Not rstRecords1.EOF
If rstRecords1!ITM_CD = rstRecords2!ITM_CD Then
rstRecords2.Edit
rstRecords2!QTY_INSTL_TO_DT =
rstRecords1!QTY_INSTL_TO_DT + rstRecords1!QTY_PD_TO_DT
rstRecords2.Update
rstRecords1.MoveNext
Else
rstRecords2.MoveNext
End If
Loop
rstRecords.MoveNext
rstRecords1.MoveLast 'Move first
rstRecords2.MoveFirst
Loop

Loop

rstRecords.Close
dbsCurrent.Close
rstRecords1.Close
dbsCurrent1.Close
'rstRecords2.Close
'dbsCurrent2.Close

Exit_Ok_Bttn_Click:
Exit Sub

Err_Ok_Bttn_Click:
MsgBox Err.Description
Resume Exit_Ok_Bttn_Click

End Sub

 

[Douglas J Steele]

Unless rstRecords!Work_Order_Name happens to return a valid SQL string (or
you have the extremely undesirable situation where each Work_Order_Name
corresponds to a separate table in your application),
dbsCurrent1.OpenRecordset(rstRecords!Work_Order_Name) is invalid.

Perhaps you can explain in words what you're trying to do.

For what it's worth, if you're trying to change values on multiple rows in a
table, it's almost always better to use an UPDATE SQL statement, as opposed
to looping through a recordset.

 

[Mike Johnson]

Ok, Here goes.

Let's say I have two tables. Names unknown. I get the names of the tables
from a table called WrkOrd_table_names. I need to take the values from these
tables and add them to another table called Master_t. So now Master_t
contains the sum of the two tables.

I was thinking about an update query but had no idea how to supply the name
of the tables. Perhaps I can use a variation of what I'm doing now. Step
through the WrkOrd_table_Names table and run the update Query for each record
found in the table. I will look into this. If you have any ideas please let
me know. Thanks

 

[Douglas J Steele]

Maybe you can give a small, concrete example.

 

[Mike Johnson]

Do you mean samples of the tables? If so How would I attach them?

 

[Douglas J Steele]

An example in words, with a few representative rows of data.

 

[Douglas J Steele]

Should also have mentioned: make sure you list the names of the fields in
the appropriate tables (and what data type they are if it's not obvious from
your example)

 

[Mike Johnson]

Ok, Here Goes.

Work_Order_Name, TEXT Field
WO#1
WO#2

Items can added to the table above. Meaing I will need to get the name of
the tables I'm working with from the Work_Order_Name table.

Table One Name: WO#1

ITM_CD IS A TEXT FILED.

ITM_CD QTY_PD_TO_DT DESC1
0102 1 0.02 MAINTENANCE OF TRAFFIC
0102 60 12 WORK ZONE SIGNS
0102 74 0 BARRICADE (TEMPORARY)(TYPES I,II,VP & DRUM)

Table Two Name: WO#2

ITM_CD QTY_PD_TO_DT DESC1
0102 1 0.04 MAINTENANCE OF TRAFFIC
0102 60 10 WORK ZONE SIGNS
0102 74 3 BARRICADE (TEMPORARY)(TYPES I,II,VP & DRUM)

Table Three Name: Master_Table

I need to sum the fields from the first two tables to get the following
results. The only filed that will change is the QTY_PD_TO_DT

ITM_CD QTY_PD_TO_DT DESC1
0102 1 0.06 MAINTENANCE OF TRAFFIC
0102 60 22 WORK ZONE SIGNS
0102 74 3 BARRICADE (TEMPORARY)(TYPES I,II,VP & DRUM)

Hope this is what you needed.

 

[Douglas J. Steele]

The problem would appear to be that you've got a separate table for each
work order. That's definitely not a good thing.

Instead, have a single work order table, and add an additional column to it
indicating which work order is which.

Once you've done that, it should be pretty simple to add together details
from specific work orders by creating a Totals query with the appropriate
WHERE clause.

 

[Mike Johnson]

Thanks Douglas, I will redesign the Data base with one Work Order Table.
Can't believe I didn’t think of that. Again thanks