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

msgbox else if

P: n/a
In this bit of code provided so helpfully by Nath:

Private Sub Command118_Click()

Dim rs As DAO.Recordset

Set db = CurrentDb

Set rs = db.openrecordset("IMPORT")

With rs

.MoveFirst

prev = !NOM_ACCOUNT

.MoveNext

Do

Select Case prev

Case "1700"

If !NOM_ACCOUNT <> "2300" Then

MsgBox "There has been an error in row # " & !USER_TXN_NO & vbCrLf
& "Sort order failed - check table", , "Warning - Table Incorrectly Sorted"

Exit Sub 'or Exit Function

End If

Case "2300"

If !NOM_ACCOUNT <> "3200" Then

MsgBox "There has been an error in row # " & !USER_TXN_NO & vbCrLf
& "Sort order failed - check table", , "Warning - Table Incorrectly Sorted"

Exit Sub 'or Exit Function

End If

Case "3200"

If !NOM_ACCOUNT <> "1700" Then

MsgBox "There has been an error in row # " & !USER_TXN_NO & vbCrLf
& "Sort order failed - check table", , "Warning - Table Incorrectly Sorted"

Exit Sub 'or Exit Function

End If

End Select

prev = !NOM_ACCOUNT

.MoveNext

Loop Until .EOF

.Close

End With

Set rs = Nothing

Set db = Nothing

End Sub

I'd like to add a msgbox if the command completes successfully, and the
NOM_ACCOUNT filed is sorted okay, have been playiong with where to insert
the text but cannot see where. Anyone help? Thanks, Lap.


--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Try adding a Case Else item in your Select structure. Add your messagebox
there.

"Lapchien" <cc****@NOSPAMeclipse.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...
In this bit of code provided so helpfully by Nath:

Nov 12 '05 #2

P: n/a

"Lapchien" <cc****@NOSPAMeclipse.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...
In this bit of code provided so helpfully by Nath:

Private Sub Command118_Click()

Dim rs As DAO.Recordset

Set db = CurrentDb

Set rs = db.openrecordset("IMPORT")

With rs

.MoveFirst

prev = !NOM_ACCOUNT

.MoveNext

Do

Select Case prev

Case "1700"

If !NOM_ACCOUNT <> "2300" Then

MsgBox "There has been an error in row # " & !USER_TXN_NO & vbCrLf & "Sort order failed - check table", , "Warning - Table Incorrectly Sorted"
Exit Sub 'or Exit Function

End If

Case "2300"

If !NOM_ACCOUNT <> "3200" Then

MsgBox "There has been an error in row # " & !USER_TXN_NO & vbCrLf & "Sort order failed - check table", , "Warning - Table Incorrectly Sorted"
Exit Sub 'or Exit Function

End If

Case "3200"

If !NOM_ACCOUNT <> "1700" Then

MsgBox "There has been an error in row # " & !USER_TXN_NO & vbCrLf & "Sort order failed - check table", , "Warning - Table Incorrectly Sorted"
Exit Sub 'or Exit Function

End If

End Select

prev = !NOM_ACCOUNT

.MoveNext

Loop Until .EOF

.Close

End With

Set rs = Nothing

Set db = Nothing

End Sub

I'd like to add a msgbox if the command completes successfully, and the
NOM_ACCOUNT filed is sorted okay, have been playiong with where to insert
the text but cannot see where. Anyone help? Thanks, Lap.


--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk


why not just put the message after Loop Until .EOF?
(since it's going to exit the sub if there's an error).
but there seems to be a problem because if there
is an error, it doesn't get a chance to do the close
or sets = nothing.

Private Sub Command118_Click()
dim prev as string, rs as dao.recordset, myMsg as string
myMsg = "successful"
Set rs = currentdb.openrecordset("IMPORT")
prev = ""
do while not rs.eof
if prev <> "" then
if (prev = "1700" and rs("NOM_ACCOUNT) <> "2300")
or (prev = "2300" and rs("NOM_ACCOUNT) <> "3200")
or (prev = "3200" and rs("NOM_ACCOUNT) <> "1700") then
myMsg = "bla bla bla"
exit do
end if
end if
prev = rs("NOM_ACCOUNT")
rs.movenext
loop
msgbox myMsg
rs.close
set rs = nothing
end sub
Nov 12 '05 #3

P: n/a
what are you guys talking about?

he only wants the successful message if the
whole table is sorted ok
(not after testing each record)


"rkc" <rk*@yabba.dabba.do.rochester.rr.com> wrote in message
news:mp*******************@twister.nyroc.rr.com...
Put the msgbox statement in a Case Else statement in your
Select Case block.

If you want the recordset sorted, why not open it with query
using an Order By clause on NOM_ACCOUNT ?

"Lapchien" <cc****@NOSPAMeclipse.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...
In this bit of code provided so helpfully by Nath:

Private Sub Command118_Click()

Dim rs As DAO.Recordset

Set db = CurrentDb

Set rs = db.openrecordset("IMPORT")

With rs

.MoveFirst

prev = !NOM_ACCOUNT

.MoveNext

Do

Select Case prev

Case "1700"

If !NOM_ACCOUNT <> "2300" Then

MsgBox "There has been an error in row # " & !USER_TXN_NO &

vbCrLf
& "Sort order failed - check table", , "Warning - Table Incorrectly

Sorted"

Exit Sub 'or Exit Function

End If

Case "2300"

If !NOM_ACCOUNT <> "3200" Then

MsgBox "There has been an error in row # " & !USER_TXN_NO &

vbCrLf
& "Sort order failed - check table", , "Warning - Table Incorrectly

Sorted"

Exit Sub 'or Exit Function

End If

Case "3200"

If !NOM_ACCOUNT <> "1700" Then

MsgBox "There has been an error in row # " & !USER_TXN_NO &

vbCrLf
& "Sort order failed - check table", , "Warning - Table Incorrectly

Sorted"

Exit Sub 'or Exit Function

End If

End Select

prev = !NOM_ACCOUNT

.MoveNext

Loop Until .EOF

.Close

End With

Set rs = Nothing

Set db = Nothing

End Sub

I'd like to add a msgbox if the command completes successfully, and the
NOM_ACCOUNT filed is sorted okay, have been playiong with where to insert the text but cannot see where. Anyone help? Thanks, Lap.


--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk


Nov 12 '05 #4

P: n/a
rkc

"dogwalker" <d@g.com> wrote in message
news:W%********************@news20.bellglobal.com. ..
what are you guys talking about?

he only wants the successful message if the
whole table is sorted ok
(not after testing each record)


You're right. Although sorting a table is a flawed concept.

I'll leave it to you to post again answering the question.
Nov 12 '05 #5

P: n/a
I get the debug error 'Loop without Do' with this code:

Private Sub Command118_Click()
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.openrecordset("IMPORT")
With rs
.MoveFirst
prev = !NOM_ACCOUNT
.MoveNext
Do
Select Case prev
Case "1700"
If !NOM_ACCOUNT <> "2300" Then
MsgBox "There has been an error in row # " & !USER_TXN_NO & vbCrLf
& "Sort order failed - check table", , "Warning - Table Incorrectly Sorted"
Exit Sub 'or Exit Function
End If
Case "2300"
If !NOM_ACCOUNT <> "3200" Then
MsgBox "There has been an error in row # " & !USER_TXN_NO & vbCrLf
& "Sort order failed - check table", , "Warning - Table Incorrectly Sorted"
Exit Sub 'or Exit Function
End If
Case "3200"
If !NOM_ACCOUNT <> "1700" Then
MsgBox "There has been an error in row # " & !USER_TXN_NO & vbCrLf
& "Sort order failed - check table", , "Warning - Table Incorrectly Sorted"
Exit Sub 'or Exit Function
End If
prev = rs("NOM_ACCOUNT")
rs.MoveNext
Loop Until .EOF
MsgBox "Rows sorted okay!", vbOKOnly, "Completed"
rs.Close
Set rs = Nothing
End Sub


--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk

"dogwalker" <d@g.com> wrote in message
news:%V********************@news20.bellglobal.com. ..

"Lapchien" <cc****@NOSPAMeclipse.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...
In this bit of code provided so helpfully by Nath:

Private Sub Command118_Click()

Dim rs As DAO.Recordset

Set db = CurrentDb

Set rs = db.openrecordset("IMPORT")

With rs

.MoveFirst

prev = !NOM_ACCOUNT

.MoveNext

Do

Select Case prev

Case "1700"

If !NOM_ACCOUNT <> "2300" Then

MsgBox "There has been an error in row # " & !USER_TXN_NO &

vbCrLf
& "Sort order failed - check table", , "Warning - Table Incorrectly

Sorted"

Exit Sub 'or Exit Function

End If

Case "2300"

If !NOM_ACCOUNT <> "3200" Then

MsgBox "There has been an error in row # " & !USER_TXN_NO &

vbCrLf
& "Sort order failed - check table", , "Warning - Table Incorrectly

Sorted"

Exit Sub 'or Exit Function

End If

Case "3200"

If !NOM_ACCOUNT <> "1700" Then

MsgBox "There has been an error in row # " & !USER_TXN_NO &

vbCrLf
& "Sort order failed - check table", , "Warning - Table Incorrectly

Sorted"

Exit Sub 'or Exit Function

End If

End Select

prev = !NOM_ACCOUNT

.MoveNext

Loop Until .EOF

.Close

End With

Set rs = Nothing

Set db = Nothing

End Sub

I'd like to add a msgbox if the command completes successfully, and the
NOM_ACCOUNT filed is sorted okay, have been playiong with where to insert the text but cannot see where. Anyone help? Thanks, Lap.


--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk


why not just put the message after Loop Until .EOF?
(since it's going to exit the sub if there's an error).
but there seems to be a problem because if there
is an error, it doesn't get a chance to do the close
or sets = nothing.

Private Sub Command118_Click()
dim prev as string, rs as dao.recordset, myMsg as string
myMsg = "successful"
Set rs = currentdb.openrecordset("IMPORT")
prev = ""
do while not rs.eof
if prev <> "" then
if (prev = "1700" and rs("NOM_ACCOUNT) <> "2300")
or (prev = "2300" and rs("NOM_ACCOUNT) <> "3200")
or (prev = "3200" and rs("NOM_ACCOUNT) <> "1700") then
myMsg = "bla bla bla"
exit do
end if
end if
prev = rs("NOM_ACCOUNT")
rs.movenext
loop
msgbox myMsg
rs.close
set rs = nothing
end sub

Nov 12 '05 #6

P: n/a
okay, managed to get that to work, with:

Private Sub Command118_Click()
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.openrecordset("IMPORT")
With rs
.MoveFirst
prev = !NOM_ACCOUNT
.MoveNext
Do
Select Case prev
Case "1700"
If !NOM_ACCOUNT <> "2300" Then
MsgBox "There has been an error in row # " & !USER_TXN_NO & vbCrLf
& "Sort order failed - check table", , "Warning - Table Incorrectly Sorted"
Exit Sub 'or Exit Function
End If
Case "2300"
If !NOM_ACCOUNT <> "3200" Then
MsgBox "There has been an error in row # " & !USER_TXN_NO & vbCrLf
& "Sort order failed - check table", , "Warning - Table Incorrectly Sorted"
Exit Sub 'or Exit Function
End If
Case "3200"
If !NOM_ACCOUNT <> "1700" Then
MsgBox "There has been an error in row # " & !USER_TXN_NO & vbCrLf
& "Sort order failed - check table", , "Warning - Table Incorrectly Sorted"
Exit Sub 'or Exit Function
End If
End Select
prev = !NOM_ACCOUNT
.MoveNext
Loop Until .EOF
.Close
End With
MsgBox "Import table rows sorted okay", vbOKOnly, "Completed"
Set rs = Nothing
Set db = Nothing
End Sub
--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk

"dogwalker" <d@g.com> wrote in message
news:%V********************@news20.bellglobal.com. ..

"Lapchien" <cc****@NOSPAMeclipse.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...
In this bit of code provided so helpfully by Nath:

Private Sub Command118_Click()

Dim rs As DAO.Recordset

Set db = CurrentDb

Set rs = db.openrecordset("IMPORT")

With rs

.MoveFirst

prev = !NOM_ACCOUNT

.MoveNext

Do

Select Case prev

Case "1700"

If !NOM_ACCOUNT <> "2300" Then

MsgBox "There has been an error in row # " & !USER_TXN_NO &

vbCrLf
& "Sort order failed - check table", , "Warning - Table Incorrectly

Sorted"

Exit Sub 'or Exit Function

End If

Case "2300"

If !NOM_ACCOUNT <> "3200" Then

MsgBox "There has been an error in row # " & !USER_TXN_NO &

vbCrLf
& "Sort order failed - check table", , "Warning - Table Incorrectly

Sorted"

Exit Sub 'or Exit Function

End If

Case "3200"

If !NOM_ACCOUNT <> "1700" Then

MsgBox "There has been an error in row # " & !USER_TXN_NO &

vbCrLf
& "Sort order failed - check table", , "Warning - Table Incorrectly

Sorted"

Exit Sub 'or Exit Function

End If

End Select

prev = !NOM_ACCOUNT

.MoveNext

Loop Until .EOF

.Close

End With

Set rs = Nothing

Set db = Nothing

End Sub

I'd like to add a msgbox if the command completes successfully, and the
NOM_ACCOUNT filed is sorted okay, have been playiong with where to insert the text but cannot see where. Anyone help? Thanks, Lap.


--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk


why not just put the message after Loop Until .EOF?
(since it's going to exit the sub if there's an error).
but there seems to be a problem because if there
is an error, it doesn't get a chance to do the close
or sets = nothing.

Private Sub Command118_Click()
dim prev as string, rs as dao.recordset, myMsg as string
myMsg = "successful"
Set rs = currentdb.openrecordset("IMPORT")
prev = ""
do while not rs.eof
if prev <> "" then
if (prev = "1700" and rs("NOM_ACCOUNT) <> "2300")
or (prev = "2300" and rs("NOM_ACCOUNT) <> "3200")
or (prev = "3200" and rs("NOM_ACCOUNT) <> "1700") then
myMsg = "bla bla bla"
exit do
end if
end if
prev = rs("NOM_ACCOUNT")
rs.movenext
loop
msgbox myMsg
rs.close
set rs = nothing
end sub

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.