Show matching data in a form

[jg95ae]

I'm working on a database that will hold bacterial strain typing information. I've created a nice user interface using forms to do Data Entry and search entries but the next part I'm stumped on.

I want to open a form, select from a drop-down the test method, say test1, test2, or test1 AND test2 to look for patients (bacterial strains) that have identical matching results. Test1 is a 12 digit code, and test 2 is a 15 digit code. I would like to show the groups of matching strains separate from each other and then assign a group ID.

At this point I've tried the Find duplicates query but I don't know how to then put that in a form and to separate the matching sets.

Can anyone point me in the right direction?

 

[DCrake]

Drop down lists (comboboxes) do not support multi select. You can only select one item from the list at once. What you could do is have a text box that contains the selections made by the user that builds up dynamically then use this for filtering purposes.

 

[Vonnie]

Depending on how large the rs is for the dropdown, you can use a listbox. It supports multi-select. You have to use a loop and go through all the items in the list however, and it would just be too cumbersome for a large list and it sounds like it is too late for db redesign.

If Not IsNull(Me.List2.Column(1)) Then
For intCurrentRow = 0 To List2.ListCount - 1
If List2.Selected(intCurrentRow) Then
strItems = strItems & List2.Column(1, intCurrentRow) & ";"
End If
Next intCurrentRow
Debug.Print strItems
End If

 

[jg95ae]

I've been working away at this and have found something that works. The code below will show a list of records that have matching test results for the 2 tests (MIRU12 and OctalCode). However I would like to show the matches set by set as I may have 100's of matches and need to identify each matching set separately. Does anyone know of a way to do this?

Thanks

Private Sub cmdSearch_Click()
Me.lstResult.RowSource = "SELECT ENTRYTABLE.KEY, ENTRYTABLE.FIRSTNAME, ENTRYTABLE.SURNAME, ENTRYTABLE.MIRU12, ENTRYTABLE.OCTALCODE, ENTRYTABLE.REGION_DESIGNATION, ENTRYTABLE.STRAIN FROM ENTRYTABLE WHERE (((ENTRYTABLE.OctalCode) In (SELECT [OctalCode] FROM [ENTRYTABLE] As Tmp GROUP BY [OctalCode],[MIRU12] HAVING Count(*)>1 And [MIRU12] = [ENTRYTABLE].[MIRU12])))ORDER BY ENTRYTABLE.MIRU12"
End Sub

 

[jg95ae]

Anyone have any ideas for this? Desparate for help.

 

[Vonnie]

Since your row source is becoming complex, perhaps you should consider a union query.
Using your current rowsource as the first query, and a limiting query as the 2nd query.

 

[jg95ae]

I've never done a union query, I'm new to VBA and ms access so I'm learning as I go.

Any examples of how this is done?

Thanks