List of values

[Frenk Nijssen]

Hi,

I have code accessing a named range in an excel sheet, filling a formfield
in word,
the access and display is in a vba created form list of values, where the
user can select the value he/she wants. The display is ok, the form displays
all selected(378 records) values, but when a selected value greater then the
row 200 the value is not filled out in the formfield, everyting below this is
ok..

Do i have to change my code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

' If no databasefile has been found reinit it.
If dataDs_Filename = "" Then
dataDs_Filename = "\\myExcelbook.xls"
End If

' Fetch tabbed out formfield, this will determine the fieldname of where
the
' data from the LoV will be put in.
If Selection.FormFields.Count = 1 Then
' No textbox but a check- or listbox
MyFieldStr = Selection.FormFields(1).Name
ElseIf Selection.FormFields.Count = 0 And Selection.Bookmarks.Count > 0
Then
MyFieldStr = Selection.Bookmarks(Selection.Bookmarks.Count).Name
End If

' Init the global field MyField for Lov
Set MyField = ActiveDocument.FormFields(MyFieldStr)

' Open the database an Excel file see dataDs_Filename
Set db = OpenDatabase(dataDs_Filename, False, False, "Excel 8.0")

' display countries
If MyFieldStr = "countries_man" Then
Set rs = db.OpenRecordset("SELECT * FROM `countries`")
' display departments
ElseIf MyFieldStr = "departments_man" Then
Set rs = db.OpenRecordset("SELECT * FROM `departments`")
Else
' No RecordSet has been found and therefor no LoV should be displayed.
db.Close
Set db = Nothing
GoTo NoLoV
End If

' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

' Set the number of Columns = number of Fields in recordset
LovForm.ListBox1.ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records
LovForm.ListBox1.Column = rs.GetRows(NoOfRecords)

' Cleanup
rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

' display the list of values form
LovForm.Show

'DEBUG MsgBox ("ShowLov " & MyFieldStr)

' Tag to abort procedure and do nothing.
NoLoV:
End Sub

Thanks in advance.

 

[Jay Freedman]

Hi Frenk,

Whatever the problem may be, it isn't in this part of your code. What you
have shown is only the code to load the data into the listbox and display
the userform. The code that places the user's selection into the form field
must be in a procedure within LovForm, probably the Click procedure of the
OK button. Please post that code in a reply.

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

 

[Frenk Nijssen]

Hi Jay,

You are right, i found it this morning there was a count:

Private Sub ButtonOK_Click()
For i = 0 To 500 ' this was 200
If LovForm.ListBox1.Selected(i) = True Then
If IsNull(LovForm.ListBox1.Value) = False Then
MyField.Result = LovForm.ListBox1.Value
Else
MyField.Result = ""
End If
End If
Next i

LovForm.hide
End Sub

is there a possibility to leave the count out???

 

[Jay Freedman]

Hi Frenk,

Looking at this code, I have to assume that the listbox is set to
allow only one row to be selected. (It's possible to set a listbox to
allow multiple rows to be selected, but then your code would use only
the last row of the selection, and that doesn't make any sense.)

With that assumption, you don't need the loop at all. The expression
LovForm.ListBox1.Value contains the text of the selected item in the
single-selection listbox (or Null if there is no selection). All you
need in your Click procedure is this:

Private Sub ButtonOK_Click()
If Not IsNull(LovForm.ListBox1.Value) Then
MyField.Result = LovForm.ListBox1.Value
Else
MyField.Result = ""
End If
Next i

Me.Hide
End Sub

If you ever do need to deal with a multiple-selection listbox, you can
use its .ListCount property to find the number of items in the list,
and write the For statement as

For i = 0 To LovForm.ListBox1.ListCount - 1

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