Query two subforms from 3 combo boxes

  • Thread starter Thread starter Raymond
  • Start date Start date
R

Raymond

I am having a problem quering two subforms from 3 combo boxesHi, Basically I
have a form that contains two sub forms and each sub form has its own
individual query. On the main form I have 3 unbound combo boxes (Item #, date
received and date shipped) that I have the user select and depending on their
selection the two sub forms should display the matching data. The first sub
form contains the "received" information based a query and the second sub
form contains the "shipped" information based on a different query. I have
linked the sub forms using the Link Master/Child fields dialogue box. For
example, if the user selects Item # 101 and received date 1/1/10 and shipped
date 1/31/10. I would like all the records for item 101 that were received on
or after 1/1/10 and shipped on or before 1/31/10 to appear. But all I get is
a compile error. The Main form is call "Inventory" and the two sub forms are
called "Received Information" and "Shipped Information". Any help would be
greatly appreciated. I have the following code but I am getting a run time
2465 error on the second to the last line (Me.[qry Received
Information].Form.RecordSource = strSQLReceived).



Private Sub Command61_Click()
Dim strSQLReceived As String
Dim strSQLShipped As String
Dim strWhere As String
strWhere = " Where 1=1 "
strSQLReceived = "SELECT * FROM [qry Received Information]"
strSQLShipped = "SELECT * FROM [qry Shipped Information]"
If Not IsNull(Me.cboItemNum) Then
strWhere = strWhere & " AND ItemCode = " & _
Me.cboItemNum & " "
End If
If Not IsNull(Me.cboRecvd) Then
strWhere = strWhere & " AND [Date Received] >= #" & _
Me.cboRecvd & "# "
End If
If Not IsNull(Me.cboShipped) Then
strWhere = strWhere & " AND [Date Shipped] <= #" & _
Me.cboShipped & "# "
End If
strSQLReceived = strSQL & strWhere & " ORDER BY Receiving.[Date Received];"
strSQLShipped = strSQL & strWhere & " ORDER BY Shipping.[Date Shipped];"
Me.[qry Received Information].Form.RecordSource = strSQLReceived
Me.[qry Shipped Information].Form.RecordSource = strSQLShipped

End Sub
 
Raymond said:
I am having a problem quering two subforms from 3 combo boxesHi, Basically I
have a form that contains two sub forms and each sub form has its own
individual query. On the main form I have 3 unbound combo boxes (Item #, date
received and date shipped) that I have the user select and depending on their
selection the two sub forms should display the matching data. The first sub
form contains the "received" information based a query and the second sub
form contains the "shipped" information based on a different query. I have
linked the sub forms using the Link Master/Child fields dialogue box. For
example, if the user selects Item # 101 and received date 1/1/10 and shipped
date 1/31/10. I would like all the records for item 101 that were received on
or after 1/1/10 and shipped on or before 1/31/10 to appear. But all I get is
a compile error. The Main form is call "Inventory" and the two sub forms are
called "Received Information" and "Shipped Information". Any help would be
greatly appreciated. I have the following code but I am getting a run time
2465 error on the second to the last line (Me.[qry Received
Information].Form.RecordSource = strSQLReceived).

Private Sub Command61_Click()
Dim strSQLReceived As String
Dim strSQLShipped As String
Dim strWhere As String
strWhere = " Where 1=1 "
strSQLReceived = "SELECT * FROM [qry Received Information]"
strSQLShipped = "SELECT * FROM [qry Shipped Information]"
If Not IsNull(Me.cboItemNum) Then
strWhere = strWhere & " AND ItemCode = " & _
Me.cboItemNum & " "
End If
If Not IsNull(Me.cboRecvd) Then
strWhere = strWhere & " AND [Date Received] >= #" & _
Me.cboRecvd & "# "
End If
If Not IsNull(Me.cboShipped) Then
strWhere = strWhere & " AND [Date Shipped] <= #" & _
Me.cboShipped & "# "
End If
strSQLReceived = strSQL & strWhere & " ORDER BY Receiving.[Date Received];"
strSQLShipped = strSQL & strWhere & " ORDER BY Shipping.[Date Shipped];"
Me.[qry Received Information].Form.RecordSource = strSQLReceived
Me.[qry Shipped Information].Form.RecordSource = strSQLShipped

End Sub
Click to expand...


Which compile error? If you did not use the Debug.Compile
menu item before testing the code, do so, it will provide
more information than compiling on the fly.

At thisn point, the only thing I can say is to double check
the subform CONTROLs are named [qry Received Information]
and [qry Shipped Information]. A subform control name may
be different from the name of the form object it will
display.
 
Marshall Thank you for your help. I do not know much about VB but I did
compile it using the Debug/Compile command and it continued to hang on
"Me.[qry Received Information].Form.RecordSource = strSQLReceived" and I am
sure it will hang on the next line of code too. When you say subform control
are you referring to the name of the subform or the name of the query that
the subform uses? What is that second to the last line referring too (someone
told me to use it) when they refer to [Received Information] is that the form
or the query name?

Thanks in Advance.

Raymond

Marshall Barton said:
Raymond said:
I am having a problem quering two subforms from 3 combo boxesHi, Basically I
have a form that contains two sub forms and each sub form has its own
individual query. On the main form I have 3 unbound combo boxes (Item #, date
received and date shipped) that I have the user select and depending on their
selection the two sub forms should display the matching data. The first sub
form contains the "received" information based a query and the second sub
form contains the "shipped" information based on a different query. I have
linked the sub forms using the Link Master/Child fields dialogue box. For
example, if the user selects Item # 101 and received date 1/1/10 and shipped
date 1/31/10. I would like all the records for item 101 that were received on
or after 1/1/10 and shipped on or before 1/31/10 to appear. But all I get is
a compile error. The Main form is call "Inventory" and the two sub forms are
called "Received Information" and "Shipped Information". Any help would be
greatly appreciated. I have the following code but I am getting a run time
2465 error on the second to the last line (Me.[qry Received
Information].Form.RecordSource = strSQLReceived).

Private Sub Command61_Click()
Dim strSQLReceived As String
Dim strSQLShipped As String
Dim strWhere As String
strWhere = " Where 1=1 "
strSQLReceived = "SELECT * FROM [qry Received Information]"
strSQLShipped = "SELECT * FROM [qry Shipped Information]"
If Not IsNull(Me.cboItemNum) Then
strWhere = strWhere & " AND ItemCode = " & _
Me.cboItemNum & " "
End If
If Not IsNull(Me.cboRecvd) Then
strWhere = strWhere & " AND [Date Received] >= #" & _
Me.cboRecvd & "# "
End If
If Not IsNull(Me.cboShipped) Then
strWhere = strWhere & " AND [Date Shipped] <= #" & _
Me.cboShipped & "# "
End If
strSQLReceived = strSQL & strWhere & " ORDER BY Receiving.[Date Received];"
strSQLShipped = strSQL & strWhere & " ORDER BY Shipping.[Date Shipped];"
Me.[qry Received Information].Form.RecordSource = strSQLReceived
Me.[qry Shipped Information].Form.RecordSource = strSQLShipped

End Sub
Click to expand...


Which compile error? If you did not use the Debug.Compile
menu item before testing the code, do so, it will provide
more information than compiling on the fly.

At thisn point, the only thing I can say is to double check
the subform CONTROLs are named [qry Received Information]
and [qry Shipped Information]. A subform control name may
be different from the name of the form object it will
display.
Click to expand...
 
Raymond said:
Marshall Thank you for your help. I do not know much about VB but I did
compile it using the Debug/Compile command and it continued to hang on
"Me.[qry Received Information].Form.RecordSource = strSQLReceived" and I am
sure it will hang on the next line of code too. When you say subform control
are you referring to the name of the subform or the name of the query that
the subform uses? What is that second to the last line referring too (someone
told me to use it) when they refer to [Received Information] is that the form
or the query name?
Click to expand...


Controls are the thingies that you put on a forms and
reports to display stuff (don't call them fields). Text
boxes, Labels, Check boxes, etc. are a few of the different
kinds of controls.

A subform control is a thingie on the main form that
displays a form object. It is neither the form that will be
displayed nor a query. You can find out what the control's
name is by opening the main report in design view, clicking
once in the subreport area and then view the property sheet.
The Name property is the first one under either the Other or
All tab and may or may not be the same as the form object
that will be displayed.
 
Back
Top