Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2007
    Posts
    102

    Unanswered: clear autonum control....

    Hello all,

    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:
    Code:
    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
    imrosie
    Last edited by imrosie; 09-03-07 at 18:14.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Unbound controls!!!
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Rosie

    Is this related to your last question?

    Has your set up changed since then?
    Quote Originally Posted by imrosie
    ....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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Aug 2007
    Posts
    102

    Which last question??.....

    Hi,
    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.


    Does this help clear anything up?

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You know, if you used unbound controls...
    Code:
    DoCmd.RunSQL "INSERT INTO contacts (FName, LName) VALUES ('George', 'V')
    is all you need. Autonumbers should be meaningless - so why does the user have to see it? Note that the autonumbers are automatically populated with insert statements.
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Aug 2007
    Posts
    102

    Neither will work...

    hi GeorgeV and Pootle Flump,

    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:
    Code:
    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.
    Last edited by imrosie; 09-04-07 at 11:09.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try the forms control source, not the orderid textbox
    George
    Home | Blog

  9. #9
    Join Date
    Aug 2007
    Posts
    102

    it's the cmd button code

    Hi GeorgeV,

    it's in the code behind the cmd button used for clearing the controls:
    Code:
    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?
    Last edited by imrosie; 09-04-07 at 12:04.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •