I've got a control the displays an autonumber (of a past OrderID).....my form is set to clear various controls, including this one with:
Forms![Add an Order and Details]![Order Ship Details Subform].Form.customerorderid = Null
I get that runtime error-----" '-2147352567 (80020009) You can't assign a value to this object"
Is there any way around this?? I tried using "False", the a second line with "True",,,,,,didn't work. I'm a newbie and I know you're laughing...
I just need a new autonumber to display, after I clear the controls, so I can begin a new order....
Here is my thought....is there a way to have the OrderId field 'False' until my "beginanorder"(which clears old Order info;productid, discount, unitprice, quantity) cmd button starts a new order? My concern is that the old OrderID would pop into the control display once the control became 'True'(since the CustomerID and OrderID are tied in the Link Master fields
Does anyone have a better and clean way to do this...I'm probably missing the big picture. Help (whimper, whimper)..this is the last piece of my form that I have to get fixed...thanks
....the CustomerID and OrderID are tied in the Link Master fields
Now - this can't be right. The CustomerID is the pk for customer and uniquely identifies a customer right? The OrderID is the pk for order and uniquely identifies a order right? Is CustomerID not a foreign key in the orders table and should this not be what you link in the Link Master-Child fields?
A quick rule of thumb - if you are struggling to set\ clear\ reset autonumbers then you are almost certainly doing something wrong because, by definition (they are call autonumbers afterall), they should look after themselves just fine.
I've posted several...could you be more specific on which question (title)...thanks
On this one, I'm not trying to clear it out of the table, just the control. What's happening is; a customer (thru an unbound combo, row source table/query) is located. The customer info populates into all the controls.....one of the bound controls is 'customerorderid' (OrderID of customer's last order)..........
You're right, CustomerID is the pk for customer and OrderID is the pk for order. This main form has 2 subforms that are 'bridged' together to the main form (works great). subform #1 has shipping and order info, both Link Master/Child fields are linked to the Main form through CustomerID, not OrderID, subform #2 has OrderDetail information links OrderID (Master) to main form.....The customerorderid, is coming off of the subform number 1.
my cmd button that supposedly clears certain fields, clear out the controls (all except) this one. I know that autonumbers take care of themselves, but a NEW autonumber won't display until I clear out that control. Now if you're saying it can't be done at all, then I'll have to figure another way to get that control (customerordid) to move to a new 'autonumber' to begin a new order....
That is why I asked if there was another way, or better workaround, which someone may have done before? Sorry if I confused you.
I think you are missing something here but to give a simple solution for now and then we can see what is happening. I still don't know why you are "clearing controls". If you are adding a new record they should all be blank...
How about clearing the control source of the control?
Me.MyControl.ControlSource = ""
then reset the control source when you do want to show the autonumber.
Why am I clearing out controls....My Ordering Dept. takes
phone orders over the phone. We have thousands of existing customers. On the Main order form is a 'Search' box for selection of a name (if already a customer). It populates all and I mean all of the customers information into controls on the Main form:
name (first & last), title, billing address, phone, city, state, zip, country, phone, fax AND the shipping information into subform #2;ship address, ship city, ship state, shipping country, ship fax, ship phone, orderdate (of past order), orderid (of last order), employeeid (of person that placed last order)AND in subform #1, the details (product information on the last order); productid, quantity, discount, unitprice........
Because the Order Dept. doesn't want to type into the controls....ALL of that information for each order (specifically the billing and shipping information......they requested that the information "automatically" is in there, when they search for an existing customer.
GeorgeV, thanks for the 'Insert into' but I'm not sure how that would fit into this scenario. Maybe I'm missing your big picture....thanks
so again, I can clear out the information in the subform #1 (product information). Its the customerorderid, that I'm having trouble with in subform #2.
Pootle, I did indeed try using the control source behind the cmd button code for customerorderid (control in subform#1),,,,,,it didn't work:
Forms![Add an Order and Details]![Order Details Subform].Form.customerorderid.ControlSource = ""
I received error msg; runtime error 2465, application-defined or object-defined error.
thanks for your help....any other ideas would be greatly appreciated.
it's in the code behind the cmd button used for clearing the controls:
Forms![Add an Order and Details]![Order Details Subform].Form.ProductID = Null
Forms![Add an Order and Details]![Order Details Subform].Form.Quantity = Null
Forms![Add an Order and Details]![Order Details Subform].Form.UnitPrice = Null
Forms![Add an Order and Details]![Order Details Subform].Form.Discount = Null
Forms![Add an Order and Details]![Order Ship Details Subform].Form.customerorderid.ControlSource = ""
Thanks now it's working to clear that control,,,,,,but I don't get the 'next' autonumber to show up in that same control....in fact, I tried pulling up other customers and now that control won't display any 'customerorderid'..
It as though your code totally took over that control or it went into effect and won't shut off. No old orderid appears with selection of another customer, or the word "autonumber" doesn't show as it did before (when Main Order form was blank before locating a customer).....Additionally, product info in Subform #1 (product details) which is bridged to the customerorderid (OrderID) isn't showing up now since the 'old' (OrderID)'customeroderid' isn't there (which means I don't need the cmd button to clear the customerorderid).....this is weird....As a newbie, you can see why I'm confused at this point.
thanks for your help.
is there a string to call it back (reactivate it)after I clear the old customerorderid?