?
Solved

Loopy Looping???

Posted on 2003-03-10
20
Medium Priority
?
264 Views
Last Modified: 2009-12-16
I posted earlier and recieved what I throught was an appropriate answer.
Well from my own inexperience I incorectly accepted the answer.
My problem is that I get the right results for the first record,but then I can no longer update the table as I get duplicate data from my function. It is obviousley going back to the first record and trying to reappend it. I want it to go to the next record in rst,loop through all records in rst and append one record into rst1 for each record that was in rst.
Here is my code.Function AddWorkInProgress3(JobNumber)
 
Dim Criteria As String, StrMsg As String
Dim Db As Database
Dim rst As Recordset
Dim rst1 As Recordset
Dim fld As Variant
 
On Error GoTo Err_AddWorkInProgress3
AddWorkInProgress3 = False
 
Set rst = CurrentDb.OpenRecordset("SELECT * FROM ScheduledWork ,WHERE JobNumber = """ & JobNumber & """")
Set rst1 = CurrentDb.OpenRecordset("ScheduleNumber", DB_OPEN_TABLE)
Criteria = "[JobNumber] = """ + JobNumber + """"
If Not rst.EOF Then
       
   Do
         
          rst.MoveLast ''''''''Populate Recordset
          JobNumber = Criteria ''''''''Set Criteria
          rst.FindFirst Criteria ''''''''Find 1st criteria that matches
          For Each fld In rst ''''''''For each field in rst loop
          If Not rst.NoMatch Then ''''''''If A match has been found
          With rst1 ''''''''Use rst1 as update table
          .AddNew ''''''''Add new record
         
          !JobNumber = rst!JobNumber
          !DelivDate = rst!DateRequired
          !ProdDate = rst!ProdDate
          If IsNumeric(rst!Cut) And Val(rst!Cut) > 0 Then ''''''''If has a numeric value greater than zero
             !CenterNo = 12
             StrMsg = rst!Cut
             !OPS = StrMsg
          ElseIf IsNumeric(rst!Edge) And Val(rst!Edge) > 0 Then
             !CenterNo = 11 And StrMsg = rst!Edge
             !OPS = StrMsg
          ElseIf IsNumeric(rst!Bore) And Val(rst!Bore) > 0 Then
             !CenterNo = 11 And StrMsg = rst!Bore
             !OPS = StrMsg
          ElseIf IsNumeric(rst!Spin) And Val(rst!Spin) > 0 Then
             !CenterNo = 11 And StrMsg = rst!Spin
             !OPS = StrMsg
          ElseIf IsNumeric(rst!Rout) And Val(rst!Rout) > 0 Then
             !CenterNo = 11 And StrMsg = rst!Rout
             !OPS = StrMsg
          ElseIf IsNumeric(rst!Sand) And Val(rst!Sand) > 0 Then
             !CenterNo = 11 And StrMsg = rst!Sand
             !OPS = StrMsg
          ElseIf IsNumeric(rst!Press) And Val(rst!Press) > 0 Then
             !CenterNo = 11 And StrMsg = rst!Press
             !OPS = StrMsg
          ElseIf IsNumeric(rst!Lac) And Val(rst!Lac) > 0 Then
             !CenterNo = 11 And StrMsg = rst!Lac
             !OPS = StrMsg
          ElseIf IsNumeric(rst!T_Mould) And Val(rst!T_Mould) > 0 Then
             !CenterNo = 11 And StrMsg = rst!T_Mould
             !OPS = StrMsg
          ElseIf IsNumeric(rst!ASSEM_COMM) And Val(rst!ASSEM_COMM) > 0 Then
             !CenterNo = 11 And StrMsg = rst!ASSEM_COMM
             !OPS = StrMsg
          End If
          .Update ''''''''Add record to table
          End With
          End If
          Next ''''''''Go back and do the next record
          Loop Until rst.EOF ''''''''Loop through all records until end of file
         
         
End If
rst.Close
Set rst = Nothing
rst1.Close
Set rst1 = Nothing
 
Exit_AddWorkInProgress3:
    Exit Function
   
Err_AddWorkInProgress3:
    MsgBox "Failed at getting data"
    Resume Exit_AddWorkInProgress3
   
 MsgBox "Successful"
End Function


Thanks in advance

Cracky141
aka Paul Jones
0
Comment
Question by:cracky141
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 3
  • +2
20 Comments
 
LVL 6

Expert Comment

by:Neadom Tucker
ID: 8109078
I am not understanding your question.  Are you adding records and then sending them somewhere?

0
 

Author Comment

by:cracky141
ID: 8109090
I am merley trying to append data from rst to rst1.
The table are not related.
Tah
0
 
LVL 1

Expert Comment

by:njelger
ID: 8109234
Why don't you do the loop like this?

rst.MoveFirst
Do Until rst.EOF



rst.MoveNext  ''''''''Go back and do the next record
Loop          ''''''''Loop through all records until end of file


I don't think this line is needed:

rst.MoveLast ''''''''Populate Recordset

/ j
0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 1

Expert Comment

by:njelger
ID: 8109253
If I understand this correctly you want to add a record to rst1 for each record in rst that has:

JobNumber = """ & JobNumber & """"

But since you have the criteria in the query you don't need to match!!!

Set rst = CurrentDb.OpenRecordset("SELECT * FROM ScheduledWork ,WHERE JobNumber = """ & JobNumber & """")

Criteria = "[JobNumber] = """ + JobNumber + """"

THIS PART CAN BE DELETED TOO:

JobNumber = Criteria ''''''''Set Criteria
rst.FindFirst Criteria ''''''''Find 1st criteria that matches
For Each fld In rst ''''''''For each field in rst loop
if Not rst.NoMatch Then ''''''''If A match has been found
With rst1 ''''''''Use rst1 as update table

/j
0
 

Author Comment

by:cracky141
ID: 8109414
Sorry,I'm a little confused.
From your second reply-what should I be deleting?
I deleted the bottom bit and combined the top with your first reply. I get an error with the from clause in the statement. No typos.

0
 
LVL 2

Expert Comment

by:stiemark
ID: 8109449
Try this:


Function AddWorkInProgress3(JobNumber)

Dim Criteria As String, StrMsg As String
Dim Db As Database
Dim rst As Recordset
Dim rst1 As Recordset
Dim fld As Variant

On Error GoTo Err_AddWorkInProgress3
AddWorkInProgress3 = False

Set rst = CurrentDb.OpenRecordset("SELECT * FROM ScheduledWork ,WHERE JobNumber = """ & JobNumber & """")
Set rst1 = CurrentDb.OpenRecordset("ScheduleNumber", DB_OPEN_TABLE)
Criteria = "[JobNumber] = """ + JobNumber + """"
       
Do While not rst.EOF
       
         JobNumber = Criteria ''''''''Set Criteria
         rst.FindFirst Criteria ''''''''Find 1st criteria that matches
         For Each fld In rst ''''''''For each field in rst loop
         If Not rst.NoMatch Then ''''''''If A match has been found
         With rst1 ''''''''Use rst1 as update table
         .AddNew ''''''''Add new record
         
         !JobNumber = rst!JobNumber
         !DelivDate = rst!DateRequired
         !ProdDate = rst!ProdDate
         If IsNumeric(rst!Cut) And Val(rst!Cut) > 0 Then ''''''''If has a numeric value greater than zero
            !CenterNo = 12
            StrMsg = rst!Cut
            !OPS = StrMsg
         ElseIf IsNumeric(rst!Edge) And Val(rst!Edge) > 0 Then
            !CenterNo = 11 And StrMsg = rst!Edge
            !OPS = StrMsg
         ElseIf IsNumeric(rst!Bore) And Val(rst!Bore) > 0 Then
            !CenterNo = 11 And StrMsg = rst!Bore
            !OPS = StrMsg
         ElseIf IsNumeric(rst!Spin) And Val(rst!Spin) > 0 Then
            !CenterNo = 11 And StrMsg = rst!Spin
            !OPS = StrMsg
         ElseIf IsNumeric(rst!Rout) And Val(rst!Rout) > 0 Then
            !CenterNo = 11 And StrMsg = rst!Rout
            !OPS = StrMsg
         ElseIf IsNumeric(rst!Sand) And Val(rst!Sand) > 0 Then
            !CenterNo = 11 And StrMsg = rst!Sand
            !OPS = StrMsg
         ElseIf IsNumeric(rst!Press) And Val(rst!Press) > 0 Then
            !CenterNo = 11 And StrMsg = rst!Press
            !OPS = StrMsg
         ElseIf IsNumeric(rst!Lac) And Val(rst!Lac) > 0 Then
            !CenterNo = 11 And StrMsg = rst!Lac
            !OPS = StrMsg
         ElseIf IsNumeric(rst!T_Mould) And Val(rst!T_Mould) > 0 Then
            !CenterNo = 11 And StrMsg = rst!T_Mould
            !OPS = StrMsg
         ElseIf IsNumeric(rst!ASSEM_COMM) And Val(rst!ASSEM_COMM) > 0 Then
            !CenterNo = 11 And StrMsg = rst!ASSEM_COMM
            !OPS = StrMsg
         End If
         .Update ''''''''Add record to table
         End With
         End If
         Next ''''''''Go back and do the next record
         rst.MoveNext ' Move to next record to process
Loop ''''Loop through all records until end of file

rst.Close
Set rst = Nothing
rst1.Close
Set rst1 = Nothing

Exit_AddWorkInProgress3:
   Exit Function
   
Err_AddWorkInProgress3:
   MsgBox "Failed at getting data"
   Resume Exit_AddWorkInProgress3
   
MsgBox "Successful"
End Function
0
 

Author Comment

by:cracky141
ID: 8109585
I really appreciate your thoughts and trouble you have taken to help me.
I'm afraid however that I'm still getting an error in the from clause. I have triple checked the table and field names.
Could the criteria be found another way, or can we eliminate the criteria and just loop each record anyway??
0
 
LVL 1

Expert Comment

by:njelger
ID: 8109644
Ok thois doesnt look right.

"SELECT * FROM ScheduledWork ,WHERE JobNumber = """ & JobNumber & """"

first the comma has to go.

"SELECT * FROM ScheduledWork WHERE JobNumber = """ & JobNumber & """"

Then what is """ & JobNumber & """" ?

I've never seen such a setting before.
what are you trying to do?


i just looked at the loops before.

/ j
0
 
LVL 2

Expert Comment

by:stiemark
ID: 8109647
Change:
Set rst = CurrentDb.OpenRecordset("SELECT * FROM ScheduledWork ,WHERE JobNumber = """ & JobNumber & """")

to
Set rst = CurrentDb.OpenRecordset("SELECT * FROM ScheduledWork WHERE JobNumber = """ & JobNumber & """")
0
 
LVL 1

Expert Comment

by:njelger
ID: 8109648
if you just ned to loop all records in the scheduledwork table then just use:

CurrentDb.OpenRecordset("ScheduledWork",2)
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 8109786
Hi cracky141,

You should remove the comma from:
Set rst = CurrentDb.OpenRecordset("SELECT * FROM ScheduledWork ,WHERE JobNumber = """ & JobNumber & """")

so its should read:
Set rst = CurrentDb.OpenRecordset("SELECT * FROM ScheduledWork WHERE JobNumber = """ & JobNumber & """")


HTH,

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 8109796
Sorry stiemark,

didn't see your comment ;-)

Nosterdamus
0
 

Author Comment

by:cracky141
ID: 8109801
Thank you both.
I'm upping the ante to 500 points.
I recieve this error: Operation is not supported for this type of object:error 3251

Oh BTW
I Took out the comma,and used CurrentDb.OpenRecordset("ScheduledWork",2)
also. Neither method worked.

0
 

Author Comment

by:cracky141
ID: 8109821
Oh , I forgot to mention where it breaks code.
For Each fld In rst ''''''''For each field in rst loop
       
My apologies
0
 
LVL 1

Accepted Solution

by:
njelger earned 2000 total points
ID: 8109879
First: Nosterdamus thanks for noticing.. :D

Secondf try this: plus all code around it.
 
Set rst = CurrentDb.OpenRecordset("ScheduledWork",2)
Set rst1 = CurrentDb.OpenRecordset("ScheduleNumber", DB_OPEN_TABLE)

rst.MoveFirst
Do Until rst.EOF

With rst1 ''''''''Use rst1 as update table
        .AddNew ''''''''Add new record
       
        !JobNumber = rst!JobNumber
        !DelivDate = rst!DateRequired
        !ProdDate = rst!ProdDate
        If IsNumeric(rst!Cut) And Val(rst!Cut) > 0 Then ''''''''If has a numeric value greater than zero
           !CenterNo = 12
           StrMsg = rst!Cut
           !OPS = StrMsg
        ElseIf IsNumeric(rst!Edge) And Val(rst!Edge) > 0 Then
           !CenterNo = 11 And StrMsg = rst!Edge
           !OPS = StrMsg
        ElseIf IsNumeric(rst!Bore) And Val(rst!Bore) > 0 Then
           !CenterNo = 11 And StrMsg = rst!Bore
           !OPS = StrMsg
        ElseIf IsNumeric(rst!Spin) And Val(rst!Spin) > 0 Then
           !CenterNo = 11 And StrMsg = rst!Spin
           !OPS = StrMsg
        ElseIf IsNumeric(rst!Rout) And Val(rst!Rout) > 0 Then
           !CenterNo = 11 And StrMsg = rst!Rout
           !OPS = StrMsg
        ElseIf IsNumeric(rst!Sand) And Val(rst!Sand) > 0 Then
           !CenterNo = 11 And StrMsg = rst!Sand
           !OPS = StrMsg
        ElseIf IsNumeric(rst!Press) And Val(rst!Press) > 0 Then
           !CenterNo = 11 And StrMsg = rst!Press
           !OPS = StrMsg
        ElseIf IsNumeric(rst!Lac) And Val(rst!Lac) > 0 Then
           !CenterNo = 11 And StrMsg = rst!Lac
           !OPS = StrMsg
        ElseIf IsNumeric(rst!T_Mould) And Val(rst!T_Mould) > 0 Then
           !CenterNo = 11 And StrMsg = rst!T_Mould
           !OPS = StrMsg
        ElseIf IsNumeric(rst!ASSEM_COMM) And Val(rst!ASSEM_COMM) > 0 Then
           !CenterNo = 11 And StrMsg = rst!ASSEM_COMM
           !OPS = StrMsg
        End If
        .Update ''''''''Add record to table
        End With
        End If

rst.MoveNext  ''''''''Go back and do the next record
Loop          ''''''''Loop through all records until end of file

0
 
LVL 1

Expert Comment

by:njelger
ID: 8109897
that is what i tried to tell you before.... in the loop description.
0
 

Author Comment

by:cracky141
ID: 8109937
Thank you all very much.
Special thanks to njelger for your help.
Apart from one too many end if's it was working fine until I ran into a null record.
I'll fix that though.

Thanks again
Cracky141
0
 
LVL 1

Expert Comment

by:njelger
ID: 8109950
Apart from one too many end if's...

well, copy-paste is and will be your best friend and your worst enemy.. :)

CU / j
0
 

Author Comment

by:cracky141
ID: 8109969
You are so right,I think I C&Ped once too many also.(:\)
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 8110079
Hi cracky141,

I know that you already accepted expert njelgers' comment as answer, but I already have the following ready, so who cares... :-)

To be honest, I kind of lost with your code:

1. StrMsg
Please notice that StrMsg is set ONLY ONCE, where:
       If IsNumeric(rst!Cut) And Val(rst!Cut) > 0 Then ''''''''If has a numeric value greater than zero
       !CenterNo = 12
       StrMsg = rst!Cut
       !OPS = StrMsg
in all other places in your code, you actualy testing its' content (rst!cut value!) with other rst fields (e.g. rst!Edge, rst!Bore etc.).
It might be that it was your initial intention, but the way it is implemented looks a bit odd to me...

2. rst1!CenterNo
I guess that [CenterNo] is a numeric field defined in table ScheduleNumber. Again, you set it to 12 only in:
       If IsNumeric(rst!Cut) And Val(rst!Cut) > 0 Then ''''''''If has a numeric value greater than zero
       !CenterNo = 12
       StrMsg = rst!Cut
       !OPS = StrMsg
in ALL the other cases, it will be set to 0 (false) because a statement such as:
       !CenterNo = 11 And StrMsg = rst!Edge
should ALWAYS be false! (because 11 AND StrMsg = rst!Edge is a LOGICAL operation rather than a NUMERIC one)

3. Add a record to rst1 with partial information
At the beginning of the loop you .AddNew record to rst1 and update fields:
       !JobNumber = rst!JobNumber
       !DelivDate = rst!DateRequired
       !ProdDate = rst!ProdDate
it is possible that NONE of the IF & ElseIf statements will be true, hence leaving rst1!CenterNo &  rst1!OPS with Null value. Does this situation is acceptable?


I'd separate your question into two parts:

1. Implement the mechanism for updating one table upon other tables' data which is pretty straightforward.

2. Implement the correct logic for update. In this case, I think that it be better if you could explain exactly what conditions you need to meet in order for the update to be implemented.



HTH,

Nosterdamus
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question