Loop dynamic query

[b82726272]

Hi,

I have created a dynamic query with the code below. This works fine for one record in a subform. But how can I execute the code for all the records in the subform? Imagine, if I have two records in the subform, one with function A and one with function B, how can I combine these in one query? Thank you for the help.



Kind regards,

Private Sub cmd_ButtonClick()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Set db = CurrentDb
    Set qdf = db.QueryDefs("Query")
    strSQL = "SELECT TABLE.FUNCTION " & _
             "FROM TABLE " & _
             "WHERE TABLE.FUNCTION='" & Me.FORM.Controls!ASS_FUNCTION.Value & "';"
    qdf.SQL = strSQL
    DoCmd.OpenQuery "Query"
    Set qdf = Nothing
    Set db = Nothing
End Sub

[ranman256]

Usu. records in a subform are from a query...use THAT query then link to it and execute your results qry.

[b82726272]

I have used the code below for the loop. But this doesn't work. Only the first function is in the loop. How can I set the subform as the datasource of the recordset?

Kind regards,

Private Sub cmdButton_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim rs As DAO.Recordset    
    Set db = CurrentDb
    Set qdf = db.QueryDefs("Query")
    Set rs = CurrentDb.OpenRecordset("TABLE")    
    If rs.Recordcount <> 0 Then
     rs.MoveFirst
     While Not rs.EOF
       strSQL = "SELECT TABLE.FUNCTION " & _
                "FROM TABLE " & _
                "WHERE TABLE.FUNCTION='" & Me.SUBFORM.Controls!ASS_FUNCTION.Value & "';"
       qdf.SQL = strSQL
      rs.MoveNext
     Wend
    End If
    DoCmd.OpenQuery "QUERY"
    rs.Close
    Set qdf = Nothing
    Set db = Nothing
    Set rs = Nothing
End Sub

[b82726272]

I have tested it with a count message and if I set the table of the main form or subform as datasource for the recordset I get all the records form the tables. But I want only to count the records of the subform. How can I do this? Between the main form and subform is an one to many relationship.

Kind regards,

[ipisors]

ranmain:
Not all forms are based on queries. Many people dynamically set recordsources in code..

b827(...)

What do you want to actually DO with the recordset after establishing it?

You can set a Recordset to any bound form's current recordsetclone at the moment:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = Form_PutYourFormNameHere.RecordsetClone

[b82726272]

Hi ipisors,

Thank you for the reaction. The form is not based on a query but on tables. I have made the form with the wizard. The problem is that I only want to get the records of the subform and not all records of the subtable. That is why I was thinking about a loop. Based on the data of the subform I want to execute a query.

Kind regards,

Based on this data I want to excecute a query.

ranmain:
Not all forms are based on queries. Many people dynamically set recordsources in code..

b827(...)

What do you want to actually DO with the recordset after establishing it?

You can set a Recordset to any bound form's current recordsetclone at the moment:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = Form_PutYourFormNameHere.RecordsetClone

[ipisors]

The problem is that I only want to get the records of the subform

I understand that, so use the code I provided you to substitute the name of your subform (it's a Form object, too, you know).
From that you'll have to decide what you want to DO with the data. I still don't understand that. "execute a query" isn't meaningful to me. If you want to select the data from the subform's recordset, then do what I posted. From there you'll need to decide what you want to do with that data.

[b82726272]

Hi ipisors,

Thank you for your help. I did what you posted and it works.

Kind regards,

[ipisors]

Glad you got it working to your satisfaction, good luck with the rest of the project.