By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

query from Form to Subform

P: 10
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!
Dec 12 '06 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,308
What SQL do you use to get all the info you want?
What are the names of the individual Options in the Option Group?
Dec 12 '06 #2

P: 10
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 !
Dec 12 '06 #3

NeoPa
Expert Mod 15k+
P: 31,308
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
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblStudent
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. MaxMark; Numeric
  7. MinMark; Numeric
Dec 12 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 13 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub commandXX_Click()
  2. Dim strSQL As String
  3. Dim itemVal As Variant
  4. Dim accList() As Variant
  5. Dim count As Integer
  6.  
  7.    count = 0
  8.  
  9.    SELECT CASE optGroupName
  10.    CASE 1
  11.       For Each itemVal In Me.accountlist.ItemsSelected
  12.          accList(count) = Me.accountlist.ItemData(itemVal)
  13.          count = count + 1
  14.       Next itemVal
  15.  
  16.       strSQL = "SELECT account, transactiondate, amount fields " & _
  17.             "FROM transactions WHERE (transactiondate " & _
  18.             "BETWEEN #" & Me.startdate & "# AND #" & Me.Endate & "#) " & _
  19.             "AND account IN (" & accList & ");"
  20.    CASE 2
  21.       strSQL = "SELECT account, transactiondate, amount fields " & _
  22.             "FROM transactions WHERE (transactiondate " & _
  23.             "BETWEEN #" & Me.startdate & "# AND #" & Me.Endate & "#);"
  24.    END SELECT
  25.  
  26.    Me.SubformName.RecordSouce = strSQL
  27.    Me.SubformName.Requery
  28.  
  29. End Sub
  30.  
  31.  
Mary
Dec 13 '06 #6

NeoPa
Expert Mod 15k+
P: 31,308
Something like this should at least start you off :
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtStartDate_AfterUpdate()
  2.     Call SetSQL()
  3. End Sub
  4.  
  5. Private Sub txtEndDate_AfterUpdate()
  6.     Call SetSQL()
  7. End Sub
  8.  
  9. 'Add similar procedures for listbox & option group
  10.  
  11. Private Sub SetSQL()
  12.     Dim strSQL As String, strWork As String
  13.  
  14.     strSQL = "SELECT *" & VbCrLf & _
  15.              "FROM [Transactions]" & VbCrLf & _
  16.              "WHERE ([TransactionDate] Between " & _
  17.              Format(Me.StartDate,"\#m/d/yyyy\#") & _
  18.              " And " & _
  19.              Format(Me.EndDate,"\#m/d/yyyy\#") & ")"
  20.     If option1account Then
  21.         'In here put code to add items to a string (strWork)
  22.         'formatted as a list separated by commas.
  23.         'Single-quotes should be used to surround each list item
  24.         strSQL = strSQL & " AND ([Account] In(" & strWork & "))"
  25.     End If
  26.     Forms!MainForm.[YourSubFormControl].RecordSource = strSQL
  27. End Sub
Dec 13 '06 #7

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

Here is the code I could get to work:

Expand|Select|Wrap|Line Numbers
  1. Private Sub SetSQL()
  2.     Dim strSQL As String
  3.     Dim strWhere As String
  4.     Dim itemVal As Variant
  5.     Dim accList() As Variant
  6.     Dim count As Integer
  7.     Dim lngLen As Long
  8.     Dim messqge As String
  9.  
  10.     strSQL = ""
  11.  
  12. strSQL = "SELECT *" & vbCrLf & _
  13.             "FROM [Transactions_table]" & vbCrLf & _
  14.              "WHERE ([Trade date] Between " & _
  15.              Format(Me.StartDate, "\#mm/dd/yyyy\#") & _
  16.              " And " & _
  17.              Format(Me.End_Date, "\#mm/dd/yyyy\#") & ")"
  18.  
  19.  
  20.     If Forms![Transactions].[Accountselect] = 1 Then
  21.  
  22.         With Me.accountlist
  23.         For Each itemVal In Me.accountlist.ItemsSelected
  24.         If Not IsNull(itemVal) Then
  25.         strWhere = strWhere & """" & .ItemData(itemVal) & """, "
  26.         End If
  27.         Next
  28.         End With
  29.         lngLen = Len(strWhere) - 2 'Without trailing comma and space.
  30.         If lngLen > 0 Then
  31.         strWhere = "[account] IN (" & Left$(strWhere, lngLen) & ")"
  32.         strSQL = strSQL & " AND " & strWhere
  33.         End If               
  34.  
  35.     End If
  36.     strSQL = (strSQL & vbCrLf & "ORDER BY [Trade date];")
  37.  
  38.     Me.Transactions_Query_subform.Form.RecordSource = strSQL
  39.  
  40. End Sub
Dec 15 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
That's great.

I'm glad you got it working.

Mary
Dec 16 '06 #9

NeoPa
Expert Mod 15k+
P: 31,308
Yes, I'm pleased for you.
Particularly as you've picked up from the suggestions and taken it further by yourself :).
Dec 16 '06 #10

Post your reply

Sign in to post your reply or Sign up for a free account.