what i want to do is to filter all the contacts that belong to the service using the junction table that includes just PK to service and PK to contact as a composite key.
I used this code
Expand|Select|Wrap|Line Numbers
- 'set up the agency binding source
- With serviceBindingSource
- .DataSource = aDataSet
- .DataMember = "Service"
- .Filter = "Agency_ID = " & CInt(CurrentAgencyID)
- End With
- 'set up the offer binding source
- With offerBindingSource
- .DataSource = aDataSet
- .DataMember = "Offer"
- .Filter = "Service_ID = " & Me.serviceBindingSource.Current("Service_ID").ToString
- End With
- 'set up the contactbinding source
- With contactBindingSource
- .DataSource = aDataSet
- .DataMember = "Contact"
- .Filter = "Contact_ID = " + offerBindingSource.Current("Contact_ID").ToString
- End With
- With Me.cboContact
- .DataSource = contactBindingSource
- .DisplayMember = "C_Name"
- .ValueMember = "Contact_ID"
- .DataBindings.Add("text", contactBindingSource, "C_Name", False, DataSourceUpdateMode.Never)
- End With
This seems to work with the = but it only filters the first record it finds in the junction table not all the "3" records that belong.
I tried using the IN but it still does the same thing.
Is there a way to use a for next to filter it to how many records of contacts belong to the services using the junction table that has a composite key for contact_id and services_id.
any help is appreciated
Okay its fixed
Expand|Select|Wrap|Line Numbers
- If offerBindingSource.Count <> 0 Then
- 'genius at work
- Me.offerBindingSource.MoveFirst()
- Dim count As Integer = Me.offerBindingSource.Count
- count -= 1
- 'Dim IDArray(count) As String
- Dim query As String = ""
- For i As Integer = 0 To count
- query += "'" + Me.offerBindingSource.Current("Contact_ID").ToString + "'"
- If i <> count Then
- query += ","
- End If
- Me.offerBindingSource.MoveNext()
- Next
- contactBindingSource.Filter = "Contact_ID IN (" + query + ")"
- End If