By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,902 Members | 1,552 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,902 IT Pros & Developers. It's quick & easy.

Do Loop provides incomplete data

P: n/a
I have a Do-Loop routine that checks the values of certain fields in a
Form (F_OmissionCheck) for omissions. If the field is blank, the
strOmissions variable is modified with additional verbiage that will
later be displayed to the user via a MsgBox. Since there may be more
than one record that needs to be checked, this routine is repeated for
however many records are returned in the underlying query/form.

The problem is when I run the routine, the resulting MsgBox
(strOmissions) only displays the omissions recorded for the first
record, even if there are omissions in the 2nd record (or 3rd...).
The strange thing is, when I hit the command button that runs this
routing a couple of more times, I keep getting the same incomplete
response, UNLESS I wait 5 or 10 seconds before hitting the command
button again. If I wait this short period before re-running this
routine, the MsgBox correctly appears with all the omissions
documented even for the 2nd or 3rd records. The same code seems to
be producing different results depending on how long I wait before
running the routine. I've tried Refresh and RunQuery, but that
didn't appear to do anything.
MsgBox results first try:
Start of List
Bay#1: Controller: Split or Common...
Bay#1: Controller: Voltage Sensing...
End of List

MsgBox results if I repeat the routine after waiting a short period:
Start of List
Bay#1: Controller: Split or Common...
Bay#1: Controller: Voltage Sensing...
Bay#2: Switch Operator: Control Power...
End of List

Any suggestions on how to resolve this would be apprecieated.
Code is below....
Private Sub InitiateComputerCheck_Click()
Rem The following Code generates a list of omissions that must be
resolved prior to the Design Request being submitted to MEGPD
Dim strOmissions As String
Dim recCheck As Integer
Dim recCount As Integer
Dim recCurrent As Integer
recCurrent = 1
OKtoProceed = "No"
strOmissions = "Start Of List"
DoCmd.OpenForm "F_OmissionCheck", acNormal, , , acFormReadOnly,
acHidden
DoCmd.GoToRecord acForm, "F_OmissionCheck", acFirst
'Start checking for omissions in key data fields
recCount = Forms![F_OmissionCheck].Recordset.RecordCount
Do
A: If Forms![F_OmissionCheck].[Controller] <> "None" Then GoTo A1 Else
GoTo B
A1: Select Case Forms![F_OmissionCheck].[SplitCommon]
Case "00" To "zz"
GoTo A2 ' something is in this field
Case Else: strOmissions = strOmissions & Chr(13) & "Bay #: " &
Forms![F_OmissionCheck].Form![BayNumber] & " " & "Controller: Split or
Common Bus Scheme"
End Select
A2: Select Case Forms![F_OmissionCheck].[VoltageSensing]
Case "00" To "zz"
GoTo A3 ' something is in this field
Case Else: strOmissions = strOmissions & Chr(13) & "Bay #: " &
Forms![F_OmissionCheck].Form![BayNumber] & " " & "Controller: Voltage
Sensing"
End Select
A3: Select Case
Forms![F_OmissionCheck].[ControlPower_Controller]
Case "00" To "zz"
GoTo B ' something is in this field
Case Else: strOmissions = strOmissions & Chr(13) & "Bay #: " &
Forms![F_OmissionCheck].Form![BayNumber] & " " & "Controller: Control
Power"
End Select
B: If Forms![F_OmissionCheck].[SwitchOperator] <> "None" Then GoTo B1
Else GoTo C
B1: Select Case Forms![F_OmissionCheck].[OperatorPowerSource]
Case "00" To "zz"
GoTo C ' something is in this field
Case Else: strOmissions = strOmissions & Chr(13) & "Bay #: " &
Forms![F_OmissionCheck].Form![BayNumber] & " " & "Switch Operator:
Control Power"
End Select
C:
If recCurrent = recCount Then Exit Do
recCurrent = recCurrent + 1
DoCmd.GoToRecord acForm, "F_OmissionCheck", acNext
recCheck = Forms![F_OmissionCheck].CurrentRecord
Loop
Rem -----------------------------------------------------------------------------
If strOmissions = "" Then OKtoProceed = "YES" Else GoTo ShowOmissions
MsgBox "There are no major omissions detected. You can proceed
to submit the Design Request Form"
GoTo EndSub
ShowOmissions:
strOmissions = strOmissions & Chr(13) & "End of List"
OKtoProceed = "No"
MsgBox strOmissions, vbOKOnly, "The following major omissions have
been detected:"
MsgBox "You need to provide the missing data prior to submitting the
Design Request to MEGPD"

EndSub: End Sub
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Doug wrote:
I have a Do-Loop routine that checks the values of certain fields in a
Form (F_OmissionCheck) for omissions. If the field is blank, the
strOmissions variable is modified with additional verbiage that will
later be displayed to the user via a MsgBox. Since there may be more
than one record that needs to be checked, this routine is repeated for
however many records are returned in the underlying query/form.

The problem is when I run the routine, the resulting MsgBox
(strOmissions) only displays the omissions recorded for the first
record, even if there are omissions in the 2nd record (or 3rd...).
The strange thing is, when I hit the command button that runs this
routing a couple of more times, I keep getting the same incomplete
response, UNLESS I wait 5 or 10 seconds before hitting the command
button again. If I wait this short period before re-running this
routine, the MsgBox correctly appears with all the omissions
documented even for the 2nd or 3rd records. The same code seems to
be producing different results depending on how long I wait before
running the routine. I've tried Refresh and RunQuery, but that
didn't appear to do anything.
MsgBox results first try:
Start of List
Bay#1: Controller: Split or Common...
Bay#1: Controller: Voltage Sensing...
End of List

MsgBox results if I repeat the routine after waiting a short period:
Start of List
Bay#1: Controller: Split or Common...
Bay#1: Controller: Voltage Sensing...
Bay#2: Switch Operator: Control Power...
End of List

Any suggestions on how to resolve this would be apprecieated.
Code is below....
Private Sub InitiateComputerCheck_Click()
Rem The following Code generates a list of omissions that must be
resolved prior to the Design Request being submitted to MEGPD
Dim strOmissions As String
Dim recCheck As Integer
Dim recCount As Integer
Dim recCurrent As Integer
recCurrent = 1
OKtoProceed = "No"
strOmissions = "Start Of List"
DoCmd.OpenForm "F_OmissionCheck", acNormal, , , acFormReadOnly,
acHidden
DoCmd.GoToRecord acForm, "F_OmissionCheck", acFirst
'Start checking for omissions in key data fields
recCount = Forms![F_OmissionCheck].Recordset.RecordCount
Do
A: If Forms![F_OmissionCheck].[Controller] <> "None" Then GoTo A1 Else
GoTo B
A1: Select Case Forms![F_OmissionCheck].[SplitCommon]
Case "00" To "zz"
GoTo A2 ' something is in this field
Case Else: strOmissions = strOmissions & Chr(13) & "Bay #: " &
Forms![F_OmissionCheck].Form![BayNumber] & " " & "Controller: Split or
Common Bus Scheme"
End Select
A2: Select Case Forms![F_OmissionCheck].[VoltageSensing]
Case "00" To "zz"
GoTo A3 ' something is in this field
Case Else: strOmissions = strOmissions & Chr(13) & "Bay #: " &
Forms![F_OmissionCheck].Form![BayNumber] & " " & "Controller: Voltage
Sensing"
End Select
A3: Select Case
Forms![F_OmissionCheck].[ControlPower_Controller]
Case "00" To "zz"
GoTo B ' something is in this field
Case Else: strOmissions = strOmissions & Chr(13) & "Bay #: " &
Forms![F_OmissionCheck].Form![BayNumber] & " " & "Controller: Control
Power"
End Select
B: If Forms![F_OmissionCheck].[SwitchOperator] <> "None" Then GoTo B1
Else GoTo C
B1: Select Case Forms![F_OmissionCheck].[OperatorPowerSource]
Case "00" To "zz"
GoTo C ' something is in this field
Case Else: strOmissions = strOmissions & Chr(13) & "Bay #: " &
Forms![F_OmissionCheck].Form![BayNumber] & " " & "Switch Operator:
Control Power"
End Select
C:
If recCurrent = recCount Then Exit Do
recCurrent = recCurrent + 1
DoCmd.GoToRecord acForm, "F_OmissionCheck", acNext
recCheck = Forms![F_OmissionCheck].CurrentRecord
Loop
Rem -----------------------------------------------------------------------------
If strOmissions = "" Then OKtoProceed = "YES" Else GoTo ShowOmissions
MsgBox "There are no major omissions detected. You can proceed
to submit the Design Request Form"
GoTo EndSub
ShowOmissions:
strOmissions = strOmissions & Chr(13) & "End of List"
OKtoProceed = "No"
MsgBox strOmissions, vbOKOnly, "The following major omissions have
been detected:"
MsgBox "You need to provide the missing data prior to submitting the
Design Request to MEGPD"

EndSub: End Sub


I have no idea why you bother to open the form when you basically hide it when
opened.

If it were me designing the code. I'd simply take the recordsource and open it in a
recordset.

I really didn't study your code. But if it were me and I was using your method of
opening the form I'd have a routine in the OnOpen event perhaps that would be
something like

If Me.OpenArgs = "Hidden" Then
'pass the word "Hidden" if opening the form not visible
Dim rst As Recordset

Set rst = Me.recordsetclone
If rst.RecordCount > 0 then
rst.MoveFirst
Do While Not rst.Eof
.....process
rst.MoveNext
Loop
msgbox "Results...."
Else
msgbox "Nothing to report"
endif
Cancel = True
Endif
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.