msgbox problem on a word user form

[joncohen]

I am trying to validate that an item was selected in a listbox when a user
goes to the next listbox on a word user form. I can trap for the error and
display a message box notifying the user that they did not select an value in
the previous listbox. However, when I try to redirect the user to the
previous listbox the msgbox wants to display twice before the vba logic
refocuses the cursor to the previous listbox. Does anyone have a some code as
a guide as to how to do this in word vba.

Thanks for your assistance in advance.

- Jonathan

 

[Jay Freedman]

Have a look at
http://www.word.mvps.org/FAQs/TblsFldsFms/ValidateFFields.htm.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Jay Freedman]

I think my first answer was for the wrong situation -- sorry!

Can you post the code that does the validation/message box/set focus?

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[joncohen]

Jay,

Thanks for the reponse. I problem is that I am trying to show the msgbox
only once on a vba form. I am trapping the error in the listbox enter event
and then trying to get the focus back to the previous listbox ... I use
both these vba statements in my logic :

ListBox2.SetFocus
Exit Sub

Yet in the debugger the code cycles through twice before the focus is
returned back to the vba form with the focus on listbox2.

The code I am using is as follows:

Private Sub ListBox3_Enter()
'sub procedure to connect to a SQL server, run a Stored Procedure then
output the value to a table in the document.
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command

'Dim casestatus As String
'Dim csstatuslst() As String

'Dim j As Integer
'Dim x As Integer

Dim strsql As String

casestatus = ""

'create a new connetion object
Set cn = New ADODB.Connection
'cn.Open "Provider=SQLOLEDB.1;Password=spunk;Persist Security Info=True;User
ID=joncohen;Initial Catalog=mms;Data Source=\test001"
'cn.Open "Provider=SQLOLEDB.1;Password=spunk;Persist Security Info=True;User
ID=joncohen;Initial Catalog=mms;Data Source=\sql2005"
cn.Open "Provider=SQLOLEDB.1;Password=andrew;Persist Security
Info=True;User ID=cindy;Initial Catalog=mms;Data Source=HCPSQL"

'create a command and Recordset
'Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
'configure the command object


ListBox3.Clear
ListBox4.Clear
ListBox4.Height = 16
ListBox5.Clear
ListBox5.Height = 16

casestatus = getcsstatuslst(casestatus)

If casestatus = "" Then

MsgBox "You have not selected the case type!", 16, "Error Message"

' Response = InputErrorMsg()
ListBox2.SetFocus
Exit Sub

End If


If ComboBox1.Value <> vbNullString And casestatus <> vbNullString Then

strsql = " select distinct case_type = UPPER(" & _
" case " & _
" when casetype = 0 then 'Asbestos' " & _
" when casetype = 1 then 'Workers Compensation'" & _
" when casetype = 2 then 'Silicosis'" & _
" when casetype = 3 then 'Toxic'" & _
" end)" & _
" from cases cs left join court ct (NOLOCK) on cs.court =
ct.code" & _
" where ct.state = " & "'" & ComboBox1.Value & "' and
cs.casestatus in (" & _
casestatus & ")" & " Order by case_type"

With rs
Set .ActiveConnection = cn
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open strsql

End With

Dim strCombo
strCombo = ""

'rs.Open cmd
rs.MoveFirst

ListBox3.AddItem ("All")
Do While Not rs.EOF
ListBox3.AddItem (rs(0).Value)
rs.MoveNext
Loop

If ListBox3.ListCount = 2 Then
ListBox3.RemoveItem (0)
ListBox3.Height = 16
Else
ListBox3.Height = 40
End If
rs.Close
cn.Close
End If

Set rs = Nothing
Set cmd = Nothing
Set cn = Nothing


End Sub

 

[joncohen]

Jay,

See my previous post ... thanks.

I think my first answer was for the wrong situation -- sorry!

Can you post the code that does the validation/message box/set focus?

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Jay Freedman]

Hi Jon,

I stripped the question down to the minimum: can a SetFocus command in
the Enter procedure of one listbox send the cursor to another listbox?
I wasn't able to find any way to do that. The focus always goes to the
control that follows ListBox2 in the tab order, not back to the
previous listbox. I'm not sure how that causes your messagebox to show
twice.

But I think there's a way to sidestep the whole issue. I'll guess that
your initialization code adds items to ListBox2.List, but it doesn't
set the value of ListBox2.ListIndex. By default, that value is
initialized to -1, which means nothing is selected. Explicitly set
ListBox2.ListIndex = 0 in the Userform_Initialize procedure after the
items are all added; that will select the first item, and thereafter
it will be impossible not to have any selection in that list. Then you
should never get casestatus = "", and you can remove that code from
the ListBox3_Enter routine.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.