Issue with While loop and Access 2007 datasheet

[jermaine123 via AccessMonster.com]

Hello Guys,

I have modified the order details form in Northwind 2007 and I am having an
issue I hope you can help me with. It is a form with a dataaheet that you use
to enter line items into a transaction. Each row contains the fields ITEMNAME,
QUANTITY PRICE, DISCOUNT, SUBTOTAL, BENEFIT TYPE.

Each line item in a transaction should be assigned to a particular benefit
type. I have a piece of code that goes through the data sheet and checks to
make sure that the benefit type field is not empty.

The problem I am having is that if I enter 3 items and dont put a benefit
type for any of them,- When I click the COMPLETE TRANSACTION button I will
get the message box "You need to enter a benefit type".

If I put in the benefit type for one of the three items and click the
COMPLETE TRANSACTION button again, I do not get the error message box again,
instead it closes the transaction.

Each time I click the COMPLETE TRANSACTION button I would like it to go
through each line item and check if it has a benefit type entered and if not
show a mesage box.

Hope my post makes sense. I am sure it is something simple, but I am just not
seeing it.

Thanks

The code is below....

Dim rsw As New RecordsetWrapper
With rsw.GetRecordsetClone(Me.sbfOrderDetails.Form.Recordset)
' Check that we have at least one specified line items
If .RecordCount = 0 Then
MsgBoxOKOnly OrderDoesNotContainLineItems
Else
Dim LineItemCount As Integer
LineItemCount = 0
While Not .EOF
LineItemCount = LineItemCount + 1

If IsNull(Forms![Order Details]![sbfOrderDetails].Form![Product
ID]) Then
MsgBoxOKOnly MustSpecifyItemName
Exit Function

ElseIf IsNull(Forms![Order Details]![sbfOrderDetails].Form!
[Benefit ID]) Then
MsgBoxOKOnly MustSpecifyBenefitType
Exit Function
End If
rsw.MoveNext
Wend

ValidateOrder = True
eh.TryToSaveRecord
Me![Status ID] = Closed_CustomerOrder
MsgBoxOKOnly OrderMarkedClosed
SetFormState


End If
End With

 

[jermaine123 via AccessMonster.com]

Guys - Any idea how to solve this?

Hello Guys,

I have modified the order details form in Northwind 2007 and I am having an
issue I hope you can help me with. It is a form with a dataaheet that you use
to enter line items into a transaction. Each row contains the fields ITEMNAME,
QUANTITY PRICE, DISCOUNT, SUBTOTAL, BENEFIT TYPE.

Each line item in a transaction should be assigned to a particular benefit
type. I have a piece of code that goes through the data sheet and checks to
make sure that the benefit type field is not empty.

The problem I am having is that if I enter 3 items and dont put a benefit
type for any of them,- When I click the COMPLETE TRANSACTION button I will
get the message box "You need to enter a benefit type".

If I put in the benefit type for one of the three items and click the
COMPLETE TRANSACTION button again, I do not get the error message box again,
instead it closes the transaction.

Each time I click the COMPLETE TRANSACTION button I would like it to go
through each line item and check if it has a benefit type entered and if not
show a mesage box.

Hope my post makes sense. I am sure it is something simple, but I am just not
seeing it.

Thanks

The code is below....

Dim rsw As New RecordsetWrapper
With rsw.GetRecordsetClone(Me.sbfOrderDetails.Form.Recordset)
' Check that we have at least one specified line items
If .RecordCount = 0 Then
MsgBoxOKOnly OrderDoesNotContainLineItems
Else
Dim LineItemCount As Integer
LineItemCount = 0
While Not .EOF
LineItemCount = LineItemCount + 1

If IsNull(Forms![Order Details]![sbfOrderDetails].Form![Product
ID]) Then
MsgBoxOKOnly MustSpecifyItemName
Exit Function

ElseIf IsNull(Forms![Order Details]![sbfOrderDetails].Form!
[Benefit ID]) Then
MsgBoxOKOnly MustSpecifyBenefitType
Exit Function
End If
rsw.MoveNext
Wend

ValidateOrder = True
eh.TryToSaveRecord
Me![Status ID] = Closed_CustomerOrder
MsgBoxOKOnly OrderMarkedClosed
SetFormState


End If
End With

 

[Dirk Goldgar]

Hello Guys,

I have modified the order details form in Northwind 2007 and I am having
an
issue I hope you can help me with. It is a form with a dataaheet that you
use
to enter line items into a transaction. Each row contains the fields
ITEMNAME,
QUANTITY PRICE, DISCOUNT, SUBTOTAL, BENEFIT TYPE.

Each line item in a transaction should be assigned to a particular benefit
type. I have a piece of code that goes through the data sheet and checks
to
make sure that the benefit type field is not empty.

The problem I am having is that if I enter 3 items and dont put a benefit
type for any of them,- When I click the COMPLETE TRANSACTION button I will
get the message box "You need to enter a benefit type".

If I put in the benefit type for one of the three items and click the
COMPLETE TRANSACTION button again, I do not get the error message box
again,
instead it closes the transaction.

Each time I click the COMPLETE TRANSACTION button I would like it to go
through each line item and check if it has a benefit type entered and if
not
show a mesage box.

Hope my post makes sense. I am sure it is something simple, but I am just
not
seeing it.

Thanks

The code is below....

Dim rsw As New RecordsetWrapper
With rsw.GetRecordsetClone(Me.sbfOrderDetails.Form.Recordset)
' Check that we have at least one specified line items
If .RecordCount = 0 Then
MsgBoxOKOnly OrderDoesNotContainLineItems
Else
Dim LineItemCount As Integer
LineItemCount = 0
While Not .EOF
LineItemCount = LineItemCount + 1

If IsNull(Forms![Order Details]![sbfOrderDetails].Form![Product
ID]) Then
MsgBoxOKOnly MustSpecifyItemName
Exit Function

ElseIf IsNull(Forms![Order Details]![sbfOrderDetails].Form!
[Benefit ID]) Then
MsgBoxOKOnly MustSpecifyBenefitType
Exit Function
End If
rsw.MoveNext
Wend

ValidateOrder = True
eh.TryToSaveRecord
Me![Status ID] = Closed_CustomerOrder
MsgBoxOKOnly OrderMarkedClosed
SetFormState


End If
End With

I don't know what you're doing with your RecordsetWrapper class, but it
seems to me that whatever looping you're doing with rsw.GetRecordsetClone --
"While Not .EOF ... rsw.MoveNext" -- is irrelevant to the checks you're
making against the form itself:

If IsNull(Forms![Order Details]![sbfOrderDetails].Form![Product
ID]) Then

Tests like that will always only be looking at the current record of the
subform, no matter what record is current in the subform's recordsetclone.

Shouldn't you be looking at the records in the recordsetclone itself? As
in:

If IsNull(![Product ID]) Then
MsgBoxOKOnly MustSpecifyItemName
Exit Function
ElseIf IsNull(![Benefit ID]) Then
MsgBoxOKOnly MustSpecifyBenefitType
Exit Function
End If

 

[jermaine123 via AccessMonster.com]

Thanks for replying Dirk,

I knew it was something simple. I moved the the pointer back to the first
record just before the while loop and it worked.

Hello Guys,

[quoted text clipped - 65 lines]

End If
End With

I don't know what you're doing with your RecordsetWrapper class, but it
seems to me that whatever looping you're doing with rsw.GetRecordsetClone --
"While Not .EOF ... rsw.MoveNext" -- is irrelevant to the checks you're
making against the form itself:

If IsNull(Forms![Order Details]![sbfOrderDetails].Form![Product
ID]) Then

Tests like that will always only be looking at the current record of the
subform, no matter what record is current in the subform's recordsetclone.

Shouldn't you be looking at the records in the recordsetclone itself? As
in:

If IsNull(![Product ID]) Then
MsgBoxOKOnly MustSpecifyItemName
Exit Function
ElseIf IsNull(![Benefit ID]) Then
MsgBoxOKOnly MustSpecifyBenefitType
Exit Function
End If