Unanswered: Can a combo box be restricted to only bring up certain data
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:
SELECT DISTINCTROW Customers.CustomerID, [FirstName] & " " & [LastName] FROM Customers ORDER BY [FirstName] & "," & [LastName];
also there's an 'After Update' event that requery's:
Private Sub custname_AfterUpdate()
Me.FilterOn = True
Me.Filter = "CustomerID = " & Me.custname
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:
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 = ""