Else without IF Help

[mattc66 via AccessMonster.com]

When I run the below code. I get a message at the ELSE that says there is no
IF. But there is an IF.

Any ideas why this is saying that?

If rs!Item = rs1!Item Then 'are they the same?
rs.MoveFirst
With rs
Do While Not rs.EOF
rs.Edit
rs![ListPrice] = Nz(rs1![ListPrice], "")
rs![AvgCost] = Nz(rs1![Avg_Cost], "")
rs![AR_CODE] = Nz(rs1![AR_CODE], "")
rs.Update
rs.MoveNext
'rs1.MoveNext
Else
rs1.MoveNext 'if not move to next record for rs1
End If
Loop
End With

 

[kingston via AccessMonster.com]

Nest the loops completely within one another:

If... Then
Do While
...
Loop
End If

When Loop is encountered the second time, End If has already been matched to
If. So, yes, the End If doesn't have a matching If.

When I run the below code. I get a message at the ELSE that says there is no
IF. But there is an IF.

Any ideas why this is saying that?

If rs!Item = rs1!Item Then 'are they the same?
rs.MoveFirst
With rs
Do While Not rs.EOF
rs.Edit
rs![ListPrice] = Nz(rs1![ListPrice], "")
rs![AvgCost] = Nz(rs1![Avg_Cost], "")
rs![AR_CODE] = Nz(rs1![AR_CODE], "")
rs.Update
rs.MoveNext
'rs1.MoveNext
Else
rs1.MoveNext 'if not move to next record for rs1
End If
Loop
End With

 

[Duane Hookom]

Your program flow is not nested properly.

If
With
Do While
Else
End If
Loop
End With

I don't want to guess what you want.

 

[Douglas J. Steele]

It's because the Loop statement that closes the Do While statement and the
End With statement that closes the With rs statement are both after the Else
statement. Try:

If rs!Item = rs1!Item Then 'are they the same?
rs.MoveFirst
With rs
Do While Not .EOF
.Edit
![ListPrice] = Nz(rs1![ListPrice], "")
![AvgCost] = Nz(rs1![Avg_Cost], "")
![AR_CODE] = Nz(rs1![AR_CODE], "")
.Update
.MoveNext
Loop
End With
Else
rs1.MoveNext 'if not move to next record for rs1
End If

Note the changes I made to take advantage of your With rs statement.

However, are you sure you need the looping? Wouldn't a simple UPDATE query
do the same?

UPDATE Table1 INNER JON Table2
ON Table1.Item = Table2.Item
SET ListPrice = Nz([Table2].[ListPrice], ""),
AvgCost = Nz([Table2].[Avg_Cost], ""),
AR_CODE = Nz([Table2].[AR_CODE], "")

Also, are your price and cost fields actually text? If they're numeric (as
I'd expect), you should be using 0, not "", in the Nz function calls.

 

[Klatuu]

I can't determine what you are trying to do by reading your code; however,
the root of the problem is that the Else inside the Do Loop. Both the If and
the End If need to be either inside the loop or outside the loop.

 

[mattc66 via AccessMonster.com]

When using an update query can you have items in one table1 without a match
in table2?

It's because the Loop statement that closes the Do While statement and the
End With statement that closes the With rs statement are both after the Else
statement. Try:

If rs!Item = rs1!Item Then 'are they the same?
rs.MoveFirst
With rs
Do While Not .EOF
.Edit
![ListPrice] = Nz(rs1![ListPrice], "")
![AvgCost] = Nz(rs1![Avg_Cost], "")
![AR_CODE] = Nz(rs1![AR_CODE], "")
.Update
.MoveNext
Loop
End With
Else
rs1.MoveNext 'if not move to next record for rs1
End If

Note the changes I made to take advantage of your With rs statement.

However, are you sure you need the looping? Wouldn't a simple UPDATE query
do the same?

UPDATE Table1 INNER JON Table2
ON Table1.Item = Table2.Item
SET ListPrice = Nz([Table2].[ListPrice], ""),
AvgCost = Nz([Table2].[Avg_Cost], ""),
AR_CODE = Nz([Table2].[AR_CODE], "")

Also, are your price and cost fields actually text? If they're numeric (as
I'd expect), you should be using 0, not "", in the Nz function calls.

When I run the below code. I get a message at the ELSE that says there is
no

[quoted text clipped - 18 lines]

Loop
End With

 

[Douglas J. Steele]

Yes. Only the ones that match will be updated.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When using an update query can you have items in one table1 without a
match
in table2?

It's because the Loop statement that closes the Do While statement and the
End With statement that closes the With rs statement are both after the
Else
statement. Try:

If rs!Item = rs1!Item Then 'are they the same?
rs.MoveFirst
With rs
Do While Not .EOF
.Edit
![ListPrice] = Nz(rs1![ListPrice], "")
![AvgCost] = Nz(rs1![Avg_Cost], "")
![AR_CODE] = Nz(rs1![AR_CODE], "")
.Update
.MoveNext
Loop
End With
Else
rs1.MoveNext 'if not move to next record for rs1
End If

Note the changes I made to take advantage of your With rs statement.

However, are you sure you need the looping? Wouldn't a simple UPDATE query
do the same?

UPDATE Table1 INNER JON Table2
ON Table1.Item = Table2.Item
SET ListPrice = Nz([Table2].[ListPrice], ""),
AvgCost = Nz([Table2].[Avg_Cost], ""),
AR_CODE = Nz([Table2].[AR_CODE], "")

Also, are your price and cost fields actually text? If they're numeric (as
I'd expect), you should be using 0, not "", in the Nz function calls.

When I run the below code. I get a message at the ELSE that says there
is
no

[quoted text clipped - 18 lines]

Loop
End With

 

[mattc66 via AccessMonster.com]

If I only want the update query to be applied to the current Order how would
I control it? From your suggestion it would update all records in the table1
that match the item#.

Yes. Only the ones that match will be updated.

When using an update query can you have items in one table1 without a
match

[quoted text clipped - 41 lines]

 

[John Vinson]

When I run the below code. I get a message at the ELSE that says there is no
IF. But there is an IF.

Any ideas why this is saying that?

If rs!Item = rs1!Item Then 'are they the same?
rs.MoveFirst
With rs
Do While Not rs.EOF
rs.Edit
rs![ListPrice] = Nz(rs1![ListPrice], "")
rs![AvgCost] = Nz(rs1![Avg_Cost], "")
rs![AR_CODE] = Nz(rs1![AR_CODE], "")
rs.Update
rs.MoveNext
'rs1.MoveNext
Else
rs1.MoveNext 'if not move to next record for rs1
End If
Loop
End With

I think that the problem is that your DO loop, and your WITH, both
overlap the IF - they start inside the If... End If but end outside
it. Try moving the End If down two lines.

John W. Vinson[MVP]

 

[mattc66 via AccessMonster.com]

The blow works actually correct, EXCEPT when it hits an Item that doesn't
match in the rs1 recordset. It stops and the message is no current record. I
want it to not stop, but rather end.

All the other items in the set are updated correctly. Any suggestions?

rs.MoveFirst
With rs
Do While Not rs.EOF
If rs!Item = rs1!Item Then 'are they the same?
rs.Edit
rs![ListPrice] = Nz(rs1![ListPrice], "")
rs![AvgCost] = Nz(rs1![Avg_Cost], "")
rs![AR_CODE] = Nz(rs1![AR_CODE], "")
rs.Update
rs.MoveNext
Else
rs1.MoveNext 'if not move to next record for rs1
End If
Loop
End With

It's because the Loop statement that closes the Do While statement and the
End With statement that closes the With rs statement are both after the Else
statement. Try:

If rs!Item = rs1!Item Then 'are they the same?
rs.MoveFirst
With rs
Do While Not .EOF
.Edit
![ListPrice] = Nz(rs1![ListPrice], "")
![AvgCost] = Nz(rs1![Avg_Cost], "")
![AR_CODE] = Nz(rs1![AR_CODE], "")
.Update
.MoveNext
Loop
End With
Else
rs1.MoveNext 'if not move to next record for rs1
End If

Note the changes I made to take advantage of your With rs statement.

However, are you sure you need the looping? Wouldn't a simple UPDATE query
do the same?

UPDATE Table1 INNER JON Table2
ON Table1.Item = Table2.Item
SET ListPrice = Nz([Table2].[ListPrice], ""),
AvgCost = Nz([Table2].[Avg_Cost], ""),
AR_CODE = Nz([Table2].[AR_CODE], "")

Also, are your price and cost fields actually text? If they're numeric (as
I'd expect), you should be using 0, not "", in the Nz function calls.

When I run the below code. I get a message at the ELSE that says there is
no

[quoted text clipped - 18 lines]

Loop
End With

 

[mattc66 via AccessMonster.com]

The below code works great until you get to a record that doesn't match the
IF statement. It stops and doesn't move to the next rs.record. Once its gets
to the end of the RS1 records set and it doesn't find a match to then move to
the next rs record. Once its gone through all the rs records to then end.

In looking at the below code what am I missing in order to do this?

rs.MoveFirst
rs1.MoveFirst
With rs
Do While Not rs1.EOF
If rs!Item = rs1!Item Then 'are they the same?
rs.Edit
rs![ListPrice] = Nz(rs1![ListPrice], 0)
rs![AvgCost] = Nz(rs1![Avg_Cost], 0)
rs![AR_CODE] = Nz(rs1![AR_CODE], "")
rs.Update
rs.MoveNext
rs1.MoveNext
Else
rs1.MoveNext 'if not move to next record for rs1
End If
Loop

End With

I can't determine what you are trying to do by reading your code; however,
the root of the problem is that the Else inside the Do Loop. Both the If and
the End If need to be either inside the loop or outside the loop.

When I run the below code. I get a message at the ELSE that says there is no
IF. But there is an IF.

[quoted text clipped - 17 lines]

Loop
End With

 

[Douglas J. Steele]

How do you identify the current order?

It would probably help if you explained what you're trying to do: if wasn't
obvious from your code.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

If I only want the update query to be applied to the current Order how
would
I control it? From your suggestion it would update all records in the
table1
that match the item#.

Yes. Only the ones that match will be updated.

When using an update query can you have items in one table1 without a
match

[quoted text clipped - 41 lines]

Loop
End With

 

[mattc66 via AccessMonster.com]

Identified by an OrderID for the main form and OrderDetID for the detail
subform.

This is a Quoting/Order input tool. I am updating the List Price and Avg Cost
from our ERP system.

There are items on the quote that are misc item that don't have a match in
the ERP part list tables. Otherwise I'd just link it via a query.

Matt

How do you identify the current order?

It would probably help if you explained what you're trying to do: if wasn't
obvious from your code.

If I only want the update query to be applied to the current Order how
would

[quoted text clipped - 9 lines]