.AddItem property in MSAccess97?

[yummy7]

Hi All
I have this code working fine in Access2002. But giving error on 'AddItem property' in Access97.Because it doesnt support it in Access97. Through my search on net,i realize that recordsource is used by replacing it.So please help me out how to resolve it.
Private Sub moveBetweenLists(lstBoxFrom As Access.ListBox, lstBoxTo As Access.ListBox)
On Error GoTo err_list
Dim counter As Integer
Dim colCounter As Integer
Dim varListItem As Variant
Dim indexArray() As Variant
Dim listValue As String
Dim intCountSelected As Integer
ReDim indexArray(0 To lstBoxFrom.ListCount)
For Each varListItem In lstBoxFrom.ItemsSelected
For colCounter = 0 To lstBoxFrom.ColumnCount - 1
listValue = listValue & """" & CStr(Nz(lstBoxFrom.Column(colCounter, varListItem), " ")) & """;"
Next colCounter
listValue = Left(listValue, Len(listValue) - 1)
lstBoxTo.AddItem (listValue)
indexArray(counter) = varListItem
counter = counter + 1
listValue = ""
Next varListItem
intCountSelected = lstBoxFrom.ItemsSelected.Count
'remove
For counter = 0 To intCountSelected - 1
lstBoxFrom.RemoveItem (indexArray(counter) - counter)
Next counter
Exit_Sub:
Exit Sub
err_list:
MsgBox Err.Description
Resume Exit_Sub
Exit Sub
End Sub

 

[jebry]

Hi!

Just add it to the RecordSource:

lstBoxTo.RecordSource = lstBoxTo.RecordSource & listValue

You will need to take it off the from list manually as well:

listPosition = InStr(lstBoxFrom.RecordSource, listValue)

lstBoxFrom.RecordSource = Left(lstBoxFrom.RecordSource, listPosition - 1) & Mid(lstBoxFrom.RecordSource, listPosition + Len(listValue))

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[yummy7]

Thanx jebry,
Explain it detail please.
listPosition from where it came.
listPosition = InStr(lstBoxFrom.RecordSource, listValue)

lstBoxFrom.RecordSource = Left(lstBoxFrom.RecordSource, listPosition - 1) & Mid(lstBoxFrom.RecordSource, listPosition + Len(listValue))

 

[johanap]

Hi

Here's a code that has been posted early on tek-tips:

Adding (text) items to listboxes and getting/setting text in textboxes

faq181-736
Posted: 18 May 01

I don't know whether it's just that I've never found it, but I was surprised to see that listboxes and comboboxes don't have the .AddItem method in Access that they have in Excel and Word.

Here's the function I use to add an item to an Access Listbox or Combobox. I had written a DelListItem sub too, but it was fundamentally flawed, so I haven't included it here.

---

Sub AddListItem(lst As Control, S As String)
With lst
If (.ControlType = acComboBox) Or (.ControlType = acListBox) Then
If .RowSourceType = "Value List" Then
If .ListCount > 0 Then
.RowSource = .RowSource & "; " & S
Else
.RowSource = .RowSource & S
End If
End If
End If
End With
End Sub

---

Call this function using:

AddListItem lstName, "text string"

---

The next one is due to the annoying problem of having to set focus to textboxes or comboboxes to read or set the information in them. The first is used to get the text from a textbox, and is simply used by calling:

GetText ctrName

The second sets the text in a textbox. Use:

SetText ctrName, "text string"

---

Function GetText(ctr As Control) As String
With ctr
If (.ControlType = acTextBox) Or (.ControlType = acComboBox) Then
.SetFocus
GetText = .Text
End If
End With
End Function

---

Sub SetText(ctr As Control, txt As String)
With ctr
If (.ControlType = acTextBox) Or (.ControlType = acComboBox) Then
.SetFocus
ctr.Text = txt
End If
End With
End Sub

-------------------------------------------------------------------------------

So far the early posted faq.

Personally I prefer an other solution !

There is a special Listbox as a ActiveX control in Access 2000 that permits to use "AddItem". I suppose that it also exists in Access 97:

Design View ==> Insert ==> ActiveX control ==> Microsoft Forms 2.0 ListBox. After inserting this listbox in your form, there should also a Reference (automatically) been set to "Microsoft Forms 2.0 Object Library".

Here's some code I use in it :

Dim lst As MSForms.ListBox
Set lst = MyListName.Object

With lst
.Font = "Tahoma"
.Font.Size = 12
.Font.Weight = 800
.ColumnCount = 3
.ColumnWidths = "1 in"
.ListWidth = "3 in"
End With

'If you want to populate the listbox from a table:

If Not rst.EOF Then
rst.MoveFirst
Do Until rst.EOF
lst.AddItem rst!MyID
lst.Column(1, lst.ListCount - 1) = rst!Field_2
lst.Column(2, lst.ListCount - 1) = rst!Field_3
lst.Column(3, lst.ListCount - 1) = rst!.....
rst.MoveNext
Loop
End If

'Or if you want to populate the listbox from a text (array)

lst.AddItem (MyID.Value)
lst.Column(1, lst.ListCount - 1) = ......
lst.Column(2, lst.ListCount - 1) = .......


Johan

 

[yummy7]

Thanx guys for helping. I will check it out.

 

[jebry]

Hi!

listPosition you will have to declare as a long and the other piece of code just snips out the part you no longer want in the From list box by concatenating the part to the left with the part to the right.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst