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

    Unanswered: Can a combo box be restricted to only bring up certain data

    Hi experts,

    I have a combo box that selects a customer and brings up all of the data associated with that customer (Billing data (on Main form), Order data (on Sub form #1) and Shipping Data on Sub form #2)...

    The combo box is unbound and uses a select table/query.
    Both subforms are 'bridged together' with the main form, so when you select a customer name all 3 forms populate...

    Main form - based on Customer table
    Sub form 1 - based on Order Detail table
    Sub form 2 - based on Order table

    It's the data from Sub form 1 and 2 that I don't want 'old' information to appear. I do need these forms 'bridged' so when I enter NEW info into sub 1 and sub 2, it will be stored in the appropriate tables.

    Here's the code for the combo box:
    Code:
    SELECT DISTINCTROW Customers.CustomerID, [FirstName] & " " & [LastName] FROM Customers ORDER BY [FirstName] & "," & [LastName];
    also there's an 'After Update' event that requery's:
    Code:
    Private Sub custname_AfterUpdate()
    Me.FilterOn = True
    Me.Filter = "CustomerID = " & Me.custname
    Me.Requery
    End Sub
    thanks for any ideas...

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How do you define "new data"?
    And just a note
    Code:
    ORDER BY [FirstName] & "," & [LastName]
    '** should read
    ORDER BY [FirstName], [LastName]
    (That's not saying your method won't work, but the second option is superior)
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2007
    Posts
    102

    Mew data??

    GeorgeV,

    thanks for the 'superior' code...I'll put it in your way. If it works, then it is indeed superior. I saw the name structured that way on another site and it worked, so I used it.

    Anyway, on to the 'new data'. I'm referring to new order data.

    After customer name is selected, the Main form autopop's the Customer's billing info (name, addr, city, state, etc.).

    Subform 1 autopop's with 'old' Order detail data (product info,i.e., unitprice, productname, discount etc.).

    Subform 2 autopop's with 'old' Shipping/order data, i.e, ship name & address, OrderID (PK from Order table), Order Subtotal, Order Total, freight, sales tax,.....

    I want ONLY the Customers' billing info to autopopulate on Main form and the Shipping name and address (if one has been stored for the selected customer) to autopop in Subform 2.

    The PK (OrderID) on Sub form 2, autopop's the 'old' OrderID which displays in the orderid control. I've tried using a "begin new order" cmd button, to clear controls on Sub form 1 and the OrderID and other old order info on Sub form 2,,,,,,,,it works, but it 'NUKE' the control..

    By that I mean, I don't get a new autonumber to appear in the OrderID control, called ["customerorderid"]...it as though it's nuked or the control is empty, but the record is still there although the controls are emptied....I can't move on to start with 'new data'.

    I thought it would be best to figure out how to simply bring up only the Customer's info on Main and maybe the ShipName and & address on Subform 2. That way the autonumber for PK OrderID will already be there when I start a new Order. I'm not trying to remove Order info from the tables, just start a new one.

    Am I way off base??

    I'll post the 'begin order' cmd button code:
    Code:
    Private Sub beginanorder_Click()
    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.FreightCharge = Null
    Forms![Add an Order and Details]![Order Ship Details Subform].Form.PurchaseOrderNumber = Null
    Forms![Add an Order and Details]![Order Ship Details Subform].Form.EmployeeID = Null
    Forms![Add an Order and Details]![Order Ship Details Subform].Form.customerorderid.ControlSource = ""
    thanks for your help

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I don't think I can get my head round this problem.
    Sample db time!

    Please only include the relevant forms/queries/tables and make sure you run a compact and repair before you upload it!
    George
    Home | Blog

Posting Permissions

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