Correctly format where statement in query

[imrosie]

Hello Experts,

It's going to take one to figure this out. My Order form is supported by a query of two tables (Customers and Orders). There is a subform within the Form for entering in the new Order infor, so I need both tables in the query. A customer is first selected through a combo box on the Order frm to begin new Order. Unfortunately, I can't bring up a newly added customer name IF that Customer doesn't have a prior order. Otherwise the combo can bring up any customer. Here's the SQL statement:

SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Customers.FirstName, Customers.LastName, Orders.OrderDate, Orders.ShipDate, Orders.FreightCharge, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipStateOrProvince, Orders.ShipZIPCode, Orders.ShipCountry, Customers.CompanyName, Customers.BillingAddress, Customers.City, Customers.StateOrProvince, Customers.ZIPCode, Customers.Country, Customers.ContactTitle, Orders.ShipPhoneNumber, Orders.ShipFaxNumber, Orders.PurchaseOrderNumber, Customers.Notes FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

What happens is when a new customer is selected, the cursor skips the first three controls and jumps to the 4th (the first 3 are , firstname, lastname, customerid). Those fields match the row query for selecting a customer i

SELECT Customers.CustomerID, [FirstName] & " " & [LastName] FROM Customers ORDER BY [FirstName] & "," & [LastName]; 

n the combo box:

This same combo box has also an AfterUpdate event:

Private Sub custname_AfterUpdate()
Private Sub custname_AfterUpdate()
Me.FilterOn = True
Me.Filter = "CustomerID = " & Me.custname
Me.Requery
End Sub
 

I think I need a Where statement that will allow me to bring up a customer when there isn't a prior order associated. Can anyone help me formulate one? Or if I'm wrong on my assumption, point me in the right direction. I tried a left join instead and it didn't work. thanks in advance

Rosie

[imrosie]

After reading my post I want to make clear the set up for the Main form. It has a subform to capture OrdersDetails(quantity, unit, price,,ect. and Products info) . But the controls on the main form capture data for the Orders table (such as Orders subtotal, Orders total, Order date, ect) and for the Customer tables (Customer name, Shipping details, etc.). Both get updated through that query for main form.

thanks
Rosie

[Scott Price]

Good morning Rosie!

You can try exploring a right or left join between your two tables in the original select query. Open the query in design view, double click on the line representing the join, it will bring up a dialog box with three options. Choose the one that says Show ALL records from table Customer and only those records from table Orders where the joined fields match (off the top of my head, here so the exact wording there might vary.)

There are a few ? marks in your code, but if it's working fine for you as is, I'll not bring them up :-)

Regards,
Scott

[imrosie]

Good morning Rosie!

You can try exploring a right or left join between your two tables in the original select query. Open the query in design view, double click on the line representing the join, it will bring up a dialog box with three options. Choose the one that says Show ALL records from table Customer and only those records from table Orders where the joined fields match (off the top of my head, here so the exact wording there might vary.)

There are a few ? marks in your code, but if it's working fine for you as is, I'll not bring them up :-)

Regards,
Scott

Scott,

Hello and thanks for your comments....I continued trying to figure out why my left join didn't work....well I figured it out and now this is working great...

What happened was that I had the CustomerID coming from the Order table...I don't know how I kept overlooking that. Anyway, its working.
take care
Rosie

[Scott Price]

Glad to hear it's working for you, Rosie, and thanks for posting back with what you did to make it work!

Regards,
Scott

[imrosie]

Glad to hear it's working for you, Rosie, and thanks for posting back with what you did to make it work!

Regards,
Scott

Hi Scott,

You're welcome.....I've noticed now I have a bit of a problem though. Since my Left Join is working beautifully, it's has broken two other features on my form.
1.) The control for OrderID (Orders) based on an autonumber too (it's part of the right side table of the Left Join), won't give me an Autonumber when I search a customer for the start of a new Order. Before (with Original Query) I would automatically get a new Autonumber (used for OrderID) in that control for the customer name in the combo box.

2.)I have a command button on the Form called "beginanorder". It's purpose was to retain customer information, while clearing old order info. When a customer was located through combo box, the old Order also populates the controls, so this command button worked like this:

Private Sub beginanorder_Click()
'Copies fields from original record to variables
Field1 = Me.custacct
Field2 = Me.atitle
Field3 = Me.fname
Field4 = Me.lname
Field5 = Me.TheCompany
 
'Begins a new record
DoCmd.GoToRecord , , acNewRec
 
'Plugs in old values into the new record
Me.custacct.Value = Field1
Me.atitle.Value = Field2
Me.fname.Value = Field3
Me.lname.Value = Field4
Me.TheCompany.Value = Field5
End Sub
 

I could really use some great ideas on this one....I'm diligently trying to figure this one out. thanks so much
Rosie

[Scott Price]

Are we talking about the main form now, or the subform?

What is your subform based on and what does it display?

In my mind (correct me if I'm wrong, since I sometimes make unwarranted assumptions): I see your main form having Customer information, I see your subform having Order information. The two will be joined by a common CustomerID control. (seance quality narrative here, eh? :-) Main form is based on the Left-joined query that brings up all customer information, and subform is based on separate query that brings up the order information.

How am I doing so far?

Basing them both on the same query won't work for the reasons that you are experiencing.

As for the second problem you mentioned, you didn't say what it was doing wrong? An error message?

Regards,
Scott

[imrosie]

Are we talking about the main form now, or the subform?

What is your subform based on and what does it display?

In my mind (correct me if I'm wrong, since I sometimes make unwarranted assumptions): I see your main form having Customer information, I see your subform having Order information. The two will be joined by a common CustomerID control. (seance quality narrative here, eh? :-) Main form is based on the Left-joined query that brings up all customer information, and subform is based on separate query that brings up the order information.

Errors:
On 1.) I simply get the Customer information, but no Orders info, such as that "OrderID" which is based on an autonum.

On 2.) Once I bring up a particu;lar customer, I click the "beginanorder" to clear out an Old order. I get this msg:
Error '2448', you can't assign a value to this object. It's referring to line number 13 in the code above for cmd(click) "beginanorder".

From what you've said so far Scott, it sounds like you're suggesting I figure out how to make the subform do Orders and Order Details??!.Is this right? (How am I doing?)

Rosie

How am I doing so far?

Basing them both on the same query won't work for the reasons that you are experiencing.

As for the second problem you mentioned, you didn't say what it was doing wrong? An error message?

Regards,
Scott

Yes Scott, we're talking Main Orders form here. Yes, it has the Customer information. It is now based on the 'left-joined query (qryOrdersSource) that includes the Customer and Orders fields. Yes, these tables are joined by the "CustomerID".

My subform within the Main Orders form is called "Orders Detail Subform". It is based on the "Orders Details" table (not a query). This table contains the fields; productid, quantity, unitprice, discount, orderid. It displays the dropdown list with products to choose, shows the quantity selected, discount, unitprice and extended price.

[Scott Price]

Yes Scott, we're talking Main Orders form here. Yes, it has the Customer information. It is now based on the 'left-joined query (qryOrdersSource) that includes the Customer and Orders fields. Yes, these tables are joined by the "CustomerID".

My subform within the Main Orders form is called "Orders Detail Subform". It is based on the "Orders Details" table (not a query). This table contains the fields; productid, quantity, unitprice, discount, orderid. It displays the dropdown list with products to choose, shows the quantity selected, discount, unitprice and extended price.

Unfortunately when one query is referring to a particular table, it will not be accessible by another query or form. So what is happening with your situation (my reading of it at the moment, anyway) is that when you open your main form query, it is accessing and thereby 'locking' the table that your subform is based on... Until you release the lock, you cannot make some (or maybe any) changes to it's data from outside of the query that is accessing it.

One solution, as you guessed above, is that your main form be used to display ONLY customer information, and your subform ONLY order information. This will allow you to isolate the two sources and should alleviate the problem of the write conflict you are experiencing with prob #2, and the related problem #1.

If you must refer to customer information on the orders subform, there are some ways we can explore to work around that, but I think you should be able to design the thing so you don't have to.

Let me know how it turns out.

Regards,
Scott

[imrosie]

Unfortunately when one query is referring to a particular table, it will not be accessible by another query or form. So what is happening with your situation (my reading of it at the moment, anyway) is that when you open your main form query, it is accessing and thereby 'locking' the table that your subform is based on... Until you release the lock, you cannot make some (or maybe any) changes to it's data from outside of the query that is accessing it.

One solution, as you guessed above, is that your main form be used to display ONLY customer information, and your subform ONLY order information. This will allow you to isolate the two sources and should alleviate the problem of the write conflict you are experiencing with prob #2, and the related problem #1.

If you must refer to customer information on the orders subform, there are some ways we can explore to work around that, but I think you should be able to design the thing so you don't have to.

Let me know how it turns out.

Regards,
Scott

Scott, hi

I'll post back when I get things scrambled around on this main form. thanks for your insight
Rosie