query from Form to Subform

[Forest14]

Hello,
Almost a newbie at VB and First time question in this forum!

I have a form "MAIN" with:

- 2 unbound textboxes ("startdate" and "endate")

- 1 optiongroup with 2 options (value "1" & "2")

- 1 unbound multiselect listbox "accountlist" shown if optiongroup value is "1" taking its (grouped) values from transactions table.

and a Subform "SUBFORM" with:-

account, transactiondate, amount fields taken from transactions table

I would like to get the result of a query in subform with the following variables:

- "transactiondate">="startdate" AND <="Endate" values taken from textboxes

- if optiongroup = 1, limit the result to those accounts selected in "accountlist" multiselect listbox

- if optiongroup = 2, select all accounts

Thank you very much for your precious help!

[NeoPa]

What SQL do you use to get all the info you want?
What are the names of the individual Options in the Option Group?

[Forest14]

What SQL do you use to get all the info you want?
What are the names of the individual Options in the Option Group?

I don't get your first question, do you mean how do i get the info on the main form?
If your question is related to the SQL needed to update the subform, it is definitely part of my request!

The individual options names in the option group are:
-option1account
-option2allaccounts

thanks a lot !

[NeoPa]

Thank you for the option names.
My first question meant :
What is the Record Source of the forms?
For subforms - what are the linked fields?
Please provide MetaData for any tables.

Here is an example of how to post table MetaData :
Posting Table/Dataset MetaData

Table Name=tblStudent
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
MaxMark; Numeric
MinMark; Numeric

[MMcCarthy]

Thank you for the option names.
My first question meant :
What is the Record Source of the forms?
For subforms - what are the linked fields?
Please provide MetaData for any tables.

Ade

I think the main form is unbound and he wants to use the controls to set the record source for the subform.

Mary

[MMcCarthy]

Firstly, you will need to add a command button to generate the records in the subform.

Create the subform to show all accounts of all dates i.e. the transactions table. Don't have any relationship i.e. master/child between the subform and the main form.

Then in the code behind the command button you will need the following:

Private Sub commandXX_Click()
Dim strSQL As String
Dim itemVal As Variant
Dim accList() As Variant
Dim count As Integer
 
   count = 0
 
   SELECT CASE optGroupName
   CASE 1
      For Each itemVal In Me.accountlist.ItemsSelected
         accList(count) = Me.accountlist.ItemData(itemVal)
         count = count + 1
      Next itemVal
 
      strSQL = "SELECT account, transactiondate, amount fields " & _
            "FROM transactions WHERE (transactiondate " & _
            "BETWEEN #" & Me.startdate & "# AND #" & Me.Endate & "#) " & _
            "AND account IN (" & accList & ");"
   CASE 2
      strSQL = "SELECT account, transactiondate, amount fields " & _
            "FROM transactions WHERE (transactiondate " & _
            "BETWEEN #" & Me.startdate & "# AND #" & Me.Endate & "#);"
   END SELECT
 
   Me.SubformName.RecordSouce = strSQL
   Me.SubformName.Requery
 
End Sub
 
 

Mary

[NeoPa]

Something like this should at least start you off :

Private Sub txtStartDate_AfterUpdate()
    Call SetSQL()
End Sub
 
Private Sub txtEndDate_AfterUpdate()
    Call SetSQL()
End Sub
 
'Add similar procedures for listbox & option group
 
Private Sub SetSQL()
    Dim strSQL As String, strWork As String
 
    strSQL = "SELECT *" & VbCrLf & _
             "FROM [Transactions]" & VbCrLf & _
             "WHERE ([TransactionDate] Between " & _
             Format(Me.StartDate,"\#m/d/yyyy\#") & _
             " And " & _
             Format(Me.EndDate,"\#m/d/yyyy\#") & ")"
    If option1account Then
        'In here put code to add items to a string (strWork)
        'formatted as a list separated by commas.
        'Single-quotes should be used to surround each list item
        strSQL = strSQL & " AND ([Account] In(" & strWork & "))"
    End If
    Forms!MainForm.[YourSubFormControl].RecordSource = strSQL
End Sub

[Forest14]

Hello guys,
thank you for your posts, were really helpful.

Here is the code I could get to work:

Private Sub SetSQL()
    Dim strSQL As String
    Dim strWhere As String
    Dim itemVal As Variant
    Dim accList() As Variant
    Dim count As Integer
    Dim lngLen As Long
    Dim messqge As String
 
    strSQL = ""
 
strSQL = "SELECT *" & vbCrLf & _
            "FROM [Transactions_table]" & vbCrLf & _
             "WHERE ([Trade date] Between " & _
             Format(Me.StartDate, "\#mm/dd/yyyy\#") & _
             " And " & _
             Format(Me.End_Date, "\#mm/dd/yyyy\#") & ")"
 
 
    If Forms![Transactions].[Accountselect] = 1 Then
 
        With Me.accountlist
        For Each itemVal In Me.accountlist.ItemsSelected
        If Not IsNull(itemVal) Then
        strWhere = strWhere & """" & .ItemData(itemVal) & """, "
        End If
        Next
        End With
        lngLen = Len(strWhere) - 2 'Without trailing comma and space.
        If lngLen > 0 Then
        strWhere = "[account] IN (" & Left$(strWhere, lngLen) & ")"
        strSQL = strSQL & " AND " & strWhere
        End If               
 
    End If
    strSQL = (strSQL & vbCrLf & "ORDER BY [Trade date];")
 
    Me.Transactions_Query_subform.Form.RecordSource = strSQL
 
End Sub

[MMcCarthy]

That's great.

I'm glad you got it working.

Mary

[NeoPa]

Yes, I'm pleased for you.
Particularly as you've picked up from the suggestions and taken it further by yourself :).