complicated query

[Jae Hood]

I have an order management database.
Here is an example of what I need:
I have 30 orders, all of which have 1 of the following status: open,
shipping, closed, dead.

I want my users to be able to open a form, select one of those statuses, and
all the orders which have the selected status appear on the form. I would
also like to add additional fields containing relevant information such as
carrier and notes.

If someone could guide me through the first step, I could probably figure
out the additional fields.

Thank you

 

[Sheila D]

Jae

It sounds like you need a simple form with a combo box based on the Status
field. Then add a command button using the Wizard to open your form with all
required fields - choose Open the form and find specific data to display and
indicate which field value matches your combo field. Hope this helps

Sheila

 

[Jae Hood]

If I use a simple Combo Box it will return the status' multiple times
representing each order.
Like it would drop down like this:
Open
Open
Open
Open
Shipping
Shipping
and so forth...

I appreciate the help though.

 

[Steve Schapel]

Jae,

The Combobox should be an *unbound* control, i.e. not the one that is
bound to the Status field in the table. Put it in the Form Header
section of the form, which I assume is a continuous view form. Lets say
you name this combobox StatusSelect. Ok, now make a Query based on the
table that is currently the Record Source of the form. And in the
Criteria of the Status column in the query, put the equivalent of this...
[Foms]![NameOfYourForm]![StatusSelect]
Now open the form in design view, and look at the form Properties. In
the Record Source property, enter the name of the query in the place of
the existing entry (presumably the table). Then select the StatusSelect
combobox, and look at its Properties. In the Row Source Type property,
enter Value List, and in the Row Source property, enter
"open";"shipping";"closed";"dead"

Ok, I think this should work now. Open the form, select one of the
status types from the StatusSelect combobox, and the form should display
only orders with the selected status.

 

[Jae Hood]

Sorry,
Had forgotten to add my OrderID field to the detail of the form...lol
But still, when I select a status it simply lists all my order id's.

Jae,

The Combobox should be an *unbound* control, i.e. not the one that is
bound to the Status field in the table. Put it in the Form Header
section of the form, which I assume is a continuous view form. Lets say
you name this combobox StatusSelect. Ok, now make a Query based on the
table that is currently the Record Source of the form. And in the
Criteria of the Status column in the query, put the equivalent of this...
[Foms]![NameOfYourForm]![StatusSelect]
Now open the form in design view, and look at the form Properties. In
the Record Source property, enter the name of the query in the place of
the existing entry (presumably the table). Then select the StatusSelect
combobox, and look at its Properties. In the Row Source Type property,
enter Value List, and in the Row Source property, enter
"open";"shipping";"closed";"dead"

Ok, I think this should work now. Open the form, select one of the
status types from the StatusSelect combobox, and the form should display
only orders with the selected status.

--
Steve Schapel, Microsoft Access MVP

If I use a simple Combo Box it will return the status' multiple times
representing each order.
Like it would drop down like this:
Open
Open
Open
Open
Shipping
Shipping
and so forth...

I appreciate the help though.

 

[Steve Schapel]

Jae,

No, the [Forms]![Status Trial]![StatusSelect] should be in the
*Criteria* of the Status field in the query.

If you still can't get it to work, can you go to the View menu in the
query design, select SQL, and then copy/paste the SQL of the query into
your reply.

You have set the Status Query as the Record Source of the form, right?

 

[Jae Hood]

Ah, Put it in the criteria for the status column, still no go.
Yes the form is pointed to Shipment Status Query.
Here is my query in SQL view:
SELECT [Shipment Details].OrderID, [Shipment Details].Status, [Shipment
Details].Notes
FROM [Shipment Details]
WHERE ((([Shipment Details].Status)=[Forms]![Status Trial]![StatusSelect]));

Thanks!

Jae,

No, the [Forms]![Status Trial]![StatusSelect] should be in the
*Criteria* of the Status field in the query.

If you still can't get it to work, can you go to the View menu in the
query design, select SQL, and then copy/paste the SQL of the query into
your reply.

You have set the Status Query as the Record Source of the form, right?

--
Steve Schapel, Microsoft Access MVP

Thanks for the help.
I followed the steps closely.
When I select a status, nothing happens.
I wanna make sure I did this right.
In my Status Query, which is based off the Shipment Status table,
I have Status: Forms![Status Trial]!StatusSelect in my 2nd column.
Is that ok?
If it is, I don't know where else my error could be.

Thanks again.

 

[Steve Schapel]

Jae,

Try this... Go to the After Update event property of the StatusSelect
combobox, enter 'Event Procedure', click the ellipsis (...) button to
the right, which will take you into the VB Editor window, and enter this
line of code...
Me.Requery

 

[Sheila D]

Jae - In the row source property click on the ellipses to go to the SQL Query
builder. Go to the Query properties (right mouse click in blank part of
query) and set Unique Values to Yes. That should do the trick

Sheila

 

[Jae Hood]

Worked!
Thanks so much Steve!
Now I can play around with different fields and create a whole lookup form

Thanks again,
Justin

Jae,

Try this... Go to the After Update event property of the StatusSelect
combobox, enter 'Event Procedure', click the ellipsis (...) button to
the right, which will take you into the VB Editor window, and enter this
line of code...
Me.Requery

--
Steve Schapel, Microsoft Access MVP

Ah, Put it in the criteria for the status column, still no go.
Yes the form is pointed to Shipment Status Query.
Here is my query in SQL view:
SELECT [Shipment Details].OrderID, [Shipment Details].Status, [Shipment
Details].Notes
FROM [Shipment Details]
WHERE ((([Shipment Details].Status)=[Forms]![Status Trial]![StatusSelect]));

Thanks!

 

[Jae Hood]

Ok,
So I have another issue.
I want to make a similar form that looks up orders based on a customer you
select from a combo box.
However, I can't use Lookup Value's for this because it would be too much to
maintain it as new customers are created each day.
I created a query that looks like this:
SELECT DISTINCT Orders.OrderID, Orders.CustomerID, Customers.CompanyName
FROM (Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
WHERE (((Customers.CompanyName)=[Forms]![Order Lookup]![CustSelect]));

My combo box pulls up values from the CompanyName field in my customers table.
I added the Me.Requery code to my after update event for that combo box, but
the orders don't come up when i select a customer.

Any suggestions would be greatly appreciated.
Thanks!

 

[Steve Schapel]

Jae,

What is the Row Source of the combobox?

 

[Jae Hood]

Hi Steve,
My Row Source:
SELECT [Customers].[CustomerID], [Customers].[CompanyName] FROM [Customers]
ORDER BY [CompanyName];

Thanks again.

Jae,

What is the Row Source of the combobox?

--
Steve Schapel, Microsoft Access MVP

Ok,
So I have another issue.
I want to make a similar form that looks up orders based on a customer you
select from a combo box.
However, I can't use Lookup Value's for this because it would be too much to
maintain it as new customers are created each day.
I created a query that looks like this:
SELECT DISTINCT Orders.OrderID, Orders.CustomerID, Customers.CompanyName
FROM (Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
WHERE (((Customers.CompanyName)=[Forms]![Order Lookup]![CustSelect]));

My combo box pulls up values from the CompanyName field in my customers table.
I added the Me.Requery code to my after update event for that combo box, but
the orders don't come up when i select a customer.

Any suggestions would be greatly appreciated.
Thanks!

 

[Steve Schapel]

Jae,

Check that the Bound Column property of the CustSelect combobox is set to 2.

 

[Jae Hood]

It worked!
Now I'm going to repeat the same process with suppliers hopefully i'll get
it first shot.

Your a lifesaver Steve, thanks so much.

Jae,

Check that the Bound Column property of the CustSelect combobox is set to 2.

--
Steve Schapel, Microsoft Access MVP

Hi Steve,
My Row Source:
SELECT [Customers].[CustomerID], [Customers].[CompanyName] FROM [Customers]
ORDER BY [CompanyName];

Thanks again.