Solved

Loopy Looping???

Posted on 2003-03-10
Medium Priority
275 Views
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.

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

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

!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
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 Function

MsgBox "Failed at getting data"

MsgBox "Successful"
End Function

Cracky141
aka Paul Jones
0
Question by:cracky141
• 7
• 7
• 3
• +2

LVL 6

Expert Comment

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

0

Author Comment

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

LVL 1

Expert Comment

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

LVL 1

Expert Comment

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

ID: 8109414
Sorry,I'm a little confused.
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

ID: 8109449
Try this:

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

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

!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
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 Function

MsgBox "Failed at getting data"

MsgBox "Successful"
End Function
0

Author Comment

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

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

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

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

ID: 8109786
Hi cracky141,

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

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

HTH,

Nosterdamus
0

LVL 7

Expert Comment

ID: 8109796
Sorry stiemark,

Nosterdamus
0

Author Comment

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

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

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

!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
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

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

Author Comment

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

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

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

LVL 7

Expert Comment

ID: 8110079
Hi cracky141,

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

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with â€¦
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient â€¦
Suggested Courses
Course of the Month7 days, 23 hours left to enroll