Array/text string issues - type mismatch error

  • Thread starter Thread starter Mazaru
  • Start date Start date
M

Mazaru

Hi,

I'm writing code for a project which involves a many-to-many
relationship, and I've created a multi-select list box to handle the
problems. It's held on a seperate form to the main one.

I've done all the code to make it feed back the selections to the
table structure, but now I'm trying to write a sequence so that when I
open up the form, it automatically selects the items that are selected
for that record. I've used a query to select the records, put it into
an array, but now I'm stuck on how to loop the array through and
compare it with the records on the list. Any help especially sample
code would be much appreciated!

Cheers
 
Why bother with an array?

There is no need to load up a recordset, and then transfer that recordset to
a array? Why would you go to all this trouble? Why not loop through the
reocrdset code? In fact, a recordset also has the find methoed. You are
already digging a hole here as far as coding goes anyway! You can't search
an array very well, and you would have to loop through the whole array each
time to look for a value. Really, very messay at a best.

If you use a sub-form with a combo box to select the value, then you
eliminate the whole process of having to code the write out of records
selected. Further, a continues sub-form with a combo box would also
eliminate the code to "load" up the display.

I guess it depends on who is paying for this!

Anyway, if you must stick to the listbox, here is some air code that shold
give you the idea. I am assuming that the first collum on the listbox is a
hidding id.


Dim strSql As String
Dim rstSel As DAO.Recordset ' list of selected reocrds
Dim i As Integer

' strSql = "select id from childtable with main_id = " & Me.ID

Set rstSel = CurrentDb.OpenRecordset(strSql)

' ok, got the selected reocrds, now hightlight any records.

For iptr = 0 To Me.lstYourListBox.ListCount - 1

strSql = "id = " & Me.lstYourListBox.Column(0, iptr)
rstSel.FindFirst strSql

If rstSel.NoMatch = False Then
Me.lstSecurity.Selected(iptr) = True
End If

Next iptr

rstSel.Close
Set rstSel = Nothing
 
Back
Top