Open a form with a recordsource based on a selection in a listbox 1

[Thingol]

Hi All,

I have a database in which I have a form containing a listbox. When a user selects some rows in the listbox and then hits a commandbutton, he should be taken to a new form, which has its recordsource turned into the records that correspond with the selection from the listbox. Here's the code I have so far:

Private Sub cmdChangeGroupProperties_Click()
Dim Criteria As String
Dim ctl As Control
Dim itm As Variant
Dim db As Database
Dim Q As QueryDef
Dim strSQL As String
Dim sql As String
Dim strQSQL As String
Dim frm As Form
Dim strForm As String

' Build a list of the selections.
Set ctl = Me.lstGroups

'If a selection is made, build a list of the selections
If Not ctl.ItemsSelected.Count = 0 Then
   'Build list
   For Each itm In ctl.ItemsSelected
      If Len(Criteria) = 0 Then
         Criteria = ctl.ItemData(itm)
      Else
         Criteria = Criteria & "," & ctl.ItemData(itm)
      End If
   Next itm
   
   'Apply list to query
   Set db = CurrentDb()
   Set Q = db.QueryDefs("qrySelectedGroups")
   
   strSQL = "SELECT * FROM tblGroups WHERE (((tblGroups.GroupID) In (" & Criteria & "))) ORDER BY tblGroups.Name;"
   Q.sql = strSQL
   
   'Apply query to form
   DoCmd.OpenForm "frmEnterGroups" , , , , , acHidden
   strForm = "EnterGroups"
   Set frm = Forms(strForm)
   With frm
      .RecordSource = Q.sql
      .Caption = "Change properties of selected groups"
      .ScrollBars = 0
      .RecordSelectors = False
      .NavigationButtons = True
      .DividingLines = False
   End With
   
   'Form is now correctly formatted and has the right
   'recordsource; now close and save it and re-open it
   'as a dialog.
   DoCmd.Close acForm, "frmEnterGroups", acSaveYes
   DoCmd.OpenForm "frmEnterGroups", acNormal, , , acFormEdit, acDialog
   
   'Close Querydef
   Q.Close
'If no rows have been selected, alert the user and exit
'the sub.
Else
   MsgBox ("You have not selected any groups!")
End If

End Sub

The form should now display the selected records only, but instead, it shows all the records in tblGroups. Does anyone understand why, and what I should do to actually make it show only those records that I want to show?

Kind regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[jaydeebe]

Do you have to use vba? Can't you set up a parameter query that takes it's parameters from the list box?

 

[Thingol]

I think I have to use VBA. The form I'm calling ("frmEnterGroups") normally has a different recordsource than during this routine. It should get its original recordsource and properties returned after this routine is run. I hadn't got that far yet though.

Anyone have any clues on how to tackle this issue?

Best regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[FancyPrairie]

First, I'm assuming that this strForm = "EnterGroups" is a typeo error. Shouldn't it be "frmEnterGroups"?

2nd, when you close the form, all of the changes you made to its properties are lost. You might try requering the form (i.e. frm.requery) rather than closing and reopening it.

 

[HerrKatz]

Why don´t you use the WhereCondition-Variable included in the Docmd.OpenForm-method, e.g.:

strSQL = "WHERE (((tblGroups.GroupID) In (" & Criteria & "))) ORDER BY tblGroups.Name;"
'Apply Where-Condition to form
DoCmd.OpenForm "frmEnterGroups" , , , strSQL

As long as you are referring to the same table in the form´s recordsource (=tblgroups) this should work fine (without any other queries).

 

[FancyPrairie]

HerrKatz, thought about that but it appears that Thingol is setting some other properties of the form (i.e. caption, scrollbars, etc). Although the properties could be set in the OnOpen event of the form.

 

[Thingol]

Hi all,

First of all, thanks for the replies!

Indeed, EnterGroups is a typo (I have translated everything from Dutch, so in my actual database, the typo isn't there). I am indeed setting other properties as well, and since I also have to be able the form from other places in the database (with different settings!), I will have to set the properties within this code.

I will try requerying the form first thing on monday.

Thanks again.

Best regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[Thingol]

But two things though:

1) I have selected the option acSaveYes; shouldn't the properties of the form be saved upon closing the form?

2) When I reopen the form, I open it with the option acDialog. Now this is important, as it will need to stop the code untill the user is done with the form, and then finish off the code after the user has closed the form (I will add more code to the end of the sub when I have this problem fixed). I don't think I will be able to reopen the form with the acDialog option with the Requery function, or can I?

Regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[willir]

Thingol

A very interesting approach.

Yes, the requery is useful

Personally, I would not save the hidden form. I would have kept it open and then used a requery.

I have never tried saving the form er your code -- again, interesting. What happens if some one else opens the form -- they may get the previous stored results.

Next, openning the form with a "WhereCondition" may be a better approach.

Again, personally, I prefer t use the OpenArgs feature instead. On the target form, I test for the OpenArgs -- if present, I apply the requried filter, it not, I open it with all records.

Your coding is quite good. Here is a sample of some code I use all the time for table I use for storing variables. The idea is that I can open the form from numerous points of the application -- so I restrict the view depending on where the form is opened from...

Private Sub Form_Load()
Option Explicit

Dim strOpenArgs As String, strQ As String, strSQL As String
Dim booLock As Boolean

Me.LockForm = False
booLock = False
strOpenArgs = ""
strQ = Chr$(34)

If Len(Nz(Me.OpenArgs, "")) > 0 Then
    strOpenArgs = Me.OpenArgs
    If DCount("gvType", "GetVar", "gvtype = " & strQ & strOpenArgs & strQ) Then
        booLock = True
    End If
End If

If booLock Then
    Me.LockForm = True
    Me.gvType.DefaultValue = strOpenArgs
    'Me.gvType = strOpenArgs
    Me.gvType.Visible = False
    Me.CategoryQry.Visible = False
    
    Me.Filter = "gvType = " & strQ & strOpenArgs & strQ
    Me.FilterOn = True
    
Else
        
    If InStr(1, "probcode resolcode devfeature", strOpenArgs) Then
        'special case where complex type used
        strSQL = "SELECT * from GetVar WHERE gvType Like " & strQ & strOpenArgs & "*" & strQ
        Me.RecordSource = strSQL
        Me.Requery
        Me.LockForm = True
    Else
    
        Me.LockForm = False
        Me.gvType.Visible = True
        Me.CategoryQry.Visible = True
    End If
End If

End Sub

Calling code examples...

Private Sub cmdResolutionType_Click()

    Dim stDocName As String, strGvType As String

    stDocName = "frmGetVar"
    strGvType = "resoltype"
    
    OpenGetVar stDocName, strGvType
    Me.ResolutionType.Requery

End Sub

Private Sub cmdSLALevel_Click()

    Dim stDocName As String, strGvType As String

    stDocName = "frmGetVar"
    strGvType = "slacode"
    
    OpenGetVar stDocName, strGvType
    Me.SLACode.Requery

End Sub

And the OpengetVar

Public Sub OpenGetVar(stDocName As String, strGvType As String)

    DoCmd.OpenForm stDocName, , , , , , strGvType
    
End Sub

Richard

 

[Thingol]

Hi Willir,

I have used the openargs property to open the form and everything works now, although I have taken a slightly different approach as you posted (to be honest, I didn't fully understand what everything did in your code; if you could add step-by-step comments, it would begreat). I gave you a star for getting me on the right track with this. Here's the code I have use now (I didn't translate from Dutch, but I think it will still be understandable):

Private Sub form_load()
Dim strSQL As String
Dim booSelectie As Boolean

Me.RecordSource = ""
Me.Requery

booSelectie = Me.OpenArgs Like "*,*"

Select Case booSelectie

Case True
   ' Build a list of the selections.
   strSQL = "SELECT * FROM tblGroepen WHERE (((tblGroepen.GroepID) In (" & Me.OpenArgs & "))) ORDER BY tblGroepen.Naam;"
   With Me
      .RecordSource = strSQL
      .Caption = "Eigenschappen van de geselecteerde groepen wijzigen"
      .AllowAdditions = False
      .DataEntry = False
      .Requery
   End With
Case False
   Select Case Me.OpenArgs
   Case "Toevoegen"
      With Me
         .RecordSource = "tblGroepen"
         .Caption = "Groepen invoeren of wijzigen"
         .AllowAdditions = True
         .DataEntry = True
         .Requery
      End With
   End Select
End Select

Private Sub cmdEigenschappenGroepenWijzigen_Click()
Dim Criteria As String
Dim ctl As Control
Dim itm As Variant

' Build a list of the selections.
Set ctl = Me.lstGroepen

If Not ctl.ItemsSelected.Count = 0 Then
   For Each itm In ctl.ItemsSelected
      If Len(Criteria) = 0 Then
         MsgBox (ctl.ItemData(itm))
         Criteria = ctl.ItemData(itm)
      Else
         MsgBox (ctl.ItemData(itm))
         Criteria = Criteria & "," & ctl.ItemData(itm)
      End If
   Next itm
   DoCmd.OpenForm "frmgroepeninvoeren", , , , , acDialog, Criteria
   ctl.Requery
Else
   MsgBox ("U heeft geen groepen geselecteerd!")
End If

End Sub

Private Sub cmdGroepToevoegen_Click()
DoCmd.OpenForm "frmgroepeninvoeren", , , , , acNormal, "Toevoegen"
End Sub

Kind regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[FancyPrairie]

1. Changes you make to the property of a form are not saved when you close the form. Unless the form was opened in design view.

2. For future reference, you might want to take a look at this FAQ FAQ181-5497. It contains a function that builds the where clause without the word where. It works on forms that contain 0 to many list boxes, combo boxes, text boxes, ranges, option groups, and check boxes.