I've got a form that tracks requests for customers by a variety of information like priority, part types, etc. Each customer has different pieces of equipment that can be serviced. I want a combo box that looks up only the equipment for the customer chosen within the form.
I swear, every query I write doesn't work. The one I'm working with now reads:
SELECT DISTINCT MachineInfo.MachineName
FROM Customer INNER JOIN MachineInfo ON Customer.CustomerID = MachineInfo.CustomerID
WHERE (((MachineInfo.CustomerID)=[Forms]![Request Form].[cbChooseCompany]));
My tables are set up as follows:
Customer(CustomerID, CustomerName, CustomerContact) where CustomerID is PK.
MachineInfo(MachineID, MachineName, CamSystem, CustomerID) where MachineID is PK and CustomerID is foreign.
I have one other problem...
The main form should track all requests for all employees.
I want to filter the forms when certain employees login so that they can view/edit only their open requests.
My questions are these:
1) Can I limit the forms that each person can see?
2) How do I get the form to filter on default? I changed the form's filter property toLookup_Combo30.EmployeeLast="Whittaker")), but it doesn't filter it when it's opened.
Well, fixed my combo box so it updates whenever a new customer is chosen.
However, it looks like it updates ALL fields instead of just one. Grrrr.
It looks like I didn't sync up my combo boxes correctly..... I have two at the bottom of my request form; one is for Machine (depending on the customer chosen), and one is for the CAM system running on that machine. I guess they're really not updating anything and are just serving as look-ups... how can I assign these machines and systems to the request screen?