Solved Form for adding a new order by customer

[debsamguru]

Hi,

Can anyone see what I'm doing wrong here?

I have 2 ways to add a new order - one from the main menu, where I have a dropdown to select the customer, which works perfectly.
The second way involves having a button on the customer's orders form (OrdersF) and passing the CustomerID through to the NewOrderbyCustomerF. However, when I do this, instead of JUST adding a new order, it adds a new customer too. It's something very obvious probably, but I've been staring at it for so long that I just can't see it.

Thanks

 

[pbaldy]

The form is bound to a query that includes the customer table. Just bind it to the orders table. You can use a combo for the customer ID and display the name via:

Autofill

 

[plog]

You've got bigger issues--I don't think your tables are set up properly. Here's the issues I see when I look at your Relationship Tool:

1. Circular relationships. There should only be 1 way to trace a path among tables, you've got multiple ways to do so. For example, I can get from CustomersT to StockCodesT in 5 different ways (directly, via OrderHeaderT, via DeliverAddressT, etc). 4 of those are wrong. Your tables shouldn't have as many direct relationships as they do.

2. Storing duplicate data. StockCodesT and OrderItemsT both have CostPrice and SalesPrice fields and are related on a third field. CostPrice and SalesPrice need to be in just one of those tables, not both.

3. Storing calculated data. In OrderHeaderT you status field and then a bunch of date fields (PaidDate, LoadingDate, etc.) that seem to correspond to a value in StatusT (Paid, Shipped, etc.). Can't you just use logic on the date fields to determine an Order's status? It seems the status field OrderHeadersT doesn't need to exist with all those date fields.

Additionally, it seems weird the way you keep track of stock. A stock code is set up for every item/customer/supplier combination. That means if Customer1 wants 20 of WidgetA they don't just get 20 WidgetA, they must get 20 WidgetA from SupplierX. And if that Customer/Widget/Supplier record doesn't exist in StockCodesT it must be set up.

 

[jdraw]

You may get some insight from this Customer Order Product data model from Barry Williams' site.

 

[debsamguru]

Thanks Plog. In answer to your questions:

1. I'm not sure what you mean by direct relationships and why 4 of them are wrong
2. The Cost price and Sales Price on the StockItemT are the default price - they can be over-ridden at ordering if needed.
3. The date fields can be used for some of the statuses but not all (e.g. part-shipped, invoiced).
4. I agree that the item/customer/supplier relationship is weird but that's how it is.

 

[debsamguru]

Thanks pbaldy.

I use the combofield in the NewOrdersF and it happily works. The problem is that I want to carry through the Customer ID/Name/Code to the NewOrdersbyCustomerF and not have to have a dropdown and I can't work out how to do that.

 

[pbaldy]

Thanks pbaldy.

I use the combofield in the NewOrdersF and it happily works. The problem is that I want to carry through the Customer ID/Name/Code to the NewOrdersbyCustomerF and not have to have a dropdown and I can't work out how to do that.

The default value should still work, as long as it's the ID field.

 

[debsamguru]

Thanks pbaldy, it was so obvious, it slapped me in the face!

Next question, I am currently using two forms to enter the order - NewOrderF, where the CustomerCombo shows all the customers and now the NewOrderbyCustomerF, where the default to the CustomerCombo is the passed CustomerID (as recommended by you above and which now works!). This is cumbersome - there should be some way that I can use the same form NewOrderF. How is that possible? I need to be able to use the default if it is coming from OrderF and no default if its coming from MainMenuF.

 

[pbaldy]

You can take off the default and populated it after you open it:

DoCmd.OpenForm...
Forms!FormName.Customer = Me.Customer

using your actual names of course. If they might add more than one order you can also set the default value from here.

 

[debsamguru]

No, I don't think I get this.

So from the MainMenuF, I just use the straight NewOrderF (which now has no default) i.e. DoCmd.OpenForm "NewOrderF", , , , acFormAdd as before
But how would it work from the OrdersF form which now needs to pass the CustomerID as the default for the ComboBox on the NewOrderF.

 

[pbaldy]

Add the second line of code I showed which will populate the customer ID. Will the user be adding more than one order when they open the form that way?

 

[debsamguru]

Add the second line of code where - in the call to the form? Won't the Do.Cmd just open the form before populating the field?
Potentially, when the NewOrderF is open, the user can add further orders.

 

[pbaldy]

Add the second line of code where - in the call to the form? Won't the Do.Cmd just open the form before populating the field?
Potentially, when the NewOrderF is open, the user can add further orders.

Right after, like I showed:

DoCmd.OpenForm...
Forms!FormName.Customer = Me.Customer

The first line opens the form, the second populates the customer ID field on the newly opened form with the value from the first form. If they might add multiple orders, add a third line:

Forms!FormName.Customer.DefaultValue = Me.Customer

 

[debsamguru]

I've tried that as follows:

DoCmd.OpenForm "NewOrderF", , , , acFormAdd
Forms!OrdersF!CustomerID.DefaultValue = Me.CustomerID

This doesn't put the CustomerID in the ComboBox and bring up the Customer Name and Code.

 

[pbaldy]

I said add a third line for the default, so:

DoCmd.OpenForm "NewOrderF", , , , acFormAdd
Forms!OrdersF!CustomerID = Me.CustomerID
Forms!OrdersF!CustomerID.DefaultValue = Me.CustomerID

 

[debsamguru]

Sorry pbaldy, I'm not normally this thick! I added the extra line and now get the following error.

I think I need to get something to eat (and maybe a VERY large glass of something alcoholic!

 

[pbaldy]

Hit Debug, which line is highlighted?

 

[debsamguru]

It's the Forms!OrdersF!CustomerID line

 

[pbaldy]

If you take that out, can you add a record manually? The error implies the form won't allow it.

 

[debsamguru]

Yes but I have to manually drop down the ComboBox. I'm just wondering, should it be the CustomerCombo.DefaultValue that is set to Me.CustomerID?