Consulting

Results 1 to 9 of 9

Thread: Solved: Displaying a generated recordset in a subform

  1. #1
    VBAX Regular
    Joined
    Apr 2009
    Posts
    41
    Location

    Solved: Displaying a generated recordset in a subform

    What do you set the subform's source to in order to load a recordset created in VBA?

  2. #2
    VBAX Tutor
    Joined
    Nov 2007
    Posts
    257
    Location
    Quote Originally Posted by Touni102
    What do you set the subform's source to in order to load a recordset created in VBA?
    How is your mainform linked to the subfrom?

    Typically there is a mainform with a record source, and there are related data that are presented on the subform. There is an option to link the related parent and child fields.

  3. #3
    VBAX Regular
    Joined
    Apr 2009
    Posts
    41
    Location
    As of now, I dragged a query onto the Mainform and it created a Subform to display the query. Currently, the subform's Source Object is set to one of my query objects I have in My access file. However, I want to dynamically create SQL and run the sql in vba (using DoCmd.RunSQL) and set the results to display in the subform instead.

    The other way I would be able to do it, but I dont know how, is to have parameters in my SQL string to set conditions in the WHERE clause, but I don't know how to do that, or if it's possible.

    Right now in my vba code, i'm referencing the subform using:
    Forms![Software Search]![tblResult]

    "Software Search" is the name of the Mainform, "tblResult" is the name of the Subform.

  4. #4
    VBAX Tutor
    Joined
    Nov 2007
    Posts
    257
    Location
    Quote Originally Posted by Touni102
    As of now, I dragged a query onto the Mainform and it created a Subform to display the query. Currently, the subform's Source Object is set to one of my query objects I have in My access file. However, I want to dynamically create SQL and run the sql in vba (using DoCmd.RunSQL) and set the results to display in the subform instead.

    The other way I would be able to do it, but I dont know how, is to have parameters in my SQL string to set conditions in the WHERE clause, but I don't know how to do that, or if it's possible.

    Right now in my vba code, i'm referencing the subform using:
    Forms![Software Search]![tblResult]

    "Software Search" is the name of the Mainform, "tblResult" is the name of the Subform.
    I'm not exactly sure of what you're trying to do. Perhaps you could explain more.
    I am attaching a file of a typical form and subform - but this does not sound like your situation.

    Photo attached is an Order form, with an OrderDetails sub form.

    Sites that may help : See Martin Green's examples of setting up forms to adjust SQL etc.
    Martin Green http://www.fontstuff.com/access/acctut18.htm

    Crystal http://www.allenbrowne.com/casu-22.html

  5. #5
    VBAX Tutor
    Joined
    Nov 2007
    Posts
    257
    Location
    Quote Originally Posted by Touni102
    What do you set the subform's source to in order to load a recordset created in VBA?
    Are you asking how to use a Form/SubForm to display the data from a query?

  6. #6
    VBAX Regular
    Joined
    Apr 2009
    Posts
    41
    Location
    Thanks for the link, that has what I need to do. I already knew how to set up a subform to display data from a stored query, by just setting the Source Object to the query desired, however, that only works for stored queries. I created a sqlString dynamically and used DoCmd.RunSQL to get the recordset. I wanted to have the subform display that information. However, the link shows that you can have the subform set to a stored query, and change that query's SQL dynamically instead.

  7. #7
    VBAX Regular
    Joined
    Apr 2009
    Posts
    41
    Location
    SQL is working and successfully updating, but for some reason when I requery, nothing changes. When I restart the form the new data appears but what should I do to get it to update right away? I've used requery before on other cases, but now that I'm changing the query's sql command does that stop the display of the query? i dunno

  8. #8
    VBAX Tutor
    Joined
    Nov 2007
    Posts
    257
    Location
    You could try repaint.

  9. #9
    VBAX Regular
    Joined
    Apr 2009
    Posts
    41
    Location
    Ok.... i thought this problem was solved. I got far though. My sql string is generating correctly and the data displays when you open the Query directly. However in my form when I interact with the controls, that's when the Sql is Updated, and it should also update the subform too. I have done a requery on the subform before, leading me to think that once I get the sql down this is solved.

    Anyway this is what i have:
    [vba]Function UpdateSQL()
    Dim varItem As Variant
    Dim whereClause As String
    Dim negator As String
    If cbNOT.Value = 0 Then
    negator = ""
    Else
    negator = "NOT "
    End If
    For Each varItem In lbContains.ItemsSelected
    whereClause = whereClause & " OR [Computer Software].ProgramName='" & lbContains.ItemData(varItem) & "'"
    Next varItem
    If Len(whereClause) = 0 Then
    whereClause = ""
    Else
    whereClause = " AND " & negator & "(" & Right(whereClause, Len(whereClause) - 4) & ")"
    End If
    Dim sqlString As String
    sqlString = "SELECT [Computer Summary].ComputerName, [Computer Summary].Info, [Computer Summary].Active, [Computer Software].ProgramName, [Computer Software].Version, [Computer Software].InstallDate "
    sqlString = sqlString & "FROM [Computer Summary], [Computer Software] WHERE [Computer Summary].ComputerName=[Computer Software].ComputerName" & whereClause
    MsgBox sqlString

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef

    Set db = CurrentDb
    Set qdf = db.QueryDefs("Advanced Query")
    qdf.SQL = sqlString
    Set qdf = Nothing
    Set db = Nothing
    Forms![Advanced Search]![tblResult].Requery
    End Function[/vba]
    The subforms data source is "Advanced Query" which gets it's SQL updated from this code here, with the help of the link supplied above - thanks. When I change the checkbox or select a new selection this function is called. However, the Requery does nothing. I just want to update the query in the subform. Also, i dunno why but there is a bug - when I select only one item in the list box lbContains, lbContains.SelectedItems shows up with nothing...

    I'm so close to finishing this but so far from knowing what to do.... Any help again is appreciated.

    I just tried the Repaint, and it also didn't work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •