Complex query building in VBA 1

[Thingol]

Hi all,

I am trying to make a smart selection using SQL in VBA.

I have an address database in Access 2000. I have a table in which relations are made between persons in the database and groups they can belong to. Each person can belong to any number of groups and each group can contain any number of persons. I have a form from which people should be able to easily manage the groups in which a selection of people should reside. Here's a picture of the form:

I want the user to be able to select the groups to which the selected persons shown in the lower listbox (the user has already made the selection of persons beforehand), should be added. The code should then figure out if any of the relations that the user wants to make are already present in the table with relations, if so, these relations do not have to be made, all the ones that do not exist yet should be made. A relationship exists if the combination of GroupID/PersonID already exists. PersonID can be taken from the lower listbox and groupID from the upper listbox.

Now I tried doing all sorts of code on this, but I think SQL is the easiest way of doing it. I haven't however managed to create a working piece of code.

My question is: can anyone give me any ideas on how to approach this? Be creative! If you need more information, then please, let me know!

Thanks a lot in advance for any feedback or suggestions.

Best regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[TCARPENTER]

Martijn
Try this:

[URL unfurl="true"]http://www.mvps.org/access/resources/downloads.htm[/url]

Search for Find Record 2000.

This should give you some good ideas.

HTH
Todd

 

[Thingol]

Hi ,

Thanks Todd. I had that file for some time already, and it can be useful sometimes.

Maybe I should add some of the code I already have, because I think I may already be going in the right direction with this. I just didn't want anyone to have any bias when thinking of a solution. Anyway, here it goes:

Private Sub cmdAddSelection_Click()
'There are a lot of variables in the declarations that need
'to be removed or that are needed for code that is not
'posted here. I didn't make time to clean up this section
'with declarations yet.
Dim intGroupID() As Integer
Dim strGroupName() As String
Dim ctlSelection As Control
Dim ctlGroups As Control
Dim db As Database
Dim rst As Recordset
Dim rst2 As Recordset
Dim sql As String
Dim frm As Form
Dim strForm As String
Dim intCounter As Integer
Dim intCounter2 As Integer
Dim intCounter3 As Integer
Dim intCounter4 As Integer
Dim intCounter5 As Integer
Dim intCounter6 As Integer
Dim intCounter7 As Integer
Dim booAdd As Boolean
Dim booAlreadyExists() As Boolean
Dim intAlreadyExists() As Integer
Dim intNumberOfExistingRecords As Integer
Dim Response
Dim booClose As Boolean

Dim itm As Variant
Dim qrySelection As QueryDef
Dim strSelectedPersons As String
Dim strSelectedGroups As String
Dim qrySelectedGroups As QueryDef
Dim qryPersonPresentInTable As QueryDef
Dim qryRelationAlreadyExists As QueryDef
Dim strSQL As String
Dim strCriteria As String


Set ctlGroups = Me.lstGroups
Set ctlSelection = Me.lstSelection
Set db = CurrentDb()

ReDim Preserve intGroupID(ctlGroups.ItemsSelected.Count)
ReDim Preserve strGroupName(ctlGroups.ItemsSelected.Count)
'ReDim Preserve booAlreadyExists(Me.lstSelection.ListCount)

'Check if any groups are selected
If Not ctlGroups.ItemsSelected.Count = 0 Then
   intCounter = 0
   For Each itm In ctlGroups.ItemsSelected
      If Not ctlGroups.ItemData(itm) = 18 Then
         intGroupID(intCounter) = ctlGroups.ItemData(itm)
         strGroupName(intCounter) = ctlGroups.Column(0, itm)
         intCounter = intCounter + 1
      End If
   Next itm
Else
   strGroupName(0) = ""
End If
If strGroupName(0) = "" Then
   MsgBox ("You haven't selected any groups!")
   Exit Sub
End If

'Build a list of the selected groups and put it in a string (Name and ID).
For Each itm In ctlGroups.ItemsSelected
   If Len(strSelectedGroups) = 0 Then '0? The routine goes through the loop for the first time, hence no comma needs to be placed.
      strSelectedGroups = ctlGroups.ItemData(itm)
   Else
      strSelectedGroups = strSelectedGroups & "," & ctlGroups.ItemData(itm) 'The routine doesn't go through the loop for the first time, hence a comma needs to be placed.
   End If
Next itm
For intCounter = 1 To lstSelection.ListCount
   If Len(strSelectedPersons) = 0 Then '0? The routine goes through the loop for the first time, hence no comma needs to be placed.
      strSelectedPersons = ctlSelection.Column(0, intCounter)
   Else
      strSelectedPersons = strSelectedPersons & "," & ctlSelection.Column(0, intCounter) 'The routine doesn't go through the loop for the first time, hence a comma needs to be placed.
   End If
Next intCounter

'Build SQL statements
Set db = CurrentDb()
Set qryPersonPresentInTable = db.QueryDefs("qryPersonPresentInTable")
Set qryRelationAlreadyExists = db.QueryDefs("qryRelationAlreadyExists")

'Creation of sql based on the selection strings:
'Persons in selection that already exist in the table with person-group relations:
strSQL = "SELECT * FROM tblPersonsAndGroups WHERE (((tblPersonsAndGroups.PersonID) In (" & strSelectedPersons & ")));"
qryPersonPresentInTable.sql = strSQL
'Selected groups that already exist in the above query:
strSQL = "SELECT * FROM qryPersonPresentInTable WHERE (((qryPersonPresentInTable.PersonID) In (" & strSelectedGroups & ")));"
qryRelationAlreadyExists.sql = strSQL

'Fill temporary table with the relations that have to be made between persons and groups.
strSQL = "INSERT INTO tblPersonsAndGroupsTemp (PersonID, GroupID, Name_Group) SELECT tblPersonsAndGroups.PersonID, tblPersonsAndGroups.GroupID, tblPersonsAndGroups.Name_Group FROM tblPersonsAndGroups WHERE (tblPersonsAndGroups.PersonID NOT IN (qryRelationAlreadyExists.PersonID))"
[highlight]CurrentDb.Execute strSQL, dbFailOnError[/highlight]
Exit Sub

'Close Querydef
qryPersonPresentInTable.Close
qryRelationAlreadyExists.Close

'I wil add more code here to actually do something with the data in the temporary table (I'm going to open a form for which the table is a source), but the above part is the part where the difficulties present themselves.

End Sub

The error that occurs upon runtime is:
Runtime error 3061:
Too few parameters. Expected 1.

The error occurs in the highlighted segment of code. I think the actual error lies in the definition of strSQL.

Can anyone tell if my approach for the above problem is in the right direction?
What is wrong in the code above?

Thanks a lot in advance for any help!

Best regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[PHV]

Replace this:
NOT IN (qryRelationAlreadyExists.PersonID))"
By this:
NOT IN (SELECT PersonID FROM qryRelationAlreadyExists))"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Thingol]

Thanks PH,

The error message is gone!

Martijn.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--