Re-open criteria-based form upon clicking field in same form

G

Guest

I've searched the past posts, and haven't been able to find a solution to
this specific problem.

Here's what I have:

1 form with combo boxes that opens a "search results" form.
This "results" form is based on a query who's criteria is based on those
previous combo boxes.

What I need is this example:

A user runs the "search" form, searching for all records that occur on a
certain date. One field in the "results" form is "company". I need a user
then to be able to click on a specific company, and it returns that same
"results" form, with all of the records that contain that company, and
obviously, all dates are now included, and the previous search query is
disregarded.

I need the "results" form to reprocess it's query to display the new
results. However, that query's criteria is based on the combo boxes in the
"search" form.

What are my options?

Is there a way in VBA to reprocess the "search" form and automatically fill
in the combo box? However, the combo boxes are not bound.

Thanks in advance.
 
G

Guest

You can set the RecordSource property of the results form to an SQL statement
which returns all rows in the underlying tables(s) where the Company column
matches the value of the current company, and then requery the form. I'd
suggest using the DblClick event procedure of the Company control rather than
the Click event procedure; its less prone to being inadvertently triggered.

Lets assume a simple example of a form of Orders based on a simple query
joining a Companies table and an Orders table on CompanyID columns, this
being the primary key of the Companies table and the foreign key in Orders
which references it. The company name is in a column Company in the
Companies table. The code for the event procedure would go something like
this:

Dim strCriteria As String, strSQL As String

strCriteria = "Company = """ & Me.Company & """"
strSQL = "SELECT Company, CompanyAddress, OrderDate " & _
"FROM Companies INNER JOIN Orders " & _
"ON Companies.CompanyID = Orders.OrderID " & _
"WHERE " & strCriteria

Me.RecordSource = strSQL
Me.Requery

If you want to return to the original set of records determined by the
selections in the combo boxes in the search form then you could have a button
on the results form to do this with the following code in its Click event
procedure:

Me.RecordSource = "YourQuery"
Me.Requery

where YourQuery is the name of the query which references the combo boxes on
the search from as parameters. The user could then double click on another
company to return its orders.
 
G

Guest

Thanks for the quick reply.

I put that sql code into the dblclick event procedure. The recordsource for
the form still needs to be that original query that is based on the combo
boxes, correct?
When I click, here's what comes up:

Run-time error '3122'
You tried to execute a query that does not include the specified expression
'Ship Date" as part of an aggregate function.

Ship date is a field in the table that is being requeried.

Any idea why this is happening?
 
G

Guest

Yep, you leave the Results form's RecordSource as it is. It sounds like the
error is in the SQL statement you are building in the DblClick event
procedure. Post back:

a. The SQL of the query which is the Results form's existing RecordSoure.
b. The full code you are using in the DdlClick event procedure.
 
G

Guest

Let me know what you find. Thanks for the help!

1.
SELECT [LINE HAUL 2005].[Ship Date], Avg([LINE HAUL 2005].[Rail Cost]) AS
[AvgOfRail Cost], [LINE HAUL 2005].[Rail Line Haul], [LINE HAUL 2005].[Load
Number], [LINE HAUL 2005].[Origin Ramp City], [LINE HAUL 2005].[Origin Ramp
State], [LINE HAUL 2005].[Dest Ramp State], [LINE HAUL 2005].[Dest Ramp City]
FROM [LINE HAUL 2005]
GROUP BY [LINE HAUL 2005].[Ship Date], [LINE HAUL 2005].[Rail Line Haul],
[LINE HAUL 2005].[Load Number], [LINE HAUL 2005].[Origin Ramp City], [LINE
HAUL 2005].[Origin Ramp State], [LINE HAUL 2005].[Dest Ramp State], [LINE
HAUL 2005].[Dest Ramp City]
HAVING ((([LINE HAUL 2005].[Rail Line Haul]) Like "*" & [Forms]![LINK UP
FORM]![CARRIER BOX]) AND (([LINE HAUL 2005].[Origin Ramp City]) Like "*" &
[Forms]![LINK UP FORM]![ORIG CITY BOX] Or ([LINE HAUL 2005].[Origin Ramp
City]) Is Null) AND (([LINE HAUL 2005].[Origin Ramp State]) Like "*" &
[Forms]![LINK UP FORM]![ORIG state BOX] Or ([LINE HAUL 2005].[Origin Ramp
State]) Is Null) AND (([LINE HAUL 2005].[Dest Ramp State]) Like "*" &
[Forms]![LINK UP FORM]![dest state BOX] Or ([LINE HAUL 2005].[Dest Ramp
State]) Is Null) AND (([LINE HAUL 2005].[Dest Ramp City]) Like "*" &
[Forms]![LINK UP FORM]![dest CITY BOX] Or ([LINE HAUL 2005].[Dest Ramp City])
Is Null))
ORDER BY [LINE HAUL 2005].[Rail Line Haul];


2.
Private Sub CARRIER_BOX_DblClick(Cancel As Integer)

Dim strCriteria As String
Dim strSQL As String

strCriteria = "Rail_Line_Haul = """ & Me.[CARRIER BOX] & """"
strSQL = "SELECT [LINE HAUL 2005].[Ship Date], Avg([LINE HAUL
2005].[Rail Cost]) AS [AvgOfRail Cost], [LINE HAUL 2005].[Rail Line Haul],
[LINE HAUL 2005].[Load Number], [LINE HAUL 2005].[Origin Ramp City], [LINE
HAUL 2005].[Origin Ramp State], [LINE HAUL 2005].[Dest Ramp State], [LINE
HAUL 2005].[Dest Ramp City] " & _
"FROM [LINE HAUL 2005]" & _
"WHERE " & strCriteria

Me.RecordSource = strSQL
Me.Requery

End Sub
 
G

Guest

You are missing the GROUP BY clause from the original query. The SQL
statement which you build in the code should be everything from the original
query apart from the HAVING clause, plus the new WHERE clause (strCriteria)
to restrict the result set to the selected company:

strSQL = "SELECT [LINE HAUL 2005].[Ship Date], AVG([LINE HAUL 2005].[Rail
Cost]) " & _
"AS [AvgOfRail Cost], [LINE HAUL 2005].[Rail Line Haul], " & _
"[LINE HAUL 2005].[Load Number], [LINE HAUL 2005].[Origin Ramp City], "
& _
"[LINE HAUL 2005].[Origin Ramp State], [LINE HAUL 2005].[Dest Ramp
State], " & _
"[LINE HAUL 2005].[Dest Ramp City] FROM [LINE HAUL 2005] WHERE " & _
strCriteria & " GROUP BY [LINE HAUL 2005].[Ship Date], " & _
"[LINE HAUL 2005].[Rail Line Haul],[LINE HAUL 2005].[Load Number], " & _
"[LINE HAUL 2005].[Origin Ramp City], [LINE HAUL 2005].[Origin Ramp
State], " & _
"[LINE HAUL 2005].[Dest Ramp State], [LINE HAUL 2005].[Dest Ramp City]
" & _
"ORDER BY [LINE HAUL 2005].[Rail Line Haul]"

Aaron said:
Let me know what you find. Thanks for the help!

1.
SELECT [LINE HAUL 2005].[Ship Date], Avg([LINE HAUL 2005].[Rail Cost]) AS
[AvgOfRail Cost], [LINE HAUL 2005].[Rail Line Haul], [LINE HAUL 2005].[Load
Number], [LINE HAUL 2005].[Origin Ramp City], [LINE HAUL 2005].[Origin Ramp
State], [LINE HAUL 2005].[Dest Ramp State], [LINE HAUL 2005].[Dest Ramp City]
FROM [LINE HAUL 2005]
GROUP BY [LINE HAUL 2005].[Ship Date], [LINE HAUL 2005].[Rail Line Haul],
[LINE HAUL 2005].[Load Number], [LINE HAUL 2005].[Origin Ramp City], [LINE
HAUL 2005].[Origin Ramp State], [LINE HAUL 2005].[Dest Ramp State], [LINE
HAUL 2005].[Dest Ramp City]
HAVING ((([LINE HAUL 2005].[Rail Line Haul]) Like "*" & [Forms]![LINK UP
FORM]![CARRIER BOX]) AND (([LINE HAUL 2005].[Origin Ramp City]) Like "*" &
[Forms]![LINK UP FORM]![ORIG CITY BOX] Or ([LINE HAUL 2005].[Origin Ramp
City]) Is Null) AND (([LINE HAUL 2005].[Origin Ramp State]) Like "*" &
[Forms]![LINK UP FORM]![ORIG state BOX] Or ([LINE HAUL 2005].[Origin Ramp
State]) Is Null) AND (([LINE HAUL 2005].[Dest Ramp State]) Like "*" &
[Forms]![LINK UP FORM]![dest state BOX] Or ([LINE HAUL 2005].[Dest Ramp
State]) Is Null) AND (([LINE HAUL 2005].[Dest Ramp City]) Like "*" &
[Forms]![LINK UP FORM]![dest CITY BOX] Or ([LINE HAUL 2005].[Dest Ramp City])
Is Null))
ORDER BY [LINE HAUL 2005].[Rail Line Haul];


2.
Private Sub CARRIER_BOX_DblClick(Cancel As Integer)

Dim strCriteria As String
Dim strSQL As String

strCriteria = "Rail_Line_Haul = """ & Me.[CARRIER BOX] & """"
strSQL = "SELECT [LINE HAUL 2005].[Ship Date], Avg([LINE HAUL
2005].[Rail Cost]) AS [AvgOfRail Cost], [LINE HAUL 2005].[Rail Line Haul],
[LINE HAUL 2005].[Load Number], [LINE HAUL 2005].[Origin Ramp City], [LINE
HAUL 2005].[Origin Ramp State], [LINE HAUL 2005].[Dest Ramp State], [LINE
HAUL 2005].[Dest Ramp City] " & _
"FROM [LINE HAUL 2005]" & _
"WHERE " & strCriteria

Me.RecordSource = strSQL
Me.Requery

End Sub
Click to expand...
 
G

Guest

Now it says "Syntax error in WHERE clause". I copied the code and pasted it in.
I've tried manipulating the code to try and fix it, but nothing has been
able to solve the problem.


strSQL = "SELECT [LINE HAUL 2005].[Ship Date], AVG([LINE HAUL
2005].[Rail Cost]) " & _
"AS [AvgOfRail Cost], [LINE HAUL 2005].[Rail Line Haul], " & _
"[LINE HAUL 2005].[Load Number], [LINE HAUL 2005].[Origin Ramp City], "
& _
"[LINE HAUL 2005].[Origin Ramp State], [LINE HAUL 2005].[Dest Ramp
State], " & _
"[LINE HAUL 2005].[Dest Ramp City] FROM [LINE HAUL 2005] WHERE " & _
strCriteria & "GROUP BY [LINE HAUL 2005].[Ship Date], " & _
"[LINE HAUL 2005].[Rail Line Haul],[LINE HAUL 2005].[Load Number], " & _
"[LINE HAUL 2005].[Origin Ramp City], [LINE HAUL 2005].[Origin Ramp
State], " & _
"[LINE HAUL 2005].[Dest Ramp State], [LINE HAUL 2005].[Dest Ramp City]"
& _
"ORDER BY [LINE HAUL 2005].[Rail Line Haul]"




Ken Sheridan said:
You are missing the GROUP BY clause from the original query. The SQL
statement which you build in the code should be everything from the original
query apart from the HAVING clause, plus the new WHERE clause (strCriteria)
to restrict the result set to the selected company:

strSQL = "SELECT [LINE HAUL 2005].[Ship Date], AVG([LINE HAUL 2005].[Rail
Cost]) " & _
"AS [AvgOfRail Cost], [LINE HAUL 2005].[Rail Line Haul], " & _
"[LINE HAUL 2005].[Load Number], [LINE HAUL 2005].[Origin Ramp City], "
& _
"[LINE HAUL 2005].[Origin Ramp State], [LINE HAUL 2005].[Dest Ramp
State], " & _
"[LINE HAUL 2005].[Dest Ramp City] FROM [LINE HAUL 2005] WHERE " & _
strCriteria & " GROUP BY [LINE HAUL 2005].[Ship Date], " & _
"[LINE HAUL 2005].[Rail Line Haul],[LINE HAUL 2005].[Load Number], " & _
"[LINE HAUL 2005].[Origin Ramp City], [LINE HAUL 2005].[Origin Ramp
State], " & _
"[LINE HAUL 2005].[Dest Ramp State], [LINE HAUL 2005].[Dest Ramp City]
" & _
"ORDER BY [LINE HAUL 2005].[Rail Line Haul]"

Aaron said:
Let me know what you find. Thanks for the help!

1.
SELECT [LINE HAUL 2005].[Ship Date], Avg([LINE HAUL 2005].[Rail Cost]) AS
[AvgOfRail Cost], [LINE HAUL 2005].[Rail Line Haul], [LINE HAUL 2005].[Load
Number], [LINE HAUL 2005].[Origin Ramp City], [LINE HAUL 2005].[Origin Ramp
State], [LINE HAUL 2005].[Dest Ramp State], [LINE HAUL 2005].[Dest Ramp City]
FROM [LINE HAUL 2005]
GROUP BY [LINE HAUL 2005].[Ship Date], [LINE HAUL 2005].[Rail Line Haul],
[LINE HAUL 2005].[Load Number], [LINE HAUL 2005].[Origin Ramp City], [LINE
HAUL 2005].[Origin Ramp State], [LINE HAUL 2005].[Dest Ramp State], [LINE
HAUL 2005].[Dest Ramp City]
HAVING ((([LINE HAUL 2005].[Rail Line Haul]) Like "*" & [Forms]![LINK UP
FORM]![CARRIER BOX]) AND (([LINE HAUL 2005].[Origin Ramp City]) Like "*" &
[Forms]![LINK UP FORM]![ORIG CITY BOX] Or ([LINE HAUL 2005].[Origin Ramp
City]) Is Null) AND (([LINE HAUL 2005].[Origin Ramp State]) Like "*" &
[Forms]![LINK UP FORM]![ORIG state BOX] Or ([LINE HAUL 2005].[Origin Ramp
State]) Is Null) AND (([LINE HAUL 2005].[Dest Ramp State]) Like "*" &
[Forms]![LINK UP FORM]![dest state BOX] Or ([LINE HAUL 2005].[Dest Ramp
State]) Is Null) AND (([LINE HAUL 2005].[Dest Ramp City]) Like "*" &
[Forms]![LINK UP FORM]![dest CITY BOX] Or ([LINE HAUL 2005].[Dest Ramp City])
Is Null))
ORDER BY [LINE HAUL 2005].[Rail Line Haul];


2.
Private Sub CARRIER_BOX_DblClick(Cancel As Integer)

Dim strCriteria As String
Dim strSQL As String

strCriteria = "Rail_Line_Haul = """ & Me.[CARRIER BOX] & """"
strSQL = "SELECT [LINE HAUL 2005].[Ship Date], Avg([LINE HAUL
2005].[Rail Cost]) AS [AvgOfRail Cost], [LINE HAUL 2005].[Rail Line Haul],
[LINE HAUL 2005].[Load Number], [LINE HAUL 2005].[Origin Ramp City], [LINE
HAUL 2005].[Origin Ramp State], [LINE HAUL 2005].[Dest Ramp State], [LINE
HAUL 2005].[Dest Ramp City] " & _
"FROM [LINE HAUL 2005]" & _
"WHERE " & strCriteria

Me.RecordSource = strSQL
Me.Requery

End Sub
Click to expand...
Click to expand...
 
G

Guest

I think I see it. When assigning the value to the strCriteria variable
you've used underscores in the column name where there should be spaces.
What you should have done is leave the spaces and wrap the column name in
brackets:

strCriteria = "[Rail Line Haul] = """ & Me.[CARRIER BOX] & """"

I've assumed the column is of text data type; hence the wrapping of the
value in quotes characters.

Aaron said:
Now it says "Syntax error in WHERE clause". I copied the code and pasted it in.
I've tried manipulating the code to try and fix it, but nothing has been
able to solve the problem.


strSQL = "SELECT [LINE HAUL 2005].[Ship Date], AVG([LINE HAUL
2005].[Rail Cost]) " & _
"AS [AvgOfRail Cost], [LINE HAUL 2005].[Rail Line Haul], " & _
"[LINE HAUL 2005].[Load Number], [LINE HAUL 2005].[Origin Ramp City], "
& _
"[LINE HAUL 2005].[Origin Ramp State], [LINE HAUL 2005].[Dest Ramp
State], " & _
"[LINE HAUL 2005].[Dest Ramp City] FROM [LINE HAUL 2005] WHERE " & _
strCriteria & "GROUP BY [LINE HAUL 2005].[Ship Date], " & _
"[LINE HAUL 2005].[Rail Line Haul],[LINE HAUL 2005].[Load Number], " & _
"[LINE HAUL 2005].[Origin Ramp City], [LINE HAUL 2005].[Origin Ramp
State], " & _
"[LINE HAUL 2005].[Dest Ramp State], [LINE HAUL 2005].[Dest Ramp City]"
& _
"ORDER BY [LINE HAUL 2005].[Rail Line Haul]"




Ken Sheridan said:
You are missing the GROUP BY clause from the original query. The SQL
statement which you build in the code should be everything from the original
query apart from the HAVING clause, plus the new WHERE clause (strCriteria)
to restrict the result set to the selected company:

strSQL = "SELECT [LINE HAUL 2005].[Ship Date], AVG([LINE HAUL 2005].[Rail
Cost]) " & _
"AS [AvgOfRail Cost], [LINE HAUL 2005].[Rail Line Haul], " & _
"[LINE HAUL 2005].[Load Number], [LINE HAUL 2005].[Origin Ramp City], "
& _
"[LINE HAUL 2005].[Origin Ramp State], [LINE HAUL 2005].[Dest Ramp
State], " & _
"[LINE HAUL 2005].[Dest Ramp City] FROM [LINE HAUL 2005] WHERE " & _
strCriteria & " GROUP BY [LINE HAUL 2005].[Ship Date], " & _
"[LINE HAUL 2005].[Rail Line Haul],[LINE HAUL 2005].[Load Number], " & _
"[LINE HAUL 2005].[Origin Ramp City], [LINE HAUL 2005].[Origin Ramp
State], " & _
"[LINE HAUL 2005].[Dest Ramp State], [LINE HAUL 2005].[Dest Ramp City]
" & _
"ORDER BY [LINE HAUL 2005].[Rail Line Haul]"

Aaron said:
Let me know what you find. Thanks for the help!

1.
SELECT [LINE HAUL 2005].[Ship Date], Avg([LINE HAUL 2005].[Rail Cost]) AS
[AvgOfRail Cost], [LINE HAUL 2005].[Rail Line Haul], [LINE HAUL 2005].[Load
Number], [LINE HAUL 2005].[Origin Ramp City], [LINE HAUL 2005].[Origin Ramp
State], [LINE HAUL 2005].[Dest Ramp State], [LINE HAUL 2005].[Dest Ramp City]
FROM [LINE HAUL 2005]
GROUP BY [LINE HAUL 2005].[Ship Date], [LINE HAUL 2005].[Rail Line Haul],
[LINE HAUL 2005].[Load Number], [LINE HAUL 2005].[Origin Ramp City], [LINE
HAUL 2005].[Origin Ramp State], [LINE HAUL 2005].[Dest Ramp State], [LINE
HAUL 2005].[Dest Ramp City]
HAVING ((([LINE HAUL 2005].[Rail Line Haul]) Like "*" & [Forms]![LINK UP
FORM]![CARRIER BOX]) AND (([LINE HAUL 2005].[Origin Ramp City]) Like "*" &
[Forms]![LINK UP FORM]![ORIG CITY BOX] Or ([LINE HAUL 2005].[Origin Ramp
City]) Is Null) AND (([LINE HAUL 2005].[Origin Ramp State]) Like "*" &
[Forms]![LINK UP FORM]![ORIG state BOX] Or ([LINE HAUL 2005].[Origin Ramp
State]) Is Null) AND (([LINE HAUL 2005].[Dest Ramp State]) Like "*" &
[Forms]![LINK UP FORM]![dest state BOX] Or ([LINE HAUL 2005].[Dest Ramp
State]) Is Null) AND (([LINE HAUL 2005].[Dest Ramp City]) Like "*" &
[Forms]![LINK UP FORM]![dest CITY BOX] Or ([LINE HAUL 2005].[Dest Ramp City])
Is Null))
ORDER BY [LINE HAUL 2005].[Rail Line Haul];


2.
Private Sub CARRIER_BOX_DblClick(Cancel As Integer)

Dim strCriteria As String
Dim strSQL As String

strCriteria = "Rail_Line_Haul = """ & Me.[CARRIER BOX] & """"
strSQL = "SELECT [LINE HAUL 2005].[Ship Date], Avg([LINE HAUL
2005].[Rail Cost]) AS [AvgOfRail Cost], [LINE HAUL 2005].[Rail Line Haul],
[LINE HAUL 2005].[Load Number], [LINE HAUL 2005].[Origin Ramp City], [LINE
HAUL 2005].[Origin Ramp State], [LINE HAUL 2005].[Dest Ramp State], [LINE
HAUL 2005].[Dest Ramp City] " & _
"FROM [LINE HAUL 2005]" & _
"WHERE " & strCriteria

Me.RecordSource = strSQL
Me.Requery

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

You're going to laugh. I didn't reinsert the entire strCriteria line after I
pasted the code, just assuming it was there and not taking a second look.
Look what 10+ hours of programming does to you!
Regardless, it works perfectly now, thank you so much. Adds a great deal of
flexibility to the program.



Ken Sheridan said:
I think I see it. When assigning the value to the strCriteria variable
you've used underscores in the column name where there should be spaces.
What you should have done is leave the spaces and wrap the column name in
brackets:

strCriteria = "[Rail Line Haul] = """ & Me.[CARRIER BOX] & """"

I've assumed the column is of text data type; hence the wrapping of the
value in quotes characters.

Aaron said:
Now it says "Syntax error in WHERE clause". I copied the code and pasted it in.
I've tried manipulating the code to try and fix it, but nothing has been
able to solve the problem.


strSQL = "SELECT [LINE HAUL 2005].[Ship Date], AVG([LINE HAUL
2005].[Rail Cost]) " & _
"AS [AvgOfRail Cost], [LINE HAUL 2005].[Rail Line Haul], " & _
"[LINE HAUL 2005].[Load Number], [LINE HAUL 2005].[Origin Ramp City], "
& _
"[LINE HAUL 2005].[Origin Ramp State], [LINE HAUL 2005].[Dest Ramp
State], " & _
"[LINE HAUL 2005].[Dest Ramp City] FROM [LINE HAUL 2005] WHERE " & _
strCriteria & "GROUP BY [LINE HAUL 2005].[Ship Date], " & _
"[LINE HAUL 2005].[Rail Line Haul],[LINE HAUL 2005].[Load Number], " & _
"[LINE HAUL 2005].[Origin Ramp City], [LINE HAUL 2005].[Origin Ramp
State], " & _
"[LINE HAUL 2005].[Dest Ramp State], [LINE HAUL 2005].[Dest Ramp City]"
& _
"ORDER BY [LINE HAUL 2005].[Rail Line Haul]"




Ken Sheridan said:
You are missing the GROUP BY clause from the original query. The SQL
statement which you build in the code should be everything from the original
query apart from the HAVING clause, plus the new WHERE clause (strCriteria)
to restrict the result set to the selected company:

strSQL = "SELECT [LINE HAUL 2005].[Ship Date], AVG([LINE HAUL 2005].[Rail
Cost]) " & _
"AS [AvgOfRail Cost], [LINE HAUL 2005].[Rail Line Haul], " & _
"[LINE HAUL 2005].[Load Number], [LINE HAUL 2005].[Origin Ramp City], "
& _
"[LINE HAUL 2005].[Origin Ramp State], [LINE HAUL 2005].[Dest Ramp
State], " & _
"[LINE HAUL 2005].[Dest Ramp City] FROM [LINE HAUL 2005] WHERE " & _
strCriteria & " GROUP BY [LINE HAUL 2005].[Ship Date], " & _
"[LINE HAUL 2005].[Rail Line Haul],[LINE HAUL 2005].[Load Number], " & _
"[LINE HAUL 2005].[Origin Ramp City], [LINE HAUL 2005].[Origin Ramp
State], " & _
"[LINE HAUL 2005].[Dest Ramp State], [LINE HAUL 2005].[Dest Ramp City]
" & _
"ORDER BY [LINE HAUL 2005].[Rail Line Haul]"

:

Let me know what you find. Thanks for the help!

1.
SELECT [LINE HAUL 2005].[Ship Date], Avg([LINE HAUL 2005].[Rail Cost]) AS
[AvgOfRail Cost], [LINE HAUL 2005].[Rail Line Haul], [LINE HAUL 2005].[Load
Number], [LINE HAUL 2005].[Origin Ramp City], [LINE HAUL 2005].[Origin Ramp
State], [LINE HAUL 2005].[Dest Ramp State], [LINE HAUL 2005].[Dest Ramp City]
FROM [LINE HAUL 2005]
GROUP BY [LINE HAUL 2005].[Ship Date], [LINE HAUL 2005].[Rail Line Haul],
[LINE HAUL 2005].[Load Number], [LINE HAUL 2005].[Origin Ramp City], [LINE
HAUL 2005].[Origin Ramp State], [LINE HAUL 2005].[Dest Ramp State], [LINE
HAUL 2005].[Dest Ramp City]
HAVING ((([LINE HAUL 2005].[Rail Line Haul]) Like "*" & [Forms]![LINK UP
FORM]![CARRIER BOX]) AND (([LINE HAUL 2005].[Origin Ramp City]) Like "*" &
[Forms]![LINK UP FORM]![ORIG CITY BOX] Or ([LINE HAUL 2005].[Origin Ramp
City]) Is Null) AND (([LINE HAUL 2005].[Origin Ramp State]) Like "*" &
[Forms]![LINK UP FORM]![ORIG state BOX] Or ([LINE HAUL 2005].[Origin Ramp
State]) Is Null) AND (([LINE HAUL 2005].[Dest Ramp State]) Like "*" &
[Forms]![LINK UP FORM]![dest state BOX] Or ([LINE HAUL 2005].[Dest Ramp
State]) Is Null) AND (([LINE HAUL 2005].[Dest Ramp City]) Like "*" &
[Forms]![LINK UP FORM]![dest CITY BOX] Or ([LINE HAUL 2005].[Dest Ramp City])
Is Null))
ORDER BY [LINE HAUL 2005].[Rail Line Haul];


2.
Private Sub CARRIER_BOX_DblClick(Cancel As Integer)

Dim strCriteria As String
Dim strSQL As String

strCriteria = "Rail_Line_Haul = """ & Me.[CARRIER BOX] & """"
strSQL = "SELECT [LINE HAUL 2005].[Ship Date], Avg([LINE HAUL
2005].[Rail Cost]) AS [AvgOfRail Cost], [LINE HAUL 2005].[Rail Line Haul],
[LINE HAUL 2005].[Load Number], [LINE HAUL 2005].[Origin Ramp City], [LINE
HAUL 2005].[Origin Ramp State], [LINE HAUL 2005].[Dest Ramp State], [LINE
HAUL 2005].[Dest Ramp City] " & _
"FROM [LINE HAUL 2005]" & _
"WHERE " & strCriteria

Me.RecordSource = strSQL
Me.Requery

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Parameter Query With Null Values 3
Parameter Value error in a Subform 0
Combo Box Question 4
Combo box in Query criteria 6
Access Sub form opens before the main form in MS Access 0
combo box filter 1
Searching multiple fields 2
Displaying query result on the same form as selection fields 6

Top